Chapter 2 · Foundations

The three isolation models

Shared table, schema-per-tenant, database-per-tenant. The differences that matter are not the ones usually listed, and one of the three is a trap at small scale.

6 min read

Every multi-tenant system puts tenant data in one of three places. The choice is usually framed as a security question. It is mostly an operations question, and the security differences are smaller than they look.

Model 1: shared table, `tenant_id` column

Every tenant's rows live in the same tables, discriminated by a column.

create table products (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null references tenants(id),
  name        text not null,
  price_cents integer not null,
  created_at  timestamptz not null default now()
);

-- The index that matters. Every query starts with tenant_id, so it must lead.
-- An index on (name) alone is nearly useless here: Postgres would have to scan
-- every tenant's products to find one tenant's "Pro plan".
create index products_tenant_name_idx on products (tenant_id, name);

That composite index is the single most common performance mistake in this model, and it is worth understanding why rather than copying the rule. A query filtered by tenant_id and sorted by name can walk (tenant_id, name) directly. Against separate single-column indexes, the planner picks one, filters, and then sorts — which at a thousand tenants means reading a thousand times more rows than the answer contains.

What it is good at: one schema to migrate, one connection pool, cheap cross-tenant queries for your own analytics, and a new tenant is an INSERT.

What it is bad at: nothing stops a bad query. The isolation is a where clause, and a where clause can be forgotten. This is why Section 3 exists.

Model 2: schema-per-tenant

Each tenant gets a Postgres schema containing the same tables.

create schema tenant_8f3a;
create table tenant_8f3a.products (/* ... */);

-- Scoping becomes a connection setting rather than a query predicate.
set search_path to tenant_8f3a, public;
select * from products;   -- resolves to tenant_8f3a.products

This reads beautifully and then bills you later.

Migrating one column across:
      10 tenants  ->  10 ALTER TABLE statements      fine
     500 tenants  ->  500 ALTER TABLE statements     slow, needs a runner
   5,000 tenants  ->  5,000 ALTER TABLE statements   a project with its own on-call

Two specific costs that surprise people:

Cost	Why
Catalogue bloat	Postgres stores every table, index and constraint in system catalogues. 5,000 schemas × 40 tables is 200,000 relations. `pg_dump`, autovacuum and the planner all slow down.
Partial migrations	A migration that fails at tenant 312 of 500 leaves the estate in two different schema versions, and your application code must tolerate both.

It is the right answer when tenants are few, large and demand demonstrable separation — enterprise deals with a compliance questionnaire. It is the wrong answer for self-serve signup, where tenant count is unbounded and most tenants are tiny.

Model 3: database-per-tenant

Each tenant gets their own database, possibly their own server.

Strongest isolation, highest cost. Every tenant needs connections, backups, monitoring, a migration run and a restore test. Connection pooling becomes the hard problem: a pool of 20 per database across 300 databases is 6,000 connections, which no single Postgres instance will give you without a proxy in front.

It is the right answer for data residency (this tenant's rows must stay in Canada), for a tenant large enough to need their own capacity, and for a regulated customer whose contract requires it. Chapter 142 covers moving one tenant here from model 1, which is the realistic path — you do not start here.

The comparison that actually decides it

	Shared table	Schema	Database
New tenant	`INSERT`	`CREATE SCHEMA` + migrate	Provision + migrate + monitor
Migration cost	O(1)	O(tenants)	O(tenants)
Connections	One pool	One pool	One pool each
Cross-tenant analytics	A `GROUP BY`	A union of N schemas	An ETL job
Noisy neighbour	Shared	Shared	Isolated
Blast radius of a bad query	Everyone	One tenant	One tenant
Data residency	Impossible	Impossible	Natural

The last three rows are the honest case for the heavier models, and the second-to-last row is the one people reach for. It is real — but note what it costs to buy, and note that model 1 plus row-level security moves that row most of the way without the migration bill.

What Meridian uses, and why

Meridian is self-serve: a vendor signs up with a card and a domain, and tenant count is unbounded. Shared table with tenant_id, row-level security as a second enforcement layer, and a documented path to extract a single tenant to its own database when one asks.

Choose the model that makes the common operation cheap. For a self-serve product the common operation is creating a tenant, and in model 1 that is one row. In model 2 it is a DDL statement inside the request that just took a payment.

The trap

Schema-per-tenant looks like a free upgrade in security for a small amount of extra work. At twenty tenants it is. The bill arrives at the first migration you have to run after you have three hundred, and by then the decision is expensive to reverse — which is the subject of Chapter 9.

Key points

Three models: shared table with a tenant_id column, schema-per-tenant, database-per-tenant.
In model 1 the composite index must lead with tenant_id. Single-column indexes make the planner scan every tenant's rows to answer one tenant's question.
Schema-per-tenant turns every migration into O(tenants) and bloats the system catalogues; a failed run leaves the estate on two schema versions at once.
Database-per-tenant multiplies connections, backups and monitoring. It is the answer for residency and dedicated capacity, not a starting point.
Model 1's weakness is that isolation is a where clause. Row-level security closes most of that gap without the migration cost.
Meridian uses shared table plus RLS, with a documented extraction path for the tenant who eventually needs its own database.

Next: Choosing an isolation model — the questions to answer before picking, in the order that makes the answer obvious.

Model 1: shared table, tenant_id column