Module 08 · Database scaling · Lesson 7 of 9
Denormalization
Deliberately storing data more than once so hot reads skip joins and aggregates. Copied columns, counters and precomputed lists, how copies stay in sync, and the discipline that separates a fast system from a corrupt one.
Why it matters
Normalisation keeps each fact in one place so writes are simple and data cannot disagree with itself. Reads pay for that with joins and aggregates. At scale the read path usually dominates, and the hottest reads cannot afford a five-table join or a COUNT(*) over millions of rows on every page load. Denormalisation moves work from read time to write time on purpose. Done carelessly it is how databases end up with two different answers to the same question.
The idea
Denormalisation stores derived or duplicated data alongside the data it came from, so a read that would need a join or a computation becomes a lookup. The source of truth stays normalised; the copies exist for speed and are maintained on write.
The common forms
| Form | Example | Saves | Must maintain when |
|---|---|---|---|
| Copied column | orders.customer_name alongside customer_id | A join on every order list | The customer renames (or accept a historical snapshot) |
| Counter cache | posts.comment_count | COUNT(*) over comments per page view | A comment is added or deleted |
| Precomputed aggregate | accounts.balance from a ledger of transactions | Summing the ledger on every read | Every ledger entry |
| Materialised list | users.feed_item_ids or a feed table per user | A query across all followed users | Any followed user posts (fan-out on write) |
| Embedded document | An order document with items inside | Joining items on every read | Items change (rare after placement) |
| Wide read table | A reporting table joining five sources | Repeated five-way joins | Any source changes; usually rebuilt periodically |
Keeping copies honest
- 1
Pick one source of truth
The ledger is the truth; the balance is a cache of it. When they disagree, the truth wins and the copy is rebuilt. Never let two copies both be authoritative. - 2
Update in the same transaction when you can
Incrementing a counter alongside the insert in one transaction keeps them consistent for free. Database triggers do this reliably and invisibly, at the cost of hidden logic. - 3
Update asynchronously when you must
Cross-service or expensive copies (search documents, feed fan-out) are updated from events or change data capture. Accept staleness measured in seconds and make the updater idempotent. - 4
Reconcile
Run a periodic job that recomputes copies from the truth and reports or fixes drift. Drift always appears eventually: a crashed job, a manual fix, a bug. Reconciliation is what makes denormalisation safe rather than hopeful. - 5
Decide what a snapshot means
An invoice should show the address at the time of purchase; that copy is correct precisely because it does not update. Name which copies are live and which are historical.
Where it goes wrong
Hot counters: a like count on a viral post updated in the same row on every like serialises thousands of writes on one row; batch increments, shard the counter or move it to a key-value store with atomic increments. Fan-out on write for users with millions of followers: writing to every follower's feed on each post is impossible, which is why large systems use fan-out on write for ordinary users and fan-out on read for celebrities. Forgotten paths: one code path updates the copy, another does not; triggers or a single write service prevent it.