Why secondary indexes enable flexible queries in DynamoDB - Why It Works This Way

Overview - Why secondary indexes enable flexible queries

What is it?

Secondary indexes in DynamoDB are special data structures that let you look up data in different ways than the main table's key. They allow you to query the database using attributes other than the primary key. This means you can ask more flexible questions about your data without scanning the whole table. Secondary indexes come in two types: Global Secondary Indexes and Local Secondary Indexes.

Why it matters

Without secondary indexes, you can only efficiently find data by the main key, which limits how you can search. This would force you to scan the entire table for many queries, which is slow and costly. Secondary indexes let you quickly find data using other attributes, making your app faster and more responsive. They solve the problem of flexible searching in large datasets.

Where it fits

Before learning about secondary indexes, you should understand DynamoDB tables, primary keys, and basic queries. After this, you can learn about query optimization, data modeling strategies, and how to design scalable applications using indexes.

Mental Model

Core Idea

Secondary indexes create alternative paths to find data quickly using different keys than the main table's primary key.

Think of it like...

Imagine a library where books are arranged by author name (primary key). A secondary index is like adding a separate shelf organized by genre or publication year, so you can find books by those categories without searching the whole library.

Main Table (Primary Key)
┌───────────────┐
│ PK: UserID    │
│ Attributes... │
└───────────────┘
       │
       ▼
Secondary Indexes
┌─────────────────────────┐   ┌─────────────────────────┐
│ Global Secondary Index   │   │ Local Secondary Index    │
│ (Different PK & SK)     │   │ (Same PK, Different SK) │
└─────────────────────────┘   └─────────────────────────┘
       │                             │
       ▼                             ▼
Query by alternate keys       Query by alternate sort keys

Build-Up - 7 Steps

1

Foundation

Understanding Primary Keys in DynamoDB

Concept: Primary keys uniquely identify each item in a DynamoDB table and determine how data is stored and retrieved.

In DynamoDB, every table must have a primary key. This key can be a simple partition key or a composite key with a partition key and sort key. The primary key is how DynamoDB organizes data internally, so queries using the primary key are very fast. For example, if your table's primary key is UserID, you can quickly find a user's data by their UserID.

Result

You can efficiently retrieve items by their primary key without scanning the whole table.

Understanding primary keys is essential because they define the main way DynamoDB stores and accesses data, setting the foundation for why secondary indexes are needed.

2

Foundation

Limitations of Queries Without Secondary Indexes

Concept: Queries without secondary indexes can only use the primary key, limiting how you can search your data.

If you want to find items based on attributes other than the primary key, DynamoDB forces you to scan the entire table. For example, if you want to find all users from a certain city but the city is not part of the primary key, you must scan every item, which is slow and expensive. This limitation shows why more flexible query options are necessary.

Result

Queries on non-key attributes require full table scans, causing slow performance and higher costs.

Knowing this limitation highlights the need for secondary indexes to enable efficient queries on different attributes.

3

Intermediate

What Are Secondary Indexes in DynamoDB?

Concept: Secondary indexes are additional data structures that let you query the table using different keys than the primary key.

DynamoDB supports two types of secondary indexes: Global Secondary Indexes (GSI) and Local Secondary Indexes (LSI). GSIs let you define a completely different partition key and sort key, while LSIs share the same partition key but have a different sort key. These indexes maintain copies of selected attributes to support fast queries on alternate keys.

Result

You can query data efficiently using alternate keys defined in secondary indexes.

Understanding the types and structure of secondary indexes is key to designing flexible queries and optimizing data access.

4

Intermediate

How Global Secondary Indexes Enable Flexibility

🤔Before reading on: do you think a Global Secondary Index can have a completely different partition key than the main table? Commit to yes or no.

Concept: Global Secondary Indexes allow queries on any attribute(s) by defining new partition and sort keys independent of the main table's keys.

A GSI is like a new table that DynamoDB maintains automatically. It can have a different partition key and sort key from the main table. For example, if your main table uses UserID as the partition key, a GSI might use Email as the partition key. This lets you query users by email quickly without scanning the main table.

Result

Queries on the GSI return results fast using the alternate keys, enabling flexible access patterns.

Knowing that GSIs act like separate tables with their own keys explains how DynamoDB supports multiple query patterns efficiently.

5

Intermediate

Role of Local Secondary Indexes in Querying

🤔Before reading on: do you think Local Secondary Indexes can change the partition key of the main table? Commit to yes or no.

Concept: Local Secondary Indexes let you query data using the same partition key but a different sort key, enabling more detailed queries within a partition.

An LSI shares the partition key with the main table but allows a different sort key. For example, if your main table's key is UserID (partition) and Timestamp (sort), an LSI might use UserID and OrderStatus as the sort key. This lets you find all orders for a user filtered or sorted by status efficiently.

Result

Queries on LSIs provide flexible sorting and filtering within the same partition key.

Understanding LSIs helps you design queries that need multiple ways to sort or filter data within a group.

6

Advanced

Consistency and Performance Trade-offs with Indexes

🤔Before reading on: do you think queries on secondary indexes are always strongly consistent? Commit to yes or no.

Concept: Secondary indexes have different consistency and performance characteristics than the main table, affecting query results and speed.

Queries on the main table can be strongly consistent, meaning they always reflect the latest data. However, GSIs only support eventually consistent reads, which might lag behind recent writes. LSIs support strong consistency but have size limits. Also, maintaining indexes adds write overhead. Understanding these trade-offs helps balance query flexibility with performance and cost.

Result

You get flexible queries but must manage consistency and performance trade-offs.

Knowing these trade-offs prevents surprises in production and guides better index design.

7

Expert

How DynamoDB Maintains Secondary Indexes Internally

🤔Before reading on: do you think DynamoDB updates secondary indexes synchronously with the main table writes? Commit to yes or no.

Concept: DynamoDB updates secondary indexes asynchronously and maintains copies of indexed attributes to support fast queries.

When you write to the main table, DynamoDB asynchronously updates GSIs to avoid slowing down writes. This means GSIs might briefly lag behind. LSIs are updated synchronously but have size limits. Each index stores copies of the attributes it indexes, which increases storage and write costs. This internal mechanism balances write speed with query flexibility.

Result

Secondary indexes provide fast queries but may have slight delays or increased costs due to internal update mechanisms.

Understanding the internal update process explains why some queries might see stale data and why indexes affect write performance.

Under the Hood

DynamoDB stores secondary indexes as separate tables internally. For GSIs, updates happen asynchronously after the main table write succeeds, copying the indexed attributes and keys. LSIs are stored with the main table data and updated synchronously. This design allows fast queries on alternate keys but can cause eventual consistency for GSIs. The indexes maintain their own partition and sort keys, enabling efficient lookups without scanning the main table.

Why designed this way?

DynamoDB was designed for high scalability and low latency. Asynchronous updates for GSIs prevent write operations from slowing down due to index maintenance. LSIs provide strong consistency but are limited in size to keep performance predictable. This balance allows flexible queries without sacrificing the core speed and scalability of DynamoDB.

┌───────────────┐      Write      ┌───────────────┐
│ Main Table    │───────────────▶│ DynamoDB Core │
│ (Primary Key) │                └───────────────┘
└───────────────┘                      │
       │                              Async
       │ Sync                          Update
       ▼                                ▼
┌───────────────┐                ┌───────────────┐
│ Local Secondary│◀─────────────│ Global Secondary│
│ Index (LSI)   │  Sync Update  │ Index (GSI)    │
└───────────────┘                └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think queries on Global Secondary Indexes are strongly consistent by default? Commit to yes or no.

Common Belief: Queries on all secondary indexes are strongly consistent just like the main table.

Reality: Global Secondary Indexes only support eventually consistent reads, which may not reflect the latest writes immediately.

Why it matters: Assuming strong consistency can cause your application to read stale data unexpectedly, leading to incorrect behavior or user confusion.

Quick: Do you think Local Secondary Indexes can have a different partition key than the main table? Commit to yes or no.

Common Belief: Local Secondary Indexes can change both partition and sort keys to enable flexible queries.

Reality: Local Secondary Indexes share the same partition key as the main table but allow a different sort key only.

Why it matters: Misunderstanding this limits your ability to design correct LSIs and may cause inefficient queries or errors.

Quick: Do you think adding many secondary indexes has no impact on write performance? Commit to yes or no.

Common Belief: Secondary indexes do not affect write speed because they are separate structures.

Reality: Each secondary index adds overhead to write operations because DynamoDB must update the index data, which can slow down writes and increase costs.

Why it matters: Ignoring this can lead to unexpected latency and higher bills in production systems.

Quick: Do you think you can query any attribute without defining a secondary index? Commit to yes or no.

Common Belief: You can query any attribute in DynamoDB efficiently without indexes by filtering after scanning.

Reality: Filtering after scanning reads the entire table, which is slow and expensive. Efficient queries require indexes on the attributes you want to query.

Why it matters: Relying on scans for flexible queries causes poor performance and high costs at scale.

Expert Zone

1

Global Secondary Indexes are eventually consistent by design to optimize write throughput, but you can design your application logic to handle this delay gracefully.

2

Local Secondary Indexes have a 10 GB size limit per partition key, which can cause unexpected errors if exceeded, so careful data modeling is required.

3

Choosing which attributes to project into an index affects both query performance and storage costs; projecting only needed attributes is a subtle but important optimization.

When NOT to use

Secondary indexes are not suitable when your access patterns are simple and always based on the primary key, or when write throughput is extremely high and cannot tolerate the overhead. In such cases, consider denormalizing data or using caching layers instead.

Production Patterns

In production, developers often use GSIs to support multiple query patterns like searching by email or status, while LSIs enable sorting within user partitions. They also carefully monitor index usage and costs, and design for eventual consistency by using timestamps or versioning.

Connections

Database Normalization

Secondary indexes provide alternative access paths similar to how normalization organizes data to reduce redundancy.

Understanding normalization helps appreciate why secondary indexes avoid duplicating entire tables but still enable flexible queries.

Caching Systems

Secondary indexes and caches both improve query speed but operate differently; indexes are part of the database, caches are external layers.

Knowing the difference helps design systems that balance fast reads with data freshness and consistency.

Library Cataloging Systems

Like secondary indexes, library catalogs organize books by multiple criteria (author, genre, year) to enable flexible searching.

Recognizing this connection clarifies how multiple indexes serve different query needs efficiently.

Common Pitfalls

#1 Trying to query a non-key attribute without a secondary index.

Wrong approach:

SELECT * FROM Users WHERE City = 'Seattle'; -- Without a secondary index on City, this causes a full table scan

Correct approach:

Create a Global Secondary Index on City, then query: SELECT * FROM Users.CityIndex WHERE City = 'Seattle';

Root cause:

Misunderstanding that DynamoDB requires indexes on attributes used in queries for efficient access.

#2 Expecting strong consistency on Global Secondary Index queries.

Wrong approach:

Querying a GSI with ConsistentRead = true expecting up-to-date data (which is not supported)

Correct approach:

Use eventually consistent reads on GSIs and design application logic to handle slight delays.

Root cause:

Confusing consistency guarantees between main table and GSIs.

#3 Adding too many secondary indexes without considering write cost impact.

Wrong approach:

Creating multiple GSIs on a high-write table without monitoring performance or cost.

Correct approach:

Limit indexes to necessary ones and monitor write capacity and costs carefully.

Root cause:

Underestimating the write overhead and cost implications of maintaining multiple indexes.

Key Takeaways

Secondary indexes let you query DynamoDB tables using different keys than the primary key, enabling flexible data access.

Global Secondary Indexes allow completely different partition and sort keys, while Local Secondary Indexes share the partition key but have a different sort key.

Using secondary indexes improves query speed but introduces trade-offs in consistency, write performance, and cost.

Understanding how DynamoDB maintains indexes internally helps design applications that handle eventual consistency and optimize performance.

Misusing or misunderstanding secondary indexes can lead to slow queries, stale data, or high costs, so careful design and monitoring are essential.