Schema per Tenant

A namespace per tenant

In databases that support schemas, such as PostgreSQL, a schema is a namespace of tables inside one database. Schema-per-tenant creates the same set of tables in tenant_acme, tenant_zen and so on. The application chooses a tenant by setting the schema search path or by qualifying table names. Compared with database-per-tenant, you share one server, one connection pool and one backup process, while names still keep tenants apart; a per-tenant export is pg_dump --schema=tenant_acme and removing a tenant is a single DROP SCHEMA tenant_acme CASCADE. The weaknesses: migrations still run once per schema, thousands of schemas means a very large system catalogue (tables, indexes and their metadata multiply), which can slow tooling, backups and planning; and isolation is only as strong as your search-path handling. One forgotten reset on a pooled connection can point the next request at the wrong tenant's schema.

Creating and selecting a tenant schema

PostgreSQL. SET LOCAL limits the change to the current transaction, which matters when connections are pooled.

CREATE SCHEMA tenant_zen;

CREATE TABLE tenant_zen.orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total_paise bigint NOT NULL
);

-- Per request, inside a transaction:
BEGIN;
SET LOCAL search_path TO tenant_zen;
SELECT count(*) FROM orders;   -- resolves to tenant_zen.orders
COMMIT;

Session-level SET leaks through pools

A plain SET search_path lasts for the whole database session. With a connection pool, that session is handed to the next request, possibly for another tenant. Use SET LOCAL inside a transaction, or reset the path whenever a connection is returned to the pool.

Separate cupboards in one room

Schema-per-tenant is one big storeroom with a labelled cupboard per family. It is cheaper than renting each family a separate storeroom, but if a helper forgets which cupboard they were last told to use, they will put things in the wrong one.

Quick check: Why is `SET LOCAL search_path` preferred over `SET search_path` behind a connection pool?

• It runs faster
• It encrypts the schema name
• It reverts at transaction end, so the next request on that connection does not inherit the previous tenant's schema
• It is the only syntax PostgreSQL accepts