Denormalization, Precomputation & Materialized Views

Trade write cost for cheap reads, on purpose

Normalization optimizes for write correctness: every fact lives in exactly one place. That is the wrong default when reads outnumber writes by 100:1 or 1000:1, which is the common web shape. Denormalization deliberately duplicates data so the read path does no joins and no aggregation at request time. You pay with write amplification (one logical write fans out to many physical writes) and the ongoing job of keeping the copies consistent. The trade is almost always worth it when a read is on the hot path and a write is not.

The three concrete tools

• Precomputed/materialized views: instead of running SELECT count(*) ... GROUP BY on every dashboard load, maintain a rollup table (daily_orders_by_region) that a job or a stream updates. Reads become a single indexed lookup. You trade freshness and storage for read latency.
• Approximate structures: when the answer does not need to be exact, use sketches. HyperLogLog counts unique visitors in ~12 KB per counter with ~0.8% standard error instead of storing every visitor id. Count-Min Sketch gives approximate frequencies for "top trending" in fixed memory. Redis ships both. Exactness is a cost you should only pay when the product needs it.
• Feed fan-out: the canonical denormalization problem. A user opens their home timeline and wants the merged, time-sorted posts of everyone they follow, in under ~100 ms.

The two feed strategies

• Fan-out-on-write (push): when Alice posts, immediately write that post id into the precomputed timeline of every follower (a per-user list, often in Redis). Reads are trivial: read your own list. But a post by someone with 50M followers triggers 50M writes. Write amplification is O(followers).
• Fan-out-on-read (pull): store each post once. At read time, query the recent posts of everyone the reader follows and merge-sort them. Writes are O(1), but a read for someone following 5,000 accounts is a large scatter-gather merge on the hot path.

 fan-out-on-write            fan-out-on-read
 Alice posts                 Bob opens feed
   |                           |
   +-> write to each of        +-> query recent posts of
       Alice's followers'          each account Bob follows,
       precomputed feed            then merge-sort at read time
 cheap reads, costly writes  cheap writes, costly reads

Neither pure form survives real distributions, because follower counts are power-law. The production answer is a hybrid: fan-out-on-write for normal accounts, but do not push posts from celebrity/whale accounts. Instead, at read time, pull the celebrity posts the reader follows and merge them into the precomputed list. This is exactly what Twitter/X described.

Interview nuance: the disqualifying mistake is proposing pure fan-out-on-write and not noticing that one celebrity post is now 50M writes and a thundering write storm. Say the threshold out loud: accounts above roughly 10k to 1M followers are handled on read; everyone else on write. The second nuance is owning the consistency cost you just created: denormalized copies (a cached follower count, a duplicated author name) can drift, and now you own an invalidation or reconciliation job.

Recap: denormalize when reads dominate, using materialized/rollup views and approximate sketches to make reads O(1) lookups; for feeds, use a hybrid that precomputes normal-user feeds and merges celebrity posts at read time, and accept that you now own write amplification and copy consistency.