Designing Multi-Tenant SaaS on PostgreSQL Without Regret

Multi-tenant is one of those architectural decisions that is hard to undo. While building Gatsu, we had to pick one of the three classic PostgreSQL models: shared schema, schema-per-tenant, or database-per-tenant. Every choice carried consequences that only surfaced six months later.

Shared schema with a tenant_id column

The simplest approach: one schema, one set of tables, a tenant_id column on every row. Cheap, quick to deploy, and migrations run only once. Great for the MVP phase.

But there is a price. Without Row-Level Security (RLS), one mis-filtered query can leak data across tenants. Composite indexes must always include tenant_id, and partitioning by tenant only becomes necessary once row counts hit hundreds of millions.

-- The minimum RLS you must ship
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

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

Schema-per-tenant

Each tenant gets its own schema (tenant_acme, tenant_globex). Isolation is cleaner, audits are easier, and per-tenant backup is straightforward. The trade-off: every DDL migration runs against N schemas. With 200 tenants, a 30-second migration becomes a 10-minute operation.

We use this pattern for the accounting module in Gatsu, where consolidated data is never shared across tenants. Per-tenant performance stays stable.

Database-per-tenant

The strictest isolation. Backup, restore, and compliance become easy stories to tell auditors. But connection counts explode fast: one pool per tenant. Without PgBouncer in front, your PostgreSQL server runs out of slots before peak hour ends.

PgBouncer in transaction pooling mode is almost always our default. Combining it with prepared statements requires care, but the payoff is real: we host 400+ tenants on a single 4 vCPU instance.

How I choose

• Shared schema + RLS for many small tenants (≥ 1,000), low data sensitivity, and tight operational budget.
• Schema-per-tenant for mid-sized tenants (10 — 500), audit-grade isolation, and an orchestrated DDL migration story.
• Database-per-tenant for enterprise tenants that demand full backup and SLA control.

No model is universally superior. The most expensive thing is not the technical choice — it is the cost of switching once the product runs. Estimate tenant growth honestly before writing your first migration.

Checklist before the first commit

1. Pick a model and write an ADR (Architecture Decision Record).
2. Add an integration test that runs cross-tenant queries and asserts isolation.
3. Enable log_min_duration_statement from day one; cross-tenant queries are usually slow.
4. Plan a backup strategy that fits the model — before real tenants come in.

Good multi-tenant work is invisible. It shows up as a system that stays calm when the 100th tenant onboards during peak hour.