Skip to content

Single-Table Design

Single-table design is the practice of storing multiple entity types in one DynamoDB table, using generic key attributes and prefixed key values so that related items sit in the same partition and can be retrieved in a single query.

It exists because DynamoDB has no joins. If you need a customer and their orders together, a relational database joins two tables. DynamoDB can’t. Single-table design solves this by putting the customer and the orders in the same partition from the start, so one Query returns both.

What it actually looks like

In a relational schema, entities live in separate tables with meaningful column names. In a single-table DynamoDB schema, entities share a table with generic key names, and the values carry the meaning:

pkskEntity data
TENANT#t_01#METADATA{ name: "Acme Corp", plan: "pro" }
TENANT#t_01#SUBSCRIPTION{ seats: 10, billingCycle: "monthly" }
TENANT#t_01USER#u_01{ name: "Alice", role: "admin" }
TENANT#t_01USER#u_02{ name: "Bob", role: "member" }
TENANT#t_01PROJECT#01HVMK3P2Q{ title: "Q1 Roadmap", status: "active" }

Four entity types. One partition. Query(pk = TENANT#t_01) returns the whole tenant in a single round trip. Query(pk = TENANT#t_01, sk begins_with "USER#") returns just the users.

That is the entire value proposition: co-location replaces joins.

The three mechanics that make it work

Prefixed key values. Every key value carries a type prefix: USER#, ORDER#, PROJECT#. Prefixes make sort key begins_with queries act as a type filter, and they keep different entity types from colliding on the same key.

Composite keys. Sort keys concatenate multiple values to encode hierarchy: ORDER#01HVMK#ITEM#prod_abc. Because sort keys are ordered lexicographically, a hierarchy encoded left-to-right can be queried at any level of specificity.

GSI overloading. Generic index attributes named gsi1pk and gsi1sk hold different key patterns for different entity types. Because the prefixes don’t collide, one index serves several unrelated access patterns, and you pay for one index instead of five.

Supporting these are sparse indexes for querying subsets, and careful partition key selection to avoid hot partitions.

The design process

Single-table design is access-pattern-driven. The order of operations is not negotiable, and reversing it is the single most common cause of failure:

  1. List every access pattern first. “Get user by email.” “List a tenant’s projects, newest first.” “Get all items in an order.” Every one, before touching keys.
  2. Group patterns by what they retrieve together. Patterns that fetch related data in one call determine your partitions.
  3. Design the primary key to serve as many patterns as possible without an index.
  4. Add GSIs only for patterns the primary key can’t serve. Usually cross-partition lookups: login by email, admin enumeration, status queues.
  5. Verify coverage. Every access pattern should resolve to a GetItem or a Query. If any resolves to a Scan, the design is incomplete.

Teams that design keys before listing access patterns end up with a schema that serves half their queries and requires a backfill migration to serve the rest.

What it costs you

The benefits are real and so are the costs. Being honest about both is what separates a working schema from a cautionary tale.

Schema rigidity. Adding an access pattern that the key structure doesn’t support may require rewriting keys across every existing item. In Postgres you add a column and an index. Here you write a migration that touches millions of records.

Readability. gsi1pk tells a new engineer nothing. Every schema change requires understanding the whole table, not one entity.

Weak analytics. DynamoDB is an OLTP store, and single-table design makes ad-hoc queries harder, not easier. Reporting needs a separate analytical store.

Coupled stream processing. DynamoDB Streams are per-table. One table means one stream carrying every entity type, so every consumer has to filter.

Organisational coupling. If two teams share a table, they now coordinate on GSI allocation and key conventions for every change.

When it’s the wrong choice

Single-table design is a tool, not a default. It’s the wrong call when:

  • Your access patterns are still changing (pre-product-market-fit)
  • Nobody on the team can confidently own the schema
  • You have fewer than about six access patterns
  • Reporting and analytics are significant requirements
  • Multiple services or teams would share the table

Multi-table DynamoDB is a legitimate design, not a failure. For the full version of this argument, see When DynamoDB Single-Table Design Is the Wrong Choice.

The consensus has shifted, and it’s worth understanding how.

Rick Houlihan, who popularised the approach, described aggressive index overloading as something that should be considered deprecated. DynamoDB now supports 25 GSIs per table and on-demand capacity, which removes much of the pressure that made extreme overloading necessary.

What replaced it is pragmatic single-table design: co-locate data that’s genuinely read together, use fewer and clearer indexes, and don’t contort the schema to hit an index count target. The core insight is unchanged, because DynamoDB still has no joins. What’s changed is the enthusiasm for squeezing eleven access patterns into one GSI.

Every major DynamoDB library still assumes the model. ElectroDB, DynamoDB-Toolbox, and OneTable are all built for it.

Worked examples

  • SaaS Multi-Tenant — 4 entities, 10 access patterns, and how three GSIs collapse into one
  • E-Commerce Orders — 3 entities, 8 access patterns, dual-key order access and a status GSI

Single-table schemas are hard to hold in your head and easy to get subtly wrong. I’m building singletable.dev so you can see the whole design on a canvas and check access pattern coverage before you deploy.

← All glossary terms