DynamoDB Single-Table Design: An Architecture Note

Requirements: enumerate access patterns first

DynamoDB has no joins and no query planner, so a table design can only be evaluated against the list of access patterns it must serve. Before designing keys, write down every read and write pattern: "get user by ID", "list orders for user newest first", "find user by email", and so on. Each pattern must map to a Query against the base table or a GSI. Any pattern that ends in a Scan is a missing index you haven't designed yet.

The smallest suitable design is one table with generic PK and SK attributes, plus one GSI per access pattern that can't be served by the primary key. Start with the base table and add GSIs only when a pattern demands it, not one GSI per entity type.

Designing the primary key

Group related items under one partition key so a single Query fetches everything for an aggregate. For example, a user and their orders:

PK=USER#123, SK=PROFILE
PK=USER#123, SK=ORDER#2026-10-01#A1
PK=USER#123, SK=ORDER#2026-09-15#B7

With this layout, Query(KeyConditionExpression="PK = :pk", ExpressionAttributeValues={":pk":"USER#123"}) returns the user profile and all orders in one call. The sort key carries the entity type and a natural ordering (e.g., a date prefix), so "list orders newest first" is a Query with ScanIndexForward=false and Limit, not a sort in application code.

Secondary access patterns ("find user by email", "list orders by status") go on GSIs. A GSI is an alternate partition/sort key. It is eventually consistent, so a read-after-write on a GSI may lag behind the base table. Never use a GSI for a pattern that requires strong consistency.

Trust boundaries: enforce invariants at the storage layer

Application-side checks race. Use ConditionExpression on PutItem to enforce uniqueness, optimistic concurrency, and state transitions. For example, to guarantee an email is unique, write a separate item PK=EMAIL#[contact removed] with a condition that the item does not exist. For optimistic concurrency, store a version number and require attribute_exists(PK) AND version = :v on update. This is the only way to make the storage layer the trust boundary; client-side uniqueness checks will eventually fail under concurrency.

Reserve TransactWriteItems for true multi-item invariants (e.g., a write that must update a user and a counter atomically). Transactions consume roughly double the capacity units of individual writes, so a transactional update of two items costs about twice a conditional write plus a PutItem. Use them sparingly.

Operational checks and failure modes

Hot partitions are the dominant failure mode. A single partition key is limited to about 1,000 WCU / 3,000 RCU regardless of table-level provisioning. A GSI with a low-cardinality or skewed partition key can throttle base-table writes even when the table has spare capacity. Load-test with realistic key distribution and watch CloudWatch Contributor Insights to confirm no partition key dominates read or write consumption.

Item size is capped at 400 KB, and Query returns at most 1 MB per call. Large payloads belong in S3 with a pointer in the item. Explicitly handle pagination via LastEvaluatedKey. A Scan in a hot path is a design smell: scans consume read capacity linearly and do not parallelize by default.

Enable point-in-time recovery (PITR) for production tables, then perform a test restore to a new table and confirm item counts and recent writes match expectations. Inject throttling by temporarily lowering capacity in a test environment and confirm SDK retry/backoff keeps the application within latency budgets.

When to change the design

Split the table when access patterns diverge and the single-table key hierarchy becomes a constraint rather than a convenience. If you find yourself adding GSIs to work around the primary key, or if the 400 KB item limit forces you to split entities across partitions, a multi-table design is cheaper than the GSI overhead. The decision point is not the number of entities but the number of access patterns that can't be served by the smallest suitable design.