How we design multi-tenant SaaS on PostgreSQL

Why multi-tenancy matters

Every SaaS product we run serves many customers from one codebase. How you separate those customers in the database decides your security, your hosting cost and how painful migrations will be later.

We have tried three approaches over six products. This is what we learned, and the default we now start every new product with.

Three ways to isolate tenants

A shared schema with a tenant_id column is cheapest and easiest to run. A schema per tenant gives stronger separation but makes migrations slower. A database per tenant is the strongest option and the most expensive to operate.

For most B2B products with hundreds or a few thousand customers, a shared schema plus row-level security gives the best balance.

• Shared schema: lowest cost, needs strict query discipline
• Schema per tenant: good isolation, slower migrations
• Database per tenant: strongest isolation, highest cost

Row-level security in practice

PostgreSQL row-level security moves the tenant check out of application code and into the database. Even a buggy query cannot read another tenant's rows.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

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

Migrations without downtime

We ship schema changes in two steps: first add new columns and backfill in batches, then switch the code and remove old columns in a later release. Large tenants are migrated during their local night.