Secondary indexes (GSI, LSI) in AWS - Step-by-Step Execution

Process Flow - Secondary indexes (GSI, LSI)

Create Main Table with Primary Key and LSI

↓

Add Global Secondary Index (GSI)

↓

Write Data to Table

↓

Data Automatically Indexed in LSI and GSI

↓

Query Using Primary Key or Secondary Index

This flow shows creating a main table with LSI, adding GSI, writing data, and querying using these indexes.

Execution Sample

AWS

Create DynamoDB table with PK userId and LSI on orderDate
Add GSI on attribute status
Write item {userId:1, orderDate:'2024-06-01', status:'shipped'}
Query by GSI status='shipped'

This example creates a table with primary key userId and LSI, adds GSI, writes an item, and queries using the GSI.

Process Table

Step	Action	Index Affected	Resulting Index State	Query Capability
1	Create table with PK userId and LSI on orderDate	Table, LSI	Table created with primary key userId and LSI userId + orderDate	Query by userId or userId + orderDate range
2	Add GSI on status	GSI	GSI created with status as PK	Query by userId + orderDate range or status independently
3	Write item {userId:1, orderDate:'2024-06-01', status:'shipped'}	Table, LSI, GSI	Item indexed in table, LSI, and GSI	Item retrievable by userId, userId+orderDate, or status
4	Query by GSI status='shipped'	GSI	Returns items with status shipped	Query successful using GSI
5	Query by LSI userId=1 and orderDate='2024-06-01'	LSI	Returns items matching userId and orderDate	Query successful using LSI
6	Query by primary key userId=1	Primary Key	Returns item with userId=1	Query successful using primary key
7	Attempt to add LSI after table creation	LSI	Error: LSI can only be added at table creation	Operation fails
8	Attempt to query GSI with non-indexed attribute	GSI	No results	Query returns empty
9	End of operations	N/A	All indexes created and used correctly	Execution complete

💡 Execution stops after all index operations and queries are demonstrated.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Table	None	Created with PK userId and LSI on orderDate	Same	Contains 1 item	Same	Contains 1 item
LSI	None	Created on orderDate	Same	Indexes item by userId+orderDate	Same	Exists with 1 indexed item
GSI	None	None	Created on status	Indexes item by status	Same	Exists with 1 indexed item
Item Count	0	0	0	1	1	1

Key Moments - 3 Insights

Why can't we add a Local Secondary Index (LSI) after the table is created?

LSIs must be defined when the table is created because they share the same partition key as the table and affect the table's storage structure. This is shown in execution_table step 7 where adding LSI later causes an error.

How does a Global Secondary Index (GSI) differ in querying compared to the primary key?

A GSI can have a different partition key and sort key, allowing queries on attributes other than the primary key. Execution_table steps 4 and 6 show querying by GSI and primary key returning different results.

What happens to data when you write an item to a table with LSI and GSI?

The item is automatically indexed in the main table, the LSI, and the GSI if it has the indexed attributes. Execution_table step 3 shows the item being indexed in all relevant indexes.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. After writing the item, which indexes contain the new item?

A Only the main table

B Main table, LSI, and GSI

C Main table and LSI only

D Main table and GSI only

💡 Hint

Check the 'Index Affected' and 'Resulting Index State' columns at step 3.

At which step does querying by the GSI 'status' attribute happen?

A Step 4

B Step 2

C Step 6

D Step 8

💡 Hint

Look for the step mentioning 'Query by GSI status' in the 'Action' column.

If you try to add an LSI after table creation, what will happen according to the execution_table?

A LSI is added successfully

B Table is deleted

C Error occurs and operation fails

D GSI is added instead

💡 Hint

Refer to step 7 in the execution_table for the result of adding LSI after creation.

Concept Snapshot

Secondary Indexes in DynamoDB:
- LSI shares the table's partition key, adds a sort key, must be created with the table.
- GSI has its own partition and optional sort key, can be added anytime.
- Both indexes speed up queries on non-primary key attributes.
- Data is automatically copied to indexes when written.
- LSI max 5 per table; GSI max 20 per table.

Full Transcript

This visual execution trace shows how secondary indexes work in AWS DynamoDB. First, a table is created with a primary key and LSI. Then, a Global Secondary Index (GSI) is added with a different partition key. When data is written, it is automatically indexed in the main table, LSI, and GSI if it contains the indexed attributes. Queries can then use the primary key, LSI, or GSI to retrieve data efficiently. The trace also shows that LSIs cannot be added after table creation, while GSIs can. This helps beginners understand how indexes improve query flexibility and performance in DynamoDB.

Practice

1. What is the main difference between a Local Secondary Index (LSI) and a Global Secondary Index (GSI) in DynamoDB?

easy

A. LSI can have a different partition key, but GSI must use the main table's partition key.

B. LSI uses the same partition key as the main table, but GSI can use a different partition key.

C. LSI and GSI both require the same partition and sort keys as the main table.

D. LSI is only for read operations, while GSI is only for write operations.

Solution

1. Step 1: Understand LSI key structure

   LSI shares the same partition key as the main table but allows a different sort key.

2. Step 2: Understand GSI key structure

   GSI can have a completely different partition key and sort key from the main table.

3. Final Answer:

   LSI uses the same partition key as the main table, but GSI can use a different partition key. -> Option B

4. Quick Check:

   LSI shares partition key, GSI can differ [OK]

Hint: LSI shares partition key; GSI can change both keys [OK]

Common Mistakes:

• Thinking LSI can have a different partition key
• Assuming GSI must use main table keys
• Confusing read/write capabilities of indexes

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

easy

A. "GlobalSecondaryIndexes": [{"IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "HASH"}], "Projection": {"ProjectionType": "KEYS_ONLY"}}]

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

C. "GlobalSecondaryIndexes": [{"IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}]

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

Solution

1. Step 1: Identify correct index type and key schema

   GlobalSecondaryIndexes is the correct property for GSI, and the partition key must have KeyType "HASH".

2. Step 2: Check projection type and syntax

   ProjectionType "ALL" is valid and commonly used; syntax matches CloudFormation format.

3. Final Answer:

   "GlobalSecondaryIndexes": [{"IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}] -> Option D

4. Quick Check:

   GSI uses GlobalSecondaryIndexes with HASH key [OK]

Hint: GSI uses GlobalSecondaryIndexes with HASH key [OK]

Common Mistakes:

• Using LocalSecondaryIndexes for GSI
• Setting KeyType to RANGE for partition key
• Incorrect projection types or syntax errors

3. Given a DynamoDB table with a Global Secondary Index (GSI) on attribute Category as partition key and Price as sort key, what will the following query return?

Query on GSI where Category = 'Books' and Price between 10 and 20

medium

A. All items with Category 'Books' and Price between 10 and 20

B. All items with Price between 10 and 20 regardless of Category

C. All items with Category 'Books' regardless of Price

D. An error because Price cannot be used in query conditions

Solution

1. Step 1: Understand GSI query conditions

   Query on GSI requires specifying the partition key (Category = 'Books') and can filter by sort key range (Price between 10 and 20).

2. Step 2: Interpret the query result

   The query returns all items matching Category 'Books' with Price in the specified range.

3. Final Answer:

   All items with Category 'Books' and Price between 10 and 20 -> Option A

4. Quick Check:

   GSI query filters partition and sort keys correctly [OK]

Hint: Query GSI with partition key + sort key range filters [OK]

Common Mistakes:

• Ignoring partition key in query
• Thinking sort key can't be filtered
• Expecting error on valid query

4. You created a Local Secondary Index (LSI) on a DynamoDB table but your query on the LSI returns no results even though matching items exist. What is the most likely cause?

medium

A. The LSI was created with a different partition key than the main table.

B. The query used a filter expression instead of a key condition.

C. The query did not specify the same partition key as the main table.

D. LSIs do not support queries, only scans.

Solution

1. Step 1: Recall LSI key requirements

   LSI shares the same partition key as the main table, so queries must specify that partition key.

2. Step 2: Analyze query behavior

   If the query does not specify the partition key, no results will be returned because partition key is mandatory for queries on LSI.

3. Final Answer:

   The query did not specify the same partition key as the main table. -> Option C

4. Quick Check:

   LSI queries require main table partition key [OK]

Hint: LSI queries must include main table partition key [OK]

Common Mistakes:

• Assuming LSI can have different partition key
• Using filter instead of key condition for partition key
• Believing LSI does not support queries

5. You have a DynamoDB table with a partition key UserId and sort key OrderDate. You want to efficiently query all orders by Status and sort them by OrderAmount. Which secondary index should you create and how?

hard

A. Create a Global Secondary Index with partition key Status and sort key OrderAmount.

B. Create a Local Secondary Index with partition key UserId and sort key Status.

C. Create a Global Secondary Index with partition key UserId and sort key Status.

D. Create a Local Secondary Index with partition key Status and sort key OrderAmount.

Solution

1. Step 1: Identify query requirements

   You want to query by Status (different from main partition key) and sort by OrderAmount.

2. Step 2: Choose index type and keys

   Since Status is different from main partition key, use a Global Secondary Index with Status as partition key and OrderAmount as sort key.

3. Final Answer:

   Create a Global Secondary Index with partition key Status and sort key OrderAmount. -> Option A

4. Quick Check:

   GSI allows different partition and sort keys [OK]

Hint: Use GSI when partition key differs from main table [OK]

Common Mistakes:

• Trying to use LSI with different partition key
• Swapping partition and sort keys incorrectly
• Choosing wrong index type for query pattern