DEV Community: Prashanth Mangipudi

Sparse & Dense Indexes in DBMS

Prashanth Mangipudi — Tue, 20 May 2025 14:45:04 +0000

Database Indexes are most crucial for efficient query performance, as discussed in my previous post,Understanding Database Indexes: A Library Analogy

Continuing with the library analogy, let's explore two types of indexes: dense and sparse, focusing on the book_title column as a unique key.
Here is the table from the previous post.

Book Title	Stream	Section	Rack	Position
Database Internals	Engineering	A	5	2
SQL Performance Tuning	Engineering	A	4	1
NoSQL Databases: A Practical Guide	Engineering	A	6	3
Quantum Circuits	Engineering	A	3	4
Regenerative Medicine	Medical	B	2	5

Dense Indexing

In dense indexing , there's an entry for every record in the table. Imagine a library catalog listing every book with its exact location (e.g., Section A, Rack 5, Position 2). The index table stores the key (book_title) and a record pointer- a reference to the record's location on disk.

Caption: A catalog listing every book in the library, with each entry pointing to its exact location.

Pros

Fast Lookups: Direct access to every record makes queries like SELECT * FROM books where book_title='Database Internals'; extremely quick.
Ideal for Random Access: Perfect for frequent lookups on unique keys, such as product_id in an e-commerce database. ### Cons
High Storage Overhead: As the table grows, the index table grows proportionally, consuming significant disk space.
Slow Write Operations: Insertion, deletions and updates require modifying both the main table and the index table, increasing memory and time costs.

Sparse Indexing

In sparse indexing , the index table stores entries for only a subset of records-typically one per data block (a group of records stored together on disk). Think of a library catalog listing only the first book on each shelf, requiring you to search within the shelf to find the exact book.

Caption: A catalog listing only the first book on each shelf, requiring a search within the shelf to find the exact book.

How does Sparse index work?

Let's look up Regenerative Medicine. The sparse index has entries for Quantum Circuits and SQL Performance Tuning. Since Regenerative Medicine falls alphabetically between these, the database starts at the block pointed to by Quantum Circuits and performs a sequential scan within that block and find a record.

Note: Sparse indexes work effectively when the data is ordered (e.g., in a clustered index or a B-tree), as this ensures records within blocks are predictable.

Pros

Low Storage Overhead: Indexes only a subset of records, making it space-efficient for large database.
Efficient for Range Queries: Ideal for queries SELECT * FROM books WHERE book_title BETWEEN 'Quantum Circuits' AND 'SQL Performance Tuning'; as it scans fewer index entries.
Lower Maintainance: Insertions and deletions have less impact since the index table updates less frequently.

Cons

Slower Lookups for Random Access: Requires scanning within a block to find the exact record, making it slower than dense indexes for specific lookups.
Requires Ordered Data: Sparse indexes are most effective when the data is ordered, which may not always be the case.

Conclusion

Dense and sparse indexes offer distinct trade-offs in DBMS. Dense indexes excel in random lookups but consume more space, while sparse indexes save storage and optimize range queries.

Understanding Database Indexes: A Library Analogy

Prashanth Mangipudi — Sat, 17 May 2025 13:14:11 +0000

Imagine searching for a book on databases in a massive library with thousands of books. Without organization, finding that book could take hours. This is where indexes come in, both in libraries and databases. In this post, we’ll use a library analogy to explain why indexes are essential for efficient data retrieval in relational databases, focusing on books about databases.

Scene 0: No Organization

Picture a library where thousands of books are stacked randomly—no labeled sections, no catalog. To find a book like Database Internals, you’d need to check the title of nearly every book. This is slow and inefficient, much like searching a database without an index.

In a relational database, the library is the database, each book is a row in a table, and the table might look like this:

Book Title
Database Internals
SQL Performance Tuning
NoSQL Databases: A Practical Guideाण
Quantum Circuits
Regenerative Medicine

Without any structure, the database must perform a full table scan, checking every row to find a specific book. This is computationally expensive, especially for large tables.

Scene 1: Basic Organization

Now, the library has labeled sections, such as Engineering, Medical, and Psychology. The librarian also maintains a catalog (an index) that lists books by their Stream, a category like Engineering. When you ask for database books, the librarian checks the catalog and directs you to the Engineering section, narrowing your search to a smaller subset of books.

In a database, this is like adding a column to categorize rows and creating an index on that column. Here’s how the table might look:

Book Title	Stream	Section
Database Internals	Engineering	A
SQL Performance Tuning	Engineering	A
NoSQL Databases: A Practical Guide	Engineering	A
Quantum Circuits	Engineering	A
Regenerative Medicine	Medical	B

The database creates an index on the Stream column—a separate data structure (often a B-tree) that maps Stream values to row locations. Querying for Stream = 'Engineering' is now much faster because the database uses the index to skip irrelevant rows, avoiding a full table scan.

Scene 2: Full Indexing

The library takes organization further. The librarian maintains a detailed catalog that pinpoints each book’s exact location: section, rack, and position. When you ask, “Where’s Database Internals by Author?”, the librarian replies, “Section A, Rack 5, Position 2.” You walk straight to the book, saving time.

In a database, this is like creating indexes on multiple columns, such as Book Title or Stream. The table might include location details for clarity, but the index is a separate structure:

Book Title	Stream	Section	Rack	Position
Database Internals	Engineering	A	5	2
SQL Performance Tuning	Engineering	A	4	1
NoSQL Databases: A Practical Guide	Engineering	A	6	3
Quantum Circuits	Engineering	A	3	4
Regenerative Medicine	Medical	B	2	5

An index on Book Title allows the database to quickly locate a specific row, like finding Database Internals in milliseconds. This is the power of indexing: transforming a slow search across thousands of rows into a fast, targeted lookup.

Note: The Section, Rack, and Position columns are for illustration. In a real database, the index itself stores pointers to rows, not physical locations.

How Indexes Work in Databases

A database index is a data structure that speeds up queries by mapping column values to row locations. Most indexes use a B-tree, which organizes data for rapid lookups, similar to a phonebook. For example, an index on Book Title lets the database find SQL Performance Tuning without scanning the entire table.

Common index types include:

Primary Key Index: Ensures unique values (e.g., a book’s ISBN).
Unique Index: Enforces uniqueness (e.g., a book’s title in a simplified system).
Composite Index: Indexes multiple columns (e.g., Stream and Book Title) for complex queries.

However, indexes have trade-offs:

Faster Reads: Queries run faster because the database uses the index.
Slower Writes: Inserting or updating rows requires updating the index, adding overhead.
Storage Overhead: Indexes consume additional disk space.

What Happens Without an Index?

Without an index, the database performs a full table scan, checking every row for a match. Imagine a table with 1 million customer records. Querying for a customer by email (e.g., email = 'jane@example.com') could take seconds or minutes. With an index on the email column, the database locates the row in milliseconds, vastly improving performance.

For example, in our library table, searching for Database Internals without an index requires checking every row. An index on Book Title makes the search near-instantaneous.

Conclusion

Indexes are the backbone of efficient database queries, acting like a library’s catalog to transform slow searches into fast lookups. By organizing data in structures like B-trees, indexes save time and resources, especially for large datasets. To dive deeper, explore index types or query optimization in resources like Database System Concepts by Silberschatz et al. or online courses on platforms like Coursera.

Next time you query a database, think of the library catalog guiding you to the perfect book—database indexes work the same magic!

Using Authorization Mechanisms and API Keys in AWS API Gateway

Prashanth Mangipudi — Thu, 25 Jul 2024 13:02:04 +0000

before starting, a small introduction on what Amazon API gateway does.

Amazon API gateway provides a robust mechanisms to create, maintain and monitor HTTP, REST, WebSocket API's(Application Programming Interface) at any Scale.

This article is intended to explain on how to use authorization mechanisms like Lambda authorizers, Cognito Pool and IAM roles for authentication and fine-grained authorization. In addition to that, the usage of API keys for access control, including rate limiting and throttling are covered.

Lambda Authorizers for Authentication & Authorization

Lambda authorizers, formerly known as custom authorizers, control access to your API's. When a client makes a request to your API's method, API Gateway calls your lambda authorizer, which takes the caller's identity as input and returns an IAM policy as output.

The Lambda authorizer's authorization workflow is as follows:

The client calls a method on an API Gateway API, passing a bearer token or request parameters.
API Gateway checks if the method request is configured with a Lambda authorizer. If it is, API Gateway calls the Lambda function. The Lambda function authenticates the caller. The function can authenticate in several ways, such as by calling out to an OAuth provider to get an OAuth access token, by calling out to a SAML provider to get a SAML assertion, by generating an IAM policy based on the request parameter values, or by retrieving credentials from a database.
The Lambda function returns an IAM policy and a principal identifier. If the Lambda function does not return that information, the call fails¹.
API Gateway evaluates the IAM policy. If access is denied, API Gateway returns a suitable HTTP status code, such as 403 ACCESS_DENIED. If access is allowed, API Gateway invokes the method.

API Keys for Access Control

API keys are used in the REST API's to control access and can be used alongside usage plans to implement tracking and throttling. They can be directly generated within API Gateway or can be imported from external sources, if custom key needs to be enforced.

A usage plan specifies who can access one or more deployed API stages and methods and optionally sets the target request rate to start throttling requests. Multiple API keys can be associated with a usage plan.
Quota Limit : 10,000 keys/AWS Region/ Account
To use API keys alongside of Lambda Authorizer, an additional header needs to be passed, X-API-KEY is the key.

Example Scenario

An Industrial IoT sensors organization is collecting huge amounts of data from the RTUs(Real Time Unit) and storing the data in the database. Assuming there is already an application which shows the data, the clients would want to have the flow of that real time/historical data to their Analytics Systems.
For instance let say that there is no access-control mechanism like rate limiting and all the clients will be requesting for the data using the APIs. Now, you don't want the premium clients to wait for long time, as multiple reads are happening on the database table.
Instead, limit the request based on the usage plan allowing the premium customers send more number of requests/second without any delay.

Video Explanation Link - Watch this space.

Conclusion

Use authorization mechanisms (e.g., Lambda authorizers, IAM roles) for authentication and fine-grained authorization.
Use API keys for access control (e.g., rate limiting, throttling).

Resources

Use API Gateway Lambda authorizers - Amazon API Gateway. https://docs.aws.amazon.com/apigateway/latest/developerguide/apigateway-use-lambda-authorizer.html.
Usage plans and API keys for REST APIs in API Gateway. https://docs.aws.amazon.com/apigateway/latest/developerguide/api-gateway-api-usage-plans.html.
Best Practices for API keys and Usage Plans https://docs.aws.amazon.com/apigateway/latest/developerguide/api-gateway-api-usage-plans.html#apigateway-usage-plans-best-practices

AWS Lambda Function Explained!!

Prashanth Mangipudi — Thu, 20 Jul 2023 17:09:02 +0000

AWS Lambda is a AWS Serverless service that lets you run code without actually thinking about the servers. In this post, the core features and configurational abilities are discussed.

Timeout

A lambda function can run from 3 seconds (default) to 15 minutes or 900 seconds. When considering lambda function during the design, the use case should align with this constraint. After 15 minutes, the lambda execution environment gets terminated.

Runtime Environment

Runtime Environment is the actual environment in which the code is supposed to be executed. Lambda supports a wide range of runtime environments including Python, Java, Nodejs, .NET, Ruby, go, C# and various others. In the chosen environment, only the default packages are included. If your script requires any other third-party libraries, then either those dependencies should be included as a part of deployment or a lambda layer can be utilized.

Layers

A lambda layer is a ZIP archive or the collection of dependencies needed for a lambda function to run. With the help of these layers, the dependencies and the actual business code can be isolated. Instead of including all the dependencies as a part of each lambda function, the lambda layer can be utilized.

Versioning

A lambda function once published can be versioned and aliased. This provides the flexibility to either roll back to a working version or point different services to unique versions if required.

Integrations

Lambda function can either be triggered manually to perform the task or ideally the lambda function is configured to a trigger event. If the result needs to be further processed then a destination service can also be configured. Lambda almost provides integration with most of the AWS Services.

Monitoring

The lambda executions can be monitored by using AWS CloudWatch Logs, AWS X-ray Services. By default, the lambda function creates a CloudWatch log group and start logging the execution logs. Other monitoring services needs to be explicitly configured and the necessary permissions has to be added to the lambda execution policy.

Concurrency

Lambda functions follow asynchronous invocation. So, the same lambda function can be triggered by multiple services and each request is handled in an isolated environment. An individual lambda can also be restricted to a certain number of concurrent lambda execution. The default concurrency limit for lambda functions is 1000 per account, this can be further increased if required.

Cold Starts

Each time a lambda function is triggered, there will be some delay before handling the actual request. Lambda function maybe a serverless service for us, but it internally runs on a server and that server's runtime environment needs to be setup as per the configured runtime. The delay is caused due to this setup and this delay is termed as Cold Start.

Provisioned Concurrency

To overcome the problem of Cold Starts, Provisioned Concurrency can be used. It the way of provisioning a certain number of lambda function ready even before the request. These x number of lambda functions will have the runtime setup configured already and start processing the request right after its arrival. This needs to configured mindfully as it might cost you a fortune if not done correctly. Since the lambda functions are being reserved, there will be an additional overhead cost for the duration of lambda functions during which they are awake.

Use cases

File Processing
Web Application Backend
IoT Backends
Stream Processing
Transformation Layer in ETL process

An Introduction to Cloud Computing

Prashanth Mangipudi — Tue, 18 Jul 2023 05:48:12 +0000

Cloud computing has revolutionized the world of technology, offering unprecedented scalability, flexibility, and cost-effectiveness. Whether you are seasoned IT professional or a tech enthusiast, understanding the foundational concepts of cloud computing is crucial for harnessing the full potential of this transformative technology. In this post, we will delve into the fundamental cloud concepts, providing a comprehensive overview of what cloud computing entails, its key components and essential benefits.

What is Cloud Computing?

At its core, cloud computing refers to the delivery of on-demand computing resources, including servers, storage, databases, networking, software, and more, over the internet. Instead of owning and maintaining physical infrastructure, cloud computing allows individuals and businesses to access and utilize there resources through a pay-as-you-go model.

Key Components of Cloud Computing

Virtualization

Virtualization is the fundamental technology that enables cloud computing. It involves creating virtual versions of computing resources, such as servers, storage, and networking devices. This allows for resource allocation and management without being tied to the physical hardware, leading to greater efficiency and flexibility.

Elasticity and Scalability

One of the significant advantages of cloud computing is its ability to scale resources dynamically based on the demand. Elasticity allows the system to automatically provision and de-provision the resources to match workload fluctuations, ensuring optimal performance and cost efficiency.

Multi-tenancy

Cloud providers operate on a multi-tenant mode, which means that a single physical resource can server multiple customers simultaneously. This efficient resource sharing contributes to lower costs for users and higher resource utilization for providers.

Cloud Deployment models

Public Cloud

In a public cloud, cloud services are offered over the public internet by third-party providers. These providers own and manage the infrastructure, making it a cost-effective option for startups, businesses and individuals. Popular public cloud providers include Amazon Web Services(AWS), Microsoft Azure and Google Cloud Platform(GCP).

Private Cloud

A private cloud is exclusively dedicated to a single organization and can be hosted either on-premises or by a third-party provider. This model ensures heightened security, control, and customization, making it suitable for organizations with strict compliance requirements or specialized workloads.

Hybrid Cloud

Combining elements of both public and private clouds, the hybrid cloud allows seamless movement of data and applications between two environments. It offers the flexibility of utilizing public cloud resources for non-sensitive tasks safeguarding critical data and applications in a private cloud setting.

Community Cloud

The community cloud serves multiple organizations with shared interests, such as industry-specific compliance requirements. This model enables cost-sharing among community members while addressing their unique needs.

Essential Benefits

Cost Savings

Cloud computing eliminates significant upfront capital expenditure on hardware and reduces ongoing maintenance costs. Users only pay for the resources they consumed, resulting in optimized spending.

Flexibility and Agility

Cloud services can be rapidly provisioned and scaled, empowering businesses to respond quickly to evolving demands and market conditions.

Reliability and High Availability

Cloud providers often offer robust infrastructure with redundant components, ensuring high availability and reliability for critical applications and data.

Security and Compliance

Cloud providers invest heavily in security measures and compliance certifications, often surpassing what individual organizations can achieve on their own.

Global Reach

Cloud Computing enables businesses to expand their reach globally by deploying applications and services in data centers worldwide, reducing latency and enhancing user experience.

Conclusion

A solid grasp of foundational cloud concepts lays the groundwork for tapping into the full potential of cloud computing. Businesses and individuals can strategically choose the right cloud approach to suit their unique requirements. As cloud technology continues to evolve, staying familiar with these fundamental concepts empowers you to make well-informed decisions and leverage cloud's potential to drive innovation and prosperity in the digital era.