Modeling for DynamoDB

Lesson 2 of 5 in Data Modeling and Schema Evolution.

Relational modelling starts from the entities and lets the query planner work out how to combine them. DynamoDB modelling starts from the access patterns and derives keys that satisfy them, and the reason is not stylistic: there is no join, and a Query must specify exactly one partition key value. Anything the engine was not told how to find, it cannot find — it can only Scan.

So the method is literally an enumeration. Write down every request the application will make, with its predicate, its expected result set and its latency budget. Then design keys so that each one is a GetItem or a Query on a single partition key value. If a pattern cannot be expressed that way, it needs a secondary index, a second access path, or a different store.

Single-table design falls out of this. Because related items can only be fetched together if they were stored together, the table names its keys generically — PK and SK rather than CustomerId and OrderId — and encodes the entity type into the value: PK=CUST#42 with sort keys PROFILE, ADDR#1, ORDER#2026-03-14#9931. One Query on PK=CUST#42 then returns the customer and their orders in a single call.

Be precise about what that buys, because the case is narrower than the enthusiasm around it. It does not make a query faster. It makes a composite view cost one round trip instead of two, and it makes the related items transactionally and operationally co-located. Everything else — fewer indexes, consistency across entity types — follows from that. The costs are real too: the table is hard to read by eye, and a genuinely new access pattern may need a new index and a backfill.

Four mechanisms do the work in a single-table design, and each has an exact property worth memorising.

Item collections are all the items sharing one partition key value, stored contiguously and ordered by sort key. That is why Query can return a prefix range of one cheaply — "the customer plus their last ten orders" is one contiguous read. Two limits shape the model: a collection that attracts disproportionate traffic is a hot partition bounded by the per-partition ceiling, and a collection in a table that has a local secondary index is capped at 10 GB, which is the main practical reason to prefer a global secondary index.

Composite sort keys concatenate fields so that begins_with cuts the collection at any hierarchy level: ORDER#2026#03#14#9931 can be queried by year, by month, by day, or compared with between for a range. Two rules make it work — components run coarse to fine, because a prefix match only cuts from the left; and anything compared by range must be lexicographically ordered, which is why dates are stored zero-padded as 2026-03-14 and numbers are padded to a fixed width.

Index overloading reuses one index for several access patterns. A table may carry up to 20 global secondary indexes by default, which sounds generous until a single-table design has eleven entity types; so the index keys get generic names — GSI1PK, GSI1SK — and each entity type populates them with whatever it needs to be found by. An order sets GSI1PK=STATUS#OPEN, an employee sets GSI1PK=DEPT#FINANCE, and one index serves both because every query supplies the prefix.

Sparse indexes are what make that safe, and they are a design tool in their own right. A global secondary index contains only items that have its key attributes, so writing an unresolved attribute on open tickets alone turns the index into exactly the set of open tickets — cheap to scan however many closed ones exist, and closing a ticket removes it from the index by deleting the attribute. It is also a troubleshooting answer: items "missing" from an index are usually items that never had the index key, though genuine propagation takes about a second and an under-provisioned index can throttle the base table's writes.

The same modelling job, done in two opposite orders
Step	Relational	DynamoDB
1	identify the entities and their relationships	enumerate every access pattern, with its predicate and latency budget
2	normalise to remove update anomalies	choose a partition key with enough cardinality to spread the writes
3	add indexes where the planner needs help	design sort keys so each pattern is a Query on one partition key value
4	write queries; the planner decides how to combine tables	add an overloaded index for the patterns the primary keys cannot serve
A new question arrives	write new SQL — the model usually already supports it	check it against the keys; a genuinely new one may need an index and a backfill
What goes wrong	a query the planner cannot optimise, fixed with an index or a rewrite	a hot partition, or a pattern that can only be answered by a Scan

Four DynamoDB modelling failures and their one-line fixes

A low-cardinality partition key. A date, a status, a device type, or a literal string: writes concentrate on a handful of partitions and the per-partition ceiling of roughly 3,000 read units and 1,000 write units per second applies no matter what the table is provisioned for. Fix: a higher-cardinality key, or write sharding with a calculated suffix — and accept the fan-out cost on reads.

A sort key that is not lexicographically ordered. 14/3/2026 does not sort, 2026-03-14 does; 9 sorts after 10 unless numbers are padded to a fixed width. Range queries on an unsorted key return nonsense rather than an error.

An under-provisioned global secondary index. Writes to the base table propagate to the index, so an index with less write capacity than the table creates backpressure that throttles the base table's writes. Fix: index write capacity equal to or greater than the table's, and project only the attributes the index actually needs.

Expecting a Scan to be the answer. "Find all items where X" is a Scan unless something indexed X. The idiomatic answer is a sparse index — write the key attribute only on the items that qualify — which turns the index itself into the filter.

Key terms: Single-table design, Item collection, Composite sort key, Index overloading, Sparse index, Write sharding

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.