Denormalization Trade-offs (Feeds & Fan-out)
You'll learn to
- -Explain denormalization as a deliberate trade: duplicate data to make one dominant read fast
- -Compare fan-out-on-write with fan-out-on-read for a news feed, using row counts
- -Name why real systems mix the two, and what any copied data needs: a plan to stay consistent
Every chapter so far has pushed toward normalization: storing each fact once. Denormalization is the deliberate, informed exception. You duplicate data on purpose, to make one specific, frequent read fast, and you accept the cost as a trade-off, not a mistake.
An Analogy: Building Each Person's Newspaper
Imagine a newsroom where reporters write articles. In one design, nothing is pre-made. When a reader asks for their paper, an assistant runs around the building collecting the latest articles from every reporter that reader likes, then sorts them. That is slow for the reader. In another design, as soon as a reporter finishes an article, an assistant drops a copy into the personal tray of every reader who follows that reporter. Reading is instant. Writing is more work. The second design is fan-out-on-write.
The Problem: A Normalized Feed Query Gets Expensive
A fully normalized feed has a posts table and a follows table, and joins them at read time to find the latest posts by everyone you follow. It is correct. But for someone who follows thousands of accounts, that join scans a large number of posts across many authors on every single feed load. It does not scale well as follower counts and post volume grow.
SELECT posts.* FROM posts
JOIN follows ON posts.author_id = follows.followee_id
WHERE follows.follower_id = :user_id
ORDER BY posts.created_at DESC
LIMIT 50;Fan-out-on-Write: Denormalizing for Fast Reads
Fan-out-on-write moves the cost from read time to write time. When a post is created, immediately write a feed entry for every follower of the author. The entry holds a reference to the post and a few duplicated fields. Reading a feed is then one simple, indexed query on one user's own pre-computed rows. There is no join and no scan across other users' data.
CREATE TABLE feed_entries (
id INTEGER PRIMARY KEY,
owner_user_id INTEGER NOT NULL, -- whose feed this entry appears in
post_id INTEGER NOT NULL,
author_id INTEGER NOT NULL, -- duplicated from posts, avoids a join to display it
created_at TIMESTAMP NOT NULL
);
-- reading a feed is now one simple, indexed query:
SELECT * FROM feed_entries WHERE owner_user_id = :user_id ORDER BY created_at DESC LIMIT 50;The Trade-off, With Numbers
The price is paid at write time, and it scales with the number of followers. A user with 200 followers who posts causes 200 feed rows. A celebrity with 1,000,000 followers who posts once causes 1,000,000 rows, and three posts in a day is 3,000,000 writes. That is why real systems use a hybrid. A well-known public example is Twitter, which fans out on write for typical accounts and fans out on read, with a normalized join at request time, for accounts with enormous followings, where writing millions of rows per post would be prohibitive.
Denormalized data can drift from its source of truth. If author_id is copied into feed_entries and a post is later reassigned to a different author, every copied row must be updated too. Denormalization always comes with a plan for keeping the copies consistent, not just a one-time write.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design the News Feed Engine in the LLD Lab's Social Network act.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!