PostgreSQL Multi-Tenant Schema Designs for B2B SaaS

A production B2B SaaS dies in two places: leaked tenant data, and a schema that cannot grow from 10 customers to 10,000. This teardown walks through the designs that actually ship, the failure modes that show up in incident reviews, and a checklist you can run before you take the first paid tenant.

The three designs that matter

1. Shared schema, tenant_id on every row (row-level tenancy)

One database, one set of tables. Every tenant-owned table has tenant_id uuid not null. Access is filtered in SQL and in the application.

Use when: early B2B, similar schema for every customer, you want one migration pipeline and cheap ops.

Structure:

create table tenants (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  created_at timestamptz not null default now()
);

create table users (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  email citext not null,
  unique (tenant_id, email)
);

create table projects (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  name text not null
);

-- composite unique so FKs cannot point across tenants
create unique index projects_tenant_id_id_uidx on projects (tenant_id, id);

create table tasks (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  project_id uuid not null,
  title text not null,
  foreign key (tenant_id, project_id) references projects (tenant_id, id)
);

Failure modes:

• Missing tenant_id on a join table. One forgotten filter returns another company invoice.

• Application-only filters. A raw SQL report, a cron, or an ORM findByPk bypasses the filter.

• Superuser connections that skip RLS. Admin scripts become a data-leak vector.

• Hot tenants. One noisy customer saturates shared indexes and autovacuum.

Hardening: enable Row Level Security on every tenant table. Set app.tenant_id at the start of the request (or transaction) and force the policy:

alter table projects enable row level security;
alter table projects force row level security;

create policy tenant_isolation on projects
  using (tenant_id = current_setting('app.tenant_id')::uuid)
  with check (tenant_id = current_setting('app.tenant_id')::uuid);

Never grant table access to a role that can disable RLS. Use a non-owner app role.

2. Schema-per-tenant

One database, one PostgreSQL schema per customer (tenant_acme, tenant_globex). Same table DDL copied per schema.

Use when: you need per-tenant restores, slightly different extensions, or isolation stronger than RLS without running 10,000 databases.

Failure modes:

• Migration fan-out. 2,000 schemas times 40 migrations is an ops product, not a rake task.

• Connection searchpath bugs. A missed `SET searchpath writes into public`.

• Catalog bloat. pg_class and planner stats grow with every schema.

• Cross-tenant reporting requires UNION ALL or a warehouse, not a simple query.

Cap this pattern around low hundreds of tenants unless you have a dedicated migrator and connection pooler that pins search_path per checkout.

3. Database-per-tenant

One database (or cluster) per customer.

Use when: enterprise contracts demand isolation, custom retention, or you must restore one tenant without touching others.

Failure modes:

• Pooling. PgBouncer cannot hide 5,000 databases behind one URL without a routing layer.

• Backup and PITR cost scales linearly.

• Schema drift. Tenant 47 is three migrations behind and nobody notices until a feature flag ships.

Treat this as an enterprise SKU, not the default.

Recommended production structure (row-level, the default)

app
  db/
    migrations/
    tenant.sql          -- tenants, memberships
    rls.sql             -- policies, force RLS
    indexes.sql
  src/
    db/pool.ts          -- SET app.tenant_id on checkout
    middleware/tenant.ts
    repos/              -- every query includes tenant_id

Rules that keep this honest:

1. tenant_id is on every tenant-owned table. No exceptions for "lookup" tables that still store customer data.

2. Foreign keys are composite (tenant_id, parent_id) so a child cannot attach to another tenant parent.

3. Unique constraints are (tenant_id, natural_key), never global email uniqueness unless the product is truly global identity.

4. The request middleware sets tenant context before any repository call. Jobs take an explicit tenant_id argument. No ambient "current user" without tenant.

5. RLS is FORCEd. Tests include a negative case: query without setting app.tenant_id must return zero rows or error.

Indexes and query shape

• Leading column on almost every index: tenant_id.

• Partial indexes for hot statuses: create index on invoices (tenant_id, created_at) where status = 'open'.

• Avoid global sequential scans in crons. Always WHERE tenant_id = $1 or iterate tenants in batches.

Checklist before first paid tenant

• [ ] Inventory every table. Mark tenant-owned vs global (plans, feature flags).

• [ ] Composite FKs on all child tables.

• [ ] RLS enabled and forced on tenant-owned tables.

• [ ] App role is not table owner and cannot bypass RLS.

• [ ] Integration test: two tenants, same email local-part, no cross read.

• [ ] Integration test: findById without tenant context fails closed.

• [ ] Backups tested for single-tenant export (dump by tenant_id).

• [ ] Runbook for noisy neighbor: statement timeout, per-tenant rate limit, kill query.

• [ ] Migration job is one pipeline, not a per-schema loop, unless you chose schema-per-tenant on purpose.

• [ ] Logging never prints full row payloads from other tenants in error traces.

What to copy vs what to invent

Do not invent a fourth isolation model. Pick row-level + RLS for almost all B2B SaaS under a few thousand tenants. Promote a single enterprise customer to database-per-tenant only when the contract pays for the ops.

If you want the production templates, schemas, and configurations (RLS policies, composite FK patterns, pool SET middleware, and the test harness), get the kit here:

Get the full production templates, schemas, and configurations at https://whop.com/whop-650b/micro-saas-production-kit