Multi-Tenancy at the Database Layer

Multi-tenancy is one codebase and one deployment serving many isolated customers. The application side is mostly straightforward. The hard, consequential decision is at the data layer: where do you draw the boundary between one tenant's data and another's? Get it wrong and you either leak data across customers or drown in operational overhead. I made this call on a health-SaaS product, so this is the reasoning from the inside.

The three models

There's a spectrum from "everything shared" to "everything separate":

• Shared database, shared schema — all tenants' rows live in the same tables, tagged with an org_id column.
• Shared database, schema-per-tenant — one database, but each tenant gets its own set of tables (a Postgres schema).
• Database-per-tenant — each tenant gets a physically separate database.

They trade the same two things against each other: isolation (how hard is a cross-tenant leak) versus cost and operational simplicity (how many things you have to run and migrate).

Shared schema: row-level with org_id

Every row carries a tenant identifier, and every query filters on it.

CREATE TABLE appointments (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    org_id  BIGINT NOT NULL REFERENCES organizations(id),
    patient TEXT NOT NULL
);
 
-- every query is scoped to the tenant
SELECT * FROM appointments WHERE org_id = $currentOrg;

This is the cheapest and most scalable to operate — one schema, one migration, thousands of tenants. Its danger is also obvious: isolation lives entirely in your queries. Forget one WHERE org_id = ? and you leak one tenant's data to another. That single missing clause is the nightmare scenario, so this model demands enforcement that doesn't rely on remembering.

Enforcing it so you can't forget

On a shared schema, isolation can't be a convention developers apply by hand — it has to be automatic. Two layers that work together:

• A guard in the request pipeline that resolves the current tenant once, from the authenticated context, and injects it — so no handler invents its own org_id.
• Row-Level Security (RLS) in Postgres itself, so the database refuses cross-tenant rows even if a query forgets the filter.

ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON appointments
    USING (org_id = current_setting('app.current_org')::bigint);

With RLS on, you set the tenant per connection and Postgres transparently filters every query. Now a forgotten WHERE clause fails safe — the database returns nothing rather than the wrong tenant's data. Defense in depth: the app scopes, and the database enforces.

Schema- and database-per-tenant

Move the boundary down and isolation gets stronger:

• Schema-per-tenant — a forgotten filter can't leak across tenants because the tables are literally separate. But now every migration runs N times, connection routing gets more complex, and thousands of schemas strain the database's catalog.
• Database-per-tenant — the strongest isolation and easiest per-tenant backup/restore or even separate hosting. But it's the heaviest to operate: N databases to provision, migrate, monitor, and connect to. It also makes any cross-tenant query (analytics across all customers) genuinely hard.

How I decided

The deciding factors, in order:

• Number of tenants. Thousands of small tenants → shared schema; it's the only one that scales operationally. A handful of large enterprise tenants → per-database becomes reasonable.
• Isolation requirements. Strict compliance or contractual "your data is physically separate" → push the boundary down.
• Operational appetite. Every step toward separation multiplies migrations, backups, and monitoring. Be honest about what the team can run.

For a SaaS with many organizations, I went shared schema with hard enforcement — an org_id on every tenant-scoped table, a tenant-context guard in the pipeline, and RLS as the backstop. It scales to many tenants cheaply, and the isolation doesn't depend on anyone remembering to filter.

The real lesson

There's no universally right answer — there's the right answer for your tenant count, isolation needs, and ops capacity. But whatever model you pick, the non-negotiable is that tenant isolation must be enforced by the system, not by discipline. The most scalable model is also the easiest to breach with one forgotten clause, so if you choose it, make the database the one that says no.