Social Network Feed

Design the schema for a Twitter-like service: users follow other users, post short messages, and like posts. Generate each user's home timeline. Discuss fan-out-on-write versus fan-out-on-read for timeline assembly.

Clarifying questions

What's the follow-graph skew — do we have celebrity accounts with 100M followers?
How fresh must the timeline be? Sub-second, or 'within a minute' acceptable?
Do we need per-user ranking (ML feed) or strict chronological?
Are likes high-volume metadata or first-class events with analytics?
Do we display follower counts in real time or eventually consistent?

Core requirements

Render a user's home timeline (posts from people they follow) in under 200ms p99.
Support follow / unfollow with reflected counts on the profile page.
Like and unlike a post with accurate aggregate counts.
Handle celebrity accounts (10M+ followers) without write amplification killing the DB.
Allow timeline backfill when a user follows someone new.
Soft-delete posts so likes / replies do not dangle.

Canonical schema (PostgreSQL)

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  handle CITEXT UNIQUE NOT NULL,
  display_name TEXT NOT NULL,
  follower_count BIGINT NOT NULL DEFAULT 0,
  following_count BIGINT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE follows (
  follower_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  followee_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (follower_id, followee_id)
);

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  author_id BIGINT NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  reply_to_id BIGINT REFERENCES posts(id),
  like_count BIGINT NOT NULL DEFAULT 0,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE likes (
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, post_id)
);

CREATE TABLE home_timeline (
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id BIGINT NOT NULL,
  author_id BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (user_id, created_at DESC, post_id)
);

Key indexes

CREATE INDEX idx_posts_author_created ON posts(author_id, created_at DESC) WHERE deleted_at IS NULL

Profile page query 'latest posts by this user' — partial index ignores tombstones.

CREATE INDEX idx_follows_followee ON follows(followee_id, follower_id)

Reverse direction of the PK — needed for fan-out-on-write to enumerate followers of an author.

CREATE INDEX idx_home_timeline_user ON home_timeline(user_id, created_at DESC)

Materialized timeline read path — keyset pagination by (user_id, created_at).

CREATE INDEX idx_likes_post ON likes(post_id, created_at DESC)

'Who liked this post' listing reverses the PK direction for the per-post view.

Key decisions to defend

Hybrid fan-out: write into home_timeline for normal users, read-merge from posts for celebrity followees. The follower_count threshold (say 100k) decides per write whether to materialize.
Denormalize follower_count / like_count onto the row instead of COUNT(*) — eventual consistency on counters is acceptable and saves a join on every render.
Partition posts by created_at — timeline queries are time-bounded, retention archival is cheap, and the hot working set stays small.
home_timeline holds only id+metadata, not the post body — bodies stay in posts so an edit doesn't fan out a rewrite.
BIGSERIAL on posts.id for monotonic ordering — also lets the id itself act as a tiebreaker on timestamp ties.

Likely follow-ups

· A celebrity posts and 50M timeline writes queue up — how do you bound the latency?
· Add a 'For You' ranked feed without throwing away the chronological infra.
· Show real-time like counts on a viral post without hammering one row.
· User unfollows someone — how do you scrub their home_timeline without a full scan?

Dive deeper

The fan-out tradeoff is the entire question. Pure fan-out-on-write blows up on celebrities; pure fan-out-on-read makes the average user's home feed expensive. A senior answer names both, picks a hybrid with an explicit threshold, and treats the home_timeline table as a derived cache that can be rebuilt — not a source of truth. The hot-row counter problem (likes on a viral post) is the implicit follow-up; mention sharded counters or async aggregation before asked.

All database design prompts