Multi-tenancy

Every B2B application arrives at this question. There are four answers, and three of them are the same answer at different granularities.

Shared schema

One set of tables, a tenant_id column, every query filtered on it.

CREATE TABLE orders (
  id         BIGINT      GENERATED ALWAYS AS IDENTITY,
  tenant_id  BIGINT      NOT NULL REFERENCES tenants(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  total      NUMERIC     NOT NULL,
  PRIMARY KEY (id)
);

-- tenant_id LEADS every index. This is the whole trick.
CREATE INDEX orders_tenant_created ON orders (tenant_id, created_at DESC);

This scales to many thousands of tenants and is the right default. One migration covers everyone, cross-tenant queries are ordinary SQL, and there is exactly one of everything to operate.

What it costs you is that isolation is now an application invariant. Every query needs the filter, and one that forgets is a data leak rather than an error. Enforce it in one place - an ORM global scope, a repository layer, a middleware that injects the predicate - not by remembering. See Row Level Security for the database-side option and its real trade-offs.

Two details that matter later:

• Use BIGINT for tenant_id, not text. It is smaller in every index, and every index is going to lead with it.
• If tenants churn, consider partitioning by tenant_id. Offboarding then becomes DROP TABLE on a partition rather than a large DELETE and its vacuum debt (Large tables). The cost is that onboarding has to create a partition.

Schema per tenant

One schema per tenant in one database, selected with SET search_path.

It looks appealing - no filter to forget, smaller indexes per tenant, one tenant's bloat cannot slow another's vacuum. It stops working somewhere in the low hundreds of tenants, and the reason is the system catalogs.

Every table, index, column and constraint is a row in the catalog. Multiply your schema by 300 tenants and the catalog is now large; the planner consults it on every query, and migrations slow down proportionally. You will also find search_path is a convention, not a constraint: nothing in the database stops a query from reading another tenant's schema, so you have swapped one application-enforced invariant for a different application-enforced invariant.

Choose this only when tenants genuinely have different schemas. If the schema is the same and only the data differs, shared schema is doing the same job with less machinery.

Database per tenant

One logical database per tenant, selected by connection string.

Better than schema-per-tenant in one respect: each database has its own catalog, so they do not accumulate into one. Worse in the respect that ends the conversation - connection pooling. Pools are per (user, database) pair, so N tenants means N pools. Even at two server connections each, 200 tenants is 400 connections before your application has done anything, and cross-tenant queries stop being possible at all.

There is also a fixed cost per database (CREATE DATABASE copies a template, ~8 MB before you insert a row), which matters when tenants are small and numerous.

Cell per tenant

Give each tenant its own CapyDB database.

This is the same idea as database-per-tenant with the objection removed: a cell is a real Postgres instance with its own pooler, its own connection budget, its own memory, and its own CPU allocation. Nothing is shared, so nothing is contended - one tenant's runaway report cannot touch another's latency, and one tenant's bloat is one tenant's problem.

It is the right model when tenants are few and large, or when isolation is a contractual requirement rather than a preference: per-tenant PITR, per-tenant restore, per-tenant deletion that is genuinely a deletion. Scale-to-zero means idle tenants cost close to nothing, which is what makes it viable at counts that would be absurd on always-on infrastructure.

It is the wrong model when tenants are many and small, or when you need to query across tenants - that becomes N queries and a merge in your application, or a separate warehouse.

Choosing

Start with shared schema. Move a tenant to its own cell when that tenant justifies it - enterprise contract, residency requirement, a workload that is disrupting everyone else. Those two compose well: a shared-schema database for the long tail, dedicated cells for the customers who are paying for one.

Noisy neighbours

Within any shared model, tenants compete for CPU, memory, I/O and connections, and nothing in Postgres arbitrates between them. Mitigate it where you can:

-- Per-role limits are the cheapest form of separation available.
ALTER ROLE app_web     SET statement_timeout = '10s';
ALTER ROLE app_reports SET statement_timeout = '5min';
ALTER ROLE app_reports SET idle_in_transaction_session_timeout = '1min';

Then rate-limit per tenant in the application, which is the only layer that knows which tenant a request belongs to. See Timeouts.