DynamoDB Single-Table Design: Modeling Around Access Patterns

Dean Jain

Senior Staff Software Engineer · Enterprise AI, Data & Cloud Architect

January 19, 2021 · 6 min read

DynamoDBAWSData ModelingNoSQL

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart TB
    subgraph REL["🗃️ Relational normalize, join at read"]
        direction LR
        U1["Users"]:::neutral -->|"JOIN"| O1["Orders"]:::neutral
        O1 -->|"JOIN"| I1["Items"]:::neutral
    end
    subgraph DDB["⚡ DynamoDB model around access patterns"]
        direction LR
        T["One table<br/>pre-joined item collection"]:::server -->|"1 query"| R["All of a user's orders"]:::good
    end
    classDef neutral fill:#ECECEC,stroke:#8A8A8A,stroke-width:2px,color:#0F172A
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A

Figure 1: The mindset flip relational normalizes and joins at read time; DynamoDB pre-shapes data around the query.

DynamoDB punishes you for modeling it like a relational database. There are no joins at scale, and the query patterns you didn’t plan for are the ones that hurt. The whole discipline inverts what you learned with SQL: instead of normalizing entities and joining them later, you start from your access patterns and shape the data to serve them often collapsing everything into a single table. Here’s how that works, and why it’s faster (and cheaper) when you do it right.

Summary

Model around access patterns, not entities. In SQL you design the schema then write queries. In DynamoDB you list the queries first, then design keys to serve them.
The partition key + sort key are everything. The partition key spreads data; the sort key orders and groups related items so one query returns a whole “item collection.”
Single-table design gives you materialized joins. Storing related items together under shared keys lets one query fetch what a relational DB would join at lower cost and operational burden.
Two index types, two rulebooks. LSIs share the partition key and must exist at table creation; GSIs can use a new partition key, be added anytime, and act as sparse indexes.
It’s not for everything. Complex, deeply nested, unpredictable-query workloads may fit a document store better. DynamoDB rewards known, high-scale access patterns.

1. The mindset flip: queries first

In the relational world you normalize split data into clean tables, then reassemble it with joins at read time. That’s flexible: you can ask questions you didn’t anticipate. DynamoDB makes the opposite trade. It’s a key-value store built for high-speed, low-latency access at any scale, and it gets there by refusing to do joins across a table at query time.

So the design process flips. You don’t start with entities; you start with a list of every access pattern your application needs “get a user’s profile,” “list a user’s orders newest-first,” “find all orders in a status.” Then you design keys so each of those is a single, fast lookup. The schema is a consequence of the queries, not the other way around.

This feels backwards if SQL is your home, but it’s the entire source of DynamoDB’s predictable performance: every supported query is pre-planned, so there are no surprise table scans or expensive runtime joins. The cost of that speed is rigidity patterns you didn’t design for are genuinely hard to add later. That’s the bargain.

2. Partition key + sort key: the whole game

Every item lives in a table as a bag of attributes (max 400 KB per item), addressed by a primary key. That key is where all the design leverage is:

Simple key just a partition key. Good for pure key-value lookups.
Composite key a partition key + sort key. This is where DynamoDB gets powerful.

The partition key decides which physical partition an item lands on DynamoDB hashes it to spread data and load evenly (so a well-chosen partition key avoids “hot partitions”). The sort key orders items within a partition and lets you group related items into an item collection: everything sharing a partition key, sorted, fetchable in one query.

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart LR
    PK["🔑 Partition key<br/>USER#42"]:::gate --> COL["📦 Item collection (sorted by SK)"]:::server
    COL --> A["SK: PROFILE"]:::obs
    COL --> B["SK: ORDER#2024-08-01"]:::obs
    COL --> C["SK: ORDER#2024-08-07"]:::obs
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef obs fill:#AED6F1,stroke:#2E86C1,stroke-width:2px,color:#0F172A

Figure 2: One partition key gathers a sorted item collection a profile and all its orders, fetched in a single query.

This is the trick that replaces joins: by giving a user’s profile and all their orders the same partition key and distinct sort keys, “get a user and their recent orders” becomes one query over a contiguous, sorted range no join, no second round-trip.

3. Indexes: LSI vs GSI

What about queries that don’t fit your primary key? That’s what secondary indexes are for and the two kinds follow different rules:

	Local Secondary Index (LSI)	Global Secondary Index (GSI)
Partition key	Same as the table	Can be a different attribute
When created	Only at table creation	Anytime, after the fact
Limit	Up to 5	Up to 20
Sparse?	No	Yes only items with the indexed attribute appear

Figure 3: LSIs reshape sort order under the same partition key; GSIs unlock entirely new access patterns.

The practical read: reach for a GSI most of the time it’s flexible, addable later, and its sparse behavior is a feature (index only the items that have the attribute, e.g. only orders currently OPEN). Use an LSI when you need an alternate sort order under the same partition key and you know that need up front because you can’t add one after the table exists.

4. Why “single-table”?

Putting multiple entity types users, orders, items into one table sounds wrong to a relational eye. But it buys three concrete things:

Materialized joins. Related entities sharing a partition key are physically co-located, so the “join” already happened at write time. One query, no runtime assembly.
Lower cost on large items. Fewer requests and less data movement to satisfy a pattern, which directly lowers your read/write capacity bill.
Less operational burden. One table to provision, monitor, scale, back up, and secure instead of many.

The mechanics: design a generic partition key and sort key (often literally named PK and SK), then encode entity type into the key values (USER#42 / PROFILE, USER#42 / ORDER#...). GSIs overlay the other access patterns on top. It’s denormalized and duplicated on purpose storage is cheap, and predictable single-digit-millisecond reads are the prize.

5. Two companions: DAX and Streams

Two features round out a production setup:

DAX (DynamoDB Accelerator) a fully managed, in-memory cache that drops read latency from milliseconds to microseconds, even at millions of requests/sec. It’s read-through/write-through and API-compatible, so you bolt it on without changing application logic when a read-heavy workload needs it.
DynamoDB Streams an ordered, near-real-time log of every item create/update/delete (24-hour retention). It’s your change-data-capture hook: trigger Lambdas, replicate, or fan out events on writes, with no performance impact on the table.

6. When not to reach for it

DynamoDB is not a default. It shines when your access patterns are known, high-scale, and latency-sensitive predictable, concurrent, spiky traffic that needs consistent response times. It’s a poor fit when you have complex, deeply nested structures or unpredictable, ad-hoc query needs that you can’t enumerate up front. For schema-flexible, query-flexible workloads (rich user profiles you’ll slice many ways), a document database like DocumentDB or MongoDB is the better tool.

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart TD
    Q1{"Access patterns<br/>known up front?"}:::gate -->|"no"| DOC["📄 Document DB<br/>(DocumentDB / MongoDB)"]:::obs
    Q1 -->|"yes"| Q2{"Deeply nested /<br/>ad-hoc queries?"}:::gate
    Q2 -->|"yes"| DOC
    Q2 -->|"no"| Q3{"High scale +<br/>low latency?"}:::gate
    Q3 -->|"yes"| DDB["⚡ DynamoDB<br/>single-table"]:::server
    Q3 -->|"no"| SQL["🗃️ Relational"]:::neutral
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A
    classDef obs fill:#AED6F1,stroke:#2E86C1,stroke-width:2px,color:#0F172A
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef neutral fill:#ECECEC,stroke:#8A8A8A,stroke-width:2px,color:#0F172A

Figure 4: DynamoDB rewards known, high-scale access patterns; reach for a document store when queries are flexible or unknown.

Why it matters: the single biggest DynamoDB mistake is bringing relational instincts to it normalizing entities and hoping to join later. Flip the order: enumerate your access patterns first, then design keys and indexes to make each one a single fast query. Do that and you get predictable performance at any scale; skip it and you’ll fight the database forever.

References

The DynamoDB Book Alex DeBrie’s definitive guide to single-table design
DynamoDB single-table design (AWS) official modeling guidance
DynamoDB Streams · DAX change capture and caching