Database Design Patterns for Multi-Tenant SaaS

The PostgreSQL toolbox for tenancy

PostgreSQL gives you four levers for multi-tenancy: tagged rows, schemas, databases, and clusters. Most successful products use two of them at once.

Row-level security (RLS)

Turn it on. Define a policy:

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

Then set app.tenant_id on every connection. A missing setting means no rows are visible — fail closed.

Schemas as bulkheads

When tenants want logical isolation:

CREATE SCHEMA tenant_acme;
SET search_path = tenant_acme, public;

Run migrations as a fan-out across all tenant schemas. Tooling like migrate-multi or a custom worker handles this cleanly.

Connection pooling

PgBouncer in transaction mode plus per-tenant search paths can blow up. Either:

• Use session pooling, or
• Always set SET LOCAL inside a transaction.

Backups and restores

The hardest part of tenancy is *not* the read path; it is restoring a single tenant after they delete production data. Plan for:

• Per-tenant logical dumps if you use the pool model.
• Per-schema pg_dump for schema-per-tenant.
• Per-cluster snapshots for silo model.

Indexes and tenant_id

Every index in the pool model starts with tenant_id. Otherwise queries become full-table scans the moment a tenant grows.

When to graduate a tenant

Promote a noisy customer to their own schema or cluster when their queries dominate latency for everyone else. Build the promotion tooling early; it pays for itself.