Chat Application Database Design: Schema, Queries, and Scale

Chat application database design looks finished after 20 minutes and then falls apart in month 3. Two tables, users and messages, and the demo works. Then the product adds group chats, then read receipts, then a conversation list that has to open instantly, and the schema that carried the demo starts scanning the whole message table on every screen.

Most engineers do not build a WhatsApp competitor. They build a chat feature inside something else: a rider-to-customer dispatch thread in a logistics product, a support conversation attached to a failed transfer in a fintech app, a buyer-to-seller thread on a marketplace. The database work is identical either way.

This guide gives you the tables, foreign keys, indexes, and queries for a chat system that holds up. Everything runs on PostgreSQL and Redis, both self-hostable on a single box, so nothing here needs a managed service billed in dollars before your first user arrives.

What a Chat Application Database Has to Do

Strip away the interface and a chat server does 4 things against storage. It appends a message, ordered and durable, and never loses one. It reads the tail of a conversation faster than anything else in the system. It lists a user's threads by recent activity, with a preview and unread count on each. And it tracks who has seen what. Every decision below serves one of those 4 operations.

The Core Entities and How They Relate

A chat system database schema needs 4 tables: users, conversations, conversation participants, and messages. Many designs collapse the third into an array column on the conversation. That is the first mistake, and it is worth knowing exactly why.

Why Participants Need a Join Table

A conversation has many users, and a user has many conversations. That is a many-to-many relationship, and the relational answer is a join table with 1 row per membership.

An array of participant ids on the conversation row breaks 4 things at once. You cannot put a foreign key on an array element, so nothing stops a deleted user lingering in a thread forever. You cannot answer "which conversations does user 7 belong to" without scanning every conversation. You have nowhere to hang per-member data, which is what chat needs most: when someone joined, whether they muted the thread, and how far they have read. And adding a member rewrites the whole array, a lost update waiting to happen when 2 people add members at once. A join table costs 1 extra table and fixes all 4.

The Schema

Create the core tables in schema.sql:

CREATE TABLE users (
    id           BIGSERIAL PRIMARY KEY,
    username     TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE conversations (
    id                BIGSERIAL PRIMARY KEY,
    kind              TEXT NOT NULL CHECK (kind IN ('direct', 'group')),
    title             TEXT,
    last_message_id   BIGINT,
    last_message_text TEXT,
    last_message_at   TIMESTAMPTZ,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE conversation_participants (
    conversation_id      BIGINT NOT NULL REFERENCES conversations (id) ON DELETE CASCADE,
    user_id              BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    joined_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    muted                BOOLEAN NOT NULL DEFAULT false,
    last_read_message_id BIGINT,
    PRIMARY KEY (conversation_id, user_id)
);

CREATE TABLE messages (
    id              BIGSERIAL PRIMARY KEY,
    conversation_id BIGINT NOT NULL REFERENCES conversations (id) ON DELETE CASCADE,
    sender_id       BIGINT NOT NULL REFERENCES users (id),
    body            TEXT NOT NULL,
    reply_to_id     BIGINT REFERENCES messages (id) ON DELETE SET NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    edited_at       TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ
);

Four choices there are load-bearing. The composite primary key on conversation_participants makes duplicate membership impossible in the database rather than in application code. TIMESTAMPTZ means a message sent in Lagos and read in Berlin sorts correctly. deleted_at keeps a removed message in position instead of leaving a hole. And BIGSERIAL gives messages a monotonically increasing identifier, the cheapest read-position marker you will ever get.

Because last_message_id points at a table that does not exist yet, add its foreign key afterwards, along with the indexes.

Complete the schema in schema.sql:

ALTER TABLE conversations
    ADD CONSTRAINT fk_conversations_last_message
    FOREIGN KEY (last_message_id) REFERENCES messages (id) ON DELETE SET NULL;

CREATE INDEX idx_messages_conversation_recent
    ON messages (conversation_id, created_at DESC, id DESC);

CREATE INDEX idx_participants_user
    ON conversation_participants (user_id);

CREATE INDEX idx_conversations_recent
    ON conversations (last_message_at DESC NULLS LAST);

Three indexes, 3 jobs. The first serves every read of a conversation's history. The second answers "which threads does this user belong to", which the join table alone cannot do quickly because its primary key leads with conversation_id. The third sorts the chat list. Add nothing else until a slow query proves you need it.

Relational vs Document Store: Choosing the Database

"What is the best database for a chat application" usually gets answered by whoever is selling something. Different engines are strong at different parts of the job.

In the Stack Overflow 2025 Developer Survey, PostgreSQL was used by 55.6% of all respondents and 58.2% of professional developers, ahead of MySQL at 40.5% and 39.6%. MongoDB sat at 24% and 24.3%, and Redis at 28% of all respondents but 30.7% of professionals, the gap you would expect from something that appears in production more than in learning projects. Those are self-reported figures from a self-selected sample, so read them as what teams say they run.

For a chat feature inside a product that already runs a relational database, use that database. The join, the transaction, and the foreign keys are worth more than schema flexibility, and messages are the most structured data in your system: a sender, a thread, a body, a time. There is very little to be flexible about. Reach for a document store when message bodies genuinely vary in shape, and put Redis in front of whichever you pick rather than treating it as the only copy.

Writing a Message: The Transaction That Matters

Inserting a message is 2 writes that must succeed or fail together: the row in messages, and the denormalised preview on conversations. If the second fails alone, the chat list shows a stale preview and sorts the thread into the wrong position, and nothing will ever correct it. PostgreSQL does both in 1 statement with a writable common table expression.

Send a message in queries/send_message.sql:

WITH new_message AS (
    INSERT INTO messages (conversation_id, sender_id, body)
    VALUES (42, 7, 'Rider is at the pickup point')
    RETURNING id, conversation_id, body, created_at
)
UPDATE conversations c
SET last_message_id   = m.id,
    last_message_text = left(m.body, 120),
    last_message_at   = m.created_at
FROM new_message m
WHERE c.id = m.conversation_id
RETURNING m.id, m.created_at;

One round trip, one transaction, and the returned id and created_at go back to the sending client so it can replace its optimistic bubble with the real message.

Note what is not in that statement: no row is written per recipient. Fan-out to connected clients belongs on a WebSocket publish after the transaction commits, because holding a transaction open while you push over the network is how a chat server runs out of connections at the moment it gets busy.

Reading History Without OFFSET

The obvious way to page backwards through a conversation is LIMIT 20 OFFSET 10000. It works on your laptop with test data and collapses in production, because OFFSET is not a seek. The database still produces all 10,000 rows in order and discards them before returning the 20 you asked for, so the cost climbs the further back a user scrolls. Worse, if a new message arrives between 2 requests, everything shifts by 1 and the reader sees a duplicated row.

Keyset pagination, sometimes called cursor pagination, fixes both. Instead of counting rows to skip, you carry the position of the last row you saw.

Fetch a page of history in queries/fetch_page.sql:

SELECT id, sender_id, body, created_at, deleted_at
FROM messages
WHERE conversation_id = 42
  AND (created_at, id) < (&#x27;2026-09-01 09:14:22+00&#x27;, 90210)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Deleted rows come back rather than being filtered out, so the client can render a tombstone in place and the page size stays predictable. The row comparison (created_at, id) < (...) is the important part. Two messages can share a timestamp, so paging on created_at alone can skip or repeat a row at a page boundary. Comparing the pair breaks the tie with the primary key, and because id is monotonic, the ordering is total.

That query matches idx_messages_conversation_recent exactly: the index leads with the equality column, then the 2 ordering columns in the direction the query asks for. PostgreSQL seeks straight to the cursor and reads 20 entries, so page 500 costs what page 1 costs. Load a million rows and run EXPLAIN ANALYZE against both versions to see it. The same reasoning applies across a backend, and our guide on why your API is slow covers it more broadly.

Request the next page in docs/api.http:

GET /v1/conversations/42/messages?before_id=90210&before_at=2026-09-01T09:14:22Z&limit=20
Authorization: Bearer <token>

The client returns the cursor it was given. The server never trusts a page number.

Conversation Titles and the Chat List

Conversation titles are where teams improvise, and the improvisation shows. Three cases, 3 different answers.

A group thread has a title a human typed. Store it in conversations.title as ordinary editable data.

A direct thread has no title of its own. The name a user sees is the other participant's display name, which differs on each side of the conversation, so resolve it at read time from conversation_participants and leave title null. Storing "Chat with Amina" would be wrong for Amina.

An assistant or support thread has a title generated from the first message, the pattern in the sidebar of any AI chat product. Generate it once after the first exchange, truncate it to fit a narrow list on a phone, and store it. Never regenerate on read, because a title that changes under the user is one they can never find again. Keep the column nullable and fall back to a snippet of the first message until generation runs.

The chat list is then 1 query, and it is why last_message_text and last_message_at are denormalised onto the conversation row.

Load a user's chat list in queries/list_conversations.sql:

SELECT c.id,
       c.kind,
       c.title,
       c.last_message_text,
       c.last_message_at
FROM conversations c
JOIN conversation_participants p ON p.conversation_id = c.id
WHERE p.user_id = 7
ORDER BY c.last_message_at DESC NULLS LAST
LIMIT 30;

Without those denormalised columns, this query has to reach into messages for every conversation the user belongs to and find the newest row in each. That is a correlated subquery per row, and the most common cause of a slow chat home screen. The denormalisation costs nothing beyond keeping the write path in 1 transaction.

Delivery and Read Receipts Without Killing the Database

The tempting model is a status column on the message row: sent, delivered, read. It works for a direct chat and is wrong for a group, because delivery is per recipient. A message to a 50-person thread has 50 delivery states and 50 read states, and 1 column cannot hold that.

The next attempt is a receipt row per recipient per state. That is honest, and it multiplies write volume by roughly twice the number of participants. A 50-person thread turns 1 message into 1 insert plus 100 receipt writes, arriving as a burst the moment every phone wakes up. The messages table is now the small one in your database.

The model that scales is a watermark: a participant has read everything up to a message id, stored as 1 row per participant per conversation and updated in place. That is the last_read_message_id column on conversation_participants.

Mark a conversation as read in queries/mark_read.sql:

UPDATE conversation_participants
SET last_read_message_id = greatest(coalesce(last_read_message_id, 0), 90210)
WHERE conversation_id = 42
  AND user_id = 7;

The greatest call makes the update idempotent and safe to reorder, so a late receipt from a phone that reconnected on a bad line cannot drag the watermark backwards. Unread counts then fall out of the same column.

Count unread messages in queries/unread_count.sql:

SELECT count(*) AS unread
FROM messages m
JOIN conversation_participants p
  ON p.conversation_id = m.conversation_id
WHERE p.user_id = 7
  AND m.conversation_id = 42
  AND m.deleted_at IS NULL
  AND m.sender_id <> p.user_id
  AND m.id > coalesce(p.last_read_message_id, 0);

Keep per-recipient receipt rows only where the product genuinely has to show who in a group has read a message, and only for recent messages. Nobody scrolls back 6 months to check a read tick.

Caching Conversation History With Redis

Chat reads are heavily skewed. The tail of an active conversation gets requested constantly, and everything older than a day is close to cold. That shape is what a cache is for.

Cache 3 things and nothing else: the last 50 or so messages of each active conversation, each user's chat list, and unread counts. Deep history is large and rarely read, and a cache full of cold data evicts the hot data you wanted. The tail fits a capped Redis list.

Update the cached tail after a successful commit in scripts/cache_message.sh:

redis-cli LPUSH "conv:42:tail" '{"id":90211,"sender_id":7,"body":"Rider is at the pickup point","created_at":"2026-09-01T09:20:11Z"}'
redis-cli LTRIM "conv:42:tail" 0 49
redis-cli EXPIRE "conv:42:tail" 86400
redis-cli DEL "user:7:conversations" "user:9:conversations"

Four rules keep that cache honest. Write to Redis only after the transaction commits, or a rolled-back message becomes visible and never goes away. Trim on every push so the key cannot grow without bound. Set a time to live on everything, so a bug in your invalidation costs a day rather than forever. And delete the chat-list key for every participant instead of patching it, because a delete is idempotent and a partial update is not.

Edits and soft deletes change a row that may already be in the cached tail, so drop the whole conv:<id>:tail key on either and let the next read rebuild it. Rebuilding 50 rows from an indexed query is cheap. Serving the body of a message a user deleted is not.

Every message still lives in PostgreSQL. Redis holds the hot slice and nothing that cannot be rebuilt in 1 query.

What Breaks at Scale, and the Order to Fix It

Chat data grows in 1 direction and never stops. The fixes have a correct order, and taking them out of order wastes weeks.

First, index. Almost every slow chat query is a missing or mismatched composite index. Run EXPLAIN ANALYZE on your history read, chat list, and unread count. An index whose column order does not match the query's equality-then-ordering shape will not be used, and hardware will not save it.

Second, partition by time. Once the messages table is large enough that a vacuum or index rebuild becomes an outage, split it by month. In PostgreSQL that means partitioning by range on created_at, which forces 2 changes to the schema above: the primary key becomes (id, created_at), because a partitioned table's key must contain the partition column, and incoming foreign keys to messages have to be dropped. Real costs, which is why this comes second.

Create a partitioned messages table in schema_partitioned.sql:

CREATE TABLE messages (
    id              BIGSERIAL,
    conversation_id BIGINT NOT NULL REFERENCES conversations (id) ON DELETE CASCADE,
    sender_id       BIGINT NOT NULL REFERENCES users (id),
    body            TEXT NOT NULL,
    reply_to_id     BIGINT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    edited_at       TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE messages_2026_09 PARTITION OF messages
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE INDEX idx_messages_2026_09_conversation_recent
    ON messages_2026_09 (conversation_id, created_at DESC, id DESC);

Because the keyset query filters on created_at, the planner prunes to the partitions it needs, and a read of the last 20 messages touches 1 partition.

Third, archive. Old partitions detach cleanly and move to cheap object storage, which is the point of partitioning by time rather than by conversation. Detaching a month is a metadata operation. Deleting a month of rows from one large table is hours of write amplification.

Do not start at step 3. Teams routinely build an archival pipeline for a table that needed 1 index.

To build this rather than read about it, the Python backend projects catalogue includes a Dating App API and a Course Platform API, both of which put you in this exact schema. Schema design also comes up constantly in hiring, and these patterns appear almost verbatim in our list of backend interview questions.

Frequently Asked Questions

What Is the Best Database for a Chat Application?

The one your product already runs, in most cases. PostgreSQL handles chat well and was the most used database at 55.6% among Stack Overflow's 2025 survey respondents. Choose a document store when message bodies vary in shape and you accept enforcing relationships in application code. Use Redis as a cache in front of either, never as the durable copy.

Can You Build a Chat System on a Relational Database?

Yes, and most production chat features are. Ordered appends, indexed range reads, and many-to-many membership are all things relational engines are good at. The scaling limits come from schema mistakes, per-recipient receipt rows and OFFSET pagination, not from the engine.

How Do You Cache Conversation History in a Chat Application?

Cache the last 50 or so messages per active conversation in a capped Redis list, plus each user's chat list and unread counts. Write only after the transaction commits, trim on every push, set a time to live on every key, and delete rather than patch the chat-list key on a new message.

What Should a Chat History Title Be?

Group threads get a stored, human-edited title. Direct threads get none, because the correct name differs for each participant, so resolve it at read time. Assistant threads get a short title generated once from the first exchange and then stored, never regenerated, so a user can find the thread again.

How Do You Store a Chat Log for Support or Auditing?

The messages table is already the log, provided you soft-delete with deleted_at and record edited_at rather than overwriting a body silently. Export a transcript ordered by created_at ASC against a read replica, so an audit never competes with live traffic.

What Is the Minimum Schema for a Chat App With Users and Messages?

Four tables: users, conversations, conversation_participants, and messages. The common 3-table shortcut drops the join table and costs you group chats, per-member read state, and referential integrity on membership.

Summary

Chat application database design comes down to a few choices that are hard to reverse later. Model participants as a join table, not an array. Denormalise the last message text and timestamp onto the conversation row, in the same transaction as the insert. Page with a keyset cursor on (created_at, id) behind a composite index in matching order. Track read state with a watermark column per participant rather than a receipt row per recipient per state. Cache the hot tail in Redis after commit, with a cap and a time to live, and rebuild rather than patch. When the table gets heavy, index first, partition by month second, archive third.

All of it runs on PostgreSQL and Redis on a single self-hosted server, so you can build the whole thing, load it with a million rows, and find your slow queries before anyone bills you for the privilege. For the surrounding fundamentals, our guide on how to master backend development covers the path.