On this page

Chat Application Schema Design

Chat schemas must handle high write throughput (messages), efficient read patterns (load channel history), and real-time state (who's online, who's read what). The critical design tension is between write speed and read flexibility — especially for features like threads, reactions, and search.

This page builds the schema in the order you would design it: requirements and access patterns first, then entities and tables, then the parts that make chat hard — message ordering with per-conversation sequence numbers, idempotent sends, read receipts and unread counts that stay cheap with thousands of channels per user, DMs versus groups, attachments, search and retention — and finally how the message store evolves when one PostgreSQL table can no longer hold it. 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 workplace chat product: 20 million daily actives, an average user in 40 channels and 30 DMs, channel sizes from 2 to 100,000 members, and message history kept for years unless a retention policy says otherwise.

OperationSteady ratePeak rateRead or writeLatency targetNotes
Send message50,000/s200,000/sWritep99 < 100 ms to ackMust be ordered within a conversation and never duplicated
Real-time delivery to online members2,000,000/s8,000,000/sPush< 500 msFan-out through a broker; not a database write per recipient
Load channel history (page of 50)30,000/s100,000/sReadp99 < 100 msNewest first, then scroll back
Sidebar: my channels with unread counts20,000/s80,000/sReadp99 < 100 msEvery app open and reconnect; 70+ channels each
Mark as read100,000/s300,000/sWriteAsync-tolerantEvery time a user looks at a channel
Reconnect sync: what did I miss20,000/s200,000/sReadp99 < 200 msAfter network drops; thundering herd after outages
Thread load5,000/s20,000/sReadp99 < 100 msParent plus replies
Reactions add/remove10,000/s50,000/sWritep99 < 100 ms
Search2,000/s8,000/sReadp99 < 500 msOnly over channels the user can see
Attachment upload2,000/s10,000/sWriteSecondsBytes to object storage; metadata to DB
Presence and typing500,000/s2,000,000/sEphemeral< 200 msNever touches the database

What the numbers say:

  1. Writes are high and per-conversation ordered. Two people typing at once in one channel must see the same order, and a retried send must not appear twice. That is a schema problem (sequence numbers and idempotency keys), not just an application one.
  2. "Mark as read" is the biggest write stream after messages. It must be a tiny, in-place, no-op-if-unchanged update, and unread counts must be computable without counting messages.
  3. History reads are always "one conversation, a contiguous range, newest first". That is the most index-friendly access pattern there is, and it is also exactly the shape a wide-column store like Cassandra serves best, which is why chat history is one of the first workloads to leave a relational database at scale.
  4. Ephemeral state is not data. Presence and typing indicators stay in memory (Redis, the WebSocket tier). Putting them in Postgres would multiply the write load tenfold for information that is stale in seconds.

Hot queries and the indexes they need:

#Hot queryIndex
Q1Channel history page, newest firstmessages PRIMARY KEY (channel_id, seq) scanned backwards
Q2Messages after seq N (reconnect sync)same primary key, forward range
Q3My channels ordered by activity, with unread countschannel_members (user_id, ...) plus channels.last_seq
Q4Find the DM between two usersdm_channels PRIMARY KEY (user_id_low, user_id_high)
Q5Thread replies in order(channel_id, thread_root_seq, seq) WHERE thread_root_seq IS NOT NULL
Q6Reactions for a page of messagesreactions PRIMARY KEY (message_id, user_id, emoji)
Q7Dedupe a retried sendUNIQUE (channel_id, sender_id, client_msg_id)
Q8Who has read message Schannel_members (channel_id, last_read_seq)
Q9Full-text search in my channelsGIN on a stored tsvector, or a search engine

Entity Relationship Overview

erDiagram
    USERS ||--o{ CHANNEL_MEMBERS : joins
    USERS ||--o{ MESSAGES : sends
    USERS ||--o{ READ_RECEIPTS : tracks
    USERS ||--o{ REACTIONS : adds

    CHANNELS ||--o{ CHANNEL_MEMBERS : has
    CHANNELS ||--o{ MESSAGES : contains
    CHANNELS ||--o{ PINNED_MESSAGES : pins

    MESSAGES ||--o{ ATTACHMENTS : has
    MESSAGES ||--o{ REACTIONS : receives
    MESSAGES ||--o{ MESSAGES : thread_replies
    MESSAGES ||--o{ READ_RECEIPTS : tracked_by

The extended model with the tables and columns this page adds, keys only:

erDiagram
    CHANNELS ||--o{ CHANNEL_MEMBERS : "has"
    USERS ||--o{ CHANNEL_MEMBERS : "member of"
    CHANNELS ||--o| DM_CHANNELS : "is a DM"
    CHANNELS ||--o{ MESSAGES : "contains"
    MESSAGES ||--o{ MESSAGE_EDITS : "edit history"
    MESSAGES ||--o{ ATTACHMENTS : "has"
    UPLOADS ||--o{ ATTACHMENTS : "stored as"
    CHANNELS ||--o| RETENTION_POLICIES : "governed by"

    CHANNELS {
        bigint id PK
        channel_type type
        bigint last_seq "highest seq allocated"
        timestamptz last_message_at
    }
    CHANNEL_MEMBERS {
        bigint channel_id PK, FK
        bigint user_id PK, FK
        bigint last_read_seq
        bigint last_delivered_seq
        int mention_count
    }
    DM_CHANNELS {
        bigint user_id_low PK
        bigint user_id_high PK
        bigint channel_id UK
    }
    MESSAGES {
        bigint channel_id PK, FK
        bigint seq PK "gapless per channel"
        bigint id UK "global id"
        bigint sender_id FK
        uuid client_msg_id "idempotency key"
        bigint thread_root_seq "nullable"
    }
    MESSAGE_EDITS {
        bigint message_id FK
        int version
        text content
    }
    UPLOADS {
        uuid id PK
        text sha256
        text status "pending, ready, rejected"
    }
    ATTACHMENTS {
        bigint id PK
        bigint message_id FK
        uuid upload_id FK
    }
    RETENTION_POLICIES {
        bigint channel_id PK, FK
        int keep_days
        bool legal_hold
    }

Two modelling observations:

  • A DM is a channel. Rather than separate dm_messages and channel_messages tables, everything that holds messages is a channels row with a type. One message table, one history query, one set of indexes; the DM-specific part (finding the DM between two people) is a small lookup table. This is the specialization pattern from ER modeling, mapped as single-table inheritance with a side table for the subtype's extra key.
  • Read state belongs to the membership. "How far has Asha read in #general" is an attribute of the Asha-in-#general relationship, so it lives on channel_members (or its 1:1 companion read_receipts), not on messages.

Core Tables

Users

CREATE TABLE users (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username     TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    email        TEXT NOT NULL UNIQUE,
    avatar_url   TEXT,
    status       TEXT DEFAULT 'offline',       -- 'online', 'away', 'dnd', 'offline'
    status_text  TEXT,                         -- custom status message
    last_seen    TIMESTAMPTZ DEFAULT NOW(),
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    updated_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_username ON users(username);

Online Status

Don't store real-time online status in the database. Use Redis or an in-memory presence service. The database last_seen timestamp is updated periodically (every 60 seconds) for "last active" display, not for real-time presence.

idx_users_username duplicates the unique index created by username ... UNIQUE; drop it. The status column here is the user's chosen status ("dnd", a custom message), which changes rarely; live online/offline presence is a separate, in-memory concern as the tip says. Even the periodic last_seen write should be conditional (WHERE last_seen < NOW() - INTERVAL '60 seconds') so that heartbeats from many devices do not each create a new row version.

Channels

CREATE TYPE channel_type AS ENUM ('public', 'private', 'direct');

CREATE TABLE channels (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name         TEXT,                          -- NULL for DMs
    type         channel_type NOT NULL DEFAULT 'public',
    description  TEXT,
    avatar_url   TEXT,
    created_by   BIGINT REFERENCES users(id),
    is_archived  BOOLEAN DEFAULT FALSE,
    -- Denormalized
    member_count INT DEFAULT 0,
    last_message_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    updated_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE channel_members (
    channel_id   BIGINT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
    user_id      BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role         TEXT NOT NULL DEFAULT 'member',  -- 'owner', 'admin', 'member'
    nickname     TEXT,                            -- channel-specific display name
    is_muted     BOOLEAN DEFAULT FALSE,
    notification_pref TEXT DEFAULT 'all',         -- 'all', 'mentions', 'none'
    joined_at    TIMESTAMPTZ DEFAULT NOW(),

    PRIMARY KEY (channel_id, user_id)
);

CREATE INDEX idx_channel_members_user ON channel_members(user_id);
CREATE INDEX idx_channels_last_message ON channels(last_message_at DESC NULLS LAST);

The columns that make ordering and unread counts cheap, added to the same two tables:

ALTER TABLE channels
    ADD COLUMN last_seq BIGINT NOT NULL DEFAULT 0,              -- highest message seq
    ADD COLUMN last_message_preview TEXT,                        -- for the sidebar
    ADD COLUMN last_sender_id BIGINT REFERENCES users(id),
    ADD CONSTRAINT chk_dm_has_no_name CHECK (type <> 'direct' OR name IS NULL),
    ADD CONSTRAINT chk_named_channel CHECK (type = 'direct' OR name IS NOT NULL);

ALTER TABLE channel_members
    ADD COLUMN last_read_seq      BIGINT NOT NULL DEFAULT 0,
    ADD COLUMN last_delivered_seq BIGINT NOT NULL DEFAULT 0,
    ADD COLUMN mention_count      INT    NOT NULL DEFAULT 0 CHECK (mention_count >= 0),
    ADD COLUMN is_hidden          BOOLEAN NOT NULL DEFAULT FALSE,   -- closed DM
    ADD CONSTRAINT chk_member_role CHECK (role IN ('owner', 'admin', 'member')),
    ADD CONSTRAINT chk_notif_pref  CHECK (notification_pref IN ('all', 'mentions', 'none'));

-- Q3: "my channels" is driven from the user side. Covering the columns the
-- sidebar needs from the membership keeps the probe index-only.
CREATE INDEX idx_members_by_user
    ON channel_members (user_id, channel_id)
    INCLUDE (last_read_seq, mention_count, is_muted)
    WHERE NOT is_hidden;

-- Q8: "who has read up to message S" in a channel.
CREATE INDEX idx_members_read_pos ON channel_members (channel_id, last_read_seq);

The original idx_channel_members_user (user_id) is superseded by idx_members_by_user. The global idx_channels_last_message index is rarely useful on its own: no query asks for "all channels in the system by activity". The sidebar asks for my channels by activity, which is driven from channel_members, then sorted (see the EXPLAIN section).

Direct Messages

Direct messages are channels with type = 'direct'. For two users, create one channel and add both as members.

-- Create or find a DM channel between two users
-- Use a deterministic lookup to avoid duplicates

CREATE TABLE dm_channels (
    channel_id   BIGINT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
    user_id_low  BIGINT NOT NULL REFERENCES users(id),
    user_id_high BIGINT NOT NULL REFERENCES users(id),

    PRIMARY KEY (user_id_low, user_id_high),
    CHECK (user_id_low < user_id_high)  -- canonical ordering
);
-- Find or create DM channel
-- Always order user IDs so (low, high) is deterministic
WITH existing AS (
    SELECT channel_id FROM dm_channels
    WHERE user_id_low = LEAST($user_a, $user_b)
      AND user_id_high = GREATEST($user_a, $user_b)
)
SELECT channel_id FROM existing

-- If no result, create:
-- INSERT INTO channels (type) VALUES ('direct') RETURNING id;
-- INSERT INTO dm_channels (channel_id, user_id_low, user_id_high) VALUES (...);
-- INSERT INTO channel_members (channel_id, user_id) VALUES (...), (...);

"Find, and if not found, create" is a race: two people opening a DM with each other at the same moment both find nothing and both create. The canonical-order primary key makes the race harmless, because only one dm_channels row can exist for the pair. Let the constraint arbitrate:

ALTER TABLE dm_channels ADD CONSTRAINT uq_dm_channel UNIQUE (channel_id);

BEGIN;
INSERT INTO channels (type) VALUES ('direct') RETURNING id;            -- :new_id
INSERT INTO dm_channels (channel_id, user_id_low, user_id_high)
VALUES (:new_id, LEAST($a, $b), GREATEST($a, $b))
ON CONFLICT (user_id_low, user_id_high) DO NOTHING
RETURNING channel_id;
-- 1 row:  we created it. Insert both channel_members rows, then COMMIT.
-- 0 rows: someone else won. ROLLBACK (discarding our unused channel row),
--         then SELECT channel_id FROM dm_channels WHERE ... in a new statement.
COMMIT;

The lookup after a lost race must be a new statement (or a new transaction): under read committed, a statement's snapshot is taken when it starts, so a query in the same statement as the conflicting insert may not see the winner's row.

DMs vs Groups vs Channels

The three conversation kinds share one message table but differ in nearly everything else:

Aspect1:1 DMGroup DM (3 to ~50)Channel (up to 100K+)
IdentityThe pair of usersThe set of membersA name and an id
Find existingdm_channels by (low, high)Hash of sorted member idsBy name within a workspace
Membership changesNeverRare; usually a new conversationConstant
Fan-out per message1 recipientTensUp to 100,000; only online members get a push
Read receipts shown"Seen" per messagePer-member ticks, practicalNever per member; only your own unread state
Unread semanticsEvery message countsEvery message countsOften "has unread" dot plus mention count only
Notification defaultAllAllMentions only
Sequence-number contentionNoneNoneReal for very active channels

Group DMs are identified by their member set. To find "the existing group with exactly Asha, Ben and Chen", store a canonical key:

ALTER TABLE channels ADD COLUMN member_set_key TEXT;   -- only for group DMs

-- e.g. sha256 of '17,42,99' (sorted member ids), computed by the application
CREATE UNIQUE INDEX uq_group_dm_members ON channels (member_set_key)
    WHERE member_set_key IS NOT NULL;

Adding someone to a group DM usually creates a new conversation (that is what most products do), precisely so that the member-set identity stays stable and the new member does not see history they were not part of. Channels are the opposite: membership changes freely, and history visibility is governed by the channel type, not by when you joined.

Messages

CREATE TABLE messages (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    channel_id   BIGINT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
    sender_id    BIGINT NOT NULL REFERENCES users(id),
    content      TEXT,                         -- NULL if only attachment
    message_type TEXT NOT NULL DEFAULT 'text', -- 'text', 'system', 'file'
    thread_id    BIGINT REFERENCES messages(id) ON DELETE SET NULL, -- parent message for threads
    is_edited    BOOLEAN DEFAULT FALSE,
    is_deleted   BOOLEAN DEFAULT FALSE,        -- soft delete (show "message deleted")
    metadata     JSONB DEFAULT '{}',           -- link previews, embeds, etc.
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    updated_at   TIMESTAMPTZ DEFAULT NOW()
);

-- Primary query: load channel history (newest first)
CREATE INDEX idx_messages_channel ON messages(channel_id, created_at DESC);

-- Thread replies
CREATE INDEX idx_messages_thread ON messages(thread_id, created_at)
    WHERE thread_id IS NOT NULL;

-- User's messages (for search, moderation)
CREATE INDEX idx_messages_sender ON messages(sender_id, created_at DESC);

Message Deletion

Most chat apps use soft deletes. The message row remains but is_deleted = TRUE and the UI shows "This message was deleted." Hard deletes break threading, read receipts, and reactions that reference the message ID.

This table orders messages by created_at. That is the weakest part of the original design, and the next section replaces it.

Ordering: per-conversation sequence numbers

Ordering by created_at has three problems:

  1. Ties. Two messages in the same millisecond have no defined order, and keyset pagination on created_at < $cursor can skip one of them at a page boundary.
  2. Clock skew and commit order. NOW() is the transaction start time on whichever server ran it. A message whose transaction started first but committed second appears "earlier" yet becomes visible later, so a client that already fetched "everything up to 10:00:00.120" never sees it.
  3. No gap detection. A client cannot tell whether it has every message, because timestamps are not contiguous.

The fix is a per-channel sequence number: message 1, 2, 3, ... within each channel, allocated at write time, gapless, and used as the ordering, the pagination cursor and the read position.

ALTER TABLE messages
    ADD COLUMN seq             BIGINT,
    ADD COLUMN client_msg_id   UUID,                 -- idempotency key from the client
    ADD COLUMN thread_root_seq BIGINT,               -- seq of the thread parent, if a reply
    ADD COLUMN edited_at       TIMESTAMPTZ;
-- (reply_count and last_reply_at are added in the Threads section below.)

-- In a new table these would be the primary key itself:
--   PRIMARY KEY (channel_id, seq), with id as a UNIQUE global identifier.
ALTER TABLE messages ALTER COLUMN seq SET NOT NULL;
CREATE UNIQUE INDEX uq_messages_channel_seq ON messages (channel_id, seq);

-- Q7: a retried send with the same client id is rejected, not duplicated.
CREATE UNIQUE INDEX uq_messages_client_id
    ON messages (channel_id, sender_id, client_msg_id)
    WHERE client_msg_id IS NOT NULL;

-- Q5: thread replies in order.
CREATE INDEX idx_messages_thread_seq ON messages (channel_id, thread_root_seq, seq)
    WHERE thread_root_seq IS NOT NULL;

Allocating the sequence and inserting the message happen in one transaction, so a failed insert also rolls back the increment and no gap appears:

BEGIN;
WITH s AS (
    UPDATE channels
    SET last_seq = last_seq + 1,
        last_message_at = NOW(),
        last_message_preview = left($content, 140),
        last_sender_id = $sender
    WHERE id = $channel
    RETURNING last_seq
)
INSERT INTO messages (channel_id, seq, sender_id, content, client_msg_id)
SELECT $channel, s.last_seq, $sender, $content, $client_msg_id FROM s
RETURNING id, seq, created_at;

-- The sender has obviously read their own message.
UPDATE channel_members SET last_read_seq = $returned_seq
WHERE channel_id = $channel AND user_id = $sender AND last_read_seq < $returned_seq;
COMMIT;

Properties of this design:

  • The channel row lock serializes sends within one channel, which is exactly the ordering guarantee you want: commit order equals sequence order, so a client that has seen seq 41 knows nothing below 41 can appear later.
  • Different channels never contend. Throughput scales with the number of channels, and the per-channel limit (one short transaction at a time, a few thousand per second) is far beyond what any human conversation produces.
  • A retry is safe. If the client resends with the same client_msg_id, the unique index rejects the insert, the whole transaction (including the last_seq increment) rolls back, and the server answers with the existing message: SELECT id, seq FROM messages WHERE channel_id = $1 AND sender_id = $2 AND client_msg_id = $3. Do not use ON CONFLICT DO NOTHING here; it would keep the increment and leave a gap.
  • The global id stays as a stable identifier for reactions, attachments, links and search results. seq is the order within a channel.

When per-channel counters are too hot

A broadcast channel with 100,000 members where bots post thousands of messages per second can saturate its single counter row. Options: allocate sequence ranges in batches to a single writer process per hot channel, keep the counter in Redis (INCR chan:{id}:seq) and accept the rare gap on failure (clients then treat gaps as "fetch to confirm", not as data loss), or use time-ordered Snowflake ids, which are unique and roughly ordered but not gapless. See ID Generation and Ordering Guarantees.

Threads

Threads use the thread_id column on messages. The first message in a thread is the "parent" — its thread_id is NULL. All replies point their thread_id to the parent's id.

-- Denormalize thread metadata on the parent message
ALTER TABLE messages ADD COLUMN reply_count INT DEFAULT 0;
ALTER TABLE messages ADD COLUMN last_reply_at TIMESTAMPTZ;

-- Load a thread
SELECT m.*, u.username, u.avatar_url
FROM messages m
JOIN users u ON u.id = m.sender_id
WHERE m.thread_id = $parent_message_id
   OR m.id = $parent_message_id        -- include the parent
ORDER BY m.created_at;

With sequence numbers, a reply carries thread_root_seq and the thread loads as an ordered range on idx_messages_thread_seq:

SELECT seq, sender_id, content, created_at
FROM messages
WHERE channel_id = $channel
  AND (seq = $root_seq OR thread_root_seq = $root_seq)
ORDER BY seq;

Replies take their seq from the same channel counter, so a thread interleaves correctly with the channel. Posting a reply increments the parent's reply_count in the same transaction; on a very hot thread that parent row becomes a contended counter, and the same mitigations as for social-media like counts apply (batch the increment asynchronously, or keep the count in Redis). Per-user thread read state, for "unread replies in threads you follow", is a small thread_subscriptions (user_id, channel_id, root_seq, last_read_seq) table.

Read Receipts

CREATE TABLE read_receipts (
    channel_id     BIGINT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
    user_id        BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    last_read_id   BIGINT NOT NULL REFERENCES messages(id),
    last_read_at   TIMESTAMPTZ DEFAULT NOW(),

    PRIMARY KEY (channel_id, user_id)
);

Unread count calculation:

-- Unread message count for a user in a channel
SELECT COUNT(*)
FROM messages m
WHERE m.channel_id = $channel_id
  AND m.created_at > (
      SELECT last_read_at
      FROM read_receipts
      WHERE channel_id = $channel_id AND user_id = $user_id
  )
  AND m.sender_id != $user_id
  AND NOT m.is_deleted;

-- All channels with unread counts (for sidebar)
SELECT
    cm.channel_id,
    c.name,
    c.type,
    c.last_message_at,
    COALESCE(unread.cnt, 0) AS unread_count
FROM channel_members cm
JOIN channels c ON c.id = cm.channel_id
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM messages m
    WHERE m.channel_id = cm.channel_id
      AND m.sender_id != cm.user_id
      AND m.created_at > COALESCE(
          (SELECT last_read_at FROM read_receipts
           WHERE channel_id = cm.channel_id AND user_id = cm.user_id),
          cm.joined_at
      )
      AND NOT m.is_deleted
) unread ON TRUE
WHERE cm.user_id = $user_id
  AND NOT c.is_archived
ORDER BY c.last_message_at DESC NULLS LAST;

Correction (2026-11-08)

The queries above compare each message's created_at with last_read_at, the moment the user performed the read. That is a different fact from how far they read. If Asha opens a channel at 10:00 but has only scrolled to a message from 09:00, every message between 09:00 and 10:00 is wrongly treated as read. The read position is last_read_id (or, with sequence numbers, last_read_seq), and the comparison should be against the message's id or seq, never its timestamp. The sidebar version also runs one COUNT(*) per channel, which for a user in 70 channels with busy ones means scanning thousands of index entries on every app open.

Optimizing Unread Counts

For high-traffic channels, counting unread messages per query is expensive. Alternatives:

  1. Denormalize: Maintain an unread_count column on channel_members. Increment on new message, reset on read.
  2. Approximation: Store last_read_message_id and compare to max(message.id) in the channel. If they differ, show "unread" without counting.
  3. Redis counter: Maintain per-user-per-channel unread count in Redis. Increment atomically on message, reset on read.

Unread counts in O(1) with sequence numbers

With a gapless per-channel sequence, there is a fourth option that beats all three: subtraction.

unread(user, channel) = channels.last_seq - channel_members.last_read_seq

No counting, no per-message writes to every member, no Redis. Option 1 above (an unread_count per member) means a channel with 10,000 members performs 10,000 counter updates per message: write amplification proportional to channel size. Subtraction costs nothing on send (the channel's last_seq is already being incremented) and one in-place update on read. The send transaction advances the sender's own last_read_seq, so your own messages never count as unread.

Two refinements:

  • Deleted messages and system messages still consume a seq, so the subtraction overcounts slightly when messages are deleted. Products accept this (show "9+" or a dot), or subtract a per-channel deleted_count maintained on delete, which is a rare operation.
  • Mentions are counted separately, because they are rare and important: the send path parses mentions and increments mention_count only for the mentioned members (UPDATE channel_members SET mention_count = mention_count + 1 WHERE channel_id = $c AND user_id = ANY($mentioned)). For @channel in a huge channel, skip the per-member increment and store a last_broadcast_seq on the channel instead; a member has an unread broadcast mention if last_broadcast_seq > last_read_seq.

Marking as read is the second-largest write stream, so make it as cheap as possible:

UPDATE channel_members
SET last_read_seq = $seq, mention_count = 0
WHERE channel_id = $channel AND user_id = $me
  AND last_read_seq < $seq;          -- monotonic, and a no-op if nothing changed

The last_read_seq < $seq predicate does two jobs: read positions only move forward (two devices reporting out of order cannot move it backwards), and a repeated report with the same position matches zero rows, so PostgreSQL writes nothing, creates no dead tuple and generates no WAL. Clients debounce read reports (at most one per channel every few seconds), and because none of the updated columns are indexed by anything except idx_members_read_pos, many updates can be HOT updates that do not touch most indexes. The original read_receipts table can carry the same last_read_seq column instead; folding read state into channel_members saves a join on the hottest read path.

Read receipts per message ("Seen by")

DMs and small groups show per-message receipts ("Delivered", "Read by Asha, Ben"). Storing one row per message per member is the obvious design and the wrong one: a 50-person group generates 50 receipt rows for every message. With sequence numbers you need no per-message rows at all, because receipts are monotonic:

-- Who has read message 1042 in channel 7? Everyone whose read position is at or past it.
SELECT user_id FROM channel_members
WHERE channel_id = 7 AND last_read_seq >= 1042 AND user_id <> $sender;

-- Delivered works the same way with last_delivered_seq, advanced when a
-- device acknowledges receipt over the WebSocket.

idx_members_read_pos (channel_id, last_read_seq) makes this a range scan. For channels beyond a few hundred members, products do not show per-member receipts at all.

Reactions

CREATE TABLE reactions (
    message_id   BIGINT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    user_id      BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    emoji        TEXT NOT NULL,                -- ':thumbsup:', unicode, or custom

    PRIMARY KEY (message_id, user_id, emoji)
);

CREATE INDEX idx_reactions_message ON reactions(message_id);
-- Get reactions grouped by emoji for a message
SELECT
    emoji,
    COUNT(*) AS count,
    ARRAY_AGG(user_id) AS user_ids
FROM reactions
WHERE message_id = $1
GROUP BY emoji;

-- Batch: reactions for multiple messages (avoids N+1)
SELECT
    message_id,
    emoji,
    COUNT(*) AS count,
    ARRAY_AGG(user_id) AS user_ids
FROM reactions
WHERE message_id = ANY($message_ids)
GROUP BY message_id, emoji;

idx_reactions_message (message_id) is redundant: the primary key leads with message_id and serves both queries by the leftmost-prefix rule. Add created_at to the table so the "who reacted" list can show the first reactors, and cap ARRAY_AGG (a message with 5,000 reactions should not ship 5,000 user ids to every client; send counts plus "did I react" and load the full list on demand). For very popular messages in huge channels, keep a denormalized reaction_summary JSONB on the message ({"thumbsup": 1203, "tada": 87}), updated asynchronously.

Attachments

CREATE TABLE attachments (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    message_id   BIGINT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    file_name    TEXT NOT NULL,
    file_type    TEXT NOT NULL,               -- MIME type: 'image/png', 'application/pdf'
    file_size    BIGINT NOT NULL,             -- bytes
    url          TEXT NOT NULL,               -- S3/CDN URL
    thumbnail_url TEXT,
    width        INT,                         -- for images/videos
    height       INT,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_attachments_message ON attachments(message_id);

Bytes never go through the database or even the API servers. The client asks for an upload slot, uploads directly to object storage with a pre-signed URL, and only then sends a message that references the finished upload:

CREATE TABLE uploads (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    uploader_id  BIGINT NOT NULL REFERENCES users(id),
    channel_id   BIGINT NOT NULL REFERENCES channels(id),
    storage_key  TEXT   NOT NULL UNIQUE,         -- object key, not a public URL
    sha256       TEXT,                           -- set after upload, for dedupe
    file_size    BIGINT CHECK (file_size > 0),
    mime_type    TEXT,
    status       TEXT NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending', 'scanning', 'ready', 'rejected')),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Orphan cleanup: uploads that never became a message.
CREATE INDEX idx_uploads_pending ON uploads (created_at) WHERE status IN ('pending', 'scanning');

ALTER TABLE attachments ADD COLUMN upload_id UUID REFERENCES uploads(id);

Design points:

  • Store the object key, not a permanent public URL. Access is checked at download time (is the requester a member of the channel?) and a short-lived signed URL is issued. A permanent CDN URL in the database is a link that keeps working after someone leaves the channel or the message is deleted.
  • Scan before ready. A malware or content scan moves the upload from scanning to ready or rejected; the message renders a placeholder until then.
  • Deduplicate by content hash within a workspace: the same 40 MB deck forwarded to ten channels is stored once, referenced ten times. Deletion then becomes reference counting, so delete bytes only when no attachment references the upload.
  • Retention applies to bytes too. Deleting messages without deleting their objects is a common compliance gap.

See Blob Storage for the storage side.

Pinned Messages

CREATE TABLE pinned_messages (
    channel_id   BIGINT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
    message_id   BIGINT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    pinned_by    BIGINT NOT NULL REFERENCES users(id),
    pinned_at    TIMESTAMPTZ DEFAULT NOW(),

    PRIMARY KEY (channel_id, message_id)
);

Edits and deletions

is_edited and is_deleted record that something changed, not what. Workplace products need the history (moderation, compliance exports, legal holds):

CREATE TABLE message_edits (
    message_id BIGINT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    version    INT    NOT NULL,
    content    TEXT,
    edited_by  BIGINT NOT NULL REFERENCES users(id),
    edited_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (message_id, version)
);

An edit copies the old content into message_edits and updates the message in one transaction. An edit or delete must also reach connected clients, so it is published as an event, and clients that were offline learn about it on sync. A common approach is a per-channel channel_events log with its own sequence (message created, edited, deleted, reaction added), so reconnect sync is "give me events after event seq N" and covers edits as well as new messages.

Common Queries

Load Channel History (Paginated)

-- Keyset pagination (cursor-based, efficient)
SELECT
    m.id, m.content, m.message_type, m.thread_id,
    m.reply_count, m.is_edited, m.is_deleted,
    m.created_at,
    u.id AS sender_id, u.username, u.avatar_url
FROM messages m
JOIN users u ON u.id = m.sender_id
WHERE m.channel_id = $channel_id
  AND m.created_at < $cursor_timestamp   -- previous page's oldest message
ORDER BY m.created_at DESC
LIMIT 50;

-- First page (no cursor)
-- Remove the AND m.created_at < $cursor line

With sequence numbers the cursor is the seq, ties are impossible, and the user join is better done once per page from a cache (a page of 50 messages typically has a handful of distinct senders):

SELECT id, seq, sender_id, content, message_type, thread_root_seq,
       reply_count, edited_at, is_deleted, created_at
FROM messages
WHERE channel_id = $channel_id
  AND seq < $cursor_seq                 -- omit on the first page
ORDER BY seq DESC
LIMIT 50;

Search Messages

-- Full-text search within a channel
CREATE INDEX idx_messages_search ON messages
    USING GIN (to_tsvector('english', COALESCE(content, '')));

SELECT
    m.id, m.content, m.created_at,
    u.username,
    c.name AS channel_name,
    ts_rank(to_tsvector('english', m.content), query) AS rank
FROM messages m
JOIN users u ON u.id = m.sender_id
JOIN channels c ON c.id = m.channel_id
CROSS JOIN plainto_tsquery('english', $search_term) query
WHERE m.channel_id = ANY(
    SELECT channel_id FROM channel_members WHERE user_id = $user_id
)
AND to_tsvector('english', m.content) @@ query
AND NOT m.is_deleted
ORDER BY rank DESC, m.created_at DESC
LIMIT 20;

Correction (2026-11-08)

The index is built on to_tsvector('english', COALESCE(content, '')), but the query filters on to_tsvector('english', m.content). PostgreSQL only uses an expression index when the query contains the same expression, so this query cannot use idx_messages_search and falls back to computing to_tsvector for every candidate row. Either write the query's predicate with the identical COALESCE expression, or, better, store the vector once in a generated column so the expression cannot drift:

ALTER TABLE messages ADD COLUMN search_tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', COALESCE(content, ''))) STORED;
CREATE INDEX idx_messages_search_tsv ON messages USING GIN (search_tsv);

-- ... AND m.search_tsv @@ query ... ts_rank(m.search_tsv, query) ...

Even with a working index, in-database search has limits at chat scale. The GIN index over billions of messages is large and slow to update under 50,000 inserts per second; "my channels" for a user in 500 channels is a large ANY list; ranking, highlighting, stemming across languages and "from:asha in:#design before:2026-06" filters are what search engines are built for. The usual architecture indexes messages into Elasticsearch or OpenSearch from a change stream, with the channel id (or a workspace plus channel ACL) on each document, and filters by the user's channel list at query time. Postgres remains the source of truth, and search results are re-checked against current membership before display. See Elasticsearch Internals and Search Patterns.

Channel Sidebar (Ordered by Last Activity)

-- User's channels ordered by most recent activity
SELECT
    c.id,
    c.name,
    c.type,
    c.last_message_at,
    cm.is_muted,
    cm.notification_pref,
    -- Last message preview
    lm.content AS last_message,
    lu.username AS last_sender
FROM channel_members cm
JOIN channels c ON c.id = cm.channel_id
LEFT JOIN LATERAL (
    SELECT content, sender_id
    FROM messages
    WHERE channel_id = c.id AND NOT is_deleted
    ORDER BY created_at DESC
    LIMIT 1
) lm ON TRUE
LEFT JOIN users lu ON lu.id = lm.sender_id
WHERE cm.user_id = $user_id
  AND NOT c.is_archived
ORDER BY c.last_message_at DESC NULLS LAST
LIMIT 50;

Hot-Path Queries with EXPLAIN Reasoning

Plans are illustrative and trimmed, in the shapes PostgreSQL produces. See Query Planning & Optimization.

Q1: History page

Limit  (rows=50)
  ->  Index Scan Backward using uq_messages_channel_seq on messages
        Index Cond: ((channel_id = 7) AND (seq < 18230))

One descending range read of 50 index entries plus 50 heap fetches. Because one channel's messages arrive over time interleaved with every other channel's, those 50 heap rows are scattered across many pages. That is the main physical cost of chat history in a heap table, and the reason wide-column stores (which physically cluster a partition's rows) serve this query with far fewer I/Os at scale. In PostgreSQL, periodically CLUSTERing old partitions by (channel_id, seq) recovers locality for archived history.

Q3: The sidebar

The original sidebar query runs a LATERAL "latest message" probe per channel. With the denormalized preview columns on channels, it becomes:

SELECT c.id, c.name, c.type, c.last_message_at, c.last_message_preview, c.last_sender_id,
       c.last_seq - cm.last_read_seq AS unread_count,
       cm.mention_count, cm.is_muted
FROM channel_members cm
JOIN channels c ON c.id = cm.channel_id
WHERE cm.user_id = $me AND NOT cm.is_hidden AND NOT c.is_archived
ORDER BY c.last_message_at DESC NULLS LAST
LIMIT 100;
Limit  (rows=100)
  ->  Sort  (Sort Key: c.last_message_at DESC NULLS LAST)  (rows=70)
        ->  Nested Loop  (rows=70)
              ->  Index Only Scan using idx_members_by_user on channel_members cm  (rows=70)
                    Index Cond: (user_id = 42)
              ->  Index Scan using channels_pkey on channels c  (loops=70)

Seventy index-only membership entries, seventy primary-key probes, and a sort of 70 rows in memory. The sort cannot be avoided by an index because the sort key lives on a different table than the filter, and that is fine: sorting 70 rows costs microseconds. No messages are touched at all. A user in 5,000 channels (bots, very large workspaces) is the outlier; clients for those users request the sidebar incrementally.

Q2: Reconnect sync

After a disconnect, the client sends its last known seq per channel. The server answers with the channels whose last_seq moved, then the client fetches each gap:

-- Which of my channels changed since the client's positions?
SELECT c.id, c.last_seq
FROM channel_members cm
JOIN channels c ON c.id = cm.channel_id
WHERE cm.user_id = $me AND c.last_seq > COALESCE(($known::jsonb ->> c.id::text)::bigint, 0);

-- Then per changed channel, a forward range:
SELECT * FROM messages WHERE channel_id = $c AND seq > $known_seq ORDER BY seq LIMIT 200;

Both are index ranges. After a regional outage, millions of clients reconnect within seconds, which is a thundering herd on exactly these queries; jittered reconnect delays on the client and a cache of channels.last_seq in Redis absorb it. See Thundering Herd.

Message Delivery Architecture

sequenceDiagram
    participant Client A
    participant API Server
    participant Database
    participant Message Broker
    participant WebSocket Server
    participant Client B

    Client A->>API Server: Send message
    API Server->>Database: INSERT message
    Database-->>API Server: message ID
    API Server->>Message Broker: Publish to channel topic
    API Server-->>Client A: 200 OK + message
    Message Broker->>WebSocket Server: Fan out to subscribers
    WebSocket Server->>Client B: Push message via WS

The flow above has a gap: if the API server crashes after the database commit but before publishing, the message is stored but never pushed, and online members only see it after their next sync. With sequence numbers and an outbox, delivery becomes reliable and self-healing:

sequenceDiagram
    participant A as Client A
    participant API as API server
    participant DB as PostgreSQL
    participant R as Outbox relay
    participant MB as Broker
    participant WS as WebSocket tier
    participant B as Client B

    A->>API: send with client_msg_id
    API->>DB: BEGIN, bump channel last_seq, insert message and outbox row, COMMIT
    DB-->>API: seq 1042
    API-->>A: ack with seq 1042
    R->>DB: read new outbox rows
    R->>MB: publish to channel topic
    MB->>WS: fan out to online members
    WS->>B: push message seq 1042
    Note over B: B holds 1040, so it fetches 1041 first
    B->>API: fetch channel 7 after seq 1040

The client's gap detection is what makes the pipeline forgiving: if a push is lost or arrives out of order, the receiver notices the missing seq and fetches the range. The broker can be at-least-once because duplicates are discarded by seq. See Transactional Outbox, Chat System and Slack for the surrounding services.

Concurrency Hot Spots

Hot spotWhat goes wrongSchema answer
Two people send at onceDifferent clients see different ordersPer-channel last_seq allocated under the channel row lock
Retried send after a timeoutDuplicate messageUNIQUE (channel_id, sender_id, client_msg_id), transaction rolls back the seq
Two people open a DM at onceTwo DM channels for one pairCanonical (user_id_low, user_id_high) primary key arbitrates
Read reports from several devicesRead position moves backwardsMonotonic WHERE last_read_seq < $seq
Unread counts in big channels10,000 counter writes per messageSubtraction of last_seq - last_read_seq
@channel in a huge channel100,000 mention incrementslast_broadcast_seq on the channel
Hot thread or viral messageContended reply_count or reaction summaryAsync batched counters
Bot flooding one channelCounter row becomes the bottleneckRate-limit per channel; batch seq allocation; Redis counter
Member joins while messages arriveJoiner sees a partial page or counts history as unreadSet the joiner's last_read_seq to the channel's current last_seq at join, in the same transaction

The last row deserves a line of SQL, because it is easy to get wrong:

INSERT INTO channel_members (channel_id, user_id, last_read_seq)
SELECT id, $user, last_seq FROM channels WHERE id = $channel
ON CONFLICT (channel_id, user_id) DO NOTHING;

Workspaces configure how long messages live: forever, 90 days, 7 years for regulated industries, or per channel. Some messages are ephemeral by design ("disappears after 24 hours"). And legal holds override everything.

CREATE TABLE retention_policies (
    scope_type  TEXT   NOT NULL CHECK (scope_type IN ('workspace', 'channel')),
    scope_id    BIGINT NOT NULL,
    keep_days   INT    CHECK (keep_days IS NULL OR keep_days > 0),   -- NULL = forever
    legal_hold  BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (scope_type, scope_id)
);

ALTER TABLE messages ADD COLUMN expires_at TIMESTAMPTZ;      -- ephemeral messages
CREATE INDEX idx_messages_expiring ON messages (expires_at) WHERE expires_at IS NOT NULL;

How deletion actually happens depends on how messages are partitioned:

  • Time-partitioned table, global policy. If every message older than N days goes, drop whole monthly partitions. Instant, no bloat.
  • Per-channel policies. Delete in batches per channel, oldest first, a few thousand rows per transaction, skipping channels under legal hold. Heavy deletes create dead tuples, so pace them against autovacuum.
  • Ephemeral messages. A sweeper uses the partial idx_messages_expiring index and FOR UPDATE SKIP LOCKED to delete due messages in batches across several workers.
  • Everything derived must follow: attachments and their stored objects, reactions, edit history, search index documents, the sidebar preview on channels, and backups (by expiring backups on a schedule shorter than or equal to the retention promise, or by crypto-shredding: encrypt each workspace's content with its own key and destroy the key).

End-to-end encrypted messengers change the schema fundamentally: the server stores ciphertext blobs per device and cannot search, preview or moderate content. Everything on this page about search and previews applies to server-readable workplace chat.

Performance Considerations

ConcernSolution
Channel history load speedIndex on (channel_id, created_at DESC); keyset pagination
Unread counts at scaleDenormalize on channel_members or use Redis counters
Message searchFull-text search index; consider Elasticsearch for advanced search
Attachment storageStore files in S3/R2; only metadata in database
Large channels (10K+ members)Fan-out via message broker, not database polling
Message table sizePartition by channel_id range or created_at month
Online presenceRedis pub/sub or dedicated presence service; not database
Typing indicatorsPure WebSocket; never touches the database
Ordering and pagination tiesPer-channel seq; paginate and sync on (channel_id, seq)
Unread counts without countinglast_seq - last_read_seq; mentions counted separately
Read-report write volumeMonotonic, no-op-if-unchanged update; client debounce
Reconnect stormsJittered reconnects; cache last_seq per channel

Schema Design Decisions

DecisionRationale
BIGINT IDENTITY over UUIDMessages are insert-heavy; sequential IDs are faster for B-tree indexes than random UUIDs
Soft delete for messagesPreserves threading, reactions, and read receipt references
dm_channels lookup tableCanonical ordering (user_id_low < user_id_high) prevents duplicate DM channels
thread_id on messagesSimpler than a separate threads table; thread is just a message with replies pointing to it
Denormalized last_message_at on channelsAvoids expensive MAX(created_at) subquery for channel ordering
Denormalized reply_count on messagesAvoids COUNT(*) query for thread preview
Reactions as separate tableSupports any emoji, easy to add/remove, queryable per message
JSONB for message metadataLink previews, embeds, and rich content vary per message; JSONB is flexible
Read receipts track last_read_id not per-messageOne row per user per channel instead of one row per user per message
Gapless per-channel seqDefined order, tie-free pagination, gap detection, O(1) unread counts
client_msg_id unique per sender and channelRetries are idempotent
Read state on channel_membersThe sidebar reads membership and read position in one index-only probe
Per-message receipts derived from read positionsNo per-message, per-member rows
Uploads table with object keysBytes in object storage, access checked at download, orphan cleanup possible

Evolution as It Scales

StageSymptomChange
1. One Postgres primaryEarly productEverything in one database; presence in Redis
2. Replicas and cachesSidebar and history reads dominateCache user profiles and channel headers; history from replicas with a seq check (if the replica lags behind the seq the client already holds, read from the primary)
3. Partition messagesBillions of rows, index maintenance and vacuum lagPartition messages, plus channel_events and reactions alongside
4. Search outSearch latency and index write costChange-data-capture into Elasticsearch/OpenSearch
5. Messages outWrite volume and storage beyond comfortable Postgres sizesMessage history to a wide-column store; metadata stays relational
6. Shard metadataChannels and memberships beyond one primaryShard by workspace (B2B) or by channel id (consumer)

Partitioning choices for messages. Range by time makes retention trivial (drop partitions) and keeps the recent, hot partition small, but every unique key must include the time column, so (channel_id, seq) becomes (channel_id, seq, created_at) and global uniqueness of (channel_id, seq) is no longer enforced by one index. Hash by channel_id keeps (channel_id, seq) as a true primary key and spreads write load, but retention becomes row deletes. A common compromise is hash by channel with time-based retention handled by batched deletes, or time partitions with seq uniqueness guaranteed by the allocation transaction itself (the counter row lock already makes duplicates impossible).

When messages leave Postgres. The history access pattern, "partition = conversation, clustering = seq, read a contiguous slice", is the native data model of Cassandra and ScyllaDB. Discord publicly described storing messages in Cassandra with a partition key of (channel_id, bucket), where the bucket is a fixed time window so a busy channel's partition does not grow without bound, and later moving that data to ScyllaDB. The equivalent table:

-- CQL (Cassandra / ScyllaDB), not PostgreSQL
CREATE TABLE messages_by_channel (
    channel_id bigint,
    bucket     int,          -- e.g. seq / 10000, or a 10-day time window
    seq        bigint,
    message_id bigint,
    sender_id  bigint,
    content    text,
    edited_at  timestamp,
    deleted    boolean,
    PRIMARY KEY ((channel_id, bucket), seq)
) WITH CLUSTERING ORDER BY (seq DESC);

What stays relational: users, workspaces, channels, memberships, read positions, DM lookup, permissions, retention policies. Those have constraints worth enforcing (one DM per pair, one membership per user and channel) and modest volume. What moves: message bodies and reactions (high volume, simple access), search (different query model), presence and typing (ephemeral), and sometimes unread state for the very largest deployments (Redis hashes per user). See Cassandra Internals and Redis Internals.

Shard keys. For workplace chat, workspace id keeps every channel, membership and message of a company on one shard, so permissions, search scoping and retention are local; the largest workspaces get dedicated shards, exactly as in SaaS multi-tenancy. For consumer messaging without workspaces, channel id shards message history, while the per-user sidebar is served from a user-keyed inbox table (user_id, channel_id, last_seq_seen) maintained on fan-out: the same "write the edge twice" idea as social-graph follows. See Sharding.

Common Mistakes

  1. Ordering by created_at. Ties skip messages at page boundaries and commit order differs from timestamp order. Use a per-channel seq.
  2. No client-generated idempotency key. Flaky mobile networks produce duplicate messages.
  3. Allocating the seq outside the insert transaction, or with ON CONFLICT DO NOTHING, which leaves gaps that clients interpret as missing data.
  4. Counting unread messages with COUNT(*) per channel on every sidebar load.
  5. Per-member unread counters in large channels. Write amplification equal to channel size on every message.
  6. Comparing read time instead of read position. "Opened the channel at 10:00" is not "read everything before 10:00".
  7. Read-position updates that move backwards or rewrite unchanged rows. Use the monotonic, no-op-if-unchanged predicate.
  8. Per-message, per-member receipt rows. Derive receipts from monotonic read positions instead.
  9. Find-or-create DMs without a constraint. Duplicate DM channels for the same pair.
  10. Expression-index mismatches in search. The query must use the indexed expression exactly; a generated column avoids it.
  11. Presence and typing in the database. Millions of writes per second of data that is stale in seconds.
  12. Permanent public URLs for attachments. Access must be checked at download time.
  13. Retention that forgets derived data. Objects, search documents, previews and backups must follow the policy.
  14. Publishing to the broker outside the transaction without an outbox. Crashes create messages that are stored but never delivered in real time.

Interview Walkthrough: "Design the Schema for a Slack-like Chat"

1. Scope (3 minutes). "Workspaces with public and private channels, 1:1 and group DMs, threads, reactions, file sharing, search, unread badges, retention policies. 20 million DAU, 50,000 messages per second, channels up to 100,000 members."

2. Access patterns (5 minutes). Sends are high-volume and must be ordered and deduplicated. History reads are per-conversation contiguous ranges. The sidebar with unread counts loads on every app open. Read reports are the second-biggest write stream. Presence and typing are ephemeral.

3. Entities (7 minutes). Users, workspaces, channels (DMs are channels with a type plus a pair lookup; group DMs keyed by member set), memberships carrying read state, messages, threads as messages with a root reference, reactions, uploads and attachments, edit history, retention policies. Draw the ER diagram.

4. Keys and the ordering decision (10 minutes). This is the heart of the answer. Per-channel gapless seq allocated in the insert transaction under the channel row lock; (channel_id, seq) as the primary key; global id for references; UNIQUE (channel_id, sender_id, client_msg_id) for idempotent retries. Explain why created_at ordering fails.

5. Unread counts and receipts (8 minutes). last_seq - last_read_seq for O(1) unread counts; separate mention counts with a broadcast-seq trick for @channel; monotonic, no-op-if-unchanged read updates; "seen by" derived from read positions.

6. Delivery (4 minutes). Commit plus outbox, broker fan-out to online members, client gap detection by seq, sync after reconnect by "messages after seq N".

7. Search, attachments, retention (4 minutes). Search in a search engine fed by change data capture, filtered by membership. Attachments direct to object storage with pre-signed URLs and scan status. Retention via partitions or batched deletes, legal holds, derived data included.

8. Evolution (4 minutes). Partition messages, move history to a wide-column store keyed by (channel_id, bucket) clustered by seq, keep metadata relational, shard by workspace.

Likely follow-ups:

  • "How does a client know it has missed a message?" The seq jumps: it holds 1040 and receives 1042, so it fetches 1041.
  • "A channel has 100,000 members; what happens on each message?" One message row, one counter increment, one broker publish; the WebSocket tier pushes only to members who are online. No per-member database writes.
  • "How do you edit or delete a message for people who were offline?" A per-channel event log with its own sequence; sync replays events after the client's last event seq.
  • "Why not one table per channel?" Millions of channels means millions of tables, catalog bloat and impossible migrations; one partitioned table with a channel key gives the same locality without the operational cost.

Where to Go Next