GSI key selection strategy in DynamoDB - Step-by-Step Execution

Concept Flow - GSI key selection strategy

Identify access patterns

↓

Choose GSI partition key

↓

Choose GSI sort key (optional)

↓

Check key uniqueness and distribution

↓

Validate query efficiency

↓

Create GSI with chosen keys

This flow shows how to select keys for a Global Secondary Index (GSI) by analyzing access patterns, choosing partition and sort keys, and validating their effectiveness.

Execution Sample

DynamoDB

{
  "TableName": "Orders",
  "GlobalSecondaryIndexUpdates": [{
    "Create": {
      "IndexName": "GSI1",
      "KeySchema": [
        {
          "AttributeName": "user_id",
          "KeyType": "HASH"
        },
        {
          "AttributeName": "order_date",
          "KeyType": "RANGE"
        }
      ],
      "Projection": {
        "ProjectionType": "ALL"
      }
    }
  }]
}

This JSON payload for the DynamoDB UpdateTable API creates a GSI named GSI1 with user_id as partition key and order_date as sort key to support queries by user and date.

Execution Table

Step	Action	Evaluation	Result
1	Identify access pattern: Query orders by user	Access pattern found	Proceed to key selection
2	Choose partition key: user_id	user_id has high cardinality	Good distribution expected
3	Choose sort key: order_date	order_date allows sorting by date	Enables range queries
4	Check uniqueness	user_id + order_date combination mostly unique	Prevents write conflicts
5	Validate query efficiency	Queries use GSI keys	Efficient query execution
6	Create GSI with keys	GSI created successfully	Ready for queries

💡 GSI created after validating keys for efficient queries and good data distribution

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
partition_key	none	user_id	user_id	user_id	user_id
sort_key	none	none	order_date	order_date	order_date
key_uniqueness	unknown	unknown	unknown	mostly unique	mostly unique
query_efficiency	unknown	unknown	unknown	unknown	efficient

Key Moments - 3 Insights

Why do we choose a partition key with high cardinality?

Choosing a partition key with many unique values (high cardinality) helps distribute data evenly across partitions, avoiding hot spots. See execution_table step 2 where user_id is chosen for this reason.

Why is a sort key optional in a GSI?

A sort key is optional because it is only needed if you want to sort or filter data within the same partition key. Step 3 shows adding order_date as sort key to enable sorting by date.

What happens if the key combination is not unique?

If the partition and sort key combination is not unique, it can cause write failures due to duplicate primary keys. Step 4 checks for uniqueness to avoid this problem.

Visual Quiz - 3 Questions

Look at the execution_table, what key is chosen as the partition key at step 2?

A user_id

B order_date

C GSI1

D Table name

💡 Hint

Refer to execution_table row with Step 2 under 'Action' and 'Result'

At which step is the sort key selected?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Check execution_table row where 'Choose sort key' is the action

If the partition key had low cardinality, what would likely happen?

A Data would be evenly distributed

B Hot partitions could form causing performance issues

C Queries would be faster

D GSI creation would fail

💡 Hint

See key_moments explanation about partition key cardinality and execution_table step 2

Concept Snapshot

GSI Key Selection Strategy:
1. Identify query access patterns.
2. Choose a partition key with high cardinality for even data spread.
3. Optionally choose a sort key for sorting/filtering.
4. Ensure key combination uniqueness to avoid write conflicts.
5. Validate query efficiency before creating the GSI.

Full Transcript

To select keys for a DynamoDB Global Secondary Index (GSI), first identify how you want to query your data. Then pick a partition key that has many unique values to spread data evenly. Optionally, pick a sort key to sort or filter data within the partition. Check that the combination of partition and sort keys is mostly unique to avoid write conflicts. Finally, validate that your queries will efficiently use the GSI before creating it.