DynamoDB Secondary Indexes: When They Save You and When They Cost You

The most expensive DynamoDB tables I have seen all shared one trait: secondary indexes added before the access patterns were understood. Indexes are not a performance tool you sprinkle on. They are an architectural commitment with a monthly bill. Let me show you how I decide.

The Core Trade-off

Every global secondary index is a second copy of your data. You pay for the storage. You pay for the writes, because every base table write propagates. If your table does one million writes a month, a GSI doubles that to two million. At scale this adds up fast.

In exchange you get query patterns that would otherwise require scans. A scan on a million-item table is slow and expensive. A GSI query on the same data is milliseconds and cheap. The trade is correct when your query pattern is frequent and narrow. It is wrong when you add a GSI for a report you run once a week.

My Decision Rule

I use a simple question: is this access pattern in the hot path of a user-facing request? If yes, a GSI is the right answer. If no, I reach for other tools first: CloudWatch scheduled Lambdas that run scans during off-peak hours, or S3 exports feeding Athena queries for analytics.

# Hot path: user loads their dashboard
# Query by user_id must be fast. GSI earns its keep.
table.query(
    IndexName='user-index',
    KeyConditionExpression=Key('user_id').eq(user_id)
)
 
# Cold path: monthly admin report
# One-off scan during off-peak is cheaper than a permanent GSI
# Even better: export to S3 and query with Athena

The Projection Trap

When you create a GSI, the default projection is ALL. That means every attribute gets copied. For large items this is wasteful. I default to KEYS_ONLY and only add specific attributes when a query needs them. This cuts storage costs and write amplification for GSIs I do add.

The single-table design philosophy treats indexes as first-class schema elements. Name them GSI1, GSI2, GSI3 and assign access patterns to each. This is covered in depth in the DynamoDB developer guide.

Indexes are leverage. Used sparingly, they make impossible queries trivial. Used carelessly, they are the fastest way to a surprise AWS bill.