LSI vs GSI comparison in DynamoDB - Hands-On Comparison

Comparing Local Secondary Index (LSI) and Global Secondary Index (GSI) in DynamoDB

📖 Scenario: You are building a simple DynamoDB table to store information about books in a library. You want to learn how to use Local Secondary Indexes (LSI) and Global Secondary Indexes (GSI) to query the data in different ways.

🎯 Goal: Create a DynamoDB table with a primary key and add one Local Secondary Index (LSI) and one Global Secondary Index (GSI). Understand how to define them and see the difference in their setup.

📋 What You'll Learn

Create a DynamoDB table named Books with Author as the partition key and Title as the sort key.

Add a Local Secondary Index (LSI) named GenreIndex with Author as the partition key and Genre as the sort key.

Add a Global Secondary Index (GSI) named PublisherIndex with Publisher as the partition key and Year as the sort key.

Use the AWS CLI JSON format for the table creation and index definitions.

💡 Why This Matters

🌍 Real World

In real-world applications, DynamoDB indexes help you query data efficiently in different ways without scanning the entire table.

💼 Career

Understanding LSIs and GSIs is important for database developers and cloud engineers working with AWS DynamoDB to optimize data access patterns.

1

Create the DynamoDB table with primary key

Create a DynamoDB table named Books with Author as the partition key (type String) and Title as the sort key (type String). Use the AWS CLI JSON format for the table definition and assign it to a variable called table_definition.

DynamoDB

table_definition = {
    # Your code here
}

Hint

Remember, the partition key is called Author and the sort key is called Title. Both are strings.

2

Add a Local Secondary Index (LSI)

Add a Local Secondary Index (LSI) named GenreIndex to the table_definition. The LSI should use Author as the partition key and Genre as the sort key (type String). Update the AttributeDefinitions to include Genre. Add the LSI inside the LocalSecondaryIndexes list.

DynamoDB

table_definition["AttributeDefinitions"].append({"AttributeName": "Genre", "AttributeType": "S"})
# Your code here to add LocalSecondaryIndexes

Hint

LSI uses the same partition key Author but a different sort key Genre. Add Genre to AttributeDefinitions.

3

Add a Global Secondary Index (GSI)

Add a Global Secondary Index (GSI) named PublisherIndex to the table_definition. The GSI should use Publisher as the partition key and Year as the sort key (both type String). Update the AttributeDefinitions to include Publisher and Year. Add the GSI inside the GlobalSecondaryIndexes list with provisioned throughput of 5 read and 5 write units.

DynamoDB

table_definition["AttributeDefinitions"].extend([
    {"AttributeName": "Publisher", "AttributeType": "S"},
    {"AttributeName": "Year", "AttributeType": "S"}
])
# Your code here to add GlobalSecondaryIndexes

Hint

GSI uses different partition and sort keys: Publisher and Year. Add both to AttributeDefinitions and define the GSI with provisioned throughput.

4

Complete the table definition with all indexes

Ensure the table_definition includes the primary key, the Local Secondary Index GenreIndex, and the Global Secondary Index PublisherIndex with all required attribute definitions and provisioned throughput settings.

DynamoDB

# Your code here to finalize the table_definition

Hint

Check that all parts are included: primary key, LSI, GSI, attribute definitions, and throughput.

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