Multi-tenant Postgres without regretting it in year two

There are three ways to hold several customers' data in Postgres, and the decision is usually made in week one by whoever is typing. It then becomes very expensive to change in year two, when a customer asks a question that your chosen model cannot answer.

The question is almost always one of these: can we have our own backup, can you delete all our data by Friday, or can you prove our data has never touched another customer's. Pick the model that makes those answerable.

Option 1 — shared schema with a tenant_id column

One set of tables, every row carrying tenant_id, every query filtered by it.

It is the cheapest to build and by far the cheapest to run. One connection pool, one migration, one set of indexes. Hundreds of tenants fit comfortably on hardware that would struggle with dozens under the other models.

The cost is that isolation is now a property of your code rather than of the database. Every query is one forgotten WHERE clause away from a cross-tenant leak — the kind of bug that ends a contract.

If you choose this, use row-level security rather than discipline:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

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

Set app.tenant_id once when the request checks out a connection. Now a missing filter returns nothing instead of returning somebody else's data, and the guarantee lives in the database where it cannot be forgotten.

Two more things that matter at scale: put tenant_id first in your composite indexes, and be aware that one enormous tenant will skew the query planner's statistics for everybody else.

Option 2 — schema per tenant

One database, one Postgres schema per tenant, identical tables inside each.

Isolation becomes structural — a query in the wrong schema finds nothing. Per-tenant backup and restore is a real operation rather than a filtered export. And you can migrate tenants in batches, which matters more than people expect when a change is risky.

The cost arrives as a number nobody predicts: catalogue bloat. Every schema multiplies your table count. Two hundred tenants with fifty tables each is ten thousand tables, and pg_dump, autovacuum and connection startup all slow down measurably. Migrations go from one statement to a loop with partial-failure handling — tenant 147 fails halfway and now you have two schema versions in production.

This model is the right answer more often than its reputation suggests, but the ceiling is real. Past roughly 200–300 tenants it stops being pleasant.

Option 3 — database per tenant

Complete separation. Separate backups, separate restore, separate tuning, and a straight answer to every compliance question.

You pay for it in operations. Connection pooling becomes a genuine problem — Postgres connections are processes, and you cannot hold a pool open per tenant past a few dozen without a proxy in front. Cross-tenant reporting needs a separate warehouse, because you can no longer write one query across all customers. Migrations become a deployment pipeline of their own.

Choose this when the customers are few, large and contractually demanding. A hundred small tenants in a hundred databases is a full-time job you did not budget for.

How to actually choose

Three questions settle it, in this order.

How many tenants, realistically, in three years? Thousands means shared schema; the other two will not hold. Dozens means database-per-tenant is affordable. In between, schema-per-tenant is usually the balance.

What will the largest customer demand? If a contract will ever say "our data is physically separate" or "restore our data without affecting others", shared schema is already ruled out. Find this out before you build, by asking sales what the enterprise deal looks like.

Who runs it at 3am? Database-per-tenant assumes someone can operate a fleet. If your team is three developers and no platform engineer, do not sign up for that.

The hedge that actually works

Start with shared schema and row-level security, but write every query through a data layer that takes the tenant as an explicit argument rather than reading a global. Keep tenant IDs as UUIDs from day one, never sequential integers.

That combination means moving a single demanding tenant into their own database later is an export, an import, and a routing change — not a rewrite. We have done that migration twice. Both times it took about a week, and both times it was only a week because of those two early decisions.

The mistake is not picking the wrong model. It is picking one in a way that makes the others unreachable.