# Schema per Tenant — Multi-Tenant SaaS Architecture

Source: https://www.skillbyai.com/en/multi-tenant-saas/mt-schema-per-tenant

> Use a separate schema per tenant inside one database, switch schemas safely with pooled connections, and know where the pattern breaks down.

## 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.

```sql
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.

**Quiz:** Why is `SET LOCAL search_path` preferred over `SET search_path` behind a connection pool?

- [ ] It runs faster
- [ ] It encrypts the schema name
- [x] 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

*Answer:* It reverts at transaction end, so the next request on that connection does not inherit the previous tenant's schema. SET LOCAL lasts only until the transaction ends. A session-level SET survives on the pooled connection and can route a later request to the wrong tenant.