Skip to main content
CodePeetCodePeet
Data Systems at Scale

Denormalization

Introduction

Your feed page shows twenty posts, each with its comment count — "142 comments." It was instant when you launched. Now, at five million comments, it takes a beat too long, because to show that little number the database has to go and count all the comments for each post, every time anyone loads the feed.

The fix is obvious and everyone reaches for it: stop counting on every read. Store the number. Add a comment_count column to posts, keep it updated, and the feed becomes a plain lookup. That is denormalization, and it is one of the most powerful — and most quietly dangerous — moves in this whole section.

Powerful, because the read genuinely gets much faster, and I'll show you the real numbers. Dangerous, because the moment you store that count, you have created a second copy of a fact — and the entire rest of this lesson is about what it costs to keep a copy from lying to you.

A before-and-after comparison of a normalized versus a denormalized schema. On the left, in blue, NORMALIZED: one fact in one place — a posts table and a comments table, with a two-way arrow between them showing that the read must JOIN and COUNT them every single time. The read takes 4.15 milliseconds and touches 1,043 pages, but a write is just one INSERT. On the right, in green, DENORMALIZED: the comment count is copied onto the post as a +count column, with a red dashed tether labelled 'sync!' connecting it to the comments table. Now the read is a single lookup — no join, no count — at 0.76 milliseconds touching 47 pages, but a write is an INSERT plus an UPDATE. A banner across the foot states the trade: you did not delete the work, you moved it from read time to write time, and moved the risk from 'slow' to 'wrong' — two copies of one fact can now disagree.

What You're De-normalizing FROM

For a second, appreciate what you're about to give up, because it's the thing that's been quietly saving you this whole time.

A normalized schema follows one rule: every fact lives in exactly one place. The comments live in the comments table. Nowhere else. The number of comments on a post isn't stored anywhere — it's derived, by counting, whenever you ask.

The magic of that rule is not tidiness. It's that a fact stored in one place can never disagree with itself. There is no "the count says 50 but there are really 48" — because there is no separate count. There's just the comments, and counting them is always right.

This is why relational databases default to normalized schemas, and why Relational Databases (#10) called it the default for a reason. Denormalization is the deliberate decision to break that rule, on purpose, for a specific slow read — and to take on, in exchange, the job the rule was doing for you: keeping the truth consistent.

The Trade, Measured

Here is the exact move, on a real Postgres: 100,000 posts, 5,000,000 comments (390 MB of them). The read is "show 20 posts, each with its comment count."

Normalized — the count doesn't exist, so you compute it by joining and counting:

SELECT p.id, p.title, count(c.id) AS comments
FROM posts p LEFT JOIN comments c ON c.post_id = p.id
WHERE p.id BETWEEN 1 AND 20
GROUP BY p.id, p.title;

-- Execution:  4.15 ms   ·   Buffers: shared hit=760 read=283   (1,043 pages)

Denormalize — store the count on the post, then the read is a plain lookup with no join and no aggregate:

ALTER TABLE posts ADD COLUMN comment_count int NOT NULL DEFAULT 0;
-- (backfill it once from the real counts)

SELECT id, title, comment_count FROM posts WHERE id BETWEEN 1 AND 20;

-- Execution:  0.76 ms   ·   Buffers: shared hit=47   (47 pages)

4.15 ms → 0.76 ms. About 5.5× faster, touching ~20× fewer pages. And it gets more lopsided as the site grows: the normalized read has to scan more and more comment rows, while the denormalized read stays flat — it's always one row, whether the post has 5 comments or 5 million.

That's the win. It's real, and for a genuinely hot read it can be the difference between a page that flies and a page that times out. Now the bill.

The Bill, Part One: Every Write Now Does More

The count didn't stop needing to be correct just because you stopped computing it. Somebody still has to make comment_count reflect reality — and that somebody is now you, on every write.

Normalized, adding a comment was one operation: INSERT. Denormalized, it's two — the insert, and an update to keep the copy honest:

-- normalized:   1 write
INSERT INTO comments(post_id, body) VALUES (7, 'nice post');

-- denormalized: 2 writes, and they must both happen
INSERT INTO comments(post_id, body) VALUES (7, 'nice post');
UPDATE posts SET comment_count = comment_count + 1 WHERE id = 7;

This is the shape of the whole trade, and it's worth saying plainly:

Denormalization does not delete the work. It moves it from read time to write time.

You were doing the counting on every read. Now you do a little bookkeeping on every write. If the thing is read far more than it's written — a comment count on a popular post, read thousands of times, written occasionally — that's a fantastic trade. If it's written as often as it's read, you've just made everything slower for nothing.

But the write amplification isn't the scary part. The scary part is what happens when those two writes don't both happen.

The Bill, Part Two: The Copy That Lies

The counter is only correct if it is updated perfectly, on every path, forever, under concurrency. That's a very high bar, and there are two classic ways it fails — I measured both.

Failure one: the forgotten path. The counter is only as good as your discipline in updating it everywhere comments change. Miss one path — a bulk cleanup, a data migration, an admin running a DELETE in a console — and the copy silently drifts from the truth:

post 60:   comment_count = 54   ·   real COUNT(*) = 54     (in sync)

-- an admin clears spam, straight in psql. your app code never runs.
DELETE FROM comments WHERE post_id = 60;

post 60:   comment_count = 54   ·   real COUNT(*) = 0      ← the counter now LIES
                                                             no error. nothing broke. it just lies.

Failure two: the lost update. Even if every path does update the counter, the naive way of doing it — read the current value, add one, write it back — quietly loses updates the instant two writes happen at once. I ran 1,008 "add a comment" operations concurrently, app-side read-modify-write:

1,008 concurrent increments (read count → add 1 → write it back):

   comment_count says:   75      ← 933 increments VANISHED
   real COUNT(*):        1008

why: two clients both read 74, both compute 75, both write 75. One increment is lost.

The counter said 75. The truth was 1,008. Ninety-two percent of the updates were lost — and, again, no error was raised. The page just cheerfully shows the wrong number.

This is the real cost of denormalization, and it's not milliseconds. It's that you now own a copy of a fact, and copies of facts drift — through paths you forgot and races you didn't see. The database was keeping your data consistent for free, and you traded that away for a faster read.

The two ways a denormalized counter drifts from the truth, each measured, plus the fix. On the left, in red, THE LOST UPDATE: when the app reads the count, adds one, and writes it back, and 1008 users comment at once, two clients read the same value and one overwrites the other — so the counter reaches only 75 while the true count is 1008, meaning 933 increments vanished. On the right, in amber, THE FORGOTTEN PATH: an admin runs a raw DELETE in psql, a code path the app never sees, so the counter stays at 54 while the true count drops to 0 — the counter cannot know it happened. The fixes: for concurrency, do the increment atomically with count = count + 1; for forgotten paths, use a database trigger the DB cannot skip. A banner states the law: the counter is a cache of the truth, the truth is COUNT(*), and when they disagree you must always keep a way to recompute the copy.

Keeping the Copy Honest

You don't have to accept the drift — you have to choose how you defend against it, and each defense has a bill.

For the lost update: never read-then-write in your app. Let the database do the arithmetic atomically.

-- WRONG (app reads, adds, writes — loses updates under concurrency):
cnt = SELECT comment_count FROM posts WHERE id = 7;   -- in app code
UPDATE posts SET comment_count = <cnt + 1> WHERE id = 7;

-- RIGHT (one atomic statement — the database serializes the row):
UPDATE posts SET comment_count = comment_count + 1 WHERE id = 7;

comment_count = comment_count + 1 reads, adds, and writes in a single indivisible step; the database locks the row for the duration, so no two increments can clobber each other. (This is MVCC (#19)'s writers-block-writers, put to work.) I re-ran the same 1,008 concurrent increments this way: counter 1,008, truth 1,008. Not one lost.

For the forgotten path: move the bookkeeping into the database, where your app can't forget it. A trigger fires automatically on every insert and delete — including the admin's raw DELETE that slipped past your app entirely:

CREATE TRIGGER trg_bump AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION bump_comment_count();
-- now even 'DELETE FROM comments WHERE post_id=70' keeps the counter correct.

The trigger is the honest default for correctness — the database cannot forget a path. Its bill: a hidden UPDATE on the parent post row on every comment write. I measured 500 inserts to one post go from 3.58 ms to 15.33 ms with the trigger — and worse, on a hot post (a viral thread), every commenter is now fighting for a lock on the same post row. At that point the counter itself becomes your bottleneck, and you're into approximate or sharded counters — a write-scaling problem for another day.

And one rule underneath all of it:

The counter is a CACHE of the truth. The truth is COUNT(*).

Whatever sync strategy you pick, always keep a way to recompute the copy from the truth. The truth is never wrong — it can only be slow. The copy is fast, and it can be wrong. When they disagree, the truth wins, and you rebuild the copy from it.

Break the Copy Yourself

Here's the whole thing, live: a post with a comment_count (the copy) next to the real number of comments (the truth). Pick a sync strategy, then attack it.

Add one comment — everything's fine. Now hit 100 at once on the app-side read-modify-write strategy and watch the copy fall 92 behind, exactly as it did on the real database. Switch to the atomic increment and the burst can't touch it — but then have an admin bulk-delete straight in the database, and watch it drift anyway, because your app code never ran. Only the trigger survives both.

The question the widget is really asking you, for each strategy, is the one you'll ask in every design review that involves a denormalized field: which failure does this survive, and which does it not?

You stored comment_count on the post so reads are fast — but now that number is a copy of the truth, and keeping a copy in agreement with the truth is the actual job. Pick a sync strategy, then drive writes: add one comment, fire 100 at once, or have an admin bulk-delete straight in the database. Watch the copy stay honest or drift. 'Do nothing' drifts immediately. App-side read-modify-write survives one write at a time but loses 92 of every 100 concurrent ones. Atomic increments survive concurrency but still miss a raw DELETE. Only the database trigger survives both — and it has its own bill. Every behaviour is from a real Postgres.

When to Reach for This (and When Not To)

Denormalization is a scalpel, not a lifestyle. Use it when all of these are true, and you can say them out loud:

  1. The read is genuinely slow — you've measured it, with EXPLAIN (ANALYZE, BUFFERS), not guessed.
  2. The read is genuinely hot — it happens far more often than the underlying data changes. A comment count read thousands of times and written occasionally: yes. A value written as often as it's read: no.
  3. You have chosen a sync strategy and can name its failure modes — atomic for concurrency, a trigger for forgotten paths — and you have a way to recompute from the truth when it drifts anyway.

If you can't say all three, you're not buying a faster read — you're buying a latent data-consistency bug in exchange for a read nobody was waiting on. Normalize by default. Denormalize on purpose, with evidence, and with a plan for the drift.

And know that this is the same move you'll see wearing different clothes for the rest of your career: a materialized view is denormalization the database manages for you (next lesson), and a cache is denormalization in a different system (#36). They are all the same trade — a faster read, paid for with a copy that can lie — and they all ask the same question: how do you keep the copy honest?

Mental-Model Corrections

"Denormalize to make it fast." Denormalization doesn't make work disappear — it moves it from read time to write time, and moves the risk from slow to wrong. You're not removing a cost; you're relocating it and changing its currency.

"It's just a counter, keep it updated." The naive read-modify-write counter loses 92% of updates under load (measured: 1,008 → 75). Use column = column + 1 atomically, or a trigger.

"Normalize everything — duplication is bad." Normalization is the right default, not a religion. A denormalized copy on a genuinely hot read path is a legitimate, deliberate trade.

"The trigger makes it free." The trigger makes it correct, not free: a hidden UPDATE per write (3.58 → 15.33 ms measured) and lock contention on hot rows.

"The counter is the source of truth." The counter is a cache of the truth. The truth is COUNT(*). When they disagree, the truth wins — always. Keep a way to recompute.

"Denormalize early, for performance." Denormalize when you've measured a read that's slow and hot. Premature denormalization buys drift risk for a read nobody was waiting on.

Key Takeaways

  • ⭐⭐⭐ Normalization stores each fact once, so it can never disagree with itself. Denormalization stores it twice for speed — and now the two copies can lie to each other. That trade, not the speed, is the lesson.
  • ⭐⭐ Denormalization moves work from read time to write time, and risk from "slow" to "wrong." Measured: the feed read went 4.15 ms → 0.76 ms (~5.5×), but every comment write went from 1 operation to 2, and the stored count can now drift.
  • ⭐⭐⭐ A copy of a fact drifts — silently, with no error. Two ways, both measured: the forgotten path (a raw DELETE your app never saw: counter 54, truth 0) and the lost update (1,008 concurrent read-modify-writes → counter 75, truth 1,008; 92% lost).
  • ⭐ The fixes, each with a bill: concurrency → count = count + 1 atomically in the DB (never loses an update; but you must remember every path); forgotten paths → a trigger the DB can't skip (survives raw deletes; but a hidden UPDATE per write + contention on hot rows).
  • The counter is a cache of the truth; the truth is COUNT(*). Always keep a way to recompute the copy — the truth is only ever slow, never wrong.
  • Normalize by default. Denormalize on purpose — only when the read is measurably slow and hot, and you've chosen a sync strategy and can name its failure modes.

Next: Materialized Views — the same trade, but you hand the bookkeeping to the database. It keeps the precomputed copy for you… and you'll meet a new question: how stale is it allowed to get, and who pays to refresh it?