We gave every customer their own database

Chat apps grow in one direction: messages. People rarely delete them, and every one is stored with its indexes, its search entries, its reactions and its mentions. When we measured Freesbe on its real schema, a message took about 1.6 KB all in, and an active person adds about 1.7 MB a month.

Our first database was a single shared SQL database for everything, and it has a hard size limit of 10 GB. At 1.7 MB a person a month, that’s about 500 person-years of chat. It sounds like a lot until you remember that one company importing five years of history from another chat tool can use gigabytes in an afternoon.

So before we got anywhere near it, we changed the shape of the problem: every workspace now gets its own database.

The shape

The shared database still exists, but it’s now a directory. It knows who people are, which workspaces exist, who is in which channel, and billing. It doesn’t hold messages any more.

Each workspace’s messages, reactions, mentions and search index live in a small SQLite database of its own. It runs next to the workspace’s data as a tiny single-purpose server that answers one request at a time and keeps its data on local disk, so a query is an in-process read measured in microseconds rather than a trip across the network.

When the app loads a channel, the request goes to the directory first (“which channels may this person see?”) and then to that workspace’s own database (“give me the latest 50 messages in these channels”). The two never join in one query.

What we turned down

• Keep one big database and hope. It works until the day it doesn’t, and that day is decided by your largest customer, not your average one.
• Split into a fixed number of shards, say 16, by workspace id. Better, but you pick the number once and live with it. Resharding later means moving data you never planned to move.
• Run a managed Postgres cluster. It scales further, but it’s a system that needs a team to watch it, and we are a small team building a chat app, not a database company.

A database per workspace has no shard count to outgrow. A small workspace costs almost nothing when it’s quiet, and a large one gets a whole database to itself.

Getting there without downtime

The hard part wasn’t the new database, it was moving the existing code and data onto it.

First, one door. Before moving anything, we put every read and write of a message behind a single module. Sending, editing, search, unread counts, AI summaries, imports: everything that touched the messages table now went through one interface, with one implementation that still talked to the shared database. That took a series of small pull requests, and at the end a simple check could prove that nothing else in the codebase touched messages directly.

Then, the same code on both sides. The new per-workspace database runs exactly the same SQL as the shared one, with the same tables and indexes. Only the place it runs changes. That meant every existing test covered both.

Then, live moves. Moving a workspace looks like this:

1. Mark it as moving. Writes to its messages pause and the app shows “Try again in a minute”. Reading carries on.
2. Wait 10 seconds, so every server that cached where the workspace lives has noticed.
3. Copy messages, reactions and mentions channel by channel, in short rounds.
4. Count all three on both sides. Only if they match, point the workspace at its new home.
5. Delete the old copy.

If anything fails, the workspace stays where it was and writes resume. New workspaces start in their own database from day one.

Writes without a shared transaction

Splitting data across two databases costs you something: you can’t wrap “save the message” and “move the channel’s last-message pointer” in one transaction any more.

We handled it by ordering and by making the second step safe to repeat. A send writes the message to the workspace’s database first, then moves a few pointers forward in the directory: the channel’s last message, the sender’s read position. Those only ever move forward. If the app retries a send that already went through, the message isn’t duplicated (each one carries an id the client chose), and moving a pointer forward to where it already is changes nothing.

What it gave us for free

Once a workspace’s data lives in its own database, where that database lives is a setting. When customers asked to keep their data in the EU or the US, the answer was mostly already built: create the workspace’s database inside that region, and put its files in a bucket there. Moving a workspace to another region later uses the same copy-and-count steps as moving it into its own database.

It also changed how we think about load. One busy workspace can’t slow down another, because they don’t share a database. The rule that keeps each one fast is simple: every query uses an index, every page has a size cap, and nothing waits on anything else in the middle of a write.

What we’d do differently

We’d build the single door on day one. Routing every message through one module was the step that made everything else boring, and boring is what you want from a migration. The rest of the workspace’s tables (scheduled messages and the like) will follow the same path when they need to.