Multi-tenant Postgres without the regrets
Row-level security, schema-per-tenant, or separate databases — when each model breaks, and the migration plan you wish you'd have at the start.
There are three honest answers to 'how do we make this multi-tenant?' and one dishonest one. The dishonest one is 'add a tenant_id column and we'll figure it out later.' The honest answers — row-level security, schema-per-tenant, database-per-tenant — each break in their own predictable ways. Picking is mostly about deciding which kind of pain you'd rather have.
Shared schema with row-level security
Cheapest to operate, easiest to back up, hardest to get wrong twice. Postgres RLS does the heavy lifting if you commit to it — every query carries the current tenant in a session variable, and policies enforce isolation at the database. Performance is fine for surprisingly large customer counts.
- Strong: cheap, simple operationally, easy to query across tenants for analytics.
- Weak: noisy-neighbour risk; a single big customer can slow every other tenant on the same indexes.
- Breaks when: you need per-tenant performance guarantees or per-region data residency.
Schema per tenant
One Postgres database, many schemas. Stronger isolation, simpler mental model — every tenant lives in its own namespace, and the application sets the search_path. Easier to migrate one tenant at a time. Costs scale linearly with tenant count, but that's usually fine until you hit five-figure tenant counts.
Database per tenant
Maximum isolation, maximum operational cost. Each tenant gets a full database, with its own backups, migrations, and connection pool. Worth it when compliance requires it, or when one tenant's failure must not touch any other tenant.
If you're not sure you need database-per-tenant, you don't. Start with RLS and earn the upgrade when you have a real reason.
The migration plan nobody writes
Whatever you pick on day one, you'll eventually move some tenants up the stack. Build the migration plan now, while you're early, even if you never run it. Two things make the move tractable:
- Make every query carry the tenant id explicitly, even when RLS would enforce it. Future-you can grep for the tenant boundary.
- Version your schema migrations per tenant. Schema-per-tenant deployments mean tenants drift; track which version each one is on.
There's no model that doesn't break. There's just the model whose breakage you've planned for. Pick the one that lets you sleep at night, then write the runbook for the day it doesn't.