Database per tenant or shared schema — how to choose

I have built products both ways: SaaS products on a single database with logical isolation, and a product with a separate database for every client. My conclusion is that “which one is better” is the wrong question. The two models optimise for different things, and the choice is made on operations, not elegance.

What are the options?

Practically three — although discussions usually mention only two:

1. Shared schema — all clients in the same tables, separated by a TenantId column.
2. Schema per tenant — same database, separate schemas.
3. Database per tenant — each client with their own database, routed by subdomain or through a tenant registry.

The rest of this article compares 1 and 3, because those are the ones I see chosen most often. But let me be fair to the middle option: schema per tenant is mainstream in PostgreSQL, where search_path makes routing nearly free and a single pg_dump -n gives you one client’s backup. It is not a wrong choice. Its real failure mode is not the one usually cited: at a few thousand tenants the system catalog bloats — pg_class and pg_attribute grow with tables × schemas — and catalog autovacuum, query planning and cluster-wide pg_dump all start to hurt. It is a good option into the hundreds of tenants and becomes painful exactly as you grow.

What does each cost you?

Criterion	Shared schema	Database per tenant
Data isolation	Logical — depends on code correctness	Engine-enforced — separation no longer rides on a predicate in your code
Blast radius of a scoping bug	Every client	A single client
Restore for one client	Hard — extract from a shared backup	Trivial — restore their database
Schema migrations	One run	N runs, with orchestration and partial states
Cost at many small clients	Very good	Poor — fixed cost per database
Connections / pooling	One pool	A pool per database; becomes a real constraint
Noisy neighbours	Real — a heavy client affects the rest	Reduced
Cross-tenant reporting	Trivial — a GROUP BY	Expensive — aggregate from N sources
Onboarding a new client	One INSERT	Provisioning + migration + configuration
Compliance argument	“We have filters”	“The data is in separate databases”

Two rows need qualifying, so you do not sell more than you deliver.

“Physical isolation” is almost always an overstatement. N databases on one instance share the process, the disk, the memory and the service account. What you gain is something else, and it is real: isolation no longer depends on every query carrying the right predicate. It becomes physical only if you actually run separate instances — which changes the cost picture entirely.

“Blast radius” applies only to scoping bugs. A bad migration, a business-logic error or a wrong calculation reaches every client under both models; you run them against every database. What database-per-tenant contains is the “forgot the WHERE TenantId” category specifically.

The last row in the table often decides it — and not for technical reasons. In a conversation with a healthcare client, “your data is in a separate database” is an argument anyone understands. “We use global query filters” is not.

When do you pick database per tenant?

From experience, when at least two of the following hold:

• clients are few and large (tens, not tens of thousands);
• the data is sensitive and isolation must be demonstrated, not merely implemented;
• you need point-in-time restore for a single client without touching the rest;
• clients require different retention periods or geographic locations;
• one large client can generate load that would affect the others.

On the medical appointments product I went with a database per client for exactly these reasons: medical data, relatively few clients, a clear separation requirement. I also added OS-level resource isolation so one client cannot starve the others’ CPU.

When do you pick a shared schema?

• many small clients, each with small volumes;
• self-service onboarding, where provisioning a database is unacceptable friction;
• a genuine need for reporting aggregated across all clients;
• a small team that cannot maintain migration orchestration across N databases.

It is the reasonable default for most B2B products. With global query filters applied consistently and write-side validation, logical isolation is solid — the problem is not the model, it is the discipline.

What changes most: migrations

This is the part everyone underestimates, myself included the first time.

With a shared database, a migration is an event. With N databases, it is a process:

• you run the migration against each database, with retries and logging;
• you accept that you will temporarily have databases at different versions — the code must tolerate that;
• you need a registry that knows each database’s version;
• rollback is no longer “restore the backup”, it is a per-client decision.

The practical rule: if you go with a database per tenant, write the migration orchestrator on day one. If you postpone it until you have 30 clients, you will write it under pressure, in production.

Can you combine them?

Yes, and it is often the right answer for products that grow: shared schema as the default, a dedicated database for clients who ask for it (and pay for it). The cost is maintaining two routing and migration paths — so it is only worth it when the demand is real rather than hypothetical.

What works well as preparation, whichever you start with: keep tenant resolution in a single place. If all code obtains its connection through a service that knows how to resolve the tenant, moving from one model to the other stays a localised change rather than a rewrite.

If you are early in a multi-tenant product and want to make this decision with your eyes open, that is exactly the conversation I have had several times.