Tenant isolation in multi-tenant architecture: three approaches, three bills

In a SaaS product, the decision made on day one and hardest to reverse is how tenant data will be separated. There are three main paths, and none is absolutely better than the others.

Path 1: A database per tenant

Each tenant lives in its own database.

Strength: Isolation is physical. A buggy query cannot reach another tenant's data because there is no connection to it. Backup, restore and data deletion happen per tenant — a real advantage for GDPR and equivalent requests.

Weakness: Schema migration. With five hundred tenants you run migrations across five hundred databases on every release. If one stalls halfway, you are debugging a mixed-version system.

When we choose it: Tenant count is low (a few hundred), data volume per tenant is high, and customers contractually require physical separation.

Path 2: One database, a schema per tenant

A single database with one PostgreSQL schema per tenant.

Strength: Migrations can still run in batch, while isolation stays logically clear. You route each connection to the right schema via search_path.

Weakness: As schema count grows, catalogue tables bloat; past a few thousand schemas, connection setup time and planner cost become noticeable.

When we choose it: A mid-sized tenant count (hundreds), frequent migrations, and a need for per-tenant custom tables.

Path 3: Shared tables with row-level security

All tenants share tables, separated by a tenant_id column, with PostgreSQL row-level security (RLS) enforced.

alter table invoices enable row level security;

create policy tenant_isolation on invoices
  using (tenant_id = current_setting('app.tenant_id')::uuid);

Strength: One schema, one migration, the lowest operating cost. Scales to thousands of tenants.

Weakness: Isolation depends entirely on correct configuration. A single connection opened without app.tenant_id set can defeat the whole policy. And the table owner role bypasses RLS by default — missing that detail leaves isolation that looks present but is not.

When we choose it: High tenant count, low data volume per tenant, and a standard product with no per-tenant schema.

The safeguards we require when using RLS

We choose the third path most often, because the operating cost is markedly lower. But we do not ship it without these four safeguards:

1. The application role is never the table owner. The owner role is used only for migrations and is never handed to the app.
2. FORCE ROW LEVEL SECURITY is on, so the owner role is subject to the policy too.
3. Tenant context is mandatory in the connection pool. Every connection taken from the pool is tagged via set_config before any query runs; code that tries to query on an untagged connection throws in development.
4. A leak test runs in CI. Two tenants are created, and every table of one is queried under the other's context; if a single row comes back, the build fails.

The fourth is the most valuable. Written once, that test caught three separate misconfigurations before production over the following two years.

Decision table

| Criterion | Separate DB | Separate schema | RLS | | --- | --- | --- | --- | | Isolation strength | High | Medium | Configuration-dependent | | Migration cost | High | Medium | Low | | Scalability | Low | Medium | High | | Ease of tenant deletion | High | High | Medium | | Operating cost | High | Medium | Low |

Summary

None of the three is "correct"; one of them fits your constraints. Before deciding, answer three questions:

• How many tenants do you expect in five years?
• When a customer says "delete all my data", how quickly must you comply?
• Do your contracts promise physical separation?

Those three answers usually narrow the choice to one.