Social Media Schema Design
Social media schemas look deceptively simple — users, posts, follows. The complexity hides in the feed: how do you efficiently show a user the latest posts from everyone they follow? This page covers the core schema and the critical feed generation strategies.
It also covers what the feed depends on: the follow graph and how to store and query its edges, privacy (private accounts, blocks, audiences) enforced in queries, and the counters (likes, followers) that become the hottest rows in the database the moment a post goes viral. The page follows the order you would design in: requirements and access patterns, entities, tables and indexes, hot-path queries, concurrency, then evolution as the system grows. If ER modelling is new to you, start with ER Modeling & Schema Design; for general OLTP principles see OLTP Database Design.
Requirements & Access Patterns
Assume a platform with 300 million registered users, 50 million daily actives, an average of 200 follows per active user, and a long tail of accounts with millions of followers.
| Operation | Steady rate | Peak rate | Read or write | Latency target | Notes |
|---|---|---|---|---|---|
| Home feed load (page of 20) | 60,000/s | 200,000/s | Read | p99 < 200 ms | The product. Everything else serves this |
| "Has viewer liked these 20 posts?" | 60,000/s | 200,000/s | Read | p99 < 20 ms | Batched, part of every feed render |
| Profile header (name, counts) | 20,000/s | 80,000/s | Read | p99 < 50 ms | Counters must be O(1) to read |
| User timeline (one author's posts) | 15,000/s | 60,000/s | Read | p99 < 100 ms | Newest first, keyset pages |
| Create post | 3,000/s | 20,000/s | Write | p99 < 300 ms | Triggers fan-out |
| Fan-out writes (feed rows) | 600,000/s | 4,000,000/s | Write | Seconds, async | Posts/s times average followers |
| Like / unlike | 30,000/s | 150,000/s | Write | p99 < 100 ms | One viral post can take 10,000/s alone |
| Comment | 3,000/s | 20,000/s | Write | p99 < 200 ms | Threaded |
| Follow / unfollow | 2,000/s | 10,000/s | Write | p99 < 200 ms | Changes counters on two users |
| Notifications list | 10,000/s | 40,000/s | Read | p99 < 100 ms | Unread badge on every app open |
| Followers list | 2,000/s | 10,000/s | Read | p99 < 200 ms | Paginated, newest first |
| Hashtag / text search | 5,000/s | 30,000/s | Read | p99 < 300 ms | A search engine's job at this scale |
What the numbers say:
- The feed is read-dominated and latency-critical, so the system precomputes it. That is the fan-out decision, and it produces the single biggest write stream (hundreds of thousands of feed rows per second), far larger than the number of posts.
- Counters are read on every render and written in bursts.
like_countmust be cheap to read and must survive 10,000 increments per second on a single post. That is a hot-row problem. - Edges are queried in both directions. "Who do I follow" drives the feed; "who follows me" drives fan-out and the followers page. Both need their own index, and once sharded, their own copy.
- Privacy filters run on every read. Blocks, private accounts and per-post audiences are predicates on the hottest queries, so they must be index-friendly.
Hot queries and the indexes they need:
| # | Hot query | Index |
|---|---|---|
| Q1 | User by username (profile URL) | UNIQUE (username) |
| Q2 | Author timeline, newest first | posts (author_id, created_at DESC) |
| Q3 | Precomputed home feed page | feed_items (user_id, created_at DESC) or a Redis sorted set |
| Q4 | Pull feed: recent posts of followed authors | follows (follower_id, ...) plus Q2's index |
| Q5 | Has viewer liked posts in a batch | post_likes PRIMARY KEY (user_id, post_id) |
| Q6 | Followers of X, newest first | follows (following_id, created_at DESC) |
| Q7 | Does A follow B / has A blocked B | composite primary keys |
| Q8 | Unread notifications count and list | partial (user_id, created_at DESC) WHERE NOT is_read |
| Q9 | Comments of a post, threaded | comments (post_id, created_at) |
Entity Relationship Overview
erDiagram
USERS ||--o{ POSTS : creates
USERS ||--o{ COMMENTS : writes
USERS ||--o{ LIKES : gives
USERS ||--o{ FOLLOWS : follows
USERS ||--o{ FOLLOWS : followed_by
USERS ||--o{ NOTIFICATIONS : receives
USERS ||--o{ BOOKMARKS : saves
POSTS ||--o{ COMMENTS : has
POSTS ||--o{ LIKES : receives
POSTS ||--o{ POST_MEDIA : contains
POSTS ||--o{ POST_TAGS : tagged_with
POSTS ||--o{ BOOKMARKS : saved_by
COMMENTS ||--o{ LIKES : receives
COMMENTS ||--o{ COMMENTS : replies_to
The follow relationship is the classic recursive M:N: a user follows many users and is followed by many. In the ER diagram it shows as two relationship lines between USERS and FOLLOWS; in the tables it is one junction table with two foreign keys to the same table. The extended model with the privacy and graph tables this page adds:
erDiagram
USERS ||--o{ FOLLOWS : "follower"
USERS ||--o{ FOLLOWS : "followee"
USERS ||--o{ FOLLOW_REQUESTS : "requests"
USERS ||--o{ BLOCKS : "blocks"
USERS ||--o{ AUDIENCE_LISTS : "owns"
AUDIENCE_LISTS ||--o{ AUDIENCE_MEMBERS : "contains"
USERS ||--o{ POSTS : "authors"
POSTS ||--o{ POST_LIKES : "liked via"
COMMENTS ||--o{ COMMENT_LIKES : "liked via"
POSTS ||--o{ MENTIONS : "mentions"
USERS ||--o{ FEED_ITEMS : "feed owner"
POSTS ||--o{ FEED_ITEMS : "fanned out as"
USERS {
bigint id PK
text username UK
bool is_private
int follower_count "denormalized"
}
FOLLOWS {
bigint follower_id PK, FK
bigint following_id PK, FK
timestamptz created_at
}
FOLLOW_REQUESTS {
bigint requester_id PK, FK
bigint target_id PK, FK
}
BLOCKS {
bigint blocker_id PK, FK
bigint blocked_id PK, FK
}
POSTS {
bigint id PK "time-ordered"
bigint author_id FK
text visibility
bigint audience_list_id FK "nullable"
int like_count "denormalized"
}
POST_LIKES {
bigint user_id PK, FK
bigint post_id PK, FK
}
FEED_ITEMS {
bigint user_id PK, FK
bigint post_id PK, FK
bigint author_id
}
Core Tables
Users & Profiles
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
display_name TEXT NOT NULL,
bio TEXT,
avatar_url TEXT,
cover_url TEXT,
website TEXT,
location TEXT,
is_verified BOOLEAN DEFAULT FALSE,
is_private BOOLEAN DEFAULT FALSE,
-- Denormalized counters (updated via triggers or app logic)
follower_count INT DEFAULT 0,
following_count INT DEFAULT 0,
post_count INT DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
Denormalized Counters
Counting followers with SELECT COUNT(*) FROM follows WHERE following_id = ? is O(n) and hits the database hard for popular accounts. Storing follower_count directly on the user row makes profile loads O(1). Update it atomically:
UPDATE users SET follower_count = follower_count + 1 WHERE id = $1;
Notes (2026-11-08)
idx_users_usernameandidx_users_emailare redundant: theUNIQUEconstraints already created unique B-tree indexes on both columns. Drop them; they only add write cost.- "O(1)" for the counter means independent of the number of followers. Finding the user row is still a B-tree lookup, O(log n) in the number of users, which in practice is three or four page reads.
- Usernames are usually case-insensitive for lookup. Enforce that with
CREATE UNIQUE INDEX uq_users_username_lower ON users (lower(username))and query withWHERE lower(username) = lower($1). - Counters should never go negative:
ALTER TABLE users ADD CHECK (follower_count >= 0 AND following_count >= 0 AND post_count >= 0).
Follows
CREATE TABLE follows (
follower_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
following_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (follower_id, following_id),
CHECK (follower_id != following_id) -- can't follow yourself
);
-- "Who does user X follow?"
CREATE INDEX idx_follows_follower ON follows(follower_id, created_at DESC);
-- "Who follows user X?"
CREATE INDEX idx_follows_following ON follows(following_id, created_at DESC);
Key queries:
-- Check if user A follows user B (O(1) with primary key)
SELECT 1 FROM follows WHERE follower_id = $A AND following_id = $B;
-- Get user X's followers (paginated)
SELECT u.id, u.username, u.display_name, u.avatar_url
FROM follows f
JOIN users u ON u.id = f.follower_id
WHERE f.following_id = $X
ORDER BY f.created_at DESC
LIMIT 20 OFFSET 0;
-- Mutual follows (people who follow each other)
SELECT f1.following_id
FROM follows f1
JOIN follows f2
ON f1.follower_id = f2.following_id
AND f1.following_id = f2.follower_id
WHERE f1.follower_id = $user_id;
Notes (2026-11-08)
- The "follows" check is a single primary-key probe: O(log n) in the table size, a handful of page reads, effectively constant. It is not literally O(1); a B-tree is not a hash table.
OFFSETpagination on the followers list degrades linearly: page 1,000 of a celebrity's followers reads and discards 20,000 index entries. Use keyset pagination with a tie-breaker, shown under Hot-Path Queries.idx_follows_following (following_id, created_at DESC)is the index that drives fan-out on write ("everyone who follows the author"), which makes it one of the most read indexes in the system. AddingINCLUDE (follower_id)is unnecessary:follower_idis part of the primary key, but secondary B-tree indexes in PostgreSQL do not contain other key columns automatically, so if you want fan-out to be an index-only scan, define it as(following_id, created_at DESC, follower_id)or(following_id) INCLUDE (follower_id).
Posts
CREATE TABLE posts (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
content TEXT NOT NULL CHECK (LENGTH(content) <= 5000),
post_type TEXT NOT NULL DEFAULT 'text', -- 'text', 'image', 'video', 'poll'
visibility TEXT NOT NULL DEFAULT 'public', -- 'public', 'followers', 'private'
parent_id BIGINT REFERENCES posts(id) ON DELETE SET NULL, -- for reposts/quotes
is_repost BOOLEAN DEFAULT FALSE,
-- Denormalized counters
like_count INT DEFAULT 0,
comment_count INT DEFAULT 0,
repost_count INT DEFAULT 0,
bookmark_count INT DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE post_media (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
media_type TEXT NOT NULL, -- 'image', 'video', 'gif'
url TEXT NOT NULL,
thumbnail TEXT,
width INT,
height INT,
sort_order INT DEFAULT 0
);
CREATE TABLE post_tags (
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
tag TEXT NOT NULL,
PRIMARY KEY (post_id, tag)
);
CREATE INDEX idx_posts_author ON posts(author_id, created_at DESC);
CREATE INDEX idx_posts_created ON posts(created_at DESC);
CREATE INDEX idx_posts_parent ON posts(parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX idx_post_media_post ON post_media(post_id);
CREATE INDEX idx_post_tags_tag ON post_tags(tag);
Production additions to the post tables:
ALTER TABLE posts
ADD CONSTRAINT chk_visibility CHECK (visibility IN ('public', 'followers', 'private', 'audience')),
ADD COLUMN audience_list_id BIGINT, -- for 'audience' (close friends)
ADD COLUMN deleted_at TIMESTAMPTZ, -- soft delete
ADD CONSTRAINT chk_audience
CHECK ((visibility = 'audience') = (audience_list_id IS NOT NULL)),
ADD CONSTRAINT chk_counters_nonneg
CHECK (like_count >= 0 AND comment_count >= 0 AND repost_count >= 0 AND bookmark_count >= 0);
-- A user can repost a given post only once.
CREATE UNIQUE INDEX uq_one_repost ON posts (author_id, parent_id) WHERE is_repost;
-- Hashtag page: newest posts with a tag. Include post_id ordering so
-- the tag page is an ordered index range, not a sort.
CREATE INDEX idx_post_tags_tag_post ON post_tags (tag, post_id DESC);
Two things to notice:
idx_posts_author (author_id, created_at DESC)is the most important index on the table. It serves the author timeline (Q2), the pull half of the hybrid feed (Q4), and fan-out catch-up. Equality on the author first, sort on time second.- Post ids should be time-ordered. With an identity column they are, on a single database. Once posts are sharded, use Snowflake-style ids (timestamp in the high bits) so that sorting by
idequals sorting by time, which lets feed tables store and sort by a single 8-byte integer. See ID Generation. That is also why the hashtag index above sorts bypost_id.
Likes
CREATE TABLE likes (
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
target_type TEXT NOT NULL, -- 'post' or 'comment'
target_id BIGINT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (user_id, target_type, target_id)
);
-- "Has user X liked this post?"
-- Covered by primary key
-- "All likes on a post" (for notification fan-out)
CREATE INDEX idx_likes_target ON likes(target_type, target_id, created_at DESC);
Polymorphic Foreign Keys
target_id references either posts.id or comments.id depending on target_type. PostgreSQL cannot enforce this with a regular FK. Options:
- Two separate tables (
post_likes,comment_likes) — cleaner, FK-safe - Application-level enforcement — simpler schema, no DB-level FK
- Trigger-based validation — custom trigger checks target exists
For most social media apps, option 1 is recommended for data integrity.
The recommended option 1, written out:
CREATE TABLE post_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)
);
-- "Who liked this post", newest first (likers list, notification aggregation)
CREATE INDEX idx_post_likes_post ON post_likes (post_id, created_at DESC);
CREATE TABLE comment_likes (
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
comment_id BIGINT NOT NULL REFERENCES comments(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (user_id, comment_id)
);
CREATE INDEX idx_comment_likes_comment ON comment_likes (comment_id, created_at DESC);
Why the primary key is (user_id, post_id) and not (post_id, user_id): the hottest like query is Q5, "which of these 20 feed posts has this viewer liked", run on every feed render. With user_id leading, that is one index range for one user, probing 20 post_id values. The reverse direction (likers of a post) is less frequent and gets the secondary index. Either order prevents duplicate likes, which is the other job of the key.
Comments
CREATE TABLE comments (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
parent_id BIGINT REFERENCES comments(id) ON DELETE CASCADE, -- threaded replies
content TEXT NOT NULL CHECK (LENGTH(content) <= 2000),
like_count INT DEFAULT 0,
depth INT NOT NULL DEFAULT 0, -- nesting level (0 = top-level)
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_comments_post ON comments(post_id, created_at);
CREATE INDEX idx_comments_parent ON comments(parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX idx_comments_author ON comments(author_id, created_at DESC);
Threaded comments query:
-- Get all comments for a post, threaded
WITH RECURSIVE thread AS (
SELECT id, author_id, parent_id, content, depth, created_at,
ARRAY[id] AS path -- sort key: ancestors' ids
FROM comments
WHERE post_id = $1 AND parent_id IS NULL
UNION ALL
SELECT c.id, c.author_id, c.parent_id, c.content, c.depth, c.created_at,
t.path || c.id
FROM comments c
JOIN thread t ON c.parent_id = t.id
)
SELECT
t.*,
u.username,
u.avatar_url
FROM thread t
JOIN users u ON u.id = t.author_id
ORDER BY t.path;
Correction (2026-11-08)
An earlier version put ORDER BY created_at inside the non-recursive part of the CTE and ordered the result by depth, created_at. The first is a syntax error in PostgreSQL (an ORDER BY cannot sit between a SELECT and UNION ALL without parentheses, and it would not survive into the result anyway). The second produced all top-level comments, then all replies, then all replies-to-replies, which is not a threaded order. Ordering by a path array of ancestor ids gives depth-first thread order: each reply sorts directly under its parent. Because ids are time-ordered, siblings come out oldest first.
Large posts have tens of thousands of comments, and nobody reads them all. Real clients load top-level comments first (keyset-paginated with idx_comments_post, sorted by time or by a score), then load replies for a parent on demand via idx_comments_parent. Make that index (parent_id, created_at) so the replies come out pre-sorted. The depth column caps nesting (CHECK (depth <= 3)) and lets the UI flatten deep threads without a recursive query.
Notifications
CREATE TYPE notification_type AS ENUM (
'like', 'comment', 'follow', 'mention', 'repost'
);
CREATE TABLE notifications (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- recipient
actor_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- who did it
type notification_type NOT NULL,
target_type TEXT, -- 'post', 'comment'
target_id BIGINT,
is_read BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_notifications_user ON notifications(user_id, created_at DESC);
CREATE INDEX idx_notifications_unread ON notifications(user_id)
WHERE NOT is_read;
A popular post produces thousands of like notifications. Nobody wants thousands of rows saying "X liked your post"; they want "Asha and 2,341 others liked your post". Store notifications aggregated per (recipient, type, target) and update the aggregate in place:
CREATE TABLE notification_groups (
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
type notification_type NOT NULL,
target_type TEXT NOT NULL,
target_id BIGINT NOT NULL,
actor_count INT NOT NULL DEFAULT 1,
latest_actors BIGINT[] NOT NULL, -- last few actor ids for the avatars
is_read BOOLEAN NOT NULL DEFAULT FALSE,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (user_id, type, target_type, target_id)
);
CREATE INDEX idx_notif_groups_recent ON notification_groups (user_id, updated_at DESC);
INSERT INTO notification_groups (user_id, type, target_type, target_id, latest_actors)
VALUES ($owner, 'like', 'post', $post, ARRAY[$actor])
ON CONFLICT (user_id, type, target_type, target_id) DO UPDATE
SET actor_count = notification_groups.actor_count + 1,
latest_actors = (ARRAY[$actor] || notification_groups.latest_actors)[1:3],
is_read = FALSE,
updated_at = NOW();
The upsert is itself a hot row for viral posts, so notification writes go through a queue and are batched per target (one upsert adding 500 likes beats 500 upserts). The unread badge reads SELECT count(*) FROM notification_groups WHERE user_id = $1 AND NOT is_read, which a partial index (user_id) WHERE NOT is_read keeps small; better still, cap the badge at "99+" and stop counting with LIMIT 100 in a subquery.
Bookmarks
CREATE TABLE bookmarks (
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 DEFAULT NOW(),
PRIMARY KEY (user_id, post_id)
);
CREATE INDEX idx_bookmarks_user ON bookmarks(user_id, created_at DESC);
Mentions
The performance table below says to parse @username in the application and store mentions; the table:
CREATE TABLE mentions (
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
mentioned_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (post_id, mentioned_user_id)
);
-- "Posts that mention me", newest first
CREATE INDEX idx_mentions_user ON mentions (mentioned_user_id, created_at DESC);
Resolve usernames to ids at write time. A username can change later; the mention must still point at the right person.
The Social Graph: Storing and Querying Edges
The follow table is an adjacency list: one row per directed edge. It is the right default for almost every social product. What matters is how you query it.
| Graph question | Query shape | Cost |
|---|---|---|
| Does A follow B? | PK probe | One index lookup |
| Who does A follow? | Range on (follower_id, ...) | Proportional to A's following count (bounded, usually < 5,000) |
| Who follows B? | Range on (following_id, ...) | Proportional to B's follower count (unbounded: millions) |
| Mutuals of A | Self-join on reversed edge | Proportional to A's following count, one probe each |
| Friends of friends (2 hops) | Join follows to follows | Following count squared; 200 × 200 = 40,000 rows, worse for heavy followers |
| Shortest path, 3+ hops | Recursive CTE | Explodes; not an OLTP query |
-- "People you may know": followed by people you follow, not yet followed by you.
SELECT f2.following_id AS candidate, COUNT(*) AS mutual_count
FROM follows f1
JOIN follows f2 ON f2.follower_id = f1.following_id
WHERE f1.follower_id = $me
AND f2.following_id <> $me
AND NOT EXISTS (SELECT 1 FROM follows x
WHERE x.follower_id = $me AND x.following_id = f2.following_id)
AND NOT EXISTS (SELECT 1 FROM blocks b
WHERE (b.blocker_id = $me AND b.blocked_id = f2.following_id)
OR (b.blocker_id = f2.following_id AND b.blocked_id = $me))
GROUP BY f2.following_id
ORDER BY mutual_count DESC
LIMIT 20;
This is fine for one user on demand if you sample (LIMIT the inner follow list to, say, 200 most recent). It is not fine to run per request for everyone; recommendation-style graph queries are computed offline in batch and stored as a suggestions (user_id, candidate_id, score) table.
Edge types. Products often grow more relationships: close friends, mute, block, follow request, subscribe-to-notifications. Two schemas work:
- One table per edge type (
follows,blocks,mutes,follow_requests): every table is narrow, gets its own constraints, and its own indexes. Preferred for a handful of types. - One
edges (src, dst, type, created_at)table withPRIMARY KEY (src, type, dst)and an inverse index(dst, type, src): generic, like Facebook's TAO association model. Better when edge types are numerous and similar, at the cost of per-type constraints.
When to reach for a graph database. When the product's core queries are multi-hop and interactive (fraud rings, "how am I connected to this person", knowledge graphs), a native graph store answers them with pointer-chasing instead of joins. A follow feed is one hop and does not need one. See Graph Databases.
Edges once sharded. If users are sharded by id, the edge A to B is naturally stored on A's shard (good for "who does A follow"), but "who follows B" then needs every shard. The standard answer is to write each edge twice: a forward row on the follower's shard and an inverse row on the followee's shard, kept in sync asynchronously, with a reconciliation job for the rare divergence.
Privacy: Private Accounts, Blocks and Audiences
Privacy rules are predicates that must be applied to every read path, which is why they belong in the schema and in shared query functions, not scattered across handlers.
-- Private accounts: a follow is a request until approved.
CREATE TABLE follow_requests (
requester_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
target_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (requester_id, target_id),
CHECK (requester_id <> target_id)
);
CREATE INDEX idx_follow_requests_target ON follow_requests (target_id, created_at DESC);
-- Blocks are directional, but enforced in both directions.
CREATE TABLE blocks (
blocker_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
blocked_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (blocker_id, blocked_id),
CHECK (blocker_id <> blocked_id)
);
CREATE INDEX idx_blocks_blocked ON blocks (blocked_id, blocker_id);
-- Mutes hide content without telling the other person.
CREATE TABLE mutes (
muter_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
muted_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
PRIMARY KEY (muter_id, muted_id)
);
-- "Close friends" style audiences.
CREATE TABLE audience_lists (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
owner_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
UNIQUE (owner_id, name)
);
CREATE TABLE audience_members (
list_id BIGINT NOT NULL REFERENCES audience_lists(id) ON DELETE CASCADE,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
PRIMARY KEY (list_id, user_id)
);
ALTER TABLE posts ADD CONSTRAINT fk_posts_audience
FOREIGN KEY (audience_list_id) REFERENCES audience_lists(id);
A single visibility function keeps every path consistent:
CREATE FUNCTION can_view_post(viewer BIGINT, p posts) RETURNS boolean
LANGUAGE sql STABLE AS $$
SELECT p.deleted_at IS NULL
AND NOT EXISTS (SELECT 1 FROM blocks b
WHERE (b.blocker_id = p.author_id AND b.blocked_id = viewer)
OR (b.blocker_id = viewer AND b.blocked_id = p.author_id))
AND (
p.author_id = viewer
OR (p.visibility = 'public'
AND NOT (SELECT is_private FROM users WHERE id = p.author_id))
OR (p.visibility IN ('public', 'followers')
AND EXISTS (SELECT 1 FROM follows f
WHERE f.follower_id = viewer AND f.following_id = p.author_id))
OR (p.visibility = 'audience'
AND EXISTS (SELECT 1 FROM audience_members m
WHERE m.list_id = p.audience_list_id AND m.user_id = viewer))
);
$$;
Each branch is one primary-key probe, so the function costs a few index lookups per post, which is acceptable for a 20-post page. Design points:
- A private account's "public" posts are still followers-only. The account flag overrides the post flag; the function encodes that so no handler has to remember.
- Blocking must clean up edges. Blocking removes follows in both directions and pending follow requests, in the same transaction, and decrements the counters. Otherwise the blocked user keeps receiving fan-out.
- Precomputed feeds can go stale on privacy changes. When a user blocks someone, goes private, or removes a follower, rows already fanned out into feeds still exist. Filter at read time with the same predicate (cheap, because the page is 20 rows) and clean up asynchronously.
- Deletion and data rights. Account deletion cascades through follows, likes, comments and feed rows. Cascades on huge tables are long transactions; mark the account deleted immediately (hide everywhere via the visibility checks), then delete in batches from a background job.
Feed Generation
The feed is the hardest part of a social media schema. There are two main strategies:
Strategy 1: Fan-Out on Read (Pull Model)
When a user opens their feed, query posts from everyone they follow in real-time.
-- Pull feed: query at read time
SELECT p.*, u.username, u.avatar_url
FROM posts p
JOIN follows f ON f.following_id = p.author_id
JOIN users u ON u.id = p.author_id
WHERE f.follower_id = $current_user
AND p.visibility IN ('public', 'followers')
ORDER BY p.created_at DESC
LIMIT 20;
| Pros | Cons |
|---|---|
| Simple schema | Slow for users following 1000+ accounts |
| Always fresh | Requires joining follows + posts tables |
| No write amplification | Hard to rank/personalize |
Strategy 2: Fan-Out on Write (Push Model)
When a user publishes a post, write it to every follower's feed table.
CREATE TABLE feed_items (
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
author_id BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (user_id, post_id)
);
CREATE INDEX idx_feed_user ON feed_items(user_id, created_at DESC);
-- On new post: fan out to all followers
INSERT INTO feed_items (user_id, post_id, author_id, created_at)
SELECT f.follower_id, $post_id, $author_id, NOW()
FROM follows f
WHERE f.following_id = $author_id;
-- Read feed: simple index scan
SELECT fi.post_id, p.content, u.username, u.avatar_url
FROM feed_items fi
JOIN posts p ON p.id = fi.post_id
JOIN users u ON u.id = fi.author_id
WHERE fi.user_id = $current_user
ORDER BY fi.created_at DESC
LIMIT 20;
| Pros | Cons |
|---|---|
| Fast reads (single index scan) | Write amplification: celebrity with 10M followers = 10M rows per post |
| Easy to personalize/rank | Storage-intensive |
| Predictable performance | Delayed delivery for large fan-outs |
Strategy 3: Hybrid (Production Standard)
- Regular users (<10K followers): Fan-out on write (push)
- Celebrities (>10K followers): Fan-out on read (pull)
- At read time, merge the user's feed table with real-time queries for celebrity accounts they follow
-- Hybrid feed query
WITH celebrity_posts AS (
SELECT p.id AS post_id, p.author_id, p.created_at
FROM posts p
WHERE p.author_id = ANY($celebrity_ids_followed)
AND p.created_at > NOW() - INTERVAL '7 days'
),
regular_feed AS (
SELECT post_id, author_id, created_at
FROM feed_items
WHERE user_id = $current_user
AND created_at > NOW() - INTERVAL '7 days'
)
SELECT * FROM (
SELECT * FROM celebrity_posts
UNION ALL
SELECT * FROM regular_feed
) combined
ORDER BY created_at DESC
LIMIT 20;
flowchart LR
W["New post by author A"] --> Q{"A has more than 10K followers?"}
Q -->|no| F["Fan-out worker: insert into followers' feeds"]
Q -->|yes| S["Store post only, mark A as pull-author"]
F --> FS[("feed store: per-user list")]
R["Viewer opens feed"] --> M["Merge: feed store page + recent posts of followed pull-authors"]
FS --> M
S --> P[("posts by author index")]
P --> M
M --> V["Visibility filter + hydrate + rank"]
Feed schema options in depth
The feed_items table above is the relational version of a per-user inbox. At scale, the inbox usually lives somewhere else. The three common shapes:
| Store | Shape | Strengths | Weaknesses |
|---|---|---|---|
PostgreSQL feed_items | Row per (user, post), index (user_id, post_id DESC) | Transactional, joins, easy to start | Billions of tiny rows; index bloat; vacuum pressure from trimming |
| Redis sorted set | Key feed:{user_id}, member post_id, score post_id (time-ordered) | Microsecond reads, cheap trim with ZREMRANGEBYRANK | Memory cost; must be rebuildable from the source of truth |
| Cassandra / wide-column | Partition per user, clustering by post_id DESC | Massive write throughput, cheap storage, natural TTL | No joins; tombstones from deletes; eventual consistency |
A wide-column version for reference:
-- CQL (Cassandra), not PostgreSQL
CREATE TABLE home_feed (
user_id bigint,
post_id bigint, -- Snowflake id, so it sorts by time
author_id bigint,
reason text, -- 'follow', 'repost:123', 'suggested'
PRIMARY KEY ((user_id), post_id)
) WITH CLUSTERING ORDER BY (post_id DESC)
AND default_time_to_live = 2592000; -- 30 days
Whichever store, the same design rules apply:
- Store ids, not content. The feed holds
post_idandauthor_id; content, counters and author profiles are hydrated from caches at read time. Edits and deletes then show up immediately, and the feed stays small. - Bound it. Keep the newest 500 to 1,000 entries per user. Nobody scrolls further, and older pages can fall back to pull.
- Skip inactive users. Fanning out to an account that has not logged in for 30 days wastes most of the write volume. Rebuild their feed with pull when they return.
- Fan out asynchronously. Post creation commits the post and enqueues a fan-out job; workers page through
idx_follows_followingin batches of a few thousand and write feed rows. Use Transactional Outbox so a crash between commit and enqueue does not lose the fan-out. - Handle the reverse operations. Unfollow removes that author's entries from the follower's feed (or just filters them at read time). Delete marks the post deleted; feeds skip it on hydration. Lazy cleanup beats synchronous fan-out of deletes.
- Make the celebrity threshold data, not code. A
pull_authors (author_id)table, maintained by a job fromfollower_count, lets you move accounts between modes without deploys. The feed query reads the viewer's followed pull-authors fromfollows JOIN pull_authors.
Ranked feeds change what is stored, not the shape: fan-out writes candidates, a ranking service scores them at read time with features (affinity, recency, engagement), and the feed store may keep a precomputed score. See Twitter Feed and Instagram for the full systems.
Hot-Path Queries with EXPLAIN Reasoning
Plans below are illustrative and trimmed, but they are the shapes PostgreSQL produces. See Query Planning & Optimization for reading plans in depth.
Q4: The naive pull feed
The original pull query joins follows to posts and sorts. For a user following 800 accounts:
Limit (rows=20)
-> Sort (Sort Key: p.created_at DESC) (rows=1,200,000)
-> Nested Loop
-> Index Only Scan using follows_pkey on follows f (rows=800)
Index Cond: (follower_id = 42)
-> Index Scan using idx_posts_author on posts p (rows=1500 per loop)
Index Cond: (author_id = f.following_id)
It reads every post ever written by all 800 authors, 1.2 million rows, then sorts to return 20. The index is right, but the query does not let the index's ordering help. Bound each author's contribution with a LATERAL subquery:
SELECT p.id, p.author_id, p.created_at
FROM follows f
CROSS JOIN LATERAL (
SELECT id, author_id, created_at
FROM posts
WHERE author_id = f.following_id
AND created_at < $cursor
AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 20
) p
WHERE f.follower_id = $me
ORDER BY p.created_at DESC
LIMIT 20;
Limit (rows=20)
-> Sort (Sort Key: p.created_at DESC) (rows=16,000)
-> Nested Loop (rows=16,000)
-> Index Only Scan using follows_pkey on follows f (rows=800)
-> Limit (rows=20)
-> Index Scan using idx_posts_author on posts (rows=20)
Index Cond: (author_id = f.following_id AND created_at < ...)
At most 20 rows per author, 16,000 total, a 75x reduction. Still 800 index probes per page, which is why pure pull does not survive large following counts and why the hybrid model limits pull to the few celebrity authors a user follows.
Q3: The pushed feed
SELECT post_id, author_id
FROM feed_items
WHERE user_id = $me AND post_id < $cursor_post_id
ORDER BY post_id DESC
LIMIT 20;
With an index on (user_id, post_id DESC) (with time-ordered ids, the primary key (user_id, post_id) scanned backwards does the job), this is one index range read of 20 entries: an index-only scan, no sort. The separate idx_feed_user (user_id, created_at DESC) becomes unnecessary once ids are time-ordered, which halves write cost on the largest table in the system.
Q5: Has the viewer liked these posts?
SELECT post_id FROM post_likes
WHERE user_id = $me AND post_id = ANY($post_ids); -- 20 ids
Index Only Scan using post_likes_pkey on post_likes
Index Cond: ((user_id = 42) AND (post_id = ANY ('{...}')))
Twenty probes into one user's slice of the primary key, no heap access. This query runs as often as the feed itself, which is why the primary key leads with user_id.
Q6: Followers list with keyset pagination
SELECT f.follower_id, f.created_at, u.username, u.display_name, u.avatar_url
FROM follows f
JOIN users u ON u.id = f.follower_id
WHERE f.following_id = $X
AND (f.created_at, f.follower_id) < ($cursor_ts, $cursor_id)
ORDER BY f.created_at DESC, f.follower_id DESC
LIMIT 20;
With the index defined as (following_id, created_at DESC, follower_id DESC), the row-value comparison becomes an index condition and the scan starts exactly where the last page ended. Page 1 and page 10,000 cost the same. With OFFSET 200000, the last page of a celebrity's followers would read 200,000 index entries first.
Q8: Notification badge
SELECT count(*) FROM (
SELECT 1 FROM notifications
WHERE user_id = $me AND NOT is_read
LIMIT 100
) s;
The partial index idx_notifications_unread (user_id) WHERE NOT is_read contains only unread rows. Most users have few, so the index is small and stays in memory. The LIMIT 100 caps the work for the user who never reads notifications.
Concurrency Hot Spots: Counters, Likes and Follows
Likes and the denormalized counter
A like is two writes: insert the like row, increment posts.like_count. They must agree, and a double-tap must not count twice. Do both in one statement so the increment happens only if the insert actually inserted:
-- Like: idempotent, counter moves only on a real insert.
WITH ins AS (
INSERT INTO post_likes (user_id, post_id) VALUES ($me, $post)
ON CONFLICT DO NOTHING
RETURNING post_id
)
UPDATE posts SET like_count = like_count + 1
WHERE id IN (SELECT post_id FROM ins);
-- Unlike: same pattern in reverse.
WITH del AS (
DELETE FROM post_likes WHERE user_id = $me AND post_id = $post
RETURNING post_id
)
UPDATE posts SET like_count = like_count - 1
WHERE id IN (SELECT post_id FROM del);
The primary key on post_likes makes duplicate likes impossible even with two concurrent taps from two devices: one insert wins, the other conflicts and does nothing, and the counter moves once.
The viral post problem
UPDATE posts SET like_count = like_count + 1 WHERE id = $post takes a row lock. Every like on the same post queues behind the previous one. A post receiving 10,000 likes per second means 10,000 transactions per second serialized on one row, each also creating a new row version for MVCC (so the posts row is rewritten 10,000 times a second, and vacuum has to clean all of them; see MVCC). Options:
- Sharded counters. Spread increments over N rows and sum on read:
CREATE TABLE post_like_counter_shards (
post_id BIGINT NOT NULL,
shard SMALLINT NOT NULL,
delta BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (post_id, shard)
);
INSERT INTO post_like_counter_shards (post_id, shard, delta)
VALUES ($post, floor(random() * 16)::smallint, 1)
ON CONFLICT (post_id, shard)
DO UPDATE SET delta = post_like_counter_shards.delta + 1;
-- Read: sum 16 small rows (or fold them into posts.like_count periodically).
SELECT COALESCE(SUM(delta), 0) FROM post_like_counter_shards WHERE post_id = $post;
Sixteen rows means sixteen independent locks: contention drops by roughly that factor.
-
Asynchronous aggregation. The like row is the source of truth; the counter is a cache. Insert likes, publish an event, and have a consumer apply
like_count = like_count + nin batches every second. One update per post per second instead of 10,000. The displayed count lags by a second, which nobody notices. -
Counters in Redis.
INCR post:{id}:likeshandles hundreds of thousands of increments per second per key. Persist periodically to Postgres, and rebuild frompost_likesif Redis loses data. See Redis Caching Patterns. -
Approximate display. Above 10,000, the UI shows "12.4K". Exactness beyond three significant digits is invisible, which is what licenses options 2 and 3.
Follows: two counters, two rows, one transaction
Following updates follows, the follower's following_count and the followee's follower_count. Use the same insert-then-increment-if-inserted pattern, and lock the two user rows in a consistent order (lower id first), because "A follows B" and "B follows A" happening simultaneously would otherwise lock the same two rows in opposite orders and deadlock:
BEGIN;
-- Lock both user rows in id order first, so opposite-direction follows
-- cannot deadlock. (An UPDATE ... WHERE id IN (...) does not promise an order.)
SELECT 1 FROM users WHERE id IN ($a, $b) ORDER BY id FOR NO KEY UPDATE;
WITH ins AS (
INSERT INTO follows (follower_id, following_id) VALUES ($a, $b)
ON CONFLICT DO NOTHING
RETURNING follower_id, following_id
)
UPDATE users u
SET follower_count = follower_count + CASE WHEN u.id = ins.following_id THEN 1 ELSE 0 END,
following_count = following_count + CASE WHEN u.id = ins.follower_id THEN 1 ELSE 0 END
FROM ins
WHERE u.id IN (ins.follower_id, ins.following_id);
COMMIT;
For a private target, the insert goes into follow_requests instead, and approval moves the row into follows in one transaction. A celebrity gaining 5,000 followers per second after a viral moment hits the same hot-row problem on follower_count; the sharded-counter and async-aggregation answers apply unchanged.
Counter drift and reconciliation
Asynchronous counters, bugs, and partial failures all make counters drift from the truth. A periodic job recomputes them from the source of truth in small batches (the original performance table mentions it), and the CHECK (... >= 0) constraints catch the most visible class of bug, the negative count, at write time.
Performance Considerations
| Concern | Solution |
|---|---|
| Feed query speed | Keyset pagination: WHERE created_at < $cursor ORDER BY created_at DESC LIMIT 20 |
| Counter accuracy | Use UPDATE ... SET like_count = like_count + 1 (atomic increment) |
| Counter drift | Periodic reconciliation job: UPDATE posts SET like_count = (SELECT COUNT(*) FROM likes WHERE ...) |
| Hashtag search | GIN index on post_tags.tag or full-text search |
| Mention extraction | Parse @username in application layer, store in mentions table, trigger notification |
| Hot posts (viral) | Cache hot post data in Redis; database only for persistence |
| Follower list for celebrity | Pre-compute in Redis sorted set; fan-out via background workers |
| Counter contention on viral posts | Sharded counter rows, batched async increments, or Redis INCR |
| Privacy on precomputed feeds | Re-check visibility at read time; clean up fanned-out rows asynchronously |
| Feed table growth | Trim to the newest ~800 entries per user; skip fan-out to inactive users |
Correction (2026-11-08): hashtag indexes
post_tags.tag is a scalar TEXT column, one tag per row, so the right index is the ordinary B-tree the schema already creates (idx_post_tags_tag, better as (tag, post_id DESC)). GIN is the right choice when tags are stored as an array on the post (posts.tags TEXT[] with WHERE tags @> ARRAY['postgres']) or for full-text search on content. A GIN index on a plain text column is not possible without the btree_gin extension and would be no faster than a B-tree for equality.
Keyset pagination on created_at alone can skip or repeat rows when two posts share a timestamp. Paginate on (created_at, id) or, with time-ordered ids, on id alone.
Schema Design Decisions
| Decision | Rationale |
|---|---|
| Denormalized counters on posts/users | COUNT(*) over millions of likes is too slow for real-time display |
| Composite PK on follows | Natural compound key; prevents duplicate follows at DB level |
| Separate media table | Posts can have 0-N media items; avoids array columns |
depth on comments | Allows limiting nesting (e.g., max depth 3) without recursive query |
visibility on posts | Supports public, followers-only, and private posts |
notification_type as ENUM | Finite set of notification types; enforced at DB level |
is_read index partial | Only unread notifications are queried frequently; partial index is smaller |
| JSONB avoided for core entities | Structured data deserves structured columns for indexing and validation |
post_likes PK leads with user_id | The batched "has viewer liked" check runs on every feed render |
| Time-ordered post ids | Feeds sort and paginate on one integer; no separate timestamp index |
Separate blocks, mutes, follow_requests tables | Each edge type gets its own constraints and indexes |
| Aggregated notification groups | One row per (recipient, type, target), not one per actor |
| Feed stores ids only | Edits, deletes and counter changes need no fan-out |
Evolution as It Scales
| Stage | Symptom | Change |
|---|---|---|
| 1. One Postgres primary | Early product | Pull feed with LATERAL, counters on rows, everything in one DB |
| 2. Caches and replicas | Profile and post reads dominate | Cache user and post objects in Redis or Memcached; route timelines and follower lists to replicas (Replication) |
| 3. Push feeds | Pull feed latency grows with following counts | Fan-out on write into Redis sorted sets; hybrid for celebrities |
| 4. Counters out | Like counters are the hottest rows | Redis counters or async batched increments; Postgres keeps the like rows |
| 5. Search out | Hashtags, text search, user search | Index posts and users into Elasticsearch via change data capture (Elasticsearch Internals) |
| 6. Partition and archive | posts, post_likes, notifications reach billions of rows | Range-partition notifications by time and drop old partitions; partition posts by id range |
| 7. Shard | Write volume beyond one primary | Shard by user id; feeds and likes into a wide-column store |
Choosing shard keys. Different tables want different keys, and that is fine as long as each hot query is single-shard:
| Table | Shard key | Why |
|---|---|---|
users, follows (forward) | user id / follower id | Profile and "who I follow" are local |
follows (inverse copy) | following id | Fan-out and followers list are local |
posts | author id | Author timeline is local; pull-merge reads a few shards |
feed_items | feed owner id | The feed read is a single-partition range |
post_likes | post id for likers lists, plus a user-keyed copy for "has viewer liked" | Both directions are hot; write both, like edges |
notifications | recipient id | Badge and list are local |
The general mechanics (hash versus range, resharding, hot shards when a celebrity's partition takes all the traffic) are in Sharding. For likes and feeds, a wide-column store like Cassandra is a common landing place because its partition-per-key model is the access pattern: see Cassandra Internals.
What stays relational the longest: users, follows, posts, comments and privacy edges. They have constraints worth enforcing and modest write rates. What leaves first: feeds (huge, derived, rebuildable), counters (hot), search (different query model) and notifications (high volume, time-bounded).
Common Mistakes
COUNT(*)for follower and like counts on every render. Denormalize, then protect the counter from hot-row contention.- Incrementing a counter without checking the insert happened. Double-taps and retries inflate counts; tie the increment to
INSERT ... ON CONFLICT DO NOTHING RETURNING. - Naive pull feed that reads every post of every followee. Bound each author with
LATERAL ... LIMIT. - Pure fan-out on write for celebrities. Ten million feed writes per post; use the hybrid.
- Storing post content in feed rows. Every edit or delete becomes a fan-out.
- Polymorphic like tables without integrity. Orphaned likes on deleted comments; use one table per target type.
OFFSETpagination for feeds and follower lists. Slow deep pages and duplicates as new rows arrive; use keyset cursors.- Privacy checked in some handlers but not others. One shared visibility function or view, applied to every path, including search and notifications.
- Forgetting blocks in precomputed data. Fanned-out feeds, suggestions and notification groups all need read-time filtering.
- Recursive threaded comments ordered by depth. Use a path array, or load top-level comments and replies separately.
- Indexing a unique column twice.
UNIQUEalready creates the index. - One notification row per like on a viral post. Aggregate by target.
- Graph queries of three or more hops online. Precompute offline or use a graph store.
Interview Walkthrough: "Design the Schema for a Twitter-like Product"
1. Scope (3 minutes). "Users post short text with media, follow others, see a home feed, like, comment, get notifications. Private accounts and blocking exist. 50 million DAU, 200 follows on average, celebrities with tens of millions of followers."
2. Access patterns (5 minutes). The feed read dominates; counters are read everywhere; posts are written about 20 times less than feeds are read, but each post fans out to hundreds of followers. Write the hot-query table.
3. Entities (7 minutes). Users; posts (with media and tags as child tables); follows as a self-referential M:N junction; likes as two junction tables; comments with a self-reference for threads; notifications; privacy edges (blocks, follow requests, audiences). Draw the ER diagram.
4. Tables, keys and indexes (10 minutes). Composite primary keys on every edge table. posts (author_id, created_at DESC) for timelines. follows indexed in both directions. post_likes (user_id, post_id) for the batched liked-check. Partial index for unread notifications. Time-ordered post ids.
5. The feed (10 minutes). Compare pull, push and hybrid; explain the LATERAL bounded pull, the feed store holding ids only, trimming, skipping inactive users, the celebrity threshold, and async fan-out through an outbox and queue. Say where the feed lives at scale (Redis sorted sets or Cassandra) and that it is rebuildable from posts and follows.
6. Concurrency (5 minutes). Idempotent like with insert-then-increment; the viral counter and its mitigations; follow with two counter updates in a consistent lock order; drift reconciliation.
7. Privacy and evolution (5 minutes). Visibility function on every read, read-time filtering of precomputed feeds, block cleanup. Then the scale ladder: caches, replicas, push feeds, counters out, search out, sharding by user with double-written edges.
Likely follow-ups:
- "A celebrity posts; how fast do followers see it?" Immediately, because celebrities are pulled at read time, not pushed.
- "A user unfollows someone; does their feed update?" The read-time filter hides the author's items at once; a background job deletes them.
- "How do you show an exact like count?" The like rows are exact; the displayed counter is eventually consistent and approximate past a threshold. If a product requirement truly needs exactness, count from the source in a background job and show the last computed value.
- "Why not a graph database?" The core queries are one hop. A relational adjacency list with two indexes answers them with index range scans; a graph store earns its keep only for multi-hop interactive queries.
Where to Go Next
- OLTP Database Design: the general principles behind these choices
- Twitter Feed, Instagram, Social Network: the full systems around this schema
- Notification System: delivering the notifications this schema stores
- Indexing Deep Dive: composite, partial and covering indexes
- Sharding and Cassandra Internals: where feeds and likes go at scale
- E-Commerce Schema Design, SaaS Multi-Tenant Schema Design, Chat Application Schema Design: the same method on other domains