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.

Practice

1. What is the main purpose of choosing a good partition key for a Global Secondary Index (GSI) in DynamoDB?

easy

A. To evenly distribute data across partitions for faster queries

B. To store large binary objects efficiently

C. To encrypt data at rest automatically

D. To backup data to another region

Solution

1. Step 1: Understand partition key role in GSI

   The partition key determines how data is spread across storage partitions in DynamoDB.

2. Step 2: Importance of even data distribution

   Choosing a partition key that evenly distributes data avoids hot partitions and improves query speed.

3. Final Answer:

   To evenly distribute data across partitions for faster queries -> Option A

4. Quick Check:

   Partition key = data distribution [OK]

Hint: Partition key controls data spread, pick for balance [OK]

Common Mistakes:

• Thinking partition key orders data inside partitions
• Confusing partition key with sort key
• Assuming partition key encrypts data

2. Which of the following is the correct syntax to define a GSI with a partition key named Category and a sort key named Timestamp in a DynamoDB table creation JSON?

easy

A. {
"IndexName": "CategoryTimestampIndex",
"KeySchema": [
{"AttributeName": "Timestamp", "KeyType": "RANGE"},
{"AttributeName": "Category", "KeyType": "HASH"}
],
"Projection": {"ProjectionType": "ALL"}
}

B. {
"IndexName": "CategoryTimestampIndex",
"KeySchema": [
{"AttributeName": "Category", "KeyType": "HASH"},
{"AttributeName": "Timestamp", "KeyType": "RANGE"}
],
"Projection": {"ProjectionType": "ALL"}
}

C. {
"IndexName": "CategoryTimestampIndex",
"KeySchema": [
{"AttributeName": "Category", "KeyType": "RANGE"},
{"AttributeName": "Timestamp", "KeyType": "HASH"}
],
"Projection": {"ProjectionType": "ALL"}
}

D. {
"IndexName": "CategoryTimestampIndex",
"KeySchema": [
{"AttributeName": "Timestamp", "KeyType": "HASH"},
{"AttributeName": "Category", "KeyType": "RANGE"}
],
"Projection": {"ProjectionType": "ALL"}
}

Solution

1. Step 1: Identify partition and sort key roles

   Partition key uses KeyType "HASH" and sort key uses KeyType "RANGE" in DynamoDB.

2. Step 2: Match keys to correct KeyType

   Category is partition key so it must have KeyType "HASH"; Timestamp is sort key so it must have KeyType "RANGE".

3. Final Answer:

   Correct JSON with Category as HASH and Timestamp as RANGE -> Option B

4. Quick Check:

   Partition key = HASH, Sort key = RANGE [OK]

Hint: Partition key = HASH, sort key = RANGE in KeySchema [OK]

Common Mistakes:

• Swapping HASH and RANGE key types
• Using RANGE for partition key
• Incorrect JSON structure for KeySchema

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

Query on GSI with UserId = 'user123' and OrderDate between '2023-01-01' and '2023-01-31'

medium

A. All orders by user123 in January 2023 sorted by OrderDate

B. All orders by all users in January 2023

C. All orders by user123 regardless of date

D. Syntax error due to missing filter expression

Solution

1. Step 1: Understand query conditions on GSI keys

   Query uses partition key UserId = 'user123' and a range condition on sort key OrderDate between two dates.

2. Step 2: Result filters by UserId and date range

   Only items with UserId 'user123' and OrderDate in January 2023 are returned, sorted by OrderDate.

3. Final Answer:

   All orders by user123 in January 2023 sorted by OrderDate -> Option A

4. Quick Check:

   Partition key + sort key range = filtered query [OK]

Hint: Query filters partition key exactly, sort key by range [OK]

Common Mistakes:

• Thinking query returns all users' data
• Ignoring sort key range condition
• Assuming syntax error without filter expression

4. You created a GSI with partition key Category and sort key Price. Your queries on this GSI are slow and uneven. What is the most likely cause?

medium

A. Price is not indexed as a sort key

B. The table has no primary key

C. You forgot to project attributes

D. Category has very few distinct values causing hot partitions

Solution

1. Step 1: Analyze partition key distribution

   If Category has few distinct values, many items share the same partition key, causing hot partitions and slow queries.

2. Step 2: Understand impact on query performance

   Hot partitions limit throughput and cause uneven query speed despite having a sort key.

3. Final Answer:

   Category has very few distinct values causing hot partitions -> Option D

4. Quick Check:

   Few partition keys = hot partitions = slow queries [OK]

Hint: Few partition key values cause hot partitions [OK]

Common Mistakes:

• Assuming missing sort key causes slowness
• Ignoring partition key cardinality
• Thinking projection affects partitioning

5. You want to design a GSI to efficiently query orders by CustomerId and sort them by OrderStatus and then by OrderDate. Which key selection strategy is best?

hard

A. Partition key: CustomerId; Sort key: OrderDate only

B. Partition key: OrderStatus; Sort key: CustomerId#OrderDate concatenated string

C. Partition key: CustomerId; Sort key: OrderStatus#OrderDate concatenated string

D. Partition key: OrderDate; Sort key: CustomerId

Solution

1. Step 1: Identify query pattern requirements

   You want to query by CustomerId and sort first by OrderStatus, then by OrderDate.

2. Step 2: Design composite sort key for multi-level sorting

   Concatenate OrderStatus and OrderDate into one sort key string (e.g., 'Shipped#2023-06-01') to sort by status then date.

3. Step 3: Assign keys correctly

   Partition key should be CustomerId for grouping orders by customer; sort key is the concatenated string for sorting.

4. Final Answer:

   Partition key: CustomerId; Sort key: OrderStatus#OrderDate concatenated string -> Option C

5. Quick Check:

   Composite sort key enables multi-level sorting [OK]

Hint: Use composite sort key for multi-level sorting [OK]

Common Mistakes:

• Using OrderStatus as partition key causing uneven distribution
• Not combining sort keys for multi-level order
• Using OrderDate as partition key which breaks grouping