Optimizing DynamoDB Data Retrieval with Global Secondary Indexes

Solving the Primary Key Limitation

In DynamoDB, you can only perform efficient queries using the table's primary key. If your application needs to retrieve items based on a different attribute—such as finding all orders for a specific customer when the table is keyed by Order ID—a full table scan is required, which is slow and expensive. A Global Secondary Index (GSI) solves this by creating a mirrored view of your data with a different partition key, allowing you to query that attribute directly.

Implementing a GSI: Worked Example

Consider an Orders table where OrderId is the partition key. To enable lookups by CustomerId, you must define a GSI that treats CustomerId as its own partition key.

1. Create the Base Table

Run this command in a terminal with AWS CLI v2. The IAM user requires dynamodb:CreateTable permissions.

aws dynamodb create-table \
    --table-name Orders \
    --attribute-definitions AttributeName=OrderId,AttributeType=S AttributeName=CustomerId,AttributeType=S \
    --key-schema AttributeName=OrderId,KeyType=HASH \
    --billing-mode PAY_PER_REQUEST

2. Add the Global Secondary Index

Update the table to add the GSI. This requires dynamodb:UpdateTable permissions. In this example, we use ProjectionType: ALL to copy all attributes to the index.

aws dynamodb update-table \
    --table-name Orders \
    --attribute-definitions AttributeName=CustomerId,AttributeType=S \
    --global-secondary-index-updates "[{\n        'Create': {\n            'IndexName': 'CustomerIdIndex',\n            'KeySchema': [{'AttributeName':'CustomerId','KeyType':'HASH'}],\n            'Projection': {'ProjectionType':'ALL'},\n            'ProvisionedThroughput': {'ReadCapacityUnits':5,'WriteCapacityUnits':5}\n        }\n}]"

3. Verify Index Status

GSIs are created asynchronously. You cannot query the index until the status is ACTIVE.

aws dynamodb describe-table --table-name Orders --query 'Table.GlobalSecondaryIndexes[?IndexName==`CustomerIdIndex`].IndexStatus'

Querying the Index

When querying a GSI, you must specify the --index-name. Note that GSIs only support eventual consistency; a write to the base table may take a short time to propagate to the index.

aws dynamodb query \
    --table-name Orders \
    --index-name CustomerIdIndex \
    --key-condition-expression "CustomerId = :cid" \
    --expression-attribute-values '{ ":cid": {"S": "CUST123"} }'

Engineering Constraints and Common Pitfalls

• Write Capacity Unit (WCU) Multiplication: Every time you write an item to the base table, DynamoDB automatically updates all GSIs that contain the modified attributes. This consumes WCUs on both the base table and the GSI. If your GSI is under-provisioned, it can cause throttling on writes to the base table.
• The Cost of Over-Projection: Using ProjectionType: ALL is convenient but increases storage costs and WCU consumption. For high-scale tables, use INCLUDE to project only the specific attributes needed for the query result, avoiding the need to fetch the full item from the base table.
• Backfill Throughput: Creating a GSI on an existing table triggers a backfill process. DynamoDB reads the entire base table to populate the index. On large tables, this can consume significant throughput; using PAY_PER_REQUEST billing is recommended during this phase to avoid throttling.
• Consistency Limitations: Unlike Local Secondary Indexes (LSIs), GSIs do not support strongly consistent reads. If your logic requires the absolute latest data, you must query the base table using the primary key.

Practical Verification

To verify the GSI is functioning and understand the consistency lag, perform the following sequence:

1. Insert a test item using put-item.
2. Immediately run the GSI query command. If the item is missing, retry after a few hundred milliseconds to observe the eventual consistency replication.
3. Compare the result with a get-item call on the base table using --consistent-read to confirm the data exists in the source of truth.

Rollback Procedure

If the GSI is no longer needed or is causing excessive costs, remove it using the following command:

aws dynamodb update-table \
    --table-name Orders \
    --global-secondary-index-updates "[{\n        'Delete': {'IndexName':'CustomerIdIndex'}\n}]"

Deleting the GSI stops WCU consumption for that index immediately, though the physical deletion of data occurs in the background.