Optimizing DynamoDB Access Patterns with Global Secondary Indexes

The Cost of the Full Table Scan

In DynamoDB, data retrieval is highly efficient when you use the primary key. However, a common engineering bottleneck occurs when you need to retrieve data using an attribute that isn't part of that primary key. Without a strategy to handle this, developers often resort to the Scan operation.

A Scan reads every single item in your table and then filters the results. If your table contains millions of records, a scan consumes massive amounts of Read Capacity Units (RCUs), introduces significant latency, and can lead to application timeouts. The practical takeaway is that Global Secondary Indexes (GSIs) allow you to pivot your data access, enabling Query operations on non-primary attributes at the cost of eventual consistency and additional write overhead.

How GSIs Restructure Your Data

A GSI is essentially a shadow table that DynamoDB maintains automatically. It allows you to define a new partition key (and an optional sort key) for your data. When you write an item to the base table, DynamoDB asynchronously replicates the specified attributes to the GSI.

This allows you to support multiple access patterns on a single table. For example, if your base table is partitioned by UserID, but your business logic requires looking up users by Email, a GSI on the Email attribute transforms a linear scan into a direct lookup.

Managing Projection Types

One of the most critical engineering decisions when creating a GSI is choosing the Projection Type. This determines which attributes are copied from the base table to the index:

• ALL: Every attribute is copied. This is the simplest for development but the most expensive in terms of storage and Write Capacity Units (WCUs).
• INCLUDE: Only a specific subset of attributes is copied. This is the recommended approach for cost-optimization.
• KEYS_ONLY: Only the base table primary key and the GSI keys are copied. If you need other data, your application must perform a second GetItem call to the base table.

Implementation Example: Email Lookup

Assume a Users table with UserID as the partition key. To enable efficient lookups by email, you can create a GSI using the AWS CLI. Run this command with permissions to modify DynamoDB tables:

aws dynamodb update-table \
    --table-name Users \
    --attribute-definitions AttributeName=Email,AttributeType=S \
    --secondary-index-specifications '[{
        "IndexName": "EmailIndex",
        "Projection": {"ProjectionType": "INCLUDE", "NonKeyAttributes": ["Username", "AccountStatus"]},
        "KeySchema": [{"AttributeName": "Email", "KeyType": "HASH"}]
    }]'

Expected Check: After running this, the index status will be CREATING. You must wait for it to reach ACTIVE before querying. The INCLUDE projection here ensures that only Username and AccountStatus are stored in the index, reducing the WCU cost per update compared to projecting ALL attributes.

Technical Trade-offs and Limitations

While GSIs solve the scanning problem, they introduce three specific engineering challenges:

1. Eventual Consistency

GSIs are eventually consistent. There is a propagation delay between the write to the base table and the update in the GSI. If your application performs a PutItem and immediately follows it with a Query on the GSI, the new data may not be present yet.

2. Write Amplification

Every time you update an attribute that is projected into a GSI, DynamoDB performs an additional write to the index. If you have five GSIs projecting the same attribute, one base table write can trigger six total write operations, rapidly consuming your provisioned WCU.

3. Immutable Schema

GSI keys cannot be modified after creation. If your access pattern changes—for instance, if you need to change the sort key of the index—you must delete the existing GSI and create a new one, which can be time-consuming for large datasets.

Verifying GSI Performance

To verify the implementation and monitor for bottlenecks, use the following diagnostic steps:

1. Latency Check: Use the SDK to perform a PutItem on the base table and immediately query the GSI. If the item is missing, your application must be designed to handle eventual consistency (e.g., via retries or by reading from the base table).
2. Throttling Monitor: Check Amazon CloudWatch for the GSIThrottledRequests metric. If the GSI is throttled, it can create backpressure that throttles writes to the base table, even if the base table has plenty of capacity.