Denormalization Trade-offs (Feeds & Fan-out)
You'll learn to
- -Decide when duplicating data (a denormalized feed table) is worth the write-time cost for read-heavy access patterns
- -Design a fan-out-on-write feed schema and explain its trade-off against fan-out-on-read
Every chapter so far has pushed toward normalization - eliminating redundancy. Denormalization is the deliberate, informed exception: intentionally duplicating data to optimize for a specific, dominant read pattern, accepted as a trade-off rather than a mistake.
The Problem: A Normalized Feed Query Gets Expensive
A fully normalized news feed - posts table, follows table, join at read time to find "posts by everyone I follow, in order" - is correct, but for a user following thousands of accounts, that join has to scan a large number of posts across a large number of authors on every single feed load, which 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 flips the cost from read time to write time: when a post is created, immediately write a copy of a reference to it into a separate feed_entries row for every one of that author's followers. Reading a feed becomes a single, simple, indexed query against one user's own pre-computed feed rows - no join, 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, Named Explicitly
Fan-out-on-write makes reads fast at the direct cost of writes: one post from a user with a million followers means writing a million feed_entries rows, and that write cost scales with follower count, which is precisely why real systems (Twitter is the canonical public example) use a hybrid - fan-out-on-write for typical users, but fan-out-on-read (a normalized join, computed at request time) for accounts with enormous follower counts, where writing millions of rows per post would be prohibitively expensive.
Denormalized data can drift out of sync with its source of truth - if author_id is duplicated into feed_entries and a post is later reassigned to a different author (an edge case, but a real one), every duplicated row needs updating too. Denormalization always comes with an explicit plan for keeping 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.