A wide-column store like Cassandra or ScyllaDB for the messages themselves, a relational database for user and channel metadata, Redis for ephemeral state and pub/sub fan-out, and an inverted index like Elasticsearch or OpenSearch for search.
No single database covers chat well. The workload is lopsided, and different pieces of chat data have very different durability and consistency needs. Discord, Slack, and Meta have each converged on roughly this shape.
Why Can’t a Single Database Handle Real-Time Chat?
When you first look into setting up chat it can look simple from the outside, a write-heavy log of messages ordered by time and filtered by channel. The hard part is doing everything at once:
- Messages within a channel must remain in order, even when thousands of clients are writing at once.
- Users scroll backward through years of history, and pagination must remain fast, even in channels with millions of messages.
- It's an append-heavy log, but users also edit, delete, react, and thread, so the store can't be purely append-only.
- Every user needs their own view of state: unread counts, last-read markers, and what mentions they haven't seen.
- Typing indicators, presence, and delivery receipts fire constantly and don't need to survive a restart.
- Search must cover only the channels a user belongs to and update within seconds when membership changes.
No general-purpose database handles all of that at once. Discord's history is the one most teams end up citing: MongoDB until the working set stopped fitting in RAM at around 100 million messages; Cassandra until JVM GC pauses and hot partitions made ops a full-time job; then ScyllaDB. Chat writes are append-heavy, and the reads concentrate on recent data at high concurrency, and most databases aren't built for that combination.
What Does the Architecture Actually Look Like?
Five stores, each handling the part of the workload it's shaped for.
| Store | What it holds | Why it's the right fit |
|---|---|---|
| Wide-column DB (Cassandra, ScyllaDB) | Messages, reactions, edits | Append-heavy writes, partition-keyed reads, scales writes horizontally |
| Relational DB (Postgres, MySQL) | Users, channels, memberships, permissions | Low volume, heavily joined, needs transactions |
| Redis | Presence, typing, unread counts, pub/sub fan-out | Churns constantly, doesn't need to survive a restart |
| Inverted index (Elasticsearch, OpenSearch) | Full-text search over messages | The only store that makes LIKE '%term%' fast |
| Object storage (S3 or equivalent) | Attachments, images, video, voice notes | Cheap per GB, CDN-friendly, keeps blobs out of the database |
The message store is where most of the data and most of the traffic lives, and most of the interesting design decisions are about it. The other four are smaller, well-understood, and mostly a question of wiring them up correctly.
Data flows between them in a specific way. New messages get written to the wide-column store first. From there, a CDC pipeline (usually Debezium into Kafka) watches for changes and copies them into the search index in the background, so search stays a second or two behind live but never blocks a write.
The relational store is where permissions live. Channel membership is the source of truth there, but it's cached in Redis so the delivery and search layers can check "can this user see this channel?" on every request without hammering Postgres.
Attachments skip the database entirely. The client uploads them directly to object storage, and only the file's reference (a key, size, and content type) ends up in the message row.
Let’s look at each in turn.
What’s the Best Database for Storing Chat Messages?
A wide-column store like Cassandra or ScyllaDB, partitioned by channel and clustered by message ID.
The workload narrows the choice a lot. Every read is a variant of "give me N messages in channel X, ordered by time, optionally before cursor Y." Writes are append-only at high concurrency. Individual channels can hold millions of messages, and the active set is always the most recent few hundred.
Wide-column stores are built for exactly this. Data is:
- Sharded across machines by a partition key. The partition key (in chat, the channel ID plus a time bucket) decides which machine holds the data, so a query for one channel goes straight to one set of nodes instead of fanning out across the cluster.
- Sorted inside each partition by a clustering key. The clustering key (in chat, the message ID) fixes the on-disk order within a partition, so "last N messages in this channel" is a cheap range read instead of a sort.
Writes go to an in-memory buffer that periodically flushes to immutable, sorted files; there's no in-place update, which is why they sustain high write rates cheaply. Reads open a small number of those files, merges them, and returns a sorted range. Because your query already names the partition, the database never searches; it just scans.
Do you actually need a wide-column store? Not always. Under ten million messages in a single region, Postgres with a composite index on (channel_id, message_id) and time-based table partitioning works fine and is much simpler to operate.
Most chat platforms end up on wide-column stores at scale for two reasons:
- They scale writes horizontally without a sharding proxy
- Compaction-based storage handles the append-heavy workload more naturally than a B-tree.
Where Should You Store User and Channel Metadata?
In a relational database. Postgres or MySQL; the choice between them doesn't matter much.
Everything that isn't a message goes here: users, workspaces, channels, memberships, roles, permissions, billing, integrations. Compared to the message store, this data is small, changes slowly, and gets joined constantly. Three queries that run on almost every page load:
| Query | Tables touched | Join path |
|---|---|---|
| Show me this user's channels | USER → MEMBERSHIP → CHANNEL | Start from user_id, follow memberships, get channels |
| Show me this channel's members | CHANNEL → MEMBERSHIP → USER | Start from channel_id, follow memberships, get users |
| Show me a user's roles in a workspace | USER → USER_ROLE → ROLE | Three-way join across user, role, and workspace scope |
The table that matters most is MEMBERSHIP for authorization. Every message delivery, every search query, every presence update checks "is this user allowed to see this channel?" against it.
That creates two requirements:
- Membership changes have to propagate fast, because a user removed from a channel should stop seeing its messages within a second or two
- The check itself has to be cheap, because it runs on every read.
The pattern is to keep membership in the relational store as the source of truth, cache it in Redis with explicit invalidation on writes, and have the delivery and search layers read the cache.
You can run this on a single Postgres instance for a long time.
Where Should You Store Presence, Typing, and Unread Counts?
In Redis, which is ideal for non-durable, non-transactional elements.
This kind of data is different from everything else in the system in two ways. It changes constantly, sometimes on every keystroke. And losing it doesn't matter, because presence resets when you reconnect, and typing indicators are stale within seconds anyway. Writing any of it to your message store incurs IO overhead, and in many setups, it would generate more writes than the actual messages do.
Three things belong here:
- Presence. Store as a hash or sorted set per channel, with a TTL on each entry. The TTL handles cleanup for you; if a client disconnects and stops refreshing, the entry expires on its own.
- Typing indicators. Use a pub/sub channel per conversation. There's no read path, only fan-out, so a key-value store with pub/sub is exactly the right shape.
- Unread counts. One key per (
user_id, channel_id) holding the user'slast_read_message_id. Reads and writes are single-key, so you don't need transactions.
In this layer, consistency is less important than in others. Unread counts can be a second behind without anyone noticing. Typing and presence can drop events; they're best-effort by design. Don't wrap any of this in transactions or try to make it survive a Redis restart. If Redis goes down, clients reconnect, and the state rebuilds itself from the durable stores within seconds.
What’s the Best Way To Implement Message Search?
In a separate inverted index, fed asynchronously from the message store. Elasticsearch and OpenSearch are the defaults; Slack uses Solr, which is the same Lucene engine underneath.
You can't search messages with LIKE '%term%' on a regular database table. The query has to read every row, because the indexes a database normally uses can't match a word in the middle of a string. That's fine for a few thousand messages, but falls apart with a few million.
An inverted index turns the data inside out. Instead of storing messages and scanning them for words, it stores each word once with a list of the messages that contain it. Looking something up is a single read, no matter how many messages are in the system. Don't have your application write to both the database and the search index. Have it write only to the database, and let a separate process copy changes into the index.
The standard tool for this is Debezium. It watches the database's change log, sends each change to Kafka, and an indexer reads from Kafka to update Elasticsearch. New messages show up in search within a second or two.
The reason to do it this way is correctness. If your app writes to the database and then to the index, anything that fails between those two steps leaves them out of sync. With this approach, the database is always the source of truth, and the index can only lag, never be wrong.
Where Should You Store Attachments and Media?
In object storage (S3 or equivalent), with only a reference in the database.
The database row holds the file's key, content type, size, and dimensions. The bytes themselves live in S3, and clients fetch them through a CDN with signed URLs. The CDN keeps your servers out of the hot path for downloads and ensures fast delivery everywhere in the world.
The mistake to avoid is putting the bytes in your database. In a wide-column store, replication traffic balloons and compactions slow to a crawl. In a relational store, a few large columns will wreck Postgres's buffer cache faster than any query pattern. And in either case, you're paying database prices for what should be cheap object storage, which usually works out to about ten times what S3 would charge.
One detail worth getting right: don't upload through your servers. Have the client upload directly to S3 using a presigned URL. The backend hands the client a short-lived URL, the client uploads to S3, and your API only sees the metadata after the upload is done. That keeps a 50 MB video from tying up a request handler for 30 seconds, and it scales upload throughput with S3 rather than your server count.
The Decisions That Get Harder To Fix Later
A few things that are easy to miss when you build this:
- Put a coalescing layer in front of the message store from day one. Hot partitions show up earlier than you'd expect.
- Use cursor pagination from day one too. Switching off OFFSET later is painful.
- Keep ephemeral state out of the durable store, even when folding it in would be simpler.
- Pick your partition key for the worst-case channel, not the median one.
If you'd rather not build any of this, Stream’s Chat SDK runs the whole stack as a managed service. Read receipts, threads, reactions, unread counts, presence, and search come with the SDKs. Building your own makes sense if you have unusual data-residency or protocol constraints. Otherwise you're signing up to operate a wide-column cluster, a search cluster, and a multi-region pub/sub layer on top of the application itself.