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.
| Operation | Steady rate | Peak rate | Read or write | Latency target | Notes |
|---|---|---|---|---|---|
| Send message | 50,000/s | 200,000/s | Write | p99 < 100 ms to ack | Must be ordered within a conversation and never duplicated |
| Real-time delivery to online members | 2,000,000/s | 8,000,000/s | Push | < 500 ms | Fan-out through a broker; not a database write per recipient |
| Load channel history (page of 50) | 30,000/s | 100,000/s | Read | p99 < 100 ms | Newest first, then scroll back |
| Sidebar: my channels with unread counts | 20,000/s | 80,000/s | Read | p99 < 100 ms | Every app open and reconnect; 70+ channels each |
| Mark as read | 100,000/s | 300,000/s | Write | Async-tolerant | Every time a user looks at a channel |
| Reconnect sync: what did I miss | 20,000/s | 200,000/s | Read | p99 < 200 ms | After network drops; thundering herd after outages |
| Thread load | 5,000/s | 20,000/s | Read | p99 < 100 ms | Parent plus replies |
| Reactions add/remove | 10,000/s | 50,000/s | Write | p99 < 100 ms | |
| Search | 2,000/s | 8,000/s | Read | p99 < 500 ms | Only over channels the user can see |
| Attachment upload | 2,000/s | 10,000/s | Write | Seconds | Bytes to object storage; metadata to DB |
| Presence and typing | 500,000/s | 2,000,000/s | Ephemeral | < 200 ms | Never touches the database |
What the numbers say:
- 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.
- "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.
- 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.
- 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 query | Index |
|---|---|---|
| Q1 | Channel history page, newest first | messages PRIMARY KEY (channel_id, seq) scanned backwards |
| Q2 | Messages after seq N (reconnect sync) | same primary key, forward range |
| Q3 | My channels ordered by activity, with unread counts | channel_members (user_id, ...) plus channels.last_seq |
| Q4 | Find the DM between two users | dm_channels PRIMARY KEY (user_id_low, user_id_high) |
| Q5 | Thread replies in order | (channel_id, thread_root_seq, seq) WHERE thread_root_seq IS NOT NULL |
| Q6 | Reactions for a page of messages | reactions PRIMARY KEY (message_id, user_id, emoji) |
| Q7 | Dedupe a retried send | UNIQUE (channel_id, sender_id, client_msg_id) |
| Q8 | Who has read message S | channel_members (channel_id, last_read_seq) |
| Q9 | Full-text search in my channels | GIN 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_messagesandchannel_messagestables, everything that holds messages is achannelsrow with atype. 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 companionread_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:
| Aspect | 1:1 DM | Group DM (3 to ~50) | Channel (up to 100K+) |
|---|---|---|---|
| Identity | The pair of users | The set of members | A name and an id |
| Find existing | dm_channels by (low, high) | Hash of sorted member ids | By name within a workspace |
| Membership changes | Never | Rare; usually a new conversation | Constant |
| Fan-out per message | 1 recipient | Tens | Up to 100,000; only online members get a push |
| Read receipts shown | "Seen" per message | Per-member ticks, practical | Never per member; only your own unread state |
| Unread semantics | Every message counts | Every message counts | Often "has unread" dot plus mention count only |
| Notification default | All | All | Mentions only |
| Sequence-number contention | None | None | Real 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:
- Ties. Two messages in the same millisecond have no defined order, and keyset pagination on
created_at < $cursorcan skip one of them at a page boundary. - 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. - 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 thelast_seqincrement) 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 useON CONFLICT DO NOTHINGhere; it would keep the increment and leave a gap. - The global
idstays as a stable identifier for reactions, attachments, links and search results.seqis 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:
- Denormalize: Maintain an
unread_countcolumn onchannel_members. Increment on new message, reset on read. - Approximation: Store
last_read_message_idand compare tomax(message.id)in the channel. If they differ, show "unread" without counting. - 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_countmaintained 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_countonly for the mentioned members (UPDATE channel_members SET mention_count = mention_count + 1 WHERE channel_id = $c AND user_id = ANY($mentioned)). For@channelin a huge channel, skip the per-member increment and store alast_broadcast_seqon the channel instead; a member has an unread broadcast mention iflast_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
scanningtoreadyorrejected; 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 spot | What goes wrong | Schema answer |
|---|---|---|
| Two people send at once | Different clients see different orders | Per-channel last_seq allocated under the channel row lock |
| Retried send after a timeout | Duplicate message | UNIQUE (channel_id, sender_id, client_msg_id), transaction rolls back the seq |
| Two people open a DM at once | Two DM channels for one pair | Canonical (user_id_low, user_id_high) primary key arbitrates |
| Read reports from several devices | Read position moves backwards | Monotonic WHERE last_read_seq < $seq |
| Unread counts in big channels | 10,000 counter writes per message | Subtraction of last_seq - last_read_seq |
@channel in a huge channel | 100,000 mention increments | last_broadcast_seq on the channel |
| Hot thread or viral message | Contended reply_count or reaction summary | Async batched counters |
| Bot flooding one channel | Counter row becomes the bottleneck | Rate-limit per channel; batch seq allocation; Redis counter |
| Member joins while messages arrive | Joiner sees a partial page or counts history as unread | Set 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;
Retention, Legal Hold and Ephemeral Messages
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_expiringindex andFOR UPDATE SKIP LOCKEDto 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
| Concern | Solution |
|---|---|
| Channel history load speed | Index on (channel_id, created_at DESC); keyset pagination |
| Unread counts at scale | Denormalize on channel_members or use Redis counters |
| Message search | Full-text search index; consider Elasticsearch for advanced search |
| Attachment storage | Store files in S3/R2; only metadata in database |
| Large channels (10K+ members) | Fan-out via message broker, not database polling |
| Message table size | Partition by channel_id range or created_at month |
| Online presence | Redis pub/sub or dedicated presence service; not database |
| Typing indicators | Pure WebSocket; never touches the database |
| Ordering and pagination ties | Per-channel seq; paginate and sync on (channel_id, seq) |
| Unread counts without counting | last_seq - last_read_seq; mentions counted separately |
| Read-report write volume | Monotonic, no-op-if-unchanged update; client debounce |
| Reconnect storms | Jittered reconnects; cache last_seq per channel |
Schema Design Decisions
| Decision | Rationale |
|---|---|
| BIGINT IDENTITY over UUID | Messages are insert-heavy; sequential IDs are faster for B-tree indexes than random UUIDs |
| Soft delete for messages | Preserves threading, reactions, and read receipt references |
dm_channels lookup table | Canonical ordering (user_id_low < user_id_high) prevents duplicate DM channels |
thread_id on messages | Simpler than a separate threads table; thread is just a message with replies pointing to it |
Denormalized last_message_at on channels | Avoids expensive MAX(created_at) subquery for channel ordering |
Denormalized reply_count on messages | Avoids COUNT(*) query for thread preview |
| Reactions as separate table | Supports any emoji, easy to add/remove, queryable per message |
| JSONB for message metadata | Link previews, embeds, and rich content vary per message; JSONB is flexible |
Read receipts track last_read_id not per-message | One row per user per channel instead of one row per user per message |
Gapless per-channel seq | Defined order, tie-free pagination, gap detection, O(1) unread counts |
client_msg_id unique per sender and channel | Retries are idempotent |
Read state on channel_members | The sidebar reads membership and read position in one index-only probe |
| Per-message receipts derived from read positions | No per-message, per-member rows |
| Uploads table with object keys | Bytes in object storage, access checked at download, orphan cleanup possible |
Evolution as It Scales
| Stage | Symptom | Change |
|---|---|---|
| 1. One Postgres primary | Early product | Everything in one database; presence in Redis |
| 2. Replicas and caches | Sidebar and history reads dominate | Cache 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 messages | Billions of rows, index maintenance and vacuum lag | Partition messages, plus channel_events and reactions alongside |
| 4. Search out | Search latency and index write cost | Change-data-capture into Elasticsearch/OpenSearch |
| 5. Messages out | Write volume and storage beyond comfortable Postgres sizes | Message history to a wide-column store; metadata stays relational |
| 6. Shard metadata | Channels and memberships beyond one primary | Shard 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
- Ordering by
created_at. Ties skip messages at page boundaries and commit order differs from timestamp order. Use a per-channel seq. - No client-generated idempotency key. Flaky mobile networks produce duplicate messages.
- Allocating the seq outside the insert transaction, or with
ON CONFLICT DO NOTHING, which leaves gaps that clients interpret as missing data. - Counting unread messages with
COUNT(*)per channel on every sidebar load. - Per-member unread counters in large channels. Write amplification equal to channel size on every message.
- Comparing read time instead of read position. "Opened the channel at 10:00" is not "read everything before 10:00".
- Read-position updates that move backwards or rewrite unchanged rows. Use the monotonic, no-op-if-unchanged predicate.
- Per-message, per-member receipt rows. Derive receipts from monotonic read positions instead.
- Find-or-create DMs without a constraint. Duplicate DM channels for the same pair.
- Expression-index mismatches in search. The query must use the indexed expression exactly; a generated column avoids it.
- Presence and typing in the database. Millions of writes per second of data that is stale in seconds.
- Permanent public URLs for attachments. Access must be checked at download time.
- Retention that forgets derived data. Objects, search documents, previews and backups must follow the policy.
- 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
- OLTP Database Design: the general principles behind these choices
- Chat System and Slack: the services around this schema
- Ordering Guarantees: ordering beyond one database
- Cassandra Internals: where message history goes at scale
- Indexing Deep Dive: composite, partial, covering and expression indexes
- E-Commerce Schema Design, Social Media Schema Design, SaaS Multi-Tenant Schema Design: the same method on other domains