Part VI · Chapter 11 · Choosing · lesson 2
Postgres or DynamoDB for this workload?
15 min
Builds on Say what would change your mind, before you look at options and One table is 400GB and everything that touches it is slow
Part VI · Chapter 11 · Choosing · lesson 2
15 min
Builds on Say what would change your mind, before you look at options and One table is 400GB and everything that touches it is slow
Your notifications platform sends a few million messages a day and resends nothing, ever. You need a store that remembers which message ids have been accepted, holds each delivery receipt for a fortnight, and answers one question per lookup: has this id been seen. Two engineers, four weeks. One architect says Postgres because the rest of the company is Postgres. One says DynamoDB because of the write rate.
Both of them are arguing about the wrong variable.
Predict
Not the schema. Not the entity diagram. The list of operations the application will actually perform, each with the key it has in hand at the moment it runs and the result shape it needs back. If you cannot produce this list, you have learned something more important than which database to pick.
AP1 claim a message id key: message_id write, conditional on absence
AP2 look up one message id key: message_id read, point, strongly consistent
AP3 append a delivery receipt key: message_id + attempt write, no read first
AP4 list receipts for a message key: message_id read, up to a handful of items
AP5 expire everything after 14d key: none (time) bulk delete
volume writes dominate reads roughly four to one
peak concentrated in two sends a day, each a few minutes long
peak write rate order of a few thousand a second, bursty
skew no single message_id is ever hot; the key space is the message id
queries none ad hoc; nobody reports off this store, ever
Two features of that list decide the lesson. The key for every single operation is the message id, and the last line says no human will ever query this store. That is not a normal shape for product data. It is a very normal shape for infrastructure data, and infrastructure data is where the non-relational answer earns its keep.
What would you do
The five access patterns above, unchanged for the life of the service. The store is internal: it sits behind the delivery worker and no other team reads it. Peak is two spikes a day of a few minutes each, with the trough an order of magnitude below the peak.
Two engineers, four weeks, and an existing managed Postgres instance that already runs the product database at moderate utilisation. The company has no DynamoDB experience and one engineer has used it elsewhere.
Swap the workload for the one most readers actually have: an order service for a product that launched last year. Orders, line items, customers, a status field, an admin tool, and a finance team.
Run the same exercise and the access pattern list refuses to close. You write down six operations, and then someone asks for orders by customer, orders by status in a date range, revenue by product line this month, and whether the refund rate changed after the pricing update. Each one arrives after the schema is in production, each one has a key you did not design for, and each one is a half-day of SQL against a relational schema or a new index and a backfill against a key-value one.
That is the whole decision, and it has nothing to do with scale. For a product with stakeholders, use Postgres. Not because it is more capable in some abstract sense, but because an unknown future query is the dominant cost in that workload and Postgres prices it at roughly zero. For infrastructure data behind a single service, with an access pattern list that closes and a written commitment that nobody reports off it, the key-value store's determinism is real and worth having.
The test, phrased so you can apply it in a meeting: can you name every query this store will ever serve, and will you put that list in the design document as a constraint rather than a snapshot? If the second half makes anyone uncomfortable, you have your answer.
The reason scale is the wrong first question is that it rules out so little. Both engines carry far more than most products need, and the honest comparison is not which number is bigger but which failure you meet first.
| Managed Postgres | DynamoDB | |
|---|---|---|
| Write ceiling | Bounded by one primary's capacity until you partition or shard | Bounded per partition key, not per table |
| What gives first | The instance, visibly, with saturation you can watch on a dashboard | One hot key, invisibly, as throttling on that key alone |
| Headroom | Buy a bigger instance, with a failover window | Automatic, until the per-key ceiling, which no amount of spend moves |
| Burst | Provisioned in advance by you | On-demand absorbs it within the table's own warm-up behaviour |
| Unforeseen query | A new index, or no change at all | A secondary index, a stream, or a rewrite |
The last row is the one that costs money two years out. The first row is the one that gets argued about in the meeting. The per-key ceiling in the second column is the subject of a later lesson in this track, and it is the limit most teams meet long before they meet any table-level quota.
Check
Every argument for the key-value answer above rests on one line of the access pattern list, and that line is the least durable thing in this lesson. Throughput, burst shape and expiry all pointed the same way, but they would have pointed the same way for a partitioned Postgres instance too, at this volume. What actually decided it was that the query set was closed.
So the condition is precise: the moment a query arrives whose key is not in the access pattern list, this analysis is void — not weakened, void, because the thing it optimised for has stopped being true. Concretely, that is the day someone needs data from this store grouped by anything other than the message id, or joined to anything, or filtered by a range over a non-key attribute.
Two things follow. Write the access pattern list into the design document as a constraint, with the sentence "an access pattern not on this list reopens the choice" next to it. And before you need it, know which escape you would take — a change stream into a queryable copy is the cheap one, and the time to decide that is while nobody is waiting for the report.