Building a Multi-Tenant Database Schema That Scales

The three classic options each trade isolation against operational cost.

The options

1. Shared tables + tenant_id — cheapest, weakest isolation, easiest to leak across tenants in a bad query.
2. Schema-per-tenant — strong isolation, manageable migrations, our choice.
3. Database-per-tenant — strongest isolation, painful at thousands of tenants.

We went with schema-per-tenant: each tenant gets an isolated Postgres schema, and a middleware sets the search_path per request.

-- Per-request, scoped to the resolved tenant
SET search_path TO tenant_acme, public;

Isolation you have to remember to apply is isolation you will eventually forget. Make it the default at the connection layer, not the query layer.

Migrations run per schema in a loop with advisory locks so two deploys never collide.