Normalization vs Denormalizationlesson 3 of 3 · 3 min read

Keeping Duplicates Honest

You converted work, not removed it

Denormalising does not remove work. It converts read-time work into write-time work, plus a standing liability.

The liability is drift. Your copies will disagree eventually, and your job is deciding how they get updated, how quickly they agree again, and how you find the ones that got away.

Which table is the truth belongs in writing for each duplicated field, before you write any code. Copies are caches. If the name on the post and the name on the user disagree, the user wins, and anything that repairs data repairs towards it. Skip this and you will spend a meeting arguing about which corrupted value is the real one.

Keeping the copies honest

Pick how the copies get updated, and know how each way fails. Updating truth and copies in one transaction is perfectly consistent and couples every write to every copy, so a rename touching 80,000 rows makes renames slow and lock-heavy.

Triggers keep the logic next to the data and hide it from code review, and a chain of them is miserable to debug at 3am.

Choose the one that scales: write the truth, emit a change event, and let a consumer update the copies. You pay with a window, usually seconds, where copies are stale, and that window has to be acceptable to the product rather than merely to the engineers.

Reconciliation matters, because propagating changes in the background fails silently. Run a scheduled job that recomputes the copies from the truth, counts what disagreed, and repairs it.

That mismatch count is a health signal. When it trends upward, something upstream is dropping events, and you would rather learn that from a graph than from a customer.

Duplicate only facts you can recompute, which is the habit this whole lesson comes down to. A copied name is always rebuildable from the user. A counter incremented in place with nothing behind it is, once wrong, wrong forever.

the shape of it

step 1 of 3

Copies update asynchronously from the source of truth, and a reconciliation job catches whatever the stream missed.

Worked example

Marco's team at a social app maintains follower_count as a column on users, incremented and decremented by application code, because COUNT(*) on a 900-million-row follows table is off the menu. Over a year, missed decrements from a buggy unfollow path let counts drift; the worst account shows 12,400 followers while the follows table holds 11,900, and a creator publicly accuses the platform of deleting followers. The repair has three layers. A nightly job recomputes counts from the follows table for accounts touched that day and logs mismatches, which start at 0.6 percent of active accounts. The counter updates move into the same transaction as the follow-row insert or delete, eliminating new drift. And the mismatch rate becomes a dashboard metric with an alert at 0.05 percent. Four weeks later the nightly repairs fall to near zero, and the metric later catches an unrelated bug in an account-merge tool within two days of its deploy.

Normalization vs Denormalization: wrapping up

In the real world

01Twitter's home timeline was famously fan-out on write: each new tweet was copied into a precomputed Redis timeline for every follower, a massive denormalization chosen because reads outnumbered writes by orders of magnitude.
02DynamoDB's single-table design guidance tells you to duplicate and prejoin data into item collections shaped for each access pattern, since the store offers no joins to lean on.
03PostgreSQL ships materialized views with REFRESH MATERIALIZED VIEW CONCURRENTLY, a built-in middle ground that stores a query's result and lets you rebuild it without blocking readers.
04MongoDB's own schema design documentation recommends embedding related data in one document for one-to-few relationships, trading duplication for single-fetch reads.
05Analytics warehouses like BigQuery and Redshift favor denormalized star schemas, because at query-scan scale, storage is cheap and join cost dominates; the same data stays normalized in the transactional systems feeding them.

Questions people ask

Should I design my schema denormalized from the start?

For a transactional system, no. Start normalized, because write correctness is hardest to retrofit and joins are rarely your first bottleneck. Denormalize specific hot paths when measurements prove the join is the cost. The exception is stores without joins, like DynamoDB or document databases, where you must design around access patterns, and denormalization is the design method rather than an optimization.

How do denormalized copies get updated when the source changes?

Three common mechanisms. Same-transaction updates keep everything consistent but make writes slower and more coupled. Database triggers automate it inside the database at the cost of hidden logic. Asynchronous propagation through change data capture or an outbox scales best but leaves a short staleness window. Most teams pair the async approach with a periodic reconciliation job that recomputes copies and repairs drift.

Is a materialized view the same thing as denormalization?

It is denormalization with the database doing the bookkeeping. The view stores a precomputed query result, so reads skip the joins, and refresh rebuilds it from the source tables, so drift cannot become permanent. The limits are freshness, since data is only as new as the last refresh, and refresh cost on large views. When those fit your needs, it beats hand-maintained copy columns.

Quick review

3NF (Third Normal Form):: no transitive dependencies. Clean, minimal duplication, many joins needed for reads
Denormalization:: duplicate data intentionally to eliminate joins. Store author_name in posts table alongside author_id
Materialized Views:: precompute and store the result of expensive queries. Refresh on schedule or on update
When to denormalize:: p99 query latency is unacceptable AND you've verified joins are the bottleneck via EXPLAIN
NoSQL denormalization:: document DBs encourage embedding related data in one document to avoid joins entirely
Write complexity:: denormalization means updating data in multiple places. Use DB triggers or application logic

the trade-off

Denormalized data can become inconsistent. Write complexity increases. Worth it only for proven hot paths.

in the room

Normalize first. Denormalize only where measured query performance is insufficient. Measure, don't guess.

the short version

The moment a fact lives in two places, you own the job of keeping them in agreement.