When to Denormalize: Breaking the Rules for Performance (and When Not To)
Denormalization trades write complexity for read speed: you store a fact in more than one place so reads don't have to join or aggregate. It's a legitimate tool, but every copy needs a sync mechanism and every sync mechanism can drift. Here's how to tell a real denormalization from a snapshot, the ways copies go stale, and a framework for deciding when the trade is worth it.
The Counter That Was Always a Little Wrong
Your forum's thread list was slow. Each row showed a reply count, and each count was a COUNT(*) over the replies table. With 50 threads per page and some threads holding tens of thousands of replies, the page took two seconds. So you added a reply_count column to threads, incremented it whenever someone replied, and the page dropped to 40 milliseconds.
Six months later a moderator notices a thread showing 1,204 replies when it has 1,219. Another thread with deleted spam still counts the spam. Nobody can say when the numbers started drifting, and the only fix is a script that recounts everything.
That's denormalization working as intended and failing as usual. The read got fast, and in exchange you now own a second copy of a fact that has to be kept in step with the first. The question isn't whether denormalization is good or bad. It's whether the read you're speeding up is worth the sync mechanism you're signing up to maintain.
What Normalization Was Protecting You From
Organizing tables so each fact is stored in exactly one place. A customer's name lives in customers; orders reference the customer by key. To see an order with its customer's name, you join.
Deliberately storing a fact in more than one place, or storing a value derived from other data, so that reads can skip a join or an aggregate. The copy is redundant by design.
The whole point of normalization is avoiding update anomalies: if a fact is stored twice, an update can change one copy and miss the other, and now the database disagrees with itself. Denormalization brings that risk back on purpose. Everything else in this post is about how to manage it.
Every arrow in that diagram is code or configuration you have to keep working. The more copies, the more arrows.
First: Is It Even Denormalization?
A lot of what looks like duplicated data isn't. Before you worry about keeping a copy in sync, check whether it's supposed to stay in sync at all.
| Snapshot | Cached copy | |
|---|---|---|
| Example | Snapshotorder_items.unit_price, shipping address on an order | Cached copythreads.reply_count, orders.customer_name for display |
| When the source changes | SnapshotThe copy must NOT change | Cached copyThe copy must change too |
| What it records | SnapshotA fact at a moment in time | Cached copyThe current value of another fact |
| Is it redundant? | SnapshotNo, it's its own fact | Cached copyYes, it can be recomputed |
| Sync mechanism needed | SnapshotNone | Cached copyAlways |
If a product's price goes up, last month's invoices shouldn't change. Copying the price into the order line isn't denormalization; it's recording what the customer agreed to pay. Teams that "normalize" this by joining to the current product price have a much worse bug than any counter drift.
✅ A quick test
Ask: "If the source changes tomorrow, should this value change too?" If the answer is no, it's a snapshot. Store it, and don't try to keep it in sync. If the answer is yes, it's a cache, and you need to decide how it stays correct.
The Shapes Denormalization Takes
Duplicated columns. Copying customer_name onto orders so the order list doesn't join to customers. Cheap to read, painful when a customer renames and you have 40,000 orders to update.
Precomputed aggregates. reply_count, total_spent, average_rating, account_balance. These replace a scan over many rows with a single-row read. They're the most common form and the one with the most ways to drift.
Summary tables. A daily_sales table holding one row per store per day, built from millions of order rows. Reports read hundreds of rows instead of scanning the raw data.
Materialized views. The database stores a query's result. In PostgreSQL, REFRESH MATERIALIZED VIEW recomputes it entirely; REFRESH ... CONCURRENTLY keeps it readable during the refresh but requires a unique index on the view. PostgreSQL has no built-in incremental refresh, so the view is exactly as stale as the time since the last refresh.
Embedded documents. Storing an order's line items as a JSON array inside the order row, or a user's settings inside the user. This trades the ability to query and constrain the nested data for fewer joins.
Read models in other systems. A search index, a cache, or a CQRS read store built from the primary database's changes. This is denormalization across systems, and it's always eventually consistent. (CQRS Explained covers this pattern.)
How Copies Stay in Sync (and How They Don't)
Every denormalized value needs an answer to "what updates it?" The answer determines how fresh the copy is and how it breaks.
In the same transaction, from application code
The code that inserts a reply also updates the thread's count, in the same transaction. The copy is never visible out of step. The risk is coverage: any code path that writes replies without going through that code (a bulk delete, a moderation script, a second service) silently skips the update. It's the same problem as application-level foreign key checks (Why Your Foreign Keys Might Be Lying to You).
In the same transaction, from a database trigger
A trigger on replies updates threads.reply_count on every insert and delete. It covers every writer, because the database runs it. The costs are visibility (the logic is hidden from anyone reading the application code), testing, and performance on bulk operations, since a row-level trigger fires once per row.
Asynchronously, from events or change data capture
The write commits, an event or a CDC stream reports it, and a worker updates the copy. The write path stays fast and the worker can batch updates, but the copy lags, and if the worker drops or double-processes an event, the copy drifts. (Event-Driven Architecture covers the delivery guarantees involved.)
Periodically, by recomputing
A job or a materialized view refresh rebuilds the derived data from scratch. It's simple and it self-heals, since every run starts from the source. The trade is staleness: the data is as old as the interval between runs, and full recomputes get expensive as the source grows.
Whichever you pick, plan for a reconciliation job: something that periodically recomputes the value from the source, compares it to the stored copy, and alerts or repairs when they differ. Every sync mechanism eventually misses something, and without reconciliation you find out from a user.
The Counter Problem, Up Close
Counters are where most teams first denormalize, and they show nearly every failure mode in one column. The obvious implementation is wrong:
-- Read-modify-write: the app reads the count, adds 1, writes it back. -- Two concurrent requests both read 1204 and both write 1205. One reply is lost. SELECT reply_count FROM threads WHERE id = 7; -- app gets 1204 UPDATE threads SET reply_count = 1205 WHERE id = 7; -- Atomic increment: the database does the arithmetic under the row lock. UPDATE threads SET reply_count = reply_count + 1 WHERE id = 7;
The atomic version fixes lost updates, but it introduces a different cost: every reply now locks the thread's row until its transaction commits. For most threads that's nothing. For one viral thread receiving hundreds of replies per second, every reply transaction queues behind the one before it. (This is a pessimistic locking problem in disguise.) Common fixes are batching increments in an async worker, or splitting the counter into several rows and summing them on read.
The simulator models one second of traffic on a single thread. Switch strategies and push the write rate up.
One second of traffic on a single post. COUNT(*) reads every comment row on every page view but is always right. A counter column makes reads one row, at the cost of a second write per comment and a lock on the post row. Read-modify-write loses increments when two writers overlap within 5ms; an atomic SET n = n + 1 doesn't. The async worker is correct eventually, 300ms behind here. Lock time assumes 2ms per update.
Some things to try:
- COUNT(*) with 100k existing comments and 10k page views per second. The count is always right, and you're reading about a billion rows per second to show it. This is the case denormalization exists for.
- Read-modify-write at low write rates, then high. At 10 comments per second the count is usually exact, which is why this bug passes testing. Push it toward 800 and increments start disappearing permanently.
- Atomic counter at 500+ writes per second. The count is exact, but the post's row lock is busy the whole second. Every new comment waits in line.
- Async counter. No lost updates and no lock pressure on the write path, but the page shows a count that's a few hundred behind until the worker catches up.
When Denormalization Is Worth It
Denormalize when all of these are true:
- You've measured a read problem, and it's the join or aggregate. Not a missing index, not an N+1 query pattern, not a query that fetches columns it doesn't use. Fix those first; they're cheaper and they don't create a second copy of anything.
- Reads vastly outnumber writes to the source. A count read on every page view and changed a few times an hour is a great candidate. A value that changes on every request is not.
- You can say how stale the copy is allowed to be. "Exact, always" pushes you to same-transaction updates and their write costs. "Within a minute" opens up async workers and periodic refreshes.
- You know every path that writes the source. If you can't list them, use a trigger or CDC so the database is the one noticing changes.
Good candidates tend to look alike: counts and totals on list pages, reporting rollups, search and feed indexes, and hot read paths whose source data rarely changes.
When It Isn't
Before you've tried indexing. Many slow joins become fast with the right composite index or a covering index. Denormalizing to avoid a join the database could have done in a millisecond adds risk for nothing. (Database Indexing: How It Actually Works is the place to start.)
When the source changes constantly. If the copied value changes as often as it's read, you've doubled your writes without saving anything on reads.
When the value has to be exactly right and you can't make it so. An account balance can be denormalized from a ledger, but only if the balance update and the ledger entry happen in the same transaction, with proper locking. A balance updated by an async worker is a bug waiting to be reported by an auditor.
When a cache would do. If the expensive query's result can be a few seconds stale, an application cache gives you the read speed without changing your schema, and the cache can be thrown away. (Caching Strategies compares the options.) Denormalization is a cache you've made part of your data model, which makes it much harder to remove.
When the problem is read capacity, not query cost. If each query is fine but there are too many of them, read replicas add capacity without duplicating anything in the schema.
Deciding
Should the copied value change when the source changes?
Key Takeaways
- Denormalization trades write complexity for read speed. You save a join or an aggregate on every read and take on a sync mechanism for every copy.
- Snapshots aren't denormalization. If the value shouldn't change when the source does, store it and leave it alone.
- Every copy needs an answer to "what updates it?" Same-transaction updates are exact but cost write latency; async updates are fast but lag; periodic recomputes are simple but stale.
- Counters are harder than they look. Read-modify-write loses updates under concurrency. Atomic increments fix that but serialize writers on hot rows.
- Reconcile. Periodically recompute from the source and compare. Every sync mechanism eventually misses something.
- Exhaust the cheaper fixes first. Indexes, query fixes, caches, and read replicas solve many "we need to denormalize" problems without a second copy of your data.
References
- PostgreSQL docs: Materialized Views
- PostgreSQL docs: REFRESH MATERIALIZED VIEW, including the unique index requirement for
CONCURRENTLY - PostgreSQL docs: Trigger Functions
- Martin Kleppmann, Designing Data-Intensive Applications, chapters 2 and 11, on data models and derived data