On this page

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.

OperationSteady ratePeak rateRead or writeLatency targetNotes
Home feed load (page of 20)60,000/s200,000/sReadp99 < 200 msThe product. Everything else serves this
"Has viewer liked these 20 posts?"60,000/s200,000/sReadp99 < 20 msBatched, part of every feed render
Profile header (name, counts)20,000/s80,000/sReadp99 < 50 msCounters must be O(1) to read
User timeline (one author's posts)15,000/s60,000/sReadp99 < 100 msNewest first, keyset pages
Create post3,000/s20,000/sWritep99 < 300 msTriggers fan-out
Fan-out writes (feed rows)600,000/s4,000,000/sWriteSeconds, asyncPosts/s times average followers
Like / unlike30,000/s150,000/sWritep99 < 100 msOne viral post can take 10,000/s alone
Comment3,000/s20,000/sWritep99 < 200 msThreaded
Follow / unfollow2,000/s10,000/sWritep99 < 200 msChanges counters on two users
Notifications list10,000/s40,000/sReadp99 < 100 msUnread badge on every app open
Followers list2,000/s10,000/sReadp99 < 200 msPaginated, newest first
Hashtag / text search5,000/s30,000/sReadp99 < 300 msA search engine's job at this scale

What the numbers say:

  1. 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.
  2. Counters are read on every render and written in bursts. like_count must be cheap to read and must survive 10,000 increments per second on a single post. That is a hot-row problem.
  3. 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.
  4. 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 queryIndex
Q1User by username (profile URL)UNIQUE (username)
Q2Author timeline, newest firstposts (author_id, created_at DESC)
Q3Precomputed home feed pagefeed_items (user_id, created_at DESC) or a Redis sorted set
Q4Pull feed: recent posts of followed authorsfollows (follower_id, ...) plus Q2's index
Q5Has viewer liked posts in a batchpost_likes PRIMARY KEY (user_id, post_id)
Q6Followers of X, newest firstfollows (following_id, created_at DESC)
Q7Does A follow B / has A blocked Bcomposite primary keys
Q8Unread notifications count and listpartial (user_id, created_at DESC) WHERE NOT is_read
Q9Comments of a post, threadedcomments (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_username and idx_users_email are redundant: the UNIQUE constraints 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 with WHERE 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.
  • OFFSET pagination 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. Adding INCLUDE (follower_id) is unnecessary: follower_id is 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 id equals 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 by post_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:

  1. Two separate tables (post_likes, comment_likes) — cleaner, FK-safe
  2. Application-level enforcement — simpler schema, no DB-level FK
  3. 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 questionQuery shapeCost
Does A follow B?PK probeOne 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 ASelf-join on reversed edgeProportional to A's following count, one probe each
Friends of friends (2 hops)Join follows to followsFollowing count squared; 200 × 200 = 40,000 rows, worse for heavy followers
Shortest path, 3+ hopsRecursive CTEExplodes; 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 with PRIMARY 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;
ProsCons
Simple schemaSlow for users following 1000+ accounts
Always freshRequires joining follows + posts tables
No write amplificationHard 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;
ProsCons
Fast reads (single index scan)Write amplification: celebrity with 10M followers = 10M rows per post
Easy to personalize/rankStorage-intensive
Predictable performanceDelayed 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:

StoreShapeStrengthsWeaknesses
PostgreSQL feed_itemsRow per (user, post), index (user_id, post_id DESC)Transactional, joins, easy to startBillions of tiny rows; index bloat; vacuum pressure from trimming
Redis sorted setKey feed:{user_id}, member post_id, score post_id (time-ordered)Microsecond reads, cheap trim with ZREMRANGEBYRANKMemory cost; must be rebuildable from the source of truth
Cassandra / wide-columnPartition per user, clustering by post_id DESCMassive write throughput, cheap storage, natural TTLNo 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_id and author_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_following in 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 from follower_count, lets you move accounts between modes without deploys. The feed query reads the viewer's followed pull-authors from follows 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:

  1. 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.

  1. 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 + n in batches every second. One update per post per second instead of 10,000. The displayed count lags by a second, which nobody notices.

  2. Counters in Redis. INCR post:{id}:likes handles hundreds of thousands of increments per second per key. Persist periodically to Postgres, and rebuild from post_likes if Redis loses data. See Redis Caching Patterns.

  3. 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

ConcernSolution
Feed query speedKeyset pagination: WHERE created_at < $cursor ORDER BY created_at DESC LIMIT 20
Counter accuracyUse UPDATE ... SET like_count = like_count + 1 (atomic increment)
Counter driftPeriodic reconciliation job: UPDATE posts SET like_count = (SELECT COUNT(*) FROM likes WHERE ...)
Hashtag searchGIN index on post_tags.tag or full-text search
Mention extractionParse @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 celebrityPre-compute in Redis sorted set; fan-out via background workers
Counter contention on viral postsSharded counter rows, batched async increments, or Redis INCR
Privacy on precomputed feedsRe-check visibility at read time; clean up fanned-out rows asynchronously
Feed table growthTrim 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

DecisionRationale
Denormalized counters on posts/usersCOUNT(*) over millions of likes is too slow for real-time display
Composite PK on followsNatural compound key; prevents duplicate follows at DB level
Separate media tablePosts can have 0-N media items; avoids array columns
depth on commentsAllows limiting nesting (e.g., max depth 3) without recursive query
visibility on postsSupports public, followers-only, and private posts
notification_type as ENUMFinite set of notification types; enforced at DB level
is_read index partialOnly unread notifications are queried frequently; partial index is smaller
JSONB avoided for core entitiesStructured data deserves structured columns for indexing and validation
post_likes PK leads with user_idThe batched "has viewer liked" check runs on every feed render
Time-ordered post idsFeeds sort and paginate on one integer; no separate timestamp index
Separate blocks, mutes, follow_requests tablesEach edge type gets its own constraints and indexes
Aggregated notification groupsOne row per (recipient, type, target), not one per actor
Feed stores ids onlyEdits, deletes and counter changes need no fan-out

Evolution as It Scales

StageSymptomChange
1. One Postgres primaryEarly productPull feed with LATERAL, counters on rows, everything in one DB
2. Caches and replicasProfile and post reads dominateCache user and post objects in Redis or Memcached; route timelines and follower lists to replicas (Replication)
3. Push feedsPull feed latency grows with following countsFan-out on write into Redis sorted sets; hybrid for celebrities
4. Counters outLike counters are the hottest rowsRedis counters or async batched increments; Postgres keeps the like rows
5. Search outHashtags, text search, user searchIndex posts and users into Elasticsearch via change data capture (Elasticsearch Internals)
6. Partition and archiveposts, post_likes, notifications reach billions of rowsRange-partition notifications by time and drop old partitions; partition posts by id range
7. ShardWrite volume beyond one primaryShard 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:

TableShard keyWhy
users, follows (forward)user id / follower idProfile and "who I follow" are local
follows (inverse copy)following idFan-out and followers list are local
postsauthor idAuthor timeline is local; pull-merge reads a few shards
feed_itemsfeed owner idThe feed read is a single-partition range
post_likespost id for likers lists, plus a user-keyed copy for "has viewer liked"Both directions are hot; write both, like edges
notificationsrecipient idBadge 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

  1. COUNT(*) for follower and like counts on every render. Denormalize, then protect the counter from hot-row contention.
  2. Incrementing a counter without checking the insert happened. Double-taps and retries inflate counts; tie the increment to INSERT ... ON CONFLICT DO NOTHING RETURNING.
  3. Naive pull feed that reads every post of every followee. Bound each author with LATERAL ... LIMIT.
  4. Pure fan-out on write for celebrities. Ten million feed writes per post; use the hybrid.
  5. Storing post content in feed rows. Every edit or delete becomes a fan-out.
  6. Polymorphic like tables without integrity. Orphaned likes on deleted comments; use one table per target type.
  7. OFFSET pagination for feeds and follower lists. Slow deep pages and duplicates as new rows arrive; use keyset cursors.
  8. Privacy checked in some handlers but not others. One shared visibility function or view, applied to every path, including search and notifications.
  9. Forgetting blocks in precomputed data. Fanned-out feeds, suggestions and notification groups all need read-time filtering.
  10. Recursive threaded comments ordered by depth. Use a path array, or load top-level comments and replies separately.
  11. Indexing a unique column twice. UNIQUE already creates the index.
  12. One notification row per like on a viral post. Aggregate by target.
  13. 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