Design: a news feed

The question is when you do the work

A feed is 'show me recent posts from everyone I follow, newest first'. It sounds like one query, and for a thousand users it is. The difficulty appears only at scale, and it is entirely a question of timing: do you build the feed when someone posts, or when someone opens the app?

Fan-out on write: the moment Ana posts, copy that post id into the feed list of every one of her followers. Opening the app is then a single read of a ready-made list - unbeatably fast. But one post by someone with a million followers means a million writes.

Fan-out on read: store each post once. When Sam opens the app, fetch the recent posts of everyone Sam follows and merge them. Writing is trivial; reading is expensive and happens far more often.

Read the ratio and the answer is obvious. People open the app far more than they post, so you want the expensive work on the rare action. Fan-out on write is the default - right up until a celebrity breaks it.

The celebrity problem, and the hybrid

Fan-out on write is beautiful until an account with 90 million followers posts. That single action becomes 90 million list writes. The queue backs up, and ordinary users' posts sit behind it - one celebrity tweet delays everyone.

This is the hot partition from the sharding lesson wearing a different hat, and the fix has the same shape: stop treating the outlier like everyone else.

The hybrid that real systems run: fan out on write for normal accounts, and skip fan-out entirely for celebrities. At read time, take the follower's precomputed feed and merge in the handful of celebrity accounts they follow, live. Most of the feed is ready-made; the expensive minority is computed for the few who asked.

CELEBRITY_THRESHOLD = 100_000

def writes_for_post(followers):
    if followers >= CELEBRITY_THRESHOLD:
        return 1                    # store once, merge at read time
    return followers                # push into each follower's feed

for followers in (150, 5_000, 90_000_000):
    print(f"{followers:>12,} followers -> {writes_for_post(followers):>10,} writes")

150 followers ->        150 writes
       5,000 followers ->      5,000 writes
  90,000,000 followers ->          1 writes

Pagination that survives an infinite scroll

One last trap, and it's the one that appears in production rather than in interviews. LIMIT 20 OFFSET 10000 makes the database produce ten thousand and twenty rows and throw away ten thousand of them. Page 500 is five hundred times more expensive than page 1.

It is also wrong. A feed changes while you scroll. Three new posts arrive between page 1 and page 2, everything shifts down by three, and page 2 shows you three posts you already read. Every offset-paginated infinite scroll has this bug.

Cursor pagination fixes both. Instead of 'skip 10,000', say 'give me what comes after this exact post'. The database jumps straight there via the index, the cost is identical on every page, and new arrivals at the top cannot shift what comes after your cursor.

-- Offset: slow at depth, and duplicates rows when the feed shifts
SELECT id, author_id, created_at FROM feed_items
WHERE owner_id = 7
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- Cursor: same cost at any depth, stable while the feed changes
SELECT id, author_id, created_at FROM feed_items
WHERE owner_id = 7
  AND (created_at, id) < ('2026-08-06 09:14:22', 88431)   -- the cursor
ORDER BY created_at DESC, id DESC
LIMIT 20;

OFFSET 10000   142 ms   (read 10,020 rows, discarded 10,000)
cursor           1.8 ms   (read 20 rows)