For a multi-tenant SaaS product on a relational database, how would you choose between enforcing tenant isolation with row policies, a schema per tenant, or a database per tenant?

## The decision is about failure modes, not elegance Every model can be made correct. What differs is what happens when something goes wrong, and what routine operations cost. ## Option A — shared tables with row policies All tenants live in the same tables, discriminated by a tenant column, with row policies enforcing the predicate. *Strengths.* One schema and one migration regardless of tenant count. One connection pool, so connection usage does not grow with tenants. Best resource utilisation: ten thousand tiny tenants share buffers, indexes and vacuum work instead of each paying a fixed overhead. Cross-tenant aggregate reporting is a plain query. Onboarding a tenant is an INSERT. *Weaknesses.* Isolation is enforced by code paths that must all be right: the policies, the context-setting mechanism, and the absence of exempt roles. A single mistake is a *cross-tenant* mistake. Per-tenant operations are awkward — restoring one tenant to yesterday means extracting rows from a whole-database backup; deleting a tenant is a large, index-churning DELETE. Noisy neighbours share a buffer pool and lock manager. Very large tenants and very small ones share the same tables and the same statistics, which can make plans that suit neither. *Performance shape.* The tenant column must lead the indexes; otherwise the policy predicate is a filter after a scan. Partitioning by tenant (or by tenant hash) restores per-tenant pruning and makes bulk delete a partition drop, at the cost of partition-count limits. ## Option B — schema per tenant One database, one namespace per tenant holding identical tables. *Strengths.* Isolation is enforced by name resolution and object privileges rather than per-row predicates — simpler to audit, and a query that forgets its scope simply cannot reach another tenant. Per-tenant dump, drop and even per-tenant index tuning become natural. Migrations can roll out tenant by tenant, which some teams value. *Weaknesses.* The catalog grows linearly: N tenants × M tables of metadata, which slows catalog lookups, autovacuum bookkeeping, dumps and connection start-up. Migrations become a loop with partial-failure semantics, and "which tenants are on which schema version" turns into a state machine. Prepared-statement and plan caches fragment because each schema is a different set of objects. Practical ceilings are in the hundreds to low thousands of tenants, engine-dependent. ## Option C — database or cluster per tenant *Strengths.* The hardest boundary short of separate accounts: independent backups and point-in-time restore, independent upgrade windows, independent resource limits, and a straightforward story for data residency, deletion guarantees and customer-managed encryption keys. When a customer asks "prove my data cannot be reached by another customer's query", this is the answer that survives an auditor. *Weaknesses.* Everything per-tenant is now a fleet operation: provisioning, monitoring, patching, connection pools, migrations. Fixed per-database overhead (background workers, memory areas, minimum storage) makes small tenants uneconomic. Cross-tenant analytics needs a separate pipeline. ## How I actually decide 1. **Tenant count and size distribution.** Tens of thousands of small tenants rules out per-database as a default. A handful of large enterprise tenants makes per-database cheap and obviously right. 2. **Regulatory and contractual asks.** Data residency, per-tenant encryption keys, provable deletion, or a per-tenant restore SLA push toward physical separation. "Logical isolation with policies" is often acceptable — but it is a conversation with the customer, not a purely technical call. 3. **Blast radius tolerance.** Ask what a single bad deploy can do. With row policies, a bad policy or a leaked context is a cross-tenant disclosure; with separate databases it is one tenant's outage. 4. **Operational maturity.** Schema-per-tenant and database-per-tenant only work with real automation for migrations, provisioning and drift detection. Without that, they are worse than the shared model, because "tenant 412 never got the migration" is a silent correctness bug. 5. **Reporting needs.** Cross-tenant analytics is trivial in the shared model and a pipeline project in the others. ## The pragmatic architecture Most mature products end at a **tiered** design: the default tier is shared tables with row policies plus an application-level tenant predicate, and specific tenants are promoted to a dedicated database when size or contract demands it. The key engineering requirement is that the application not care: a routing layer resolves tenant to a data source and to a tenant context, and the same code runs against both. Choosing that indirection early is what keeps the decision reversible — which matters more than getting it right the first time, because tenant mix is the input you cannot predict.