DynamoDB single-table design

"Design a DynamoDB table for these five access patterns."

What it is

Single-table design puts entities of different types into one DynamoDB table, using generic partition and sort key attributes (PK, SK) whose values encode both the entity type and its identity. A customer is PK=CUST#42, SK=PROFILE; that customer's orders are PK=CUST#42, SK=ORDER#2026-08-03#9f2a. A query for PK=CUST#42 with begins_with(SK, "ORDER#") returns all their orders in date order, from one partition, in one request.

The reason it exists is that DynamoDB has no joins. In a relational database you normalise and join at read time; DynamoDB has no query planner and no join operator, so the "join" must be done at write time by placing related items in the same partition. Single-table design is that idea taken to its conclusion.

The confusion worth clearing: it is not "one table because tables cost money" (they do not, meaningfully). It is "one table so that a single Query can return a heterogeneous set of related items", which is the only mechanism DynamoDB offers for retrieving related data in one round trip.

The problem it solves

DynamoDB gives you single-digit millisecond latency at effectively unbounded scale, and it does that by refusing to do anything that cannot be done in constant time. No joins, no ad hoc queries, no aggregation.

That constraint inverts the design process. The relational habit is: model the entities, then figure out the queries. In DynamoDB the queries come first, because the key schema is the query plan and you cannot change it later without rewriting the data.

Say "access patterns first, schema second" before you draw anything. It is the sentence interviewers are listening for, and everything below is an elaboration of it.

Mechanics

The primitives, precisely

Partition key (PK) determines physical placement. A GetItem or Query must supply it exactly; there is no scanning across partitions except a full table Scan, which is not a query pattern.
Sort key (SK) orders items within a partition and supports range conditions: begins_with, between, >, <. All the expressiveness lives here.
Global secondary index (GSI) is a different key schema over the same data, maintained asynchronously. Eventually consistent, its own capacity, and projections matter: an index that does not project an attribute forces a fetch back to the base table.
Local secondary index (LSI) shares the partition key with a different sort key. Strongly consistent, must be created with the table, and imposes a 10 GB limit per partition key value, which is a real constraint that GSIs do not have.

The limits worth having memorised: 400 KB per item, 1 MB per Query result page, 100 items per TransactWriteItems (at roughly twice the write cost), and 3,000 read units or 1,000 write units per partition before throttling.

The five-step method

1. Write down the access patterns as a numbered list. Not entities. Patterns, each with its input and its expected result:

1. Get a customer's profile by customer id
2. List a customer's orders, most recent first, paginated
3. Get one order with all its line items, in one request
4. List all orders in a given status, across customers (ops dashboard)
5. Get the order that carries a given external payment reference

2. Identify the item collections. Which items are always fetched together? Pattern 3 says an order and its line items are one collection. Pattern 2 says a customer and their orders are one collection.

3. Choose the partition key from the most common access pattern. Patterns 1, 2 and 3 all key on customer or order, so PK is the entity identity.

4. Design the sort key so range conditions serve the patterns. Sort keys are where the expressiveness is, and a compound sort key with a # hierarchy is the standard idiom.

5. Add GSIs for patterns the base table cannot serve. Patterns 4 and 5 query on something other than the customer or order id, so they each need an index.

The table

Entity	PK	SK	GSI1PK	GSI1SK
Customer profile	`CUST#42`	`PROFILE`
Order	`ORDER#9f2a`	`METADATA`	`STATUS#SHIPPED`	`2026-08-03T14:22Z`
Order line item	`ORDER#9f2a`	`ITEM#001`
Customer-order pointer	`CUST#42`	`ORDER#2026-08-03#9f2a`
Payment reference	`PAY#ch_3Ab`	`ORDER#9f2a`

And the queries each pattern becomes:

# 1. Customer profile: a GetItem. Single-digit ms.
table.get_item(Key={'PK': 'CUST#42', 'SK': 'PROFILE'})

# 2. Customer's orders, newest first, paginated. One partition, sorted by the
#    date embedded in the sort key. ScanIndexForward=False reverses it.
table.query(
    KeyConditionExpression=Key('PK').eq('CUST#42') & Key('SK').begins_with('ORDER#'),
    ScanIndexForward=False, Limit=25)

# 3. Order plus all its line items in ONE request. This is the whole point of
#    single-table design: the "join" happened at write time by co-locating them.
table.query(KeyConditionExpression=Key('PK').eq('ORDER#9f2a'))
#    -> [{SK: METADATA, ...}, {SK: ITEM#001, ...}, {SK: ITEM#002, ...}]

# 4. Orders by status, newest first. GSI, so eventually consistent: fine for
#    an ops dashboard, not fine for an authorisation decision.
table.query(IndexName='GSI1',
    KeyConditionExpression=Key('GSI1PK').eq('STATUS#SHIPPED'),
    ScanIndexForward=False)

# 5. Order by payment reference. A sparse item type: only orders with a payment
#    have one, so this "index" costs nothing for orders that don't.
table.query(KeyConditionExpression=Key('PK').eq('PAY#ch_3Ab'))

Pattern 3 is the demonstration. In a relational schema that is a join; here it is a single Query against one partition, which is why it runs in single-digit milliseconds at any table size.

Two techniques worth naming

GSI overloading. GSI1PK and GSI1SK are generic. Different entity types put different values in them, so one index serves several access patterns. This matters because you get a limited number of GSIs per table and each one costs capacity.

Sparse indexes. An item only appears in a GSI if it has the index's key attributes. So writing GSI2PK only on orders needing manual review gives you an index containing exactly those orders, and querying it is proportional to the review queue rather than to the table. This is the cheapest "query for the exceptional case" mechanism DynamoDB offers and it is underused.

Hot partitions

A partition supports about 3,000 read units or 1,000 write units. Exceed that on one key and you throttle regardless of how much table capacity you have provisioned.

The classic mistake is a partition key with low cardinality or temporal skew: PK=ORDERS#2026-08-03 puts every order for a day in one partition, so today's partition takes 100 percent of the write traffic and yesterday's takes none.

Write sharding is the fix:

# Spread writes across N synthetic shards; reads fan out and merge.
shard = random.randint(0, 9)
pk = f"ORDERS#2026-08-03#{shard}"
# Reading a day now means 10 queries in parallel instead of 1. That is the
# trade: write throughput multiplied by 10, read cost multiplied by 10.

Choose the shard count from the throughput you need, not by habit: ten shards buys 10,000 writes per second on that logical key, and costs ten queries per read.

Transactions and optimistic concurrency

# Condition expression: optimistic concurrency without a lock. The update
# applies only if the version is what we read, so a concurrent writer fails
# rather than silently overwriting.
table.update_item(
    Key={'PK': 'ORDER#9f2a', 'SK': 'METADATA'},
    UpdateExpression='SET #s = :new, version = version + :one',
    ConditionExpression='version = :expected',
    ExpressionAttributeValues={':new': 'SHIPPED', ':expected': 3, ':one': 1})

TransactWriteItems gives you all-or-nothing across up to 100 items at roughly double the write cost. Use it for genuine multi-item invariants and not as a default, because most of what a relational developer reaches for a transaction for is achievable with a condition expression on a single item, which is far cheaper.

A worked example: the cost of getting the key wrong

An e-commerce table, 50 million orders, 2,000 writes per second at peak.

First design: PK=ORDER#{id}, and to list a customer's orders, a GSI with GSI1PK=CUST#{id}.

The problem, discovered later: the ops dashboard needs orders by status and by date range and filtered by region. Each combination wants its own GSI, and there is a limit on GSIs per table. Worse, STATUS#PENDING is a low-cardinality partition key: at any moment most orders are in two or three statuses, so a handful of index partitions take all the traffic and throttle.

The fix: make the GSI partition key higher cardinality by composing it with something that spreads:

GSI1PK = STATUS#PENDING#2026-08-03     # status plus day
GSI1SK = REGION#eu-west#ORDER#9f2a     # region first so it prefixes cleanly

Now querying pending orders for a day hits one partition of reasonable size, and adding a region filter is a begins_with on the sort key rather than a new index. The cost is that "all pending orders ever" becomes N queries, one per day, which is the correct outcome, because that query was never going to be efficient and making it awkward is a feature.

The cost math, which is what makes this concrete:

Poorly modelled: listing a customer's 20 orders requires a GSI query
  returning keys, then 20 GetItems to fetch the bodies.
  = 1 query (1 RCU) + 20 GetItems (20 x 0.5 RCU eventually consistent)
  = 11 RCU per page view

Well modelled: one Query against the customer's partition returns the
  order items directly (the pointer items carry the summary fields the
  list view needs).
  20 items x ~1 KB = 20 KB = 2.5 RCU (eventually consistent)

4.4x fewer read units for the same page. At 500 page views/sec:
  poor:  5,500 RCU  ~= $1,800/month provisioned
  good:  1,250 RCU  ~=   $410/month

That factor is why "a badly modelled table costs several times a well modelled one" is not rhetoric. The mechanism is over-fetching and fetch-back from indexes, and it compounds with traffic.

The thing I would say out loud: the design above serves five access patterns. A sixth arriving in six months may not fit, and the honest answer is that adding an access pattern to DynamoDB sometimes means a backfill. That is the cost of the model, it should be stated up front, and it is the strongest argument for not using DynamoDB when the query patterns are genuinely unknown.

Production evidence

Amazon's own guidance states that as a general rule you should maintain as few tables as possible in a DynamoDB application, and the best-practices documentation on modelling relational data, GSI overloading and sparse indexes is the primary source for every technique above.

Rick Houlihan's re:Invent talks ("Advanced Design Patterns for DynamoDB") are the canonical treatment and the origin of most of the single-table vocabulary. He ran the team that migrated large parts of Amazon's own workloads off relational databases, and the talks include the migration cost data.

Alex DeBrie's The DynamoDB Book is the practical reference and is where most teams learn the numbered-access-patterns method.

The original Dynamo paper (DeCandia et al., SOSP 2007) explains the constraint underneath all of it: consistent hashing over a partition key with no cross-partition coordination is what buys the predictable latency, and it is also what makes joins impossible.

The debate

The credible criticism of single-table design is that it optimises for a scale most applications never reach, and costs comprehensibility every day until then. A table where PK might be a customer, an order or a payment reference is genuinely harder to read, harder to query ad hoc, and harder to onboard someone onto. Analytics is awkward, and every new access pattern is a design exercise rather than a WHERE clause.

The counter-argument is that the alternative in DynamoDB is not "a nicer schema", it is multiple round trips, which at scale is both slower and more expensive.

My position: use single-table design when you have chosen DynamoDB for a reason (known access patterns, extreme scale, predictable latency, serverless operations) and when the entities have genuine parent-child relationships you retrieve together. Use multiple tables when the entities are unrelated, because putting a users table and an audit-log table in one table buys nothing. And be honest that the real decision was made earlier: if the query patterns are unknown or the domain is relational, the mistake was choosing DynamoDB, not choosing how to model in it.

DynamoDB is the wrong choice for ad hoc queries, for anything needing joins or aggregation, for a domain where access patterns are still being discovered, or for low-scale applications where a managed Postgres is cheaper, more flexible and more familiar. Saying that unprompted is a stronger signal than any modelling technique, because the most common DynamoDB failure is not bad modelling, it is choosing DynamoDB for a relational problem.

Follow-up Q&A

"Design a table for these five access patterns." Number the patterns first and do not draw anything until they are on the board. Identify which items are always fetched together, because those become item collections sharing a partition key. Pick the partition key from the most frequent pattern, design the sort key so range conditions serve the rest, and add GSIs only for patterns the base table cannot serve. Then, unprompted, name the hot-partition risk of the key you chose and how you would shard it.

"What is the difference between a GSI and an LSI, and when would you use each?" A GSI has its own partition key and its own capacity, is maintained asynchronously so reads are eventually consistent, and can be added after the table exists. An LSI shares the base table's partition key with a different sort key, supports strongly consistent reads, must be created with the table, and imposes a 10 GB limit per partition key value. In practice I default to GSIs: the flexibility of adding one later matters more than strong consistency on an index, and the 10 GB LSI limit is a trap that only bites once a customer gets large.

"A customer with a million orders. What breaks?" The partition. Every order shares PK=CUST#{id}, so the partition grows without bound and takes all that customer's read and write traffic against a per-partition ceiling of roughly 3,000 read units. The fix is to bound the partition by composing the key with time, PK=CUST#42#2026-08, so listing recent orders queries one or two monthly partitions and the partition size is bounded by a month of activity. It makes "all orders ever" a fan-out, which is the correct trade because that query was never going to be cheap.

"How do you handle a query pattern you didn't anticipate?" Three options, worst to best. A Scan with a filter, which works and does not scale and is a temporary measure only. A new GSI, which DynamoDB backfills online but costs capacity and counts against the index limit. Or, if the pattern is analytical rather than operational, stream the table to something else: DynamoDB Streams into a Lambda into OpenSearch or S3, and query there. The last is usually right, because DynamoDB is not an analytics database and forcing analytical queries into it is how you get an unmaintainable index sprawl.

"When would you not use DynamoDB?" When the access patterns are unknown, because the key schema is the query plan and changing it means rewriting the data. When the domain needs joins or aggregation. When ad hoc querying is a requirement for support or operations. And below a certain scale, where a managed relational database is cheaper, more flexible and something the whole team already understands. The most expensive DynamoDB mistake is not bad modelling, it is choosing it for a relational problem and then discovering the constraint six months in.

Common misconceptions

The most common is that single-table design is about saving on table costs. Tables are effectively free; the point is that a single Query can return a heterogeneous set of related items, which is DynamoDB's only mechanism for retrieving related data in one round trip.

The second is that a GSI behaves like a relational index. It is an asynchronously maintained copy of the data with its own key schema, its own capacity and its own throttling, and it is eventually consistent. Reading your own write from a GSI is not guaranteed.

The third is treating Scan as a query. It reads the whole table and its cost grows with table size, which means it works fine in development and becomes an incident in production.

Interview delivery note

Open with the sentence the round is listening for: "Access patterns first, schema second." Then actually do it: number the patterns on the board before drawing any keys.

Then: "Items that are always fetched together share a partition key, so the join happens at write time. The sort key is where the expressiveness lives: a compound key with a hierarchy lets one partition serve several patterns through begins_with. GSIs only for patterns the base table genuinely can't serve, and I'd overload them so one index serves several patterns."

The depth signal is naming the failure mode of your own key before being asked: "the risk with keying on customer is a customer with a million orders, which makes an unbounded hot partition. I'd bound it by composing the key with a month." And the strongest close is the scoping honesty: "this serves the five patterns we listed. A sixth might need a backfill, which is the real cost of the model, and if the patterns are genuinely unknown then DynamoDB was the wrong choice, not the schema."

The Staff & Team Lead Interview Reference