GSI overloading technique in DynamoDB - Step-by-Step Execution

Concept Flow - GSI overloading technique

Write item with multiple attribute types

↓

Store item in base table

↓

Write item to GSI with overloaded attribute

↓

Query GSI with specific key pattern

↓

Filter results by attribute type or value

↓

Return filtered results to application

This flow shows how a single Global Secondary Index (GSI) can store different types of data by overloading attributes, then queries filter results based on attribute patterns.

Execution Sample

DynamoDB

PutItem: {PK: 'USER#123', SK: 'PROFILE', GSI1PK: 'TYPE#PROFILE', GSI1SK: 'USER#123'}
PutItem: {PK: 'USER#123', SK: 'ORDER#456', GSI1PK: 'TYPE#ORDER', GSI1SK: 'DATE#20240101'}
Query GSI1 where GSI1PK = 'TYPE#ORDER'

Stores user profile and order items with different GSI1PK values to overload the GSI, then queries orders by filtering on GSI1PK.

Execution Table

Step	Action	Item Attributes	GSI1PK Value	Query Condition	Query Result
1	Put user profile item	{PK:'USER#123', SK:'PROFILE', GSI1PK:'TYPE#PROFILE', GSI1SK:'USER#123'}	TYPE#PROFILE	N/A	Item stored in base and GSI
2	Put user order item	{PK:'USER#123', SK:'ORDER#456', GSI1PK:'TYPE#ORDER', GSI1SK:'DATE#20240101'}	TYPE#ORDER	N/A	Item stored in base and GSI
3	Query GSI1 for orders	N/A	N/A	GSI1PK = 'TYPE#ORDER'	Returns order item with PK 'USER#123' and SK 'ORDER#456'
4	Filter results	N/A	N/A	Filter by GSI1PK prefix 'TYPE#ORDER'	Only order items returned
5	End query	N/A	N/A	No more items	Query ends

💡 Query ends after returning all items with GSI1PK = 'TYPE#ORDER'

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Base Table Items	[]	[User Profile Item]	[User Profile Item, User Order Item]	[User Profile Item, User Order Item]	[User Profile Item, User Order Item]
GSI1 Items	[]	[User Profile Item with GSI1PK='TYPE#PROFILE']	[User Profile Item with GSI1PK='TYPE#PROFILE', User Order Item with GSI1PK='TYPE#ORDER']	[User Profile Item with GSI1PK='TYPE#PROFILE', User Order Item with GSI1PK='TYPE#ORDER']	[User Profile Item with GSI1PK='TYPE#PROFILE', User Order Item with GSI1PK='TYPE#ORDER']
Query Result	[]	[]	[]	[User Order Item]	[User Order Item]

Key Moments - 2 Insights

Why do we use different prefixes like 'TYPE#PROFILE' and 'TYPE#ORDER' in GSI1PK?

Using prefixes lets us overload the same GSI attribute to store different item types. This helps us query only the items we want by filtering on these prefixes, as shown in execution_table step 3 and 4.

How does the query know which items to return from the overloaded GSI?

The query uses the condition on GSI1PK (e.g., 'TYPE#ORDER') to select only items with that prefix. This filtering is shown in execution_table step 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the GSI1PK value for the user order item?

A TYPE#PROFILE

B TYPE#ORDER

C USER#123

D DATE#20240101

💡 Hint

Check the 'GSI1PK Value' column in step 2 of the execution_table.

At which step does the query filter results to only include order items?

A Step 4

B Step 3

C Step 1

D Step 5

💡 Hint

Look at the 'Action' and 'Query Condition' columns in execution_table steps 3 and 4.

If we changed the GSI1PK prefix for orders to 'CATEGORY#ORDER', how would the query condition change?

A GSI1PK = 'ORDER#CATEGORY'

B GSI1PK = 'TYPE#ORDER'

C GSI1PK = 'CATEGORY#ORDER'

D No change needed

💡 Hint

Refer to the query condition in execution_table step 3 and how it matches the GSI1PK value.

Concept Snapshot

GSI Overloading Technique:
- Use one GSI attribute (e.g., GSI1PK) to store different item types by prefixing values.
- Store multiple item types in the same GSI to save costs and simplify design.
- Query GSI with prefix filters to retrieve specific item types.
- Filter results by attribute patterns after query.
- Enables flexible, efficient queries on diverse data in DynamoDB.

Full Transcript

This visual execution shows how the GSI overloading technique works in DynamoDB. First, items of different types like user profiles and orders are stored with a shared GSI attribute called GSI1PK. Each item type uses a unique prefix in GSI1PK, such as 'TYPE#PROFILE' for profiles and 'TYPE#ORDER' for orders. When querying the GSI, the application specifies the prefix to filter only the desired item type. The execution table traces putting items into the base table and GSI, then querying and filtering results. The variable tracker shows how items accumulate in the base table and GSI, and how the query result narrows to only order items. Key moments clarify why prefixes are used and how queries filter by them. The quiz tests understanding of GSI1PK values and query filtering steps. This technique helps store multiple data types in one GSI and query them efficiently by overloading the GSI key attribute with type prefixes.

Practice

1. What is the main benefit of using the GSI overloading technique in DynamoDB?

easy

A. It allows using a single Global Secondary Index for multiple query patterns.

B. It automatically encrypts data stored in the table.

C. It increases the size limit of DynamoDB items.

D. It enables multi-region replication without extra setup.

Solution

1. Step 1: Understand GSI overloading purpose

   GSI overloading is designed to let one index serve many query types by using prefixes and composite keys.

2. Step 2: Identify the main benefit

   This reduces the number of GSIs needed, saving costs and improving query flexibility.

3. Final Answer:

   It allows using a single Global Secondary Index for multiple query patterns. -> Option A

4. Quick Check:

   GSI overloading = single index, multiple queries [OK]

Hint: Remember: one GSI, many queries saves cost and complexity [OK]

Common Mistakes:

• Thinking GSI overloading encrypts data
• Confusing GSI overloading with item size limits
• Assuming it enables multi-region replication

2. Which of the following is the correct way to define a partition key for a GSI using overloading with prefixes?

easy

A. PartitionKey = "USER#" + UserId

B. PartitionKey = UserId + "#USER"

C. PartitionKey = UserId

D. PartitionKey = "#" + UserId + "USER"

Solution

1. Step 1: Understand prefix usage in GSI keys

   Prefixes like "USER#" help separate different entity types in the same GSI.

2. Step 2: Check correct concatenation

   The prefix should come first, then the unique identifier, so "USER#" + UserId is correct.

3. Final Answer:

   PartitionKey = "USER#" + UserId -> Option A

4. Quick Check:

   Prefix before ID = "USER#" + UserId [OK]

Hint: Prefix always goes before the ID in composite keys [OK]

Common Mistakes:

• Putting prefix after the ID
• Using no prefix at all
• Adding extra symbols incorrectly

3. Given a DynamoDB table with a GSI using overloading where the partition key is PK and sort key is SK, what will be the result of this query?

Query:
PK = "ORDER#123"
SK begins_with "ITEM#"

medium

A. Returns no results because SK cannot be filtered

B. Returns all orders with PK starting with "ORDER#"

C. Returns all items with SK exactly equal to "ITEM#"

D. Returns all items related to order 123 with SK starting with "ITEM#"

Solution

1. Step 1: Understand the query keys

   The query filters on PK = "ORDER#123" and SK begins with "ITEM#" which targets items under order 123.

2. Step 2: Interpret begins_with on SK

   Using begins_with on SK returns all sort keys starting with "ITEM#", so all order items are returned.

3. Final Answer:

   Returns all items related to order 123 with SK starting with "ITEM#" -> Option D

4. Quick Check:

   PK exact + SK begins_with = filtered order items [OK]

Hint: begins_with on SK filters items under one PK [OK]

Common Mistakes:

• Thinking begins_with applies to PK
• Assuming SK must be exact match
• Believing SK cannot be filtered in query

4. You have a GSI with partition key GSI_PK and sort key GSI_SK. You want to query all "USER" entities but your query returns no results. Which of the following is the most likely cause?

medium

A. You used begins_with on the partition key instead of the sort key.

B. You forgot to add the prefix "USER#" to the partition key value in the query.

C. You queried the base table instead of the GSI.

D. You used a filter expression instead of a key condition.

Solution

1. Step 1: Check prefix usage in GSI overloading

   GSI overloading requires prefixes like "USER#" in the partition key to separate entity types.

2. Step 2: Identify missing prefix impact

   If the query omits the prefix, it won't match any items because keys are stored with prefixes.

3. Final Answer:

   You forgot to add the prefix "USER#" to the partition key value in the query. -> Option B

4. Quick Check:

   Missing prefix in PK = no results [OK]

Hint: Always include prefixes in GSI key queries [OK]

Common Mistakes:

• Using begins_with on partition key
• Querying base table instead of GSI
• Using filter instead of key condition

5. You want to design a GSI overloading scheme to support queries for both USER and ORDER entities in the same index. Which key design below best supports efficient queries for:
1) All orders by a user
2) All users by status

Choose the best partition key and sort key combination.

hard

A. Partition Key: "TYPE#" + EntityType, Sort Key: "ID#" + EntityId

B. Partition Key: "USER#" + UserId for orders, "USER#" + UserId for users; Sort Key: "ORDER#" + OrderId for orders, "USER#" + UserId for users

C. Partition Key: "USER#" + UserId for orders, "STATUS#" + Status for users; Sort Key: "ORDER#" + OrderId for orders, "USER#" + UserId for users

D. Partition Key: EntityId, Sort Key: EntityType

Solution

1. Step 1: Analyze query requirements

   Query 1 needs all orders by a user, so partition key should be "USER#" + UserId for orders.
   Query 2 needs all users by status, so partition key should be "STATUS#" + Status for users.

2. Step 2: Check sort key design

   Sort keys differentiate entities: "ORDER#" + OrderId for orders, "USER#" + UserId for users to keep uniqueness and sorting.

3. Step 3: Evaluate options

   Partition Key: "USER#" + UserId for orders, "STATUS#" + Status for users; Sort Key: "ORDER#" + OrderId for orders, "USER#" + UserId for users matches these requirements by overloading the GSI with different prefixes for partition keys and sort keys.

4. Final Answer:

   Partition Key: "USER#" + UserId for orders, "STATUS#" + Status for users; Sort Key: "ORDER#" + OrderId for orders, "USER#" + UserId for users -> Option C

5. Quick Check:

   Use different prefixes per entity and query need [OK]

Hint: Use distinct prefixes per entity and query type in keys [OK]

Common Mistakes:

• Using same prefix for all entities
• Not differentiating partition keys by query type
• Using entity ID as partition key without prefix