Multi-Tenant Database Design

In one line

Multi-tenant SaaS stores many customers' data with one of four models: shared schema with a tenant_id column (cheapest, needs strict isolation), schema per tenant, database per tenant, or cluster per tenant (strongest isolation, highest cost). Most platforms mix them: pooled tenants in shared tables, big or regulated tenants isolated. Noisy neighbours, quotas, migrations and deletions must be designed in.

0/3 · 0%

Think of it like this

Housing. A hostel with shared rooms (shared schema), an apartment block with private flats (schema per tenant), terraced houses (database per tenant), or detached houses on separate plots (cluster per tenant). More privacy costs more rent and maintenance.

Key ideas

01
Shared schema: every table has tenant_id, part of primary keys and indexes ((tenant_id, id)), enforced with RLS; it scales to millions of tenants and is simple to migrate once. The risks are a missed filter leaking data (mitigated by RLS and tests), and noisy neighbours.
02
Schema per tenant: stronger logical separation, easy per-tenant restore; but thousands of schemas bloat the catalogue, and migrations run N times. Database per tenant: separate backups, encryption keys and resource limits; connection pooling gets harder. Cluster per tenant: for enterprise and regulated customers, and the most expensive.
03
Noisy neighbour: one tenant's heavy report slows everyone. Mitigate with per-tenant rate limits and quotas, statement timeouts, moving big tenants to dedicated shards, and routing analytics to replicas or a warehouse.
04
Tenant lifecycle: onboarding (provision, seed), migration between shards or tiers (copy, sync via CDC, cut over), backup and restore of one tenant (easy with database-per-tenant; needs tenant-scoped export in shared schema), deletion (drop the database or delete by tenant_id in batches), and per-tenant encryption keys for crypto-shredding.
05
Tenant-aware indexes: lead with tenant_id; per-tenant custom fields via JSONB with GIN or tenant-specific partial indexes for big tenants.

Code & diagrams

shared-schema.sqlsql

CREATE TABLE project (
  tenant_id  bigint NOT NULL,
  id         bigint NOT NULL,
  name       text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, id)
);
CREATE TABLE task (
  tenant_id  bigint NOT NULL,
  id         bigint NOT NULL,
  project_id bigint NOT NULL,
  title      text NOT NULL,
  status     text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id) REFERENCES project (tenant_id, id)  -- no cross-tenant links
);
CREATE INDEX ON task (tenant_id, project_id, status);

ALTER TABLE task ENABLE ROW LEVEL SECURITY;
ALTER TABLE task FORCE ROW LEVEL SECURITY;
CREATE POLICY t ON task USING (tenant_id = current_setting('app.tenant_id')::bigint)
                      WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

tenancy-models.mermaiddiagram

Rendering diagram…

Interview problem

The problem

Tiered tenancy for a B2B SaaS

The product has 50K small tenants, 200 mid-size, and 10 enterprise tenants who demand dedicated infrastructure and their own encryption keys; one enterprise tenant is 20% of all data. Design the tenancy model, routing and tenant migration.

When it breaks

Cross-tenant data leak via a missing tenant filter

What you see

One API endpoint lists tasks without tenant_id; customers see each other's data, which is a reportable breach.

Fix & prevent

RLS as defence in depth, tenant_id in composite keys and FKs, repository layers that inject tenant scope, and automated cross-tenant access tests.

Explain it without notes

Compare shared schema and database-per-tenant.

Practice

Why include tenant_id in foreign keys?

Trade-offs

↔
Isolation strength versus cost and operational load; tiered models give each customer segment the right point.

Done when you can

I can choose a tenancy model, enforce isolation, handle noisy neighbours, and migrate tenants between tiers.

Command Palette