I come from PostgreSQL, so the first time I saw a DynamoDB single-table design — users, orders, and products living in the same table, with keys like USER#123 and ORDER#456 — I thought someone had lost their mind. It took me a while because I was asking the wrong question: in a relational model you design by entities, and in DynamoDB you design by access patterns. Once that clicks, the single-table stops being an aberration and becomes the natural way to get value out of the database. This article is how I made that mental shift and where the traps are.
Why DynamoDB isn't Postgres with different skin
In Postgres I normalize, create tables per entity, and let the planner resolve joins at query time. It's flexible: if tomorrow I need a new query, I write it and the engine figures it out, maybe with an extra index. I pay for that flexibility with performance that depends on the plan the engine picks.
DynamoDB flips the deal. There are no joins and no planner: every access is a lookup by key, and if you didn't design the key for that query, that query is either impossible or forces a Scan that walks the whole table. In return, a well-designed access has predictable latency no matter how much data there is. The consequence is brutal: in DynamoDB you can't start from the data model, you have to start from the list of queries your product is going to make.
Start from the access patterns, always
Before touching a table, I write the list of accesses in plain language. For an orders product it'd be something like: give me a user by id; give me all of a user's orders; give me an order with its line items; give me a user's orders sorted by date. That list isn't documentation, it's the design. Each pattern has to map to a partition-key Query, not a Scan.
This step is the one people coming from SQL skip, and it's exactly the one you can't skip. If a pattern you didn't foresee shows up later, in Postgres it's a new query; in DynamoDB it can be a key redesign or a global secondary index. The cost of getting this wrong is high, so this is where I spend the thinking.
Generic keys: PK and SK that don't mean one single thing
The single-table trick is that the partition key and sort key aren't called user_id or order_id. They're called plain PK and SK, and their contents change depending on the entity. A user lives at PK = USER#123, SK = PROFILE. Their orders live in the same partition, PK = USER#123, SK = ORDER#456. So asking for a user and all their orders is a single Query on PK = USER#123: the partition already brings the profile and the orders together, no join.
// All of a user's items (profile + orders) in one query
const res = await ddb.query({
TableName: 'app',
KeyConditionExpression: 'PK = :pk',
ExpressionAttributeValues: { ':pk': 'USER#123' },
});
// Orders only: narrow by the sort key prefix
const orders = await ddb.query({
TableName: 'app',
KeyConditionExpression: 'PK = :pk AND begins_with(SK, :prefix)',
ExpressionAttributeValues: { ':pk': 'USER#123', ':prefix': 'ORDER#' },
});
The begins_with on the sort key is the lever: by prefixing the types (ORDER#, PROFILE, ADDRESS#) I can pull exactly the subset I want from a partition, sorted, in a single call.
💡 In DynamoDB the sort key doesn't sort data, it designs queries. The prefix you give it is what decides which questions you'll be able to ask cheaply.
GSIs: when you need to look at the data along another axis
Patterns that don't fit the primary key are solved with global secondary indexes. A GSI is essentially a projection of the table with a different PK and SK, maintained by DynamoDB asynchronously. If I need "all orders with status PENDING sorted by date," I create a GSI whose PK is the status and whose SK is the date, and that access is a clean Query again.
The pattern I use is index overloading: the same generic GSI1PK and GSI1SK attributes mean different things depending on the entity, just like the primary key. With a couple of well-thought-out GSIs I cover almost any product. What I don't do is create a GSI on a whim: each index costs writes and storage, because every put on the table replicates to the indexes that apply.
The trade-offs nobody tells you about up front
Single-table is powerful, but it's fair to admit what it costs. The first thing is the mental curve: modeling this way is uncomfortable until you internalize thinking in accesses, and a new team takes a while to read a table where everything is called PK and SK. The second is rigidity: if a truly unforeseen access pattern appears, adapting costs more than in SQL, where an ad-hoc query is always possible even if it's slow.
That's why I don't use single-table for everything. When a product has exploratory queries, shifting reports, or relationships I can't anticipate, Postgres gives me a flexibility that DynamoDB would charge dearly for. I pick DynamoDB when the access patterns are known, bounded, and I need predictable latency at scale without operating a relational engine. There the single-table shines: fewer pieces, flat latency, and a bill that scales with real usage and not with the size of the table. Like everything in AWS, it isn't the best tool, it's the best tool for a problem with the right shape.
