Multi-Tenant SaaS: Shared Schema, Schema-per-Tenant, or Database-per-Tenant

Which tenancy model should you choose?

Start with a shared schema and row-level security unless a contract already requires otherwise. It is the cheapest model to run, the only one where a migration is a single deployment, and it can be selectively split later for the few tenants that genuinely need physical separation. Teams that start at database-per-tenant to be safe usually discover they bought an isolation guarantee nobody asked for and a migration process nobody can run.

Here is the whole decision in one table.

Shared schema, done properly

Every table carries a tenant_id, and the database, not the application, enforces the filter. Application-level filtering is one forgotten WHERE clause away from a cross-tenant data leak, and that is the single worst incident class a SaaS product has.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Then set app.tenant_id once, at connection checkout, from the authenticated session, never from a request parameter.

Two failure modes to design against explicitly. Row-level security is bypassed by superusers and by the table owner, so the application role must be neither. And every index needs tenant_id as its leading column, or a query for one tenant scans across all of them and the largest customer sets the latency for everyone.

When to actually split a tenant out

Not on a hunch. There are three defensible triggers:

1. A contractual or regulatory requirement for physical separation, common in health and public-sector deals, and usually non-negotiable.
2. A tenant large enough to distort the shared cluster, where their query patterns set p99 latency for every other customer.
3. A residency requirement placing one tenant's data in a specific jurisdiction.

Design for this from the start by keeping the tenant-to-connection-string mapping in a routing layer, even while every tenant resolves to the same database. Adding that layer later means touching every data-access path in the product; having it from day one makes splitting a tenant a configuration change.

The part that gets underestimated

Operations, not architecture. Whichever model you pick, you will eventually need to: restore one tenant to a point in time without touching the others, export everything one tenant owns on request, delete it all on request, and run a schema migration across the fleet without a maintenance window.

Write those four runbooks before you have fifty customers. Every model can satisfy them; they just cost wildly different amounts of engineering depending on how early you asked. Teams that pick a tenancy model on isolation alone, and meet these requirements later, are the ones who end up rebuilding.

References

• PostgreSQL. Row Security Policies.
• AWS. SaaS tenant isolation strategies.
• Microsoft. Multi-tenant SaaS database tenancy patterns.