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.

Practice

1. Which statement correctly describes the difference between a Local Secondary Index (LSI) and a Global Secondary Index (GSI) in DynamoDB?

easy

A. LSI uses the same partition key but a different sort key; GSI uses different partition and sort keys.

B. LSI uses different partition keys; GSI uses the same partition key but different sort keys.

C. LSI and GSI both use the same partition and sort keys but differ in storage location.

D. LSI uses different partition and sort keys; GSI uses the same partition key and sort key.

Solution

1. Step 1: Analyze LSI key schema

   LSI shares the same partition key as the base table but allows a different sort key for alternate sorting within that partition.

2. Step 2: Analyze GSI key schema

   GSI allows both partition key and sort key to be different from the base table, enabling flexible query patterns across partitions.

3. Final Answer:

   LSI uses the same partition key but a different sort key; GSI uses different partition and sort keys. -> Option A

4. Quick Check:

   LSI = same partition key, different sort key; GSI = different keys [OK]

Hint: LSI = same partition key, GSI = different partition key [OK]

Common Mistakes:

• Confusing which keys LSI and GSI use
• Thinking LSI can have different partition keys
• Assuming GSI uses the same keys as the base table

2. Which of the following is the correct syntax to define a Global Secondary Index (GSI) in a DynamoDB table creation JSON?

easy

A. {
"IndexName": "LSI1",
"KeySchema": [
{"AttributeName": "UserId", "KeyType": "HASH"},
{"AttributeName": "OrderDate", "KeyType": "RANGE"}
],
"Projection": {"ProjectionType": "INCLUDE"}
}

B. {
"IndexName": "GSI1",
"KeySchema": [
{"AttributeName": "UserId", "KeyType": "HASH"},
{"AttributeName": "OrderDate", "KeyType": "RANGE"}
],
"Projection": {"ProjectionType": "ALL"}
}

C. {
"IndexName": "GSI1",
"KeySchema": [
{"AttributeName": "OrderDate", "KeyType": "RANGE"}
],
"Projection": {"ProjectionType": "INCLUDE", "NonKeyAttributes": ["Status"]}
}

D. {
"IndexName": "GSI1",
"KeySchema": [
{"AttributeName": "UserId", "KeyType": "RANGE"},
{"AttributeName": "OrderDate", "KeyType": "HASH"}
],
"Projection": {"ProjectionType": "ALL"}
}

Solution

1. Step 1: Check key schema order for GSI

   GSI requires a partition key (HASH) and optionally a sort key (RANGE). The partition key must come first in the KeySchema array.

2. Step 2: Validate attribute roles and projection

   { "IndexName": "GSI1", "KeySchema": [ {"AttributeName": "UserId", "KeyType": "HASH"}, {"AttributeName": "OrderDate", "KeyType": "RANGE"} ], "Projection": {"ProjectionType": "ALL"} } correctly defines UserId as HASH and OrderDate as RANGE with ProjectionType ALL, which is valid for GSI.

3. Final Answer:

   {\n \"IndexName\": \"GSI1\",\n \"KeySchema\": [\n {\"AttributeName\": \"UserId\", \"KeyType\": \"HASH\"},\n {\"AttributeName\": \"OrderDate\", \"KeyType\": \"RANGE\"}\n ],\n \"Projection\": {\"ProjectionType\": \"ALL\"}\n} -> Option B

4. Quick Check:

   GSI KeySchema starts with HASH key [OK]

Hint: GSI KeySchema starts with HASH key, then RANGE key [OK]

Common Mistakes:

• Swapping HASH and RANGE key types
• Using LSI name for GSI definition
• Missing required HASH key in KeySchema

3. Given a DynamoDB table with a partition key 'UserId' and sort key 'Timestamp', and an LSI defined with the same partition key 'UserId' but sort key 'OrderStatus', what will be the result of this query?

Query on LSI where UserId = '123' and OrderStatus = 'Pending'

medium

A. Returns all items with UserId '123' and Timestamp 'Pending'.

B. Query fails because LSI cannot be queried on OrderStatus.

C. Returns all items with UserId '123' regardless of OrderStatus.

D. Returns all items with UserId '123' and OrderStatus 'Pending' sorted by OrderStatus.

Solution

1. Step 1: Recall LSI query keys

   LSI uses the same partition key as the base table but allows querying by the alternate sort key, here 'OrderStatus'.

2. Step 2: Analyze query conditions

   The query filters on UserId = '123' (partition key) and OrderStatus = 'Pending' (LSI sort key), so it returns matching items sorted by OrderStatus.

3. Final Answer:

   Returns all items with UserId '123' and OrderStatus 'Pending' sorted by OrderStatus. -> Option D

4. Quick Check:

   LSI query uses same partition key + LSI sort key [OK]

Hint: LSI queries use same partition key, different sort key [OK]

Common Mistakes:

• Confusing sort key names between base and LSI
• Assuming LSI cannot be queried on alternate sort key
• Thinking query returns all UserId items ignoring sort key

4. You defined a GSI with partition key 'Category' and sort key 'Price'. Your query on the GSI with KeyConditionExpression 'Category = :cat and Price < :maxPrice' returns no results, but you know matching items exist. What is the most likely cause?

medium

A. The GSI was not properly populated because the base table items lack the 'Category' or 'Price' attributes.

B. The query syntax is invalid because GSI queries cannot use sort key conditions.

C. LSI should be used instead of GSI for this query pattern.

D. The base table partition key must be included in the query condition.

Solution

1. Step 1: Check attribute presence for GSI indexing

   GSI indexes only items that have the specified partition and sort key attributes. Missing attributes mean no index entry.

2. Step 2: Understand query behavior on GSI

   If items lack 'Category' or 'Price', they won't appear in the GSI, causing queries to return no results despite existing base table items.

3. Final Answer:

   The GSI was not properly populated because the base table items lack the 'Category' or 'Price' attributes. -> Option A

4. Quick Check:

   Missing GSI keys in items = no query results [OK]

Hint: GSI needs all key attributes in items to index them [OK]

Common Mistakes:

• Assuming GSI queries can't filter on sort key
• Confusing LSI and GSI usage
• Thinking base table keys must be in GSI query

5. You have a DynamoDB table with partition key 'UserId' and sort key 'Timestamp'. You want to support queries to find all orders by 'UserId' sorted by 'Timestamp', and also queries to find all orders by 'Status' sorted by 'OrderDate'. Which index setup is best?

hard

A. Use a GSI with partition key 'UserId' and sort key 'Timestamp' only.

B. Use an LSI with partition key 'Status' and sort key 'OrderDate'.

C. Use the base table and a GSI with partition key 'Status' and sort key 'OrderDate'.

D. Use only the base table keys; no indexes needed.

Solution

1. Step 1: Base table supports UserId-Timestamp query

   Queries by 'UserId' sorted by 'Timestamp' use base table keys directly.

2. Step 2: GSI supports Status-OrderDate query

   Queries by 'Status' sorted by 'OrderDate' require a different partition key ('Status'), so a GSI with partition key 'Status' and sort key 'OrderDate' is needed.

3. Final Answer:

   Use the base table and a GSI with partition key 'Status' and sort key 'OrderDate'. -> Option C

4. Quick Check:

   LSI = same partition key, GSI = different partition key [OK]

Hint: LSI for alternate sort key; GSI for different partition key [OK]

Common Mistakes:

• Trying to use LSI with different partition key
• Using only base table keys for all queries
• Confusing which index supports which query pattern