Multi-tenant architecture: choosing an isolation model

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.

And there is a boundary we went and found ourselves: at ten tables per customer, pg_dump --schema-only on PostgreSQL 17.11 with default settings broke somewhere between 1,200 and 1,300 schemas with out of shared memory. Not slow — broken, and repairing it requires restarting the database. The measurement is in multi-tenant SaaS on PostgreSQL: six decisions.

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.

The five places where row-level security is silently off afterwards, or leaks around the edges, we measured separately in five gaps in tenant isolation. Three details make the difference here 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. So give every table with a policy both ENABLE and FORCE.
Let an empty context return nothing. Have the policy require 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. Route that through one function with a name nobody can miss — bypassRls, say — 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 with BYPASSRLS always bypass the policy, FORCE or 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 — but the shared model has its own day-to-day costs, and we measured them.

What does the shared model cost in day-to-day operations?

In a shared database, much of the cost sits not in the everyday queries but in the work around them: deleting, exporting or moving one customer’s data in a table every customer shares, a migration, a count, a job queue, a trigger, the policy itself. Each of those has one thing that decides how you do it safely. We measured them on PostgreSQL 17.10 in throwaway containers, with 2 to 1,000 customers with data (and up to 10,000 roles); the table below gives the finding per job, and each row links to the full measurement and its setup.

Job in a shared database	What we measured	What follows from it
Delete one large customer (60,000 invoices, 240,000 lines)	1.34 s, cascade or table by table alike; 1.16 s of it went to the foreign-key trigger. Dropping that customer’s own partition: 2–3 ms per statement, but under an `ACCESS EXCLUSIVE` lock on the whole table	if you batch, choose the size with `EXPLAIN ANALYZE` (at 5,000 per batch the plan read both whole tables); a partition only with a `lock_timeout`
Export one customer with `pg_dump` under row-level security	the rows were right, but the dump carried `setval` lines with the shared counters (311,205 invoices across all 100 customers)	export with `--exclude-table-data='*_seq'`
Move one customer to its own database with a row-filtered publication	with the default replica identity, every `UPDATE` and `DELETE` on the table failed — for every customer; with `REPLICA IDENTITY FULL` 19% more WAL, initial copy 1.3 s	set the replica identity before you create the publication
Add a column while a three-second report runs	the `ALTER TABLE` waited 2.5 s, and another customer’s query waited 2.0 s behind it; with `lock_timeout = '200ms'` the `ALTER` gave up after 200 ms and the other query took 5 ms	never run a migration on a shared table without a lock timeout
Count the largest customer’s invoices after one update of 80,000 of its invoices	13.2 ms became 64.7 ms; autovacuum did not start, because the threshold was 415,032 dead rows for the whole table	lower `autovacuum_vacuum_scale_factor` on large shared tables (0.01: vacuum after 31 s, back to 13.3 ms)
One customer queues 1,000 jobs	ten small customers waited a median 4.5 s with FIFO, 0.10–0.15 s with round-robin per customer	take jobs in turn per customer, not by arrival — and check the plan of that query: written the obvious way, it took 10 s per job
Set the customer context behind PgBouncer in transaction mode	after a plain `SET`, the next client, which set nothing, counted 267,023 invoices of another customer; with `SET LOCAL` in a transaction, 0	only `SET LOCAL`, and only inside a transaction
Run a worker that serves every customer from its own psycopg connection pool	with the pool’s defaults and a session-level `set_config(…, false)`, a job that succeeded left its customer set for the next job, which set none itself and counted 20,000 invoices of the wrong customer; a failing job left its own customer behind only in autocommit	set the customer with `set_config(…, true)` inside the job’s transaction
Switch customer with a role per customer instead of a session variable	first `SET ROLE` in a connection 211 ms at 10,000 roles; after adding one customer, the next switch paid the full cost again (87 ms at 5,000); the variable 0.05–0.11 ms	a role per customer costs you on every new customer, in every open connection
A unique constraint across all customers	a customer who cannot see another customer’s row still got `duplicate key value violates unique constraint`	put `tenant_id` in the constraint
An audit trigger on a shared table	32 µs per single-row update; one bulk `UPDATE` of 100,000 rows 4.4 times slower	a statement trigger for bulk changes (3.6 instead of 4.4 times)
The policy itself	counting the largest customer: 11.0 ms against 11.0 ms without row-level security; a membership subquery in the policy 79–99 ms on every query, a `VOLATILE` PL/pgSQL function 1.9 s	keep the policy a plain comparison with the session variable

None of these is a reason to choose another model: each has a known fix. Several only show once one customer is much larger than the rest — the count, the queue, the delete — and that is the moment the fix is hardest to roll out.

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 — restore the backup elsewhere and export that customer from it with pg_dump under row-level security, without the sequence values.

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. What moving one large customer out of shared tables into its own database takes — and the replica identity step that blocks every customer’s updates if you forget it — we measured in moving one tenant with logical replication row filters.

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.