Choosing a Multi-Tenant Isolation Model

Most products should start on a shared schema with a tenant column, and most teams who regret their isolation model do not regret the model — they regret never writing down which failure mode they were accepting when they picked it. The three options are not a maturity ladder. They are three different trades between the cost of a migration and the granularity of an operation, and the right one depends on answers you can get in an afternoon.

The three models differ in one thing: what a tenant is made of

Everything else follows from that.

Model	A tenant is	Cheap	Expensive
Shared schema, tenant column	A value in a column	Migrations, cross-tenant queries, provisioning, pooling	Per-tenant restore, retention, noisy neighbours
Schema per tenant	A namespace	Per-tenant backup, DDL and export	Migration fan-out, catalogue bloat, pooling, drift
Database per tenant	A database	Hard isolation, residency, per-tenant keys	Everything operational, multiplied by tenant count

Read the right-hand column first. You are choosing which of those problems you want, because you will get one of them.

Shared schema is the default because a migration stays one migration

That is the whole argument, and it is a strong one. ALTER TABLE runs once. A backfill runs once. A bad migration is discovered on the first tenant that touches it, rather than on tenant 340 at two in the morning.

The price is that isolation becomes a property of your queries, and queries are written by people. Three defences, in order of how much they actually help:

• Every index leads with tenant_id. Not for correctness — for the query planner. A product that indexes (created_at) instead of (tenant_id, created_at) performs beautifully until the second large tenant arrives.
• Isolation is enforced below business logic. Postgres row-level security with the tenant set per transaction, or a data-access layer that cannot build an unscoped query. A convention that every WHERE clause includes the tenant is not a control; it is a hope with a code review attached.
• Tests run against a neighbour. Every fixture creates two tenants and asserts the second’s rows are invisible.

Where it genuinely breaks: restore granularity, retention, and neighbours. When a customer deletes a category by accident, “restore this tenant to 14:00” is a filtered export and a careful re-import, not a restore. When one tenant imports a 400,000-row catalogue, everyone else queues behind it unless that work already has its own connection pool and queue. And when a contract requires data to be deletable on demand, deletion becomes a campaign across every table carrying the column.

Schema per tenant buys per-tenant operations and sells you the migration

Pick it when tenants are few and large, and when a per-tenant restore point or export is a contractual matter rather than a support ticket.

Then budget for four things the tutorials skip. Migrations become a job rather than a command: progress, retries, partial-failure handling and a per-tenant schema-version table, because the interesting state is “409 of 412 schemas are on version 118”. Fleet drift is the real risk — one schema fails, nobody notices, a support ticket finds it six weeks later. Pooling turns hostile: a per-connection search_path plus a pooler in transaction mode will eventually hand a query the wrong namespace, so tenant selection has to happen somewhere the pooler cannot undo. And every schema multiplies the rows in the system catalogue, which slows autovacuum, dumps and anything that introspects the database — noticeable in the thousands.

Database per tenant is for when a tenant is a contract, not a row

Choose it for reasons that come from outside engineering: residency in a named jurisdiction, per-tenant encryption keys, an auditor who wants separate credentials, or one tenant whose load genuinely deserves its own instance. No amount of clever schema design substitutes for those.

Everything operational then multiplies — pools, backup policies, credentials, monitoring targets, migration runs. Provisioning stops being a row insert and becomes an infrastructure operation, so it needs to be idempotent and observable like any other; half-created tenants are the characteristic failure. Cross-tenant reporting becomes a pipeline. The pragmatic middle is per-tenant databases on shared instances, grouped by region or plan.

Moving later costs whatever you skipped at the start

The direction matters enormously.

Splitting a shared schema into per-tenant schemas or databases is mechanical if every table already carries the tenant column and every row is unambiguously owned: filter, export, import, cut over, one tenant at a time, with a read-only window measured in minutes. It is a project, not a rewrite.

If the column is missing, the work is archaeology before it is engineering. You infer ownership through join paths — this order belongs to that location, which belongs to that brand — then prove no row is ambiguous, and handle the ones that are, because there always are some. That inference is the expensive part, and it is why we argue for a tenant id in the first migration even on products that will never leave a shared schema. It costs one column and buys the option.

Merging back down is worse. Primary keys collide, so you re-key, and every identifier you have exposed — in a URL, an invoice, a receipt, a webhook payload another system stored — changes underneath you. Plan the split; do not plan the merge.

Most products have two levels of tenancy, and only one of them is isolation

This is the mistake that produces a database per restaurant. The isolation model applies to the entity that signs the contract; everything inside it is authorisation.

Both platforms we run have this shape. SuperApp is a marketplace under one operator’s brand with many independent vendors trading inside it: the operator is the tenant, the vendors are scoped actors with their own users, catalogues and payouts. Supaorder sells to a restaurant brand that may run one location or forty: the brand is the tenant, the locations are a hierarchy inside it. Vendors and locations need strict scoping, roles and audit trails. They do not need their own database, and giving them one converts a permissions problem into an infrastructure problem you own forever.

Decide which level signs the contract. That level is the tenant. Everything below it is a foreign key.

Four things look the same in all three models — get them right once

• Tenant resolution happens at the edge, in exactly one place. Subdomain, host header or a token claim, resolved into a context object. Two places means two answers.
• Context propagates into background work. The most common leak is not a missing WHERE clause; it is a queue consumer or scheduled job that inherited the previous request’s context. Jobs should carry the tenant explicitly and refuse to run without one.
• Cache keys include the tenant. A cache key without one is a data breach with a TTL.
• Logs and metrics are tagged with the tenant. You will want this during an incident, and that is the wrong day to add it — a point running our own platforms made expensive.

The five questions we ask before writing the first migration

1. Will any customer ever be able to demand that their data lives in a named jurisdiction, or under their own key? If yes, you are choosing between the two per-tenant models today.
2. What restore granularity would you be asked for in an incident — the platform, or one customer at 14:00 yesterday?
3. How many tenants exist in year two: tens, or tens of thousands? That moves migration fan-out from a footnote to a subsystem.
4. Does the product itself need cross-tenant aggregates, or only your own analytics? In-product cross-tenant reporting is nearly free on a shared schema and a pipeline everywhere else.
5. Who runs the migration when there are 400 tenants, and what do they watch while it runs?

If those answers are not obvious yet, the cheap move is a shared schema with a rigorously enforced tenant column and no cross-tenant joins in application code — the model that keeps every door open. That is also the right call inside a four-to-six-week MVP, where the column costs an hour and buys an option. That decision belongs in the architecture phase of a multi-tenant SaaS build, not in the sprint where the first enterprise customer asks about residency. If you are looking at a platform that already exists and cannot say which model it is actually running, that is a normal starting point for an architecture review.