DynamoDB GSI Design: When to Add One vs Reshape Your Sort Key
A new access pattern shows up. Someone needs to query users by signup date, or orders by status, or projects by creator. The reflex is to add a GSI. It’s a clean mental model: GSI = index = make a query fast.
But every GSI has a real cost. It’s a second copy of your data with its own throughput, its own storage, and its own write amplification. A table with three GSIs pays 4x the write cost of a table with none. And half the time, the access pattern someone was about to add a GSI for can be served by reshaping the existing sort key.
This post is the decision framework I use, with concrete examples of both calls.
The two questions
Before adding any index, answer these two questions about the new access pattern:
- Does the query share a partition with an existing access pattern?
- Is the query a different cut of the same data, or fundamentally different data?
If the answer to (1) is yes and (2) is “different cut”, you almost certainly want to reshape the sort key. If (1) is no, you need a GSI. If (2) is “fundamentally different data”, you might not need either - you might need a separate item.
Here’s what that looks like in practice.
When a sort key reshape is the right call
The sort key in DynamoDB is not just an identifier - it’s a hierarchy. A composite sort key like STATUS#pending#2026-05-01#order_01 lets you query at any prefix:
begins_with("STATUS#pending")- all pending ordersbegins_with("STATUS#pending#2026-05")- pending orders from MayBETWEEN "STATUS#pending#2026-05-01" AND "STATUS#pending#2026-05-07"- pending orders from a week- exact match - one specific order
If your new access pattern is a different slice of items that already share a partition, reshaping the sort key gets you there without a GSI.
Example: orders by customer, sorted by status
You start with this:
PK: CUSTOMER#<customerId>
SK: ORDER#<ulid>This handles “list orders for a customer, newest first” in one query. Now someone asks for “list pending orders for a customer.”
You could add a GSI with gsi1pk = CUSTOMER#<id>#STATUS#pending. Or you can reshape the SK:
PK: CUSTOMER#<customerId>
SK: ORDER#<status>#<ulid>Now Query(pk=CUSTOMER#c_01, sk begins_with ORDER#pending) does it. No GSI. Same partition.
The catch: when an order’s status changes, you have to delete the old item and write a new one (DynamoDB doesn’t let you mutate a key). That’s two writes per status transition instead of one. Whether that’s worth it depends on how often statuses change vs how often you query by status. For most order systems, status transitions are rare (pending → confirmed → shipped → delivered, four times in the order’s lifetime) and “list pending orders” runs all day on the dashboard. Reshape wins.
The e-commerce orders pattern goes deeper on this trade-off.
Example: users by signup date
You have:
PK: TENANT#<tenantId>
SK: USER#<userId>Now you need “show me users who signed up in the last 30 days.”
You could add a GSI keyed on createdAt. Or you can reshape:
PK: TENANT#<tenantId>
SK: USER#<createdAt>#<userId>Same partition, sorted by signup date. Query(pk=TENANT#t_01, sk BETWEEN USER#2026-04-01 AND USER#2026-04-30) answers the question. The trade-off this time is the direct-lookup-by-userId problem - you can’t GetItem without knowing createdAt. Use ULIDs as user IDs and the reshape becomes free: the timestamp is in the ID, ordering and direct lookup both work.
When you actually need a GSI
A sort key reshape only works if the new query lives inside the same partition. If the query crosses partitions, you need a GSI. There’s no way around it.
The clearest signal: the new query starts with a different identifier than your PK.
Example: login by email
PK: TENANT#<tenantId>
SK: USER#<userId>The login flow has the user’s email and needs the user record. There is no tenantId in the request - that’s what login is supposed to figure out. You can’t reshape your way to a tenant-scoped key from data outside the tenant scope. You need a GSI keyed on email:
gsi1pk: USER_EMAIL#<email>
gsi1sk: USER#<userId>This is a genuinely cross-partition lookup. GSI is correct.
Example: projects by creator
PK: TENANT#<tenantId>
SK: PROJECT#<ulid>“Show me my projects” - across all tenants the user has access to, or scoped to one. If scoped to one tenant, the request has both tenantId and userId, so a sort key reshape PROJECT#<userId>#<ulid> works (with the same write trade-off as before). If cross-tenant, no reshape can serve it. GSI on USER#<userId> is the right call.
The SaaS multi-tenant pattern uses both techniques: tenant-scoped queries get sort-key reshapes, cross-tenant queries get GSIs.
When you need neither (the third option people forget)
Sometimes the right answer is to write the data differently in the first place. Two common cases:
Denormalize a counter or aggregate: “Total orders for this customer” doesn’t need a GSI or a query - it needs a counter on the customer record. Increment it atomically when an order is created. The query becomes a GetItem.
Add a junction record: many-to-many relationships (article tags, project members, group invitations) are usually best served by a dedicated item per relationship - not by stuffing the relationship into an existing item’s GSI. The content management pattern uses this for article tags: a TAG#<tag> / ARTICLE#<id> item per tag-article pair, queryable from either side without any GSI on the article entity itself.
Both of these reduce GSI count by removing the need for them entirely.
The cost difference, concretely
A 1KB item with three GSIs (all ALL projection): 1 WCU for the base table write, 3 WCUs for GSI replication, 4 WCUs total per write.
At 200 writes/second sustained, that’s 800 provisioned WCUs - roughly $80/month. The same workload on one base table is $20/month. Each GSI is real money, every month, forever.
GSIs also charge for storage (each one is a full copy of the projected attributes). And for read capacity when you query them. There is no free GSI.
This is also why overloaded GSIs matter. If three access patterns can share a single GSI because their key prefixes don’t collide, you pay for one GSI’s worth of write amplification, not three. The SaaS multi-tenant pattern shows the math in detail.
The decision tree
When a new access pattern shows up:
- Can it be served by a denormalized counter or stored aggregate? Use that. No index needed.
- Does it share a partition with an existing pattern? Reshape the sort key. Watch for direct-lookup edge cases (ULIDs help).
- Is it a many-to-many relationship? Add a junction item, not a GSI on the parent.
- Is it cross-partition? GSI. Try to overload it onto an existing GSI if the prefixes don’t collide.
- Is it a reporting query (aggregations, full-table scans)? It probably shouldn’t be a DynamoDB query at all. Stream to a data warehouse.
Most teams I’ve seen jump straight to step 4. That’s how you end up with six GSIs on a single entity and a write bill three times bigger than it needs to be.
What to actually write down
For every entity in your schema, keep two lists:
- Access patterns the entity supports - which key, which index
- Access patterns it doesn’t - and what you’d do if those become real (see unsupported queries)
When a new query shows up, check list (1) first. If reshaping the SK serves it without breaking existing patterns, do that. If it requires a new index, run the cost math: is this query worth $20-80/month forever?
A GSI is a decision you can’t easily walk back. Reshaping a sort key is also hard to walk back, but at least it doesn’t compound your write cost. Both deserve more thought than the reflex of “add an index.”
singletable.dev is built around making the GSI vs SK trade-off visible at design time, not at the bill review. The pattern library on the blog is full of worked examples - the SaaS multi-tenant pattern and e-commerce orders pattern both show sort-key reshapes that other writeups solve with extra GSIs.
