LSI vs GSI comparison in DynamoDB - Trade-offs & Expert Analysis

Overview - LSI vs GSI comparison

What is it?

In DynamoDB, LSI (Local Secondary Index) and GSI (Global Secondary Index) are tools that help you find data faster by creating alternative ways to look up your information. An LSI uses the same partition key as the main table but allows a different sort key, while a GSI can use a completely different partition and sort key. Both indexes let you query your data in ways that the main table's keys don't support.

Why it matters

Without LSI and GSI, you would be limited to searching data only by the main keys, which can be slow or impossible for some queries. These indexes make your database flexible and efficient, saving time and computing power. Imagine trying to find a book in a library without a catalog; LSI and GSI are like different catalogs that help you find books by author, genre, or year.

Where it fits

Before learning about LSI and GSI, you should understand basic DynamoDB concepts like tables, partition keys, and sort keys. After mastering these indexes, you can explore advanced topics like query optimization, capacity planning, and data modeling strategies in DynamoDB.

Mental Model

Core Idea

LSI and GSI are special indexes in DynamoDB that let you search your data using different keys to speed up queries beyond the main table's keys.

Think of it like...

Think of a library where the main catalog sorts books by shelf and position (partition and sort key). An LSI is like a secondary catalog that uses the same shelf but sorts books by author instead of position. A GSI is like a completely separate catalog that organizes books by genre and publication year, independent of the shelf.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Main Table  │       │      LSI      │       │      GSI      │
│ Partition Key │──────▶│ Partition Key │       │ Partition Key │
│ Sort Key     │       │ Different Sort│       │ Different Key │
└───────────────┘       └───────────────┘       └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding DynamoDB Keys

Concept: Learn what partition keys and sort keys are in DynamoDB tables.

DynamoDB tables store data using a partition key to distribute data across servers. A sort key orders data within each partition. Together, they uniquely identify each item. For example, a table of orders might use 'CustomerID' as the partition key and 'OrderDate' as the sort key.

Result

You can uniquely find any item by specifying both keys.

Understanding keys is essential because LSI and GSI build on these concepts to enable flexible queries.

2

Foundation

What is an Index in DynamoDB?

Concept: Indexes provide alternative ways to query data besides the main keys.

An index is like a shortcut that lets you find data using different keys. Instead of scanning the whole table, you use an index to quickly locate items. DynamoDB supports two types: Local Secondary Index (LSI) and Global Secondary Index (GSI).

Result

Indexes speed up queries and allow searching by different keys.

Knowing what an index is helps you understand why LSI and GSI exist and how they improve performance.

3

Intermediate

Local Secondary Index (LSI) Explained

🤔Before reading on: Do you think LSI can have a different partition key than the main table? Commit to your answer.

Concept: LSI uses the same partition key as the main table but allows a different sort key.

An LSI shares the partition key with the main table but lets you define a new sort key. This means you can query items within the same partition but order or filter them differently. For example, if the main sort key is 'OrderDate', an LSI might use 'OrderAmount' to sort orders by price.

Result

You can query data by the same partition but with a different sorting or filtering method.

Understanding that LSI shares the partition key but changes the sort key explains its use for queries within a partition.

4

Intermediate

Global Secondary Index (GSI) Explained

🤔Before reading on: Can GSI have a completely different partition key from the main table? Commit to your answer.

Concept: GSI can have different partition and sort keys from the main table.

A GSI lets you create an index with its own partition and sort keys, independent of the main table. This means you can query data in ways the main table keys don't allow. For example, you might create a GSI to find orders by 'ProductID' and 'OrderDate' instead of 'CustomerID'.

Result

You can query data across all partitions using new keys.

Knowing that GSI can redefine both keys shows its power for flexible, wide-ranging queries.

5

Intermediate

Differences in Storage and Capacity

Concept: LSI and GSI differ in how they store data and consume capacity.

LSI shares the same partition key and storage as the main table, so it doesn't increase write capacity units much but has a limit of 5 LSIs per table. GSI stores data separately and consumes additional write capacity units because it replicates data. GSIs can be added or removed anytime, unlike LSIs which must be defined at table creation.

Result

You understand cost and flexibility differences between LSI and GSI.

Knowing storage and capacity differences helps you choose the right index for your needs and control costs.

6

Advanced

Query Behavior and Consistency Differences

🤔Before reading on: Do you think queries on LSI and GSI both support strongly consistent reads? Commit to your answer.

Concept: LSI supports strongly consistent reads; GSI supports only eventually consistent reads.

When you query an LSI, you can request strongly consistent reads, meaning you get the latest data immediately. GSIs only support eventually consistent reads, which might return slightly outdated data shortly after a write. This affects how fresh your query results are.

Result

You can decide which index to use based on consistency needs.

Understanding consistency differences is crucial for applications where data freshness matters.

7

Expert

Choosing Between LSI and GSI in Production

🤔Before reading on: Would you choose LSI or GSI if you need to query by a new partition key after table creation? Commit to your answer.

Concept: LSI must be created with the table and shares partition key; GSI can be added later and has independent keys.

In production, if you need to query by a new partition key or add indexes after the table exists, you must use GSI. LSIs are limited to the table's partition key and must be defined when creating the table. Also, GSIs offer more flexibility but cost more in write capacity. Choosing depends on your query patterns, consistency needs, and cost constraints.

Result

You can design DynamoDB tables with the right index strategy for scalability and performance.

Knowing these trade-offs prevents costly redesigns and ensures your application scales efficiently.

Under the Hood

LSI works by creating an alternate sort key within the same partition, storing pointers to the original items without duplicating partition data. GSI creates a separate table-like structure that duplicates selected attributes and uses its own partition and sort keys, allowing independent scaling and query patterns. Both indexes maintain synchronization with the main table during writes, but GSIs replicate data asynchronously, causing eventual consistency.

Why designed this way?

LSI was designed to allow efficient queries within a partition without duplicating partition data, saving storage and write costs. GSI was introduced later to provide more flexible querying across partitions with different keys, accepting higher costs and eventual consistency as trade-offs. This design balances performance, cost, and flexibility for different use cases.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Main Table  │       │      LSI      │       │      GSI      │
│ Partition Key │──────▶│ Same Partition│       │ Different     │
│ Sort Key     │       │ Different Sort│       │ Partition Key │
│ Data Items   │       │ Index Points  │       │ Separate Data │
│              │       │ to Main Items │       │ Storage       │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can you add an LSI to an existing DynamoDB table anytime? Commit yes or no.

Common Belief: You can add or remove LSIs anytime after creating the table.

Reality: LSIs must be defined when the table is created and cannot be added or removed later.

Why it matters: Trying to add an LSI later leads to costly table recreation or redesign, causing downtime and data migration.

Quick: Do GSIs support strongly consistent reads? Commit yes or no.

Common Belief: GSIs support strongly consistent reads just like the main table.

Reality: GSIs only support eventually consistent reads, which may return stale data shortly after writes.

Why it matters: Assuming strong consistency on GSIs can cause bugs where your application reads outdated information.

Quick: Does an LSI have a different partition key than the main table? Commit yes or no.

Common Belief: LSIs can have different partition keys from the main table.

Reality: LSIs share the same partition key as the main table; only the sort key differs.

Why it matters: Misunderstanding this limits your ability to design correct queries and leads to inefficient data access.

Quick: Do GSIs consume the same write capacity units as the main table? Commit yes or no.

Common Belief: GSIs do not affect write capacity units because they are just indexes.

Reality: GSIs consume additional write capacity units because they replicate data separately.

Why it matters: Ignoring GSI write costs can lead to unexpected billing and throttling in production.

Expert Zone

1

LSIs are limited to 5 per table, so careful planning is needed to avoid hitting this limit.

2

GSIs can be projected with only selected attributes to reduce storage and write costs, but this limits query flexibility.

3

The eventual consistency of GSIs can be mitigated by application logic or by using DynamoDB Streams for real-time updates.

When NOT to use

Avoid LSIs if you need to add indexes after table creation or require different partition keys; use GSIs instead. Avoid GSIs if your application requires strongly consistent reads or if write capacity costs are prohibitive; consider redesigning your data model or using LSI where possible.

Production Patterns

In production, GSIs are commonly used for flexible querying by different keys and to support multiple access patterns. LSIs are used when queries need strong consistency within the same partition and when the sort key needs to vary. Many systems combine both to balance cost, performance, and query flexibility.

Connections

Database Indexing

LSI and GSI are specific types of database indexes tailored for DynamoDB's architecture.

Understanding general database indexing concepts helps grasp why LSI and GSI improve query speed and how they differ from traditional indexes.

Eventual Consistency in Distributed Systems

GSI's eventual consistency reflects a common pattern in distributed databases to balance availability and performance.

Knowing eventual consistency principles clarifies why GSI queries might return stale data and how to design applications accordingly.

Library Catalog Systems

Like different catalogs organizing books by various attributes, LSI and GSI organize data for different query needs.

Recognizing this connection helps appreciate the purpose of multiple indexes in organizing and retrieving data efficiently.

Common Pitfalls

#1 Trying to add an LSI after table creation.

Wrong approach:

aws dynamodb update-table --table-name Orders --attribute-definitions AttributeName=NewSortKey,AttributeType=S --local-secondary-indexes '[{"IndexName":"NewLSI","KeySchema":[{"AttributeName":"PartitionKey","KeyType":"HASH"},{"AttributeName":"NewSortKey","KeyType":"RANGE"}],"Projection":{"ProjectionType":"ALL"}}]'

Correct approach:

Define all LSIs during the initial table creation using aws dynamodb create-table with LocalSecondaryIndexes parameter.

Root cause:

Misunderstanding that LSIs are fixed at table creation and cannot be modified later.

#2 Assuming GSI queries return strongly consistent data.

Wrong approach:

Querying a GSI with ConsistentRead=true expecting up-to-date data.

Correct approach:

Query GSI with ConsistentRead=false (default) and design application to handle eventual consistency.

Root cause:

Confusing consistency guarantees between main table and GSIs.

#3 Using different partition keys in LSI definition.

Wrong approach:

Creating an LSI with a partition key different from the main table's partition key.

Correct approach:

Ensure LSI uses the same partition key as the main table; only the sort key can differ.

Root cause:

Not knowing LSI's partition key must match the main table's partition key.

Key Takeaways

LSI and GSI are powerful DynamoDB indexes that enable flexible and efficient queries beyond the main table keys.

LSI shares the partition key with the main table but allows a different sort key, while GSI can have completely different partition and sort keys.

LSIs must be defined when creating the table and support strongly consistent reads; GSIs can be added later but only support eventually consistent reads.

Choosing between LSI and GSI depends on your query patterns, consistency needs, and cost considerations.

Understanding these indexes deeply helps design scalable, performant, and cost-effective DynamoDB applications.