SaaS architecture
Multi-tenant architecture: choosing an isolation model
Database per customer, schema per customer or one database with tenant_id and row-level security: the models side by side, and the three questions that decide.
Anyone building a SaaS product has to decide early how customer data stays apart. In practice there are four ways to do it, and the debate tends to be about technology while the choice is almost always settled by something else: what your customers demand of you contractually, and how many customers you expect.
This article puts the models side by side. It is not a neutral list: we have a preference, we say which one, and we also say when that preference is the wrong one.
Which multi-tenant model should a SaaS product use?
For most B2B SaaS products, one shared database with a tenant_id column on every table is the right choice, provided the separation is also enforced inside the database with row-level security (RLS) and not only in the application. A database per customer is only the better choice when a customer contractually requires their own database or storage location, or when you have a small group of very large customers. A schema per customer looks like a middle ground, but in practice combines the operational load of one model with the risk of the other.
The models in one table
| Model | Where the separation lives | What an extra customer costs | Migrations | Restoring one customer on its own | Reports across all customers | Biggest risk |
|---|---|---|---|---|---|---|
| Own installation per customer (single-tenant) | separate servers and a separate database | a complete extra environment | per installation, and installations drift apart | simple | only through a separate integration | versions diverging per customer |
| Database per customer | a separate database on the same or another server | a database, plus connections and backups | once per database | simple | only through a separate integration | operations grow linearly with the number of customers |
| Schema per customer | its own set of tables per customer in one database | an extra set of tables | once per schema | reasonable | awkward | the separation depends on which schema the connection picks |
| Shared tables, filter in the application | a tenant_id and a filter in the code | a row in the customers table | once | work: extracting one customer’s rows from a backup | simple | one forgotten filter is a data leak |
| Shared tables with row-level security | a tenant_id, the filter and a policy in the database | a row in the customers table | once | work: extracting one customer’s rows from a backup | simple, through an explicit exception | a connection that silently bypasses RLS |
Two columns usually decide it: what an extra customer costs and biggest risk. The rest follows from those.
Database per customer: strong separation, linear operations
Every customer gets their own database. The separation is then physical: a query can only end up at the wrong customer if the application connects to the wrong database. Restoring one customer to yesterday is simple, and a customer who wants their data in a particular region can have it.
The price is that everything you do for one database, you do for every customer. A migration runs per database, and halfway through a rollout some customers are on the old structure and some on the new. Connections, backups and monitoring grow with the number of customers. And a simple question like how many invoices were created this month across all customers suddenly needs a separate pipeline.
Choose this when a customer requires it contractually, when you have a handful of large customers rather than hundreds of small ones, or when one customer is heavy enough to slow down the rest.
Schema per customer: the middle ground that rarely is
In PostgreSQL one database can hold several schemas, and each customer then gets their own set of tables. That sounds like the best of both worlds: one server, separate tables all the same.
In practice you inherit the operational load from the database-per-customer side — every migration runs per schema, and every new customer adds a complete set of tables to the same database — and the risk from the shared side. The separation depends on which schema the connection is using at that moment, and the application sets that. A pooled connection still pointing at the previous customer’s schema is the same kind of mistake as a forgotten filter: one line, and the result is a data leak.
Choose this when you already have a single-tenant product whose tables cannot change, and you still want to run it on one server. As a new design, we advise against it.
Shared tables with a tenant_id: cheap, and one forgotten filter away from a leak
All customers live in the same tables, and every row carries the number of the customer it belongs to. An extra customer is a row in a table. A migration runs once. Reports across all customers are an ordinary query.
The risk is in the filter. It usually lives as a global scope in the ORM, and the holes are always in the same places: a raw query for a report that was too slow, a background job without a logged-in user, an import that bypasses the ORM, and a relation that reaches one step too far. We work through those four in multi-tenant from day one.
Do not choose this without the next layer.
Shared tables with row-level security: the model we choose
The same data model, with one addition: the database itself refuses rows that do not belong to the current customer. The application sets one session variable per connection with the customer number, and a policy on every table compares tenant_id against it. A raw query, a background job or an import without customer context gets zero rows back.
That is how booxx is set up, and three details make the difference between a policy that exists and a policy that works:
- Enable RLS and force it. According to the PostgreSQL documentation, the owner of a table bypasses the policy unless you use
ALTER TABLE … FORCE ROW LEVEL SECURITY. If your migrations and your application share one database user, the application is the owner and the policy does nothing without that line. In booxx every table with a policy has bothENABLEandFORCE. - Let an empty context return nothing. Our policy requires the session variable to be set before it is compared. A connection that forgets to set it therefore sees nothing instead of everything: annoying, but wrong in the right direction.
- Make the exception visible. Maintenance tasks that work across all customers need a way around the policy. In booxx that goes through one function literally called
bypassRls, so every use of it stands out in a review. And never connect the application as a superuser: according to the same documentation, superusers and roles withBYPASSRLSalways bypass the policy,FORCEor not.
What remains is the drawback of the shared model: restoring one customer on its own means extracting their rows from a backup, and that is work. For most products that does not outweigh operations that do not grow with the number of customers.
Three questions that decide the choice
1. Does a customer require their own database or storage location? If it is in a contract or a tender, the debate is over for that customer. It does not have to apply to everyone: a shared database for most customers and a dedicated database for that one large customer can live side by side, as long as the application runs the same code in both cases.
2. Do you need to restore one customer on its own? If “restore only our data to yesterday” is a request you expect, that is an argument for a database per customer. If it is a rarity, a script that extracts one customer’s rows from a backup is cheaper than those operations.
3. Do you expect dozens of customers or thousands? With dozens, running separate databases is manageable. With thousands, it has become the job itself, and the shared model wins on cost and on migration speed.
Can you switch models later?
One direction is reasonable, the other barely. Moving from a shared database to a dedicated database for one customer is a copy of every row with their tenant_id, and because the tables are identical, the application code does not change — only the connection chosen for that customer. The other way round — merging separate databases into one — is a project: every table still needs a tenant_id, every query has to be reviewed, and if each database had its own auto-incrementing keys, those keys collide and every reference has to be renumbered.
That is why separating customers is one of the three decisions in a SaaS product you never get to undo. The safest starting point is the model you can move away from: shared tables, RLS enabled and forced, and keys that are not counted per database.
Facing that choice for a new SaaS product, or want to know which model your existing product actually runs and whether it holds? Send an email. Half an hour with the database schema in front of us is usually enough to see it.