DynamoDB Data Modeling: Single-Table Design
DynamoDB rewards a different way of thinking than a relational database: not starting from the data model but from the access patterns. Single-table design combines several entity types in one table through composite keys.
DynamoDB Data Modeling: Single-Table Design
When you define the partition key in DynamoDB, you have already decided which queries will be cheap later and which won’t work at all. There is no optimizer that rescues a clumsy choice after the fact, and no server-side join that connects two tables at runtime. The question “Where do I put the foreign keys?” simply has no answer – the relationship has to be in the key before the first item is written.
That turns the familiar order around. With a relational database, I first design a clean, normalized model and think about the queries later; the optimizer makes sure most queries work somehow. With DynamoDB, I first write down which questions the application will ask of the data and derive the key schema from that. The data model is the result of these patterns, not their starting point.
The three building blocks everything starts with#
Single-table design stands or falls with three basic building blocks. Until they are in place, every description of the pattern hangs in the air.
A DynamoDB table has a primary key in one of two forms. Either a simple primary key – just a partition key (PK, historically “hash key”). Or a composite primary key made of partition key plus sort key (SK, “range key”). In the composite variant, the combination of PK and SK must be unique per item; the PK alone may repeat. That is exactly the lever for everything else.
Through an internal hash, the partition key determines on which physical partition an item lands. The sort key orders the items within a partition and allows range conditions. All items with the same partition key form an item collection – and this item collection is the actual core of single-table design.
The Query API always reads exactly one item collection, that is, exactly one PK value. Optionally, a condition on the sort key narrows the result, with operators such as =, <, <=, >, >=, BETWEEN or begins_with. One call returns up to 1 MB; after that, you paginate via LastEvaluatedKey. The opposite is the Scan, which goes through the whole table – something I practically never want in production.
So the roles stay clear, here is the division of responsibilities at a glance:
- The PK determines partition and distribution. Every query needs exactly one PK value – it decides which item collection gets read at all.
- The SK orders and filters within the collection, through range conditions and
begins_with. That is how I model one-to-many relationships and hierarchies. - A GSI (global secondary index) defines a new PK/SK combination for additional access patterns. It can be added at any time, is only eventually consistent and has its own throughput.
The LSI (local secondary index) is the contrasting case: the same PK as the base table, just an alternative SK. It supports strong consistency, but it must be defined when the table is created and binds the item collection per PK to a 10 GB limit. When in doubt, I take the GSI.
Why there is no join – and what happens instead#
DynamoDB has no server-side joins. That sounds like a shortcoming at first, but it is a deliberate decision for predictable latency. The trick: I don’t connect the data when reading but when writing. If different entity types carry the same partition key, they live in the same item collection and can be read together with a single query. That is pre-joining – the relationship is already built into the key schema before the first read happens.
Take a small CMS with users, their posts and the comments on a post. The central access pattern is: “Get a user and all their posts in one call.” That is exactly what I derive the key design from, not from an ER diagram.
flowchart LR A["Access pattern:<br/>Get user + their posts"] --> B["Choose PK:<br/>USER#id"] B --> C["Choose SK prefix:<br/>USER# and POST#"] C --> D["Query on PK = USER#123<br/>returns one item collection"] D --> E["Result:<br/>user item + all post items"]
The direction of derivation matters: I start on the left with the pattern and work my way to the key. That is the mental move that irritates relational developers at first.
A single-table layout#
Single-table design means: several entity types in a single table, held together by generic, composite keys. Instead of descriptive attribute names such as UserId, I deliberately use generic names such as PK and SK, because the same column carries different meanings depending on the entity type. For the CMS, a layout looks like this:
PK SK weitere Attribute
-------------- -------------------- -----------------------------
USER#123 USER#123 name, email, createdAt
USER#123 POST#2021-09-01T... title, body, status
USER#123 POST#2021-09-14T... title, body, status
POST#987 COMMENT#2021-09-15.. author, text
POST#987 COMMENT#2021-09-16.. author, text
The user and their posts share the partition key USER#123 and thus form one item collection. The user item has an SK that repeats the PK (USER#123); the posts use the prefix POST# followed by a timestamp. As a bonus, the timestamp in the sort key sorts the posts chronologically – without any additional index.
For the pattern “Get all comments on a post”, the primary index isn’t enough, because comments are sensibly grouped under a different PK. This is where a GSI comes into play. I add the attributes GSI1PK and GSI1SK and project an index over them that makes post-to-comment readable in reverse. That is the idea of the inverted index: a GSI with swapped roles to read a relationship from the opposite direction.
I define the table itself with generic keys and one GSI. An excerpt of the CreateTable parameters:
{
"TableName": "cms",
"BillingMode": "PAY_PER_REQUEST",
"AttributeDefinitions": [
{ "AttributeName": "PK", "AttributeType": "S" },
{ "AttributeName": "SK", "AttributeType": "S" },
{ "AttributeName": "GSI1PK", "AttributeType": "S" },
{ "AttributeName": "GSI1SK", "AttributeType": "S" }
],
"KeySchema": [
{ "AttributeName": "PK", "KeyType": "HASH" },
{ "AttributeName": "SK", "KeyType": "RANGE" }
],
"GlobalSecondaryIndexes": [
{
"IndexName": "GSI1",
"KeySchema": [
{ "AttributeName": "GSI1PK", "KeyType": "HASH" },
{ "AttributeName": "GSI1SK", "KeyType": "RANGE" }
],
"Projection": { "ProjectionType": "ALL" }
}
]
}
BillingMode: PAY_PER_REQUEST is the on-demand mode, available since the end of 2018. I like to use it for examples and fluctuating load because I don’t have to worry about reserving capacity in advance. For a GSI, I deliberately choose via ProjectionType which attributes come along – KEYS_ONLY, INCLUDE or ALL. ALL is convenient but costs more storage and write throughput.
The query over an item collection#
Now the part that justifies all the modeling. With SDK v3 and the DynamoDBDocumentClient from @aws-sdk/lib-dynamodb, I get all of a user’s posts with exactly one call:
import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, QueryCommand } from "@aws-sdk/lib-dynamodb";
const client = DynamoDBDocumentClient.from(new DynamoDBClient({}));
async function getPostsByUser(userId: string) {
const result = await client.send(
new QueryCommand({
TableName: "cms",
KeyConditionExpression: "PK = :pk AND begins_with(SK, :prefix)",
ExpressionAttributeValues: {
":pk": `USER#${userId}`,
":prefix": "POST#",
},
})
);
return result.Items ?? [];
}
The KeyConditionExpression addresses exactly one item collection via PK = :pk and cuts out the posts with begins_with(SK, :prefix). DynamoDB only reads the items that match POST# – the prefix acts directly on the sort key, not as an after-the-fact filter. If I want the user together with all posts, I drop the begins_with condition and get the complete collection in one go.
For the reverse pattern via the inverted index, little changes. I address the GSI and use its keys:
async function getCommentsByPost(postId: string) {
const result = await client.send(
new QueryCommand({
TableName: "cms",
IndexName: "GSI1",
KeyConditionExpression: "GSI1PK = :g AND begins_with(GSI1SK, :prefix)",
ExpressionAttributeValues: {
":g": `POST#${postId}`,
":prefix": "COMMENT#",
},
})
);
return result.Items ?? [];
}
At its core, this is the same query mechanism, just on a different index. If you have understood the primary pattern, you can transfer it to the GSI without any adjustment.
The pitfall with FilterExpression#
The FilterExpression looks like a cheap shortcut, but it isn’t one. Unlike the KeyConditionExpression, it only filters after the items have been read. That means I also pay the read capacity units for the items the filter then throws away. Worse still, combined with Limit, I can get empty pages back even though matching data exists – because the limit applies before the filter.
The consequence is clear: selectivity belongs in the key design, not in the filter. If I often filter by a criterion, that criterion should move into the sort key or into a GSI. I only use FilterExpression for rare, non-critical refinements at the edges.
When single-table pays off – and when it’s overkill#
As elegant as the pattern is, it isn’t an end in itself. Single-table design also gets introduced where it mainly looked impressive in a conference talk – and then saddles the application with complexity that nobody pays back. That’s why an honest look at the motivation is worth it.
Single-table pays off when the access patterns are stable and known and when it matters to read a lot of related data with as few round trips as possible. The gain is performance and scaling with predictable latency, not storage savings. I deliberately accept data duplication and denormalization – that is a feature, not an accident.
The approach is overkill in several situations:
- The access patterns are still unclear early in the project or change frequently. Single-table makes later changes to the patterns expensive, because the key schema is anchored deep in the data.
- The application needs ad-hoc evaluations or analytics. Without SQL, the export and reporting story gets tedious, and a single-table layout with many entity types doesn’t make it easier.
Alex DeBrie, whose writing on DynamoDB I like to pass on, names another case: GraphQL. A GraphQL server resolves fields individually via resolvers anyway, so the big advantage of loading everything with one query fizzles out. There, a multi-table approach often gives more flexibility. I described how GraphQL and DynamoDB work together concretely on AWS in GraphQL APIs with AppSync and DynamoDB.
A technical detail that becomes a problem with many entity types in one table is DynamoDB Streams: AWS recommends running no more than two concurrent consumers per shard, otherwise throttling effects loom. When different entities need different processing logic, you quickly end up wanting more consumers than a stream can handle.
A word on the hard limits#
So the modeling doesn’t fail because of physical limits, I keep a few numbers in mind that hold steady in 2021. An item may be at most 400 KB in size. A query returns at most 1 MB per call; after that, you paginate. BatchGetItem fetches up to 100 items or 16 MB. By default, up to 20 GSIs and a maximum of 5 LSIs are possible per table. These limits force deliberate design – for example, not letting very large or unboundedly growing item collections come into being in the first place.
Conclusion#
The decisive rebuild happens in your head, not in the code. If you treat DynamoDB like a relational database, you fight against the system. If you start with the access patterns and derive the partition key, sort key and suitable GSIs from them, you work with it. Single-table design then follows from the wish to read related data with one query and without a join – and not from the appeal of the pattern itself.
Pragmatically, the way leads through three steps: first write down the access patterns, then design the key schema, then check whether a single primary index plus a few GSIs covers the patterns. If the patterns are still shaky or ad-hoc analyses are central, a simpler multi-table layout is the more honest choice. The goal is never the most elegant diagram, but the application that stays predictable under load.
Further reading#
- Slide deck on this topic: https://slides.mikebild.dev/introduction-aws-serverless.en.html
- Repository and accompanying article: GraphQL APIs with AppSync and DynamoDB
- AWS Docs, Core Components (tables, items, keys, item collections): https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.CoreComponents.html
- AWS Docs, Query API: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html
- AWS Docs, Global Secondary Indexes: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html
- AWS Docs, Local Secondary Indexes: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/LSI.html
- AWS Docs, best practices and relational modeling: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-relational-modeling.html
- Alex DeBrie, “The What, Why, and When of Single-Table Design”: https://www.alexdebrie.com/posts/dynamodb-single-table/
- Alex DeBrie, “How to model one-to-many relationships”: https://www.alexdebrie.com/posts/dynamodb-one-to-many/
- AWS Database Blog, “Single-table vs. multi-table design in Amazon DynamoDB”: https://aws.amazon.com/blogs/database/single-table-vs-multi-table-design-in-amazon-dynamodb/
Revised and translated from German with the help of AI.