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.