2.1 Case study: social network home timelines

The numbers (X/Twitter-shaped, simplified):

1.1 The naive design: query on read

Relational schema: users, posts, follows.

SELECT posts.*, users.* FROM posts
  JOIN follows ON posts.sender_id = follows.followee_id
  JOIN users   ON posts.sender_id = users.id
  WHERE follows.follower_id = current_user
  ORDER BY posts.timestamp DESC
  LIMIT 1000

Execution: use follows to find everyone current_user follows → look up recent posts by those users → sort by timestamp → take the most recent 1,000.

Why it doesn't work — do the arithmetic:

• Freshness via polling: every client re-runs the query every 5 s — 10,000,000 online users ÷ 5 s = 2,000,000 timeline queries/second.
• Each query must merge posts from ~200 followed accounts — 2,000,000 × 200 = 400,000,000 lookups/second.

400 million lookups per second — and that's the average case. Some users follow tens of thousands of accounts, and for them this query is very expensive and hard to make fast.

Two separate problems identified:

• Polling wastes work re-asking a question whose answer usually hasn't changed
• Query-on-read re-does the same expensive merge for every request

1.2 The fix: push + materialize

1. Push instead of poll — the server actively pushes new posts to followers who are online.
2. Precompute the query result — store, per user, a data structure containing their home timeline. On every post, look up all the poster's followers and insert that post into each follower's timeline — like delivering a message to a mailbox.

On login, hand over the precomputed timeline. For notifications, the client just subscribes to the stream of posts being added to their timeline.

Fan-out = the factor by which one initial request multiplies into downstream requests.

The new arithmetic:

  5,800 posts/s  ×  200 followers  =  ~1,160,000 timeline writes/second

~1.16 million writes/s vs 400 million lookups/s — a ~350× saving.

And during a spike, timeline deliveries don't have to be immediate — enqueue them, accept that posts temporarily take a bit longer to appear. Timelines stay fast to load throughout the spike, because reads are served from cache.

This is materialization; the timeline cache is a materialized view. The trade: speeds up reads, costs more work on writes.

1.3 The two extreme cases (this is the real lesson)

This hybrid fan-out (write-path for the many, read-path for the few) is the canonical answer to skew, and it recurs in Ch 7 (hot-spot handling). Even with it, "handling celebrities on a social network can require a lot of infrastructure."