Multi-tenancy decisions get argued like religion and measured like astrology. Strip away the dogma and three questions decide almost everything: how noisy are your tenants, how customisable must each tenant's data be, and what does compliance say about data residency?
The shared-schema default
One schema, tenant_id on every tenant-owned table, enforced by composite foreign keys and row-level security. It is the cheapest to operate, the easiest to migrate, and the only option where connection pooling behaves itself. For ninety percent of SaaS products, it is simply correct.
<pre><code class="language-sql">ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
USING (organisation_id = current_setting('app.tenant_id')::uuid);</code></pre>When tenants get loud
One customer importing a million rows can degrade everyone sharing their indexes. Before reaching for schema-per-tenant, try cheaper mitigations: partition hot tables by tenant hash, cap bulk operations with workload management, or route heavy tenants to a read replica tuned for their reporting appetite.
Enforce isolation in the model layer too
Database constraints are your safety net, not your primary defence. Global scopes keyed on the authenticated tenant, verified by tests that deliberately attempt cross-tenant access, catch application bugs before RLS ever fires. Both layers exist precisely because either can fail independently.
- Composite FKs: tenant_id + id references, never bare ids
- RLS policies tested with malicious contexts, not just happy paths
- Tenant-scoped cache keys — prefix everything, test the prefix
- Migrations rehearsed on a seeded copy with realistic tenant distribution
Start shared-schema. Partition when measurements demand it. Reserve database-per-tenant for customers whose contracts literally require it — and price that requirement accordingly, because you will pay for it in operational complexity forever.