Chapter 3 · Foundations
Choosing an isolation model
Six questions in the order that makes the answer fall out, plus the two reasons people pick wrongly and what each one costs when it is discovered late.
7 min read
Chapter 2 described the three models. This chapter is the procedure for choosing, because the decision is usually made by instinct and the instinct is usually "the more isolated one sounds safer".
Answer these in order. The first question that gives a hard answer ends the process.
1. Is tenant count bounded?
Bounded and small (tens), sales-led, each one negotiated -> schema-per-tenant is affordable. Keep reading, but it is on the table. Unbounded, self-serve, a tenant can be created by a stranger with a card -> shared table. Stop here.
The reasoning is not security, it is the creation path. Self-serve signup means tenant creation happens inside an HTTP request, triggered by someone you have never met. In model 1 that is an INSERT. In model 2 it is DDL — CREATE SCHEMA, then a dozen CREATE TABLE statements — executed by a web request, inside a transaction that also took a payment, by a role with permission to alter the database structure.
// What self-serve signup looks like in model 2. Read it and ask what happens
// when it fails halfway, and what that role can do if the request is abused.
await db.transaction(async (tx) => {
await tx.execute(sql`create schema ${sql.identifier(schemaName)}`)
await runMigrations(tx, schemaName) // ~40 DDL statements
await tx.insert(tenants).values({ schemaName })
})Granting DDL rights to the application role, on the path a stranger can trigger, is a much larger security concession than the one model 2 was chosen to avoid.
2. Does a signed contract require separation?
Not "would a customer prefer it" — whether a contract or a procurement questionnaire states it. This is the one answer that legitimately overrides question 1, and it is worth checking what the document actually says. "Logical separation of customer data" is satisfied by row-level security and is the most common wording. "Physically separate database" is not, and it is rarer than sales teams imply.
If one tenant in twenty needs it, that is not an argument for moving all twenty. It is an argument for extracting one.
3. Is there a data residency requirement?
A hard geographic constraint — this tenant's rows must remain in a named country — can only be met by putting those rows in a database located there. No where clause satisfies a regulator.
This forces database-per-tenant for the affected tenants only. A Canadian-residency customer does not require that your American customers also get their own databases.
4. How often will you query across tenants?
Your own product needs cross-tenant reads: billing rollups, usage reports, the admin console, abuse detection.
| Shared table | Schema-per-tenant | |
|---|---|---|
| "Total seats sold this month" | select sum(seats) from subscriptions | A UNION ALL across N schemas, regenerated whenever a tenant is added |
| Cost of adding a tenant to that query | Zero | Edit the query, or build generation for it |
Teams that pick model 2 usually discover this on the day the first board report is due, and the fix is a nightly ETL into a warehouse — a whole second system, acquired by accident.
5. What is the realistic distribution of tenant size?
Pull the number if you have a comparable product; estimate honestly if you do not. SaaS tenant size is reliably long-tailed: the largest tenant commonly holds two to three orders of magnitude more rows than the median.
This does not change the model. It changes the indexing and the queries, and it is why Section 11 is about one tenant degrading everyone. But if you expect uniformly large tenants — five customers each with a hundred million rows — shared tables stop being obviously right, because the index that makes one tenant fast is now enormous.
6. Who will run the migrations in two years?
The honest answer is often "one person, part-time, between feature work". A migration strategy that needs a bespoke runner, per-tenant failure handling and a reconciliation report is a strategy that will be skipped, and skipped migrations are how an estate ends up on four schema versions.
Choose the model whose failure mode you can survive at 3 a.m. with one engineer. Model 1's worst day is a bad query, which you prevent with a second enforcement layer. Model 2's worst day is a migration that stopped at tenant 312, which you prevent with a system you have to build and maintain.
The two reasons people choose wrongly
"Separate databases are more secure." Partly true and usually overstated. The common breach in a multi-tenant system is not a database escaping its boundary; it is the application returning the wrong rows — a missing scope, a leaked identifier in a URL, a cache key without the tenant in it. Every one of those bugs is possible in all three models, because all three resolve the tenant in application code. Separate databases narrow the blast radius of a query bug; they do nothing about an authorisation bug, and Chapter 44 shows how much of the real risk is the second kind.
"We'll start with separate schemas and consolidate later." Consolidation is harder than extraction. Merging N schemas into one shared table means generating a tenant_id, rewriting every foreign key, deduplicating primary keys that collided, and doing it with no downtime. Going the other way — one tenant out of a shared table into its own database — is a copy with a filter. Start shared and extract, not separate and merge.
Meridian, answered
| Question | Answer | Implication |
|---|---|---|
| Tenant count bounded? | No — self-serve | Shared table |
| Contract requires separation? | Not at launch | — |
| Residency requirement? | Not at launch | — |
| Cross-tenant queries? | Yes — platform fee rollups | Favours shared |
| Size distribution? | Long-tailed | Index carefully; expect a noisy neighbour |
| Who runs migrations? | A small team | Avoid O(tenants) migrations |
Shared table with tenant_id, RLS as the second layer, extraction documented but not built until a tenant pays for it.
Key points
- Answer in order; the first hard answer ends the process. Bounded tenant count is question one because it decides whether tenant creation is an
INSERTor a DDL statement in a web request. - Self-serve plus schema-per-tenant means granting DDL rights on a path a stranger can trigger — a bigger concession than the one it was meant to buy.
- Read the actual contract wording. "Logical separation" is satisfied by RLS; "physically separate database" is rarer than it is quoted.
- Residency and dedicated capacity apply to specific tenants, never to the whole estate.
- Cross-tenant reporting is the cost of model 2 that gets discovered last, and the accidental fix is an entire warehouse.
- Separate databases narrow the blast radius of a query bug and do nothing for an authorisation bug, which is the more common failure.
- Start shared and extract later. Extraction is a filtered copy; consolidation is a rewrite of every key.
Next: Whether you need multi-tenancy at all — the version of this project that is three tables and a login.