Skip to content
LLD Learn/Intermediate Modeling Patterns
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Denormalization Trade-offs (Feeds & Fan-out)

4 min read

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.

The normalized version - correct, but an expensive join at read time
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.

A denormalized, pre-computed feed table
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.

Fan-out-on-Write vs. Fan-out-on-Read
Cheap, fast reads; expensive writes that scale with follower count
Fan-out-on-write
Cheap writes; reads must join/scan at request time
Fan-out-on-read
Fan-out-on-write for most users, fan-out-on-read for huge accounts
Hybrid (real-world)

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.

Ready to Build This?

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.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!