LSI vs GSI comparison in DynamoDB - Visual Side-by-Side Comparison

Concept Flow - LSI vs GSI comparison

Start: Table with Primary Key

↓

Add LSI?

Yes→LSI uses same Partition Key, different Sort Key

↓

Query uses Partition + Sort Key

↓

Add GSI?

Yes→GSI uses different Partition Key and Sort Key

↓

Query uses GSI keys

↓

Use base table keys

Shows decision flow for choosing Local Secondary Index (LSI) or Global Secondary Index (GSI) in DynamoDB based on key usage and query needs.

Execution Sample

DynamoDB

CREATE TABLE Music (
  Artist string,
  SongTitle string,
  AlbumTitle string,
  Year int,
  PRIMARY KEY (Artist, SongTitle),
  LOCAL SECONDARY INDEX AlbumIndex (Artist, AlbumTitle)
);

CREATE GLOBAL SECONDARY INDEX YearIndex ON Music (Year, SongTitle);

Defines a table with a primary key and adds an LSI on AlbumTitle and a GSI on Year.

Execution Table

Step	Action	Index Type	Partition Key Used	Sort Key Used	Query Capability
1	Create base table	None	Artist	SongTitle	Query by Artist and SongTitle
2	Add LSI AlbumIndex	LSI	Artist (same as base)	AlbumTitle (different)	Query by Artist and AlbumTitle
3	Add GSI YearIndex	GSI	Year (different)	SongTitle (different)	Query by Year and SongTitle
4	Query base table	None	Artist	SongTitle	Can only query by Artist and SongTitle
5	Query LSI AlbumIndex	LSI	Artist	AlbumTitle	Can query by Artist and AlbumTitle
6	Query GSI YearIndex	GSI	Year	SongTitle	Can query by Year and SongTitle
7	Attempt query with different Partition Key on LSI	LSI	Year	AlbumTitle	Not possible - LSI partition key fixed
8	Attempt query with different Partition Key on GSI	GSI	Year	SongTitle	Possible - GSI partition key flexible
9	Table size limit check	LSI	N/A	N/A	LSI shares table size limit (10GB)
10	Table size limit check	GSI	N/A	N/A	GSI has separate size limit, scales independently
11	End	N/A	N/A	N/A	Comparison complete

💡 All key differences between LSI and GSI demonstrated, including key usage and query capabilities.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
Partition Key	Artist	Artist	Year (for GSI)	Year (for GSI)
Sort Key	SongTitle	AlbumTitle (LSI)	SongTitle (GSI)	SongTitle (GSI)
Query Capability	Artist+SongTitle	Artist+AlbumTitle	Year+SongTitle	Year+SongTitle
Size Limit	10GB (table)	10GB (table shared with LSI)	Separate limit for GSI	Separate limit for GSI

Key Moments - 3 Insights

Why can't we use a different partition key in an LSI?

Because LSI uses the same partition key as the base table, only the sort key changes (see execution_table row 2 and 7).

Can a GSI have a completely different partition key from the base table?

Yes, GSI allows a different partition key and sort key, enabling flexible queries (see execution_table row 3 and 6).

How does the size limit differ between LSI and GSI?

LSI shares the 10GB size limit with the base table, while GSI has its own separate size limit and scales independently (see execution_table rows 9 and 10).

Visual Quiz - 3 Questions

Look at the execution table, at which step is the LSI added and what partition key does it use?

A Step 3, uses Year as partition key

B Step 5, uses AlbumTitle as partition key

C Step 2, uses Artist as partition key

D Step 6, uses SongTitle as partition key

💡 Hint

Check execution_table row 2 for LSI details.

According to variable_tracker, what is the sort key used by the GSI after step 3?

A SongTitle

B Artist

C AlbumTitle

D Year

💡 Hint

Look at the 'Sort Key' row in variable_tracker after step 3.

From the execution table, which index type allows querying by a different partition key than the base table?

A LSI

B GSI

C Both LSI and GSI

D Neither

💡 Hint

Compare rows 2, 3, 6, and 7 in execution_table.

Concept Snapshot

LSI (Local Secondary Index):
- Uses same partition key as base table
- Different sort key
- Shares table size limit (10GB)
- Enables queries on alternate sort keys

GSI (Global Secondary Index):
- Uses different partition and sort keys
- Has separate size and throughput limits
- Enables flexible queries on different keys

Full Transcript

This visual execution compares Local Secondary Index (LSI) and Global Secondary Index (GSI) in DynamoDB. The flow starts with a base table having a primary key composed of partition and sort keys. Adding an LSI keeps the same partition key but changes the sort key, allowing queries on the same partition key but different sort keys. Adding a GSI allows both partition and sort keys to differ, enabling queries on completely different keys. The execution table traces creation steps, key usage, and query capabilities. Variable tracking shows how partition keys, sort keys, query capabilities, and size limits change with each step. Key moments clarify common confusions about partition key restrictions in LSI, flexibility in GSI, and size limits. The quiz tests understanding of index creation steps, key usage, and query flexibility. The snapshot summarizes key differences: LSI shares partition key and size limit with the base table, while GSI allows different keys and scales independently.

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