Shared Schema or Database Per Tenant, and How to Decide

The tenancy decision gets made in week one, by someone who has not yet run a migration across 100 tenants, restored a single customer's data without touching anyone else's, or explained to a prospect's security reviewer where their records physically live. Then it stays made for years, because changing it later means moving data while the product is live.

We have operated both shapes. On one platform we ran 100+ per-tenant Postgres databases behind 30+ Node microservices on Kubernetes, and later migrated over a hundred of those tenant databases onto a new platform and decommissioned the old one, with a per-tenant reconciliation report gating every cutover. On others we run a single shared schema with a tenant_id column and row-level security, and the whole fleet moves on one ALTER TABLE.

Neither is the right answer in general. The choice is determined by four things: how many tenants you expect, how large the largest one gets, what your buyers demand in writing about isolation, and how often you ship schema changes. Everything else — ORM support, connection pooling, blast radius rhetoric — is downstream of those.

The three models, stated precisely

People use "multi-tenant" to mean three different architectures, and arguments go in circles because of it.

Shared schema. One database, one set of tables, a tenant_id on every tenant-scoped row. Isolation is enforced in code and, if you are sensible, in the database with row-level security. One migration covers everyone. One connection pool. Backups are all-or-nothing.

Schema per tenant. One database, N Postgres schemas, identical tables inside each. Isolation is a search_path. Migrations run N times against one server. This is the option that looks like a compromise and behaves like the worst of both: you still share a single server's resources, single WAL, single backup unit, single upgrade window — but you now have N copies of every DDL to keep in step. Past a few hundred schemas, pg_dump, autovacuum and system catalog queries all start to complain.

Database per tenant. N databases, potentially on N servers. Real isolation of data, credentials, backups, restore, and noisy-neighbor load. Also N migrations, N connection pools, and a control plane you now have to build and operate whether you planned to or not.

The decision rule we use

Work down this list and stop at the first line that matches. It is deliberately blunt, because the failure mode is not picking wrong — it is deliberating for three weeks and then picking wrong anyway.

1. A contract or regulator requires physically separate storage or per-tenant encryption keys, for even one deal you intend to close → database per tenant. This is not negotiable by architecture cleverness, and retrofitting it under a signed deal is how you burn a quarter.
2. You expect fewer than ~50 tenants, each large, each paying enough that per-tenant infrastructure and operational time is a rounding error → database per tenant. Enterprise B2B, high ACV, tenant-specific data volumes measured in tens of gigabytes.
3. You expect thousands of tenants, most of them small, self-serve signup, and free trials → shared schema with row-level security. Provisioning must be an INSERT, not an orchestration job.
4. You expect hundreds of tenants of mixed size, one or two of which will be 100x the median → shared schema by default, with an escape hatch: a tenants.database_url column, so any single tenant can be lifted onto its own database without an application rewrite.
5. None of the above, and you genuinely do not know → shared schema. It is the cheaper mistake to unwind. Moving one tenant out of a shared schema is a filtered export. Merging a hundred separate databases into one is a reconciliation project.

Note what is not on the list: "which one is more secure". A shared schema with RLS and a forced tenant context is more secure in practice than 100 databases where the app connects as a superuser and picks a connection string from a variable. Isolation is a property of the enforcement mechanism, not the file layout.

If you choose shared schema, enforce it in the database

The catastrophic bug in shared-schema systems is a query that forgot its WHERE tenant_id = $1. Code review does not catch this reliably, ORMs make it easy to omit, and reporting endpoints written under deadline are where it happens. Push enforcement below the application.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- set once per request, on the pooled connection, inside the transaction
SET LOCAL app.tenant_id = '0f8c...';

Three details matter more than the policy itself. Use SET LOCAL inside a transaction, or a pooled connection will carry one tenant's context into the next request. FORCE ROW LEVEL SECURITY so the table owner is not exempt. And make current_setting(..., true) returning NULL fail closed — a missing tenant context should return zero rows, never all rows. Write an integration test that opens a connection with no tenant set and asserts every tenant-scoped table returns nothing.

Then add the composite indexes. Every index on a tenant-scoped table should lead with tenant_id. A shared-schema system with single-column indexes reads fine at 10 tenants and collapses at 500, because the planner is scanning other people's rows to find yours.

What database per tenant actually costs

The isolation is real. So is the bill, and it is mostly paid in operations rather than compute.

Migrations become a distributed system

Running one ALTER TABLE across 100+ databases means some will fail: one is mid-restore, one has a long-running lock, one drifted because someone hotfixed it in 2023. You need a migration runner that records per-tenant version state, retries, and reports drift — and you need it before tenant 20, not after. Deploys must tolerate a fleet where 3 databases are one version behind, which means every migration is expand-and-contract, always, with no exceptions.

Connections do not divide neatly

Thirty services times a hundred tenant databases times even a small pool is a number Postgres will not accept. You end up with a shared proxy layer, lazily opened pools with idle eviction, and a hard cap on concurrent tenant databases per process. This is the part teams consistently underestimate.

Cross-tenant questions get expensive

"How many active users across all tenants this week" is one query in a shared schema and a fan-out job in database-per-tenant. Plan on a separate analytics store fed by change data capture or a nightly export. That is a second data path to build, monitor and reconcile — and the reason our platform work usually includes an analytics and admin console layer rather than treating it as a later phase.

The compensation is worth naming honestly, because it is the strongest argument for the model: restoring a single tenant to a point in time is trivial. In a shared schema it is a surgical, hand-written, high-risk operation you will get wrong under pressure. If your customers' data loss scenarios are a real support burden, per-tenant databases buy you a clean answer.

The hybrid, and the escape hatch that makes it possible

Most products that survive end up hybrid: a shared schema carrying the long tail, plus dedicated databases for the handful of tenants who are too large, too loud, or too regulated to share. This only works if you build one thing on day one: resolve the connection from tenant metadata, not from configuration.

Keep a control-plane table — in its own database, never in a tenant's — holding tenant id, status, region, and a nullable database_url. Null means "shared pool, apply RLS context". Non-null means "connect here, no RLS needed". Every request resolves tenant, then resolves connection, then proceeds. If that indirection exists from the start, promoting a tenant to its own database is a data move and a metadata update, not a refactor.

Resolve the tenant's connection at runtime from metadata, and the tenancy model stops being an architectural commitment and becomes a per-tenant setting.

When we moved a hundred-plus tenant databases onto a new platform, the thing that made cutovers survivable was not the copy mechanism. It was a per-tenant reconciliation report — row counts per table, checksums on money-bearing columns, a spot-check of the newest and oldest records — that had to come back clean before traffic moved. Tenant-by-tenant migration gives you that gate for free. It is one of the genuine advantages of the model, and we describe how we sequence work like this in our approach.

Where this advice does not apply

If your product runs partly on the client, tenancy is not only a server-side question. Our offline-capable POS in Retailer OS holds a local store per device, syncs to per-location inventory, and reconciles when the connection returns. The interesting isolation boundary there is the device and the location, not the database; the server-side model matters less than the conflict-resolution rules.

If you are edge-first, the calculus changes again. On Cloudflare D1, a database per tenant is cheap to create and pushes data near the user, so the usual "provisioning is expensive" objection largely disappears — while the "cross-tenant queries are hard" objection gets worse.

And if you have exactly one customer and a signed pilot, do not build a tenancy model. Build the product with a tenant_id column on every table and no isolation machinery at all. You will know a great deal more about your real tenants by the time the second one signs, and adding RLS to a schema that already carries the column is an afternoon. That is the pattern behind most of the platforms in our portfolio, and where we usually start when a founder brings us an empty repo and a deadline.

The decision you cannot defer is the connection-resolution indirection. Everything else is reversible at a price. That one is not.