DynamoDB Single-Table Design Basics

Introduction¶

DynamoDB is a key-value / document store with predictable performance at any scale — if you model for it. Unlike SQL, you can't add arbitrary queries later; every access pattern must be served by the partition/sort key or a secondary index. So you list your access patterns first, then design the table.

The key model¶

• Partition key (PK) — hashed to pick a physical partition. Determines data distribution. Query and GetItem require an exact PK.
• Sort key (SK) — items with the same PK are stored together, sorted by SK. Enables range queries: begins_with, between, >, <.
• Together (PK, SK) = the primary key, must be unique.

A Query on PK USER#123 with SK begins_with("ORDER#") returns all that user's orders, sorted, in one request.

Access-pattern-first¶

Write them down:

1. Get user by id
2. Get user's orders, newest first
3. Get order by id, with its line items
4. Get all orders in status SHIPPED (across users)  -> needs a GSI
5. Get products in a category

Then design keys so each pattern is a single Query/GetItem.

Single-table design¶

Put multiple entity types in one table, using key prefixes:

• Item collection — all items sharing a PK. Query PK=USER#123 gets the profile and the orders in one call (pattern 2 + a bit of 1).
• Composite SK (ORDER#<date>#<id>) — sorts orders by date for free.

Why one table: fewer round trips (one query returns related entities), one set of capacity/alarms, transactions across entity types. Cost: it's harder to reason about and change; the table looks like line noise.

Overloaded GSIs¶

A Global Secondary Index has its own PK/SK and its own partitions. For pattern 4 ("orders by status"), add a GSI:

GSI1PK = "STATUS#SHIPPED"   GSI1SK = "2026-08-27#ORDER#456"

Different entity types can reuse the same GSI with different meanings for GSI1PK/GSI1SK ("overloading") — one GSI serves many patterns. Projections: KEYS_ONLY, INCLUDE, or ALL (more storage/cost).

The hot-partition problem¶

DynamoDB spreads throughput across partitions by PK hash. If one PK gets disproportionate traffic (a celebrity user, a STATUS#PENDING GSI key that every new order writes to), that partition throttles even though the table has spare capacity.

Mitigations:

• Choose a high-cardinality PK. USER#123, not TENANT#acme for a big tenant.
• Write sharding — append a suffix STATUS#PENDING#<0-9> and query all shards in parallel. Trades read fan-out for write spread.
• On-demand capacity adapts faster than provisioned, but a single hot key still caps at the per-partition limit (~1000 WCU / 3000 RCU).

Query vs Scan¶

• Query — targeted, uses the key, fast, cheap. Always prefer.
• Scan — reads the whole table (or GSI), filters after. Slow, expensive, throttles. Only for admin/export jobs, and use Segment/TotalSegments for parallel scan.

FilterExpression runs after the read — it reduces the response size, not the consumed capacity. Filtering 1M items down to 10 still costs 1M items' reads.

When single-table is overkill¶

• Small app, few access patterns, low traffic — multiple simple tables are fine and much easier to understand.
• You genuinely need ad-hoc queries / analytics — use Athena over an S3 export, or a relational database.
• Team unfamiliar with DynamoDB — the learning curve of single-table under deadline pressure produces worse designs than a few clean tables.

Verification and troubleshooting¶

aws dynamodb describe-table --table-name app
aws dynamodb query --table-name app \
  --key-condition-expression "PK = :pk AND begins_with(SK, :sk)" \
  --expression-attribute-values '{":pk":{"S":"USER#123"},":sk":{"S":"ORDER#"}}'
# CloudWatch: ThrottledRequests, ConsumedReadCapacityUnits, per-GSI metrics

• ProvisionedThroughputExceededException / throttling with capacity to spare — hot partition. Check contributor insights (aws dynamodb update-contributor-insights) for the hot key; shard it or raise cardinality.
• ValidationException: Query key condition not supported — you tried a condition DynamoDB can't do on the key (contains, <> on PK). PK is exact match only; that's a GSI or a scan.
• A new query the product needs isn't possible — no index serves it. Add a GSI (backfills automatically), or restructure keys, or accept a scan for a rare admin query.
• GSI throttling but the base table is fine — GSIs have separate capacity; a low-cardinality GSIxPK (like a status enum) is a classic hot GSI. Shard it.
• Costs higher than expected — Scan somewhere, FilterExpression doing heavy lifting, or ALL projections on big GSIs. Check ConsumedCapacity per operation.
• Item too large — 400KB max per item. Move blobs to S3, store the key.

Related tools and reading¶

• On-site: JSON formatter.
• Related posts: Table partitioning strategies, Connection pooling explained.