How do you design a multi-tenant database — shared schema vs. schema-per-tenant vs. database-per-tenant?

#Three points on a spectrum

Multi-tenant SaaS backends put every tenant's data in one of three places, and the choice is really about how much isolation you buy and at what operational cost:

1. Shared schema (pooled) — one database, one set of tables, every row tagged with a tenant_id. All tenants share the same schema, the same connection pool, the same indexes.
2. Schema-per-tenant (bridge / siloed-lite) — one database instance, but each tenant gets its own schema/namespace with an identical set of tables (tenant_123.orders, tenant_456.orders).
3. Database-per-tenant (siloed) — each tenant gets a fully separate database (potentially on separate instances), with no shared storage engine footprint at all.

#Shared schema: the default for most SaaS

CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   BIGINT NOT NULL REFERENCES tenants(id),
    customer_id BIGINT NOT NULL,
    total_cents INTEGER NOT NULL,
    status      TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Every index that supports a tenant-scoped query leads with tenant_id,
-- so the planner can prune to a single tenant's rows immediately.
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at DESC);

-- Belt-and-suspenders isolation: Postgres row-level security means even a
-- forgotten WHERE clause in application code can't leak another tenant's rows.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

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

The application sets app.current_tenant_id once per connection/transaction (typically right after checking out a pooled connection, from the authenticated request's tenant claim), and from that point every query — even a lazily-written SELECT * FROM orders, no WHERE clause at all — is transparently filtered to that tenant by Postgres? itself. This is the single most important safety net in a shared-schema design, because the alternative is "every engineer, on every query, forever, must remember to filter by tenant_id" — which is a promise a codebase eventually breaks.

The cost of shared schema is entirely operational risk concentrated in one place: a full table scan from one tenant's bad query burns I/O and buffer cache that every other tenant is also relying on, and a single index bloat or vacuum problem is everyone's problem simultaneously.

#Schema-per-tenant: isolation without infrastructure sprawl

-- Onboarding tenant 123: clone the template schema's structure.
CREATE SCHEMA tenant_123;

CREATE TABLE tenant_123.orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total_cents INTEGER NOT NULL,
    status      TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Here tenant_id disappears from every table — the schema boundary is the isolation, so no WHERE tenant_id = ? can ever be forgotten. The catch shows up in operations: adding a column now means iterating every tenant schema and running the migration N times (usually via a migration tool driven in a loop, with retry/rollback tracked per schema), and most engines have a practical ceiling on schema count per instance (Postgres handles thousands of schemas but query planning, pg_dump, and connection-time catalog lookups all get slower as schema count grows into the tens of thousands).

#Database-per-tenant: full isolation, full operational weight

This is the right call when tenants are large enough that noisy-neighbor risk is unacceptable, when contracts require data residency or dedicated infrastructure, or when a single enterprise tenant's compliance/audit requirements (its own backup schedule, its own encryption key, its own retention policy) can't be satisfied by anything short of a dedicated instance. The cost is that "provision a tenant" becomes real infrastructure work, and running a migration across a fleet of 500 tenant databases needs its own orchestration (a migration runner that iterates connection strings, tracks per-tenant migration version, and handles partial failure without corrupting the fleet's state).

#The pattern almost nobody skips: a hybrid

Production SaaS backends rarely pick one point on the spectrum for every tenant. The common shape is a pooled tier (shared schema) for the long tail of free/small-plan tenants, where per-tenant overhead must be near zero, and a siloed tier (schema- or database-per-tenant) for enterprise accounts paying for isolation, compliance, or guaranteed performance — with a migration path to move a tenant from pooled to siloed as they grow or upgrade their plan. Designing for that migration path from day one (consistent tenant identifiers, a data-access layer that doesn't hard-code "everything is one shared table") is far cheaper than retrofitting it once a single enterprise prospect requires isolation your architecture never anticipated.

🧠 Check your understanding
0/3 · 0/3 answered
1. In a shared-schema multi-tenant design, what is the main practical benefit of enabling row-level security on top of application-level tenant_id filtering?
It automatically shards the table by tenantIt provides a database-enforced backstop so a missing WHERE clause in application code cannot leak another tenant's rowsIt removes the need for a tenant_id column entirelyIt improves query performance by adding an implicit index
2. Which multi-tenancy model makes 'delete all of a tenant's data' for a compliance request cleanest to execute?
Shared schema, via a DELETE statement per tableDatabase-per-tenant, via dropping the whole databaseSchema-per-tenant is impossible to fully deleteShared schema, via row-level security policies
3. Why do most large SaaS platforms end up using a hybrid multi-tenancy strategy rather than one model for every tenant?
Database engines only support one tenancy model per installationSmall tenants need near-zero per-tenant overhead (pooled), while large or regulated tenants need isolation guarantees that only a siloed schema or database can provideShared schema and database-per-tenant cannot coexist in the same companyHybrid designs are mandated by SQL standards