CS Fundamentals Trainer · Guides

Designing a multi-tenant system

Silo, bridge and pool tenancy compared with measurements on PostgreSQL: Row-Level Security, composite foreign keys, a schema and role per tenant, the pooled-connection leak, per-tenant timeouts, migrations and dumps across 1,000 tenants, tenant resolution and noisy neighbours.

36 min read

A multi-tenant system is one running application that serves many separate customers (the tenants) while keeping each customer's data, settings and experience apart. Almost every software-as-a-service product is built this way, from a project tracker to a content management system that hosts a hundred university websites. The idea is simple to state and easy to get subtly wrong: sharing the code and the machines is what makes it cheap, and separating the data is what makes it safe, and every design choice is a position between those two. This guide covers the three standard ways to separate tenants, how to enforce the separation so a forgotten WHERE clause cannot leak data, how to find out which tenant a request belongs to, how to stop one busy tenant from slowing the others, and how to operate hundreds of tenants without losing your weekend. I ran the database experiments on PostgreSQL 16 and the code is in the appendix.

1. What we are building

A tenant is a customer organisation (a company, a school, a department). It is not the same as a user: one tenant has many users, and one user can belong to several tenants (a consultant who works for three customers). Keeping those two ideas apart avoids a whole family of design errors.

Functional requirements

• Each tenant has its own data, users, roles and settings, invisible to every other tenant.
• A new tenant can be created quickly, ideally without a human touching a server.
• Tenants can have their own look, domain and configuration, from one shared codebase.
• Operators can manage, back up, restore, move and delete one tenant without affecting the rest.

Non-functional requirements

• Isolation: a bug in one query, one request or one tenant's behaviour must not expose or damage another tenant's data. This is the one rule that must never break.
• Efficiency: the cost per tenant should be small enough that small tenants are worth having.
• Fairness: one tenant's heavy usage must not make the others slow (the "noisy neighbour" problem).
• Operability: migrations, backups, upgrades and monitoring must scale with the number of tenants without manual steps for each one.

How many tenants?

The number of tenants changes the answer, so write down what you expect:

Most products start in the middle row and have a few tenants that outgrow it, which is why the design should let you move one tenant to its own resources later (section 11).

2. The three models

Diagram: Three ways to separate tenants in a database

The names (silo, bridge, pool) are the ones commonly used in cloud-vendor guidance on SaaS tenancy [1]. Mixed designs are normal: the platform's own tables (tenants, users, memberships, billing) are shared, and each tenant's content is separate.

3. The pool model: shared tables and Row-Level Security

In the pool model every tenant-owned table has a tenant_id, and every query must filter by it. The danger is the one query that forgets. Rely on discipline and sooner or later a report, a background job or a new developer's first pull request leaks data. PostgreSQL's Row-Level Security (RLS) moves the filter into the database, where the application cannot forget it [2]:

ALTER TABLE docs ENABLE ROW LEVEL SECURITY;
CREATE POLICY by_tenant ON docs
  USING      (tenant_id = current_setting('app.tenant_id')::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

-- per request, inside the transaction:
SELECT set_config('app.tenant_id', '7', true);   -- true = local to this transaction

I built a table of 500,000 rows over 100 tenants, connected as an ordinary role, and tested what RLS does and does not do:

What to take from it:

• It works as a safety net: the forgotten WHERE returned only the tenant's own rows, and an unset tenant failed closed instead of returning everything.
• The cost is small but not zero. The policy expression is evaluated for every row a query touches. With tenant_id leading an index (here (tenant_id, id)) a page of 20 rows took a few thousandths to a hundredth of a millisecond longer on the server, which the network round trip hides completely (0.096 against 0.094 ms from the client). A scan over 5,000 rows took 3% to 27% longer across my runs (roughly 0.1 to 0.3 ms). So the overhead grows with the rows scanned: negligible for lookups and pages, worth measuring for big aggregations. The runs differ from one another, so measure on your own data.
• The application must connect as an ordinary role. The owner of the table and any superuser bypass RLS (the owner unless you use FORCE). Run migrations as the owner and the application as a role that owns nothing.
• Set the tenant with a local setting (set_config(..., true) or SET LOCAL) so it disappears at the end of the transaction. A session-level setting survives on a pooled connection (section 4 shows exactly how that leaks).
• RLS filters rows but does not stop cross-tenant references. Compare two designs for a comment that points at a post:

-- naive: the comment references the post by id alone
FOREIGN KEY (post_id) REFERENCES posts (id)
-- safe: the reference includes the tenant, so it cannot cross
FOREIGN KEY (tenant_id, post_id) REFERENCES posts (tenant_id, id)

With the naive key, a tenant-1 comment on a tenant-2 post was accepted. With the composite key the same insert was refused with a foreign-key violation, and a comment on the tenant's own post worked. Likewise, uniqueness must be per tenant: UNIQUE (tenant_id, slug) let two tenants both have a page called about and still refused a duplicate inside one tenant.

The pool model's costs are different ones: one schema for everybody (no per-tenant columns without everyone getting them), a restore of one tenant means extracting rows, and a single huge table (with a hot tenant inside it) is shared by all, which is where partitioning by tenant and careful indexing come in.

4. The bridge model: a schema and a role per tenant

Here each tenant has its own schema (a namespace of tables inside one database) and its own role that can see only that schema. Queries are written without a tenant filter at all, because the tables themselves belong to one tenant. The isolation does not depend on every query being right; it depends on one small piece of code that opens the tenant's scope, plus grants that make the wrong scope impossible.

CREATE ROLE app LOGIN NOINHERIT;           -- what the application connects as: no rights of its own
CREATE ROLE r_alpha NOLOGIN;               -- one role per tenant
GRANT r_alpha TO app;                      -- app may SET ROLE to it
CREATE SCHEMA t_alpha;
GRANT USAGE ON SCHEMA t_alpha TO r_alpha;
GRANT SELECT ON ALL TABLES IN SCHEMA t_alpha TO r_alpha;   -- (plus the writes the app needs)

import re, contextlib
import psycopg
from psycopg import sql

SLUG = re.compile(r"^[a-z][a-z0-9_]{1,30}$")

@contextlib.contextmanager
def in_tenant(conn, slug, timeout_ms=5000):
    """The ONE place that opens a tenant scope. Everything the caller runs inside it can only see this tenant."""
    if not SLUG.match(slug):                                   # the slug becomes an identifier: validate it anyway
        raise ValueError("bad tenant slug")
    with conn.transaction():                                   # BEGIN ... COMMIT (or ROLLBACK on error)
        conn.execute(sql.SQL("SET LOCAL ROLE {}").format(sql.Identifier("r_" + slug)))
        conn.execute(sql.SQL("SET LOCAL search_path TO {}").format(sql.Identifier("t_" + slug)))
        conn.execute(sql.SQL("SET LOCAL statement_timeout = {}").format(sql.Literal(f"{int(timeout_ms)}ms")))
        yield conn                                             # unqualified queries now resolve inside t_<slug>

in_tenant is the only function that opens a tenant scope. Three rules make it safe:

1. Validate the slug with a strict pattern even though it came from your own tenants table, because it is spliced into SQL as an identifier (identifiers cannot be bind parameters); use the driver's identifier quoting too.
2. Use SET LOCAL so the role and search path end with the transaction.
3. All tenant queries go through it; nothing else calls SET ROLE or changes search_path.

What I tested against two real tenants:

The pooled-connection trap

Connection pools hand the same database connection to consecutive requests. A setting made with plain SET stays on the connection after your request ends and belongs to whoever gets the connection next. I simulated a pool with a single connection: request one switched to alpha with a plain SET ROLE and SET search_path; request two was a code path that forgot to choose a tenant.

This is the single most common way multi-tenant systems leak with a pool, and it is why every example above uses SET LOCAL inside an explicit transaction. (In the first experiment the plain SET of the search path alone did not leak, because the role app had no rights on the schema; the extra layer of grants is what saved it. Together, SET LOCAL and grants give you two independent safeguards.)

How it scales, and how it fails

I created tenant schemas of five tables and a few indexes each, then looked at costs:

Each tenant costs about 0.2 MB and 14 ms to create in this shape (the numbers will differ with your tables), the catalog grows with the number of tables (tens of thousands of entries at a thousand tenants, which is fine), and a schema-wide ALTER TABLE … ADD COLUMN is a metadata change that took 0.37 s for 1,000 tenants. A new connection touching one tenant ran its first query in about 3 to 5 ms regardless of the number of schemas. A full pg_dump of the 1,000 tenants (5,000 tables) took 2.6 s (9.1 MB), and dumping one tenant took 0.36 s (9.9 KB).

A limit to know about: pg_dump takes a lock on every table it dumps, and PostgreSQL keeps all those locks in a shared table of about max_locks_per_transaction × max_connections entries (default 64 × 100 = 6,400) [4]. With default settings, pg_dump of my test database succeeded at 6,200 tables and failed at 6,400 and 6,800 with out of shared memory (the hint names max_locks_per_transaction). That is about 1,250 tenants of five tables each. After raising max_locks_per_transaction to 256 and restarting, the same 6,800-table dump worked (3.5 s, 12.3 MB). So if you choose a schema per tenant, put this in your capacity plan: more tables per tenant means fewer tenants per default-sized lock table, and the fix is a setting, not a redesign.

5. The silo model: a database or a deployment per tenant

At the strong end, each tenant gets its own database, or its own whole copy of the application (its own servers and storage). You gain the cleanest isolation (separate credentials, separate files, separate resource limits), an easy answer for customers with regulatory or data-residency demands (their database can live in their country), and the simplest per-tenant backup, restore and deletion. You pay in count: every tenant is a database to migrate, monitor and connect to, and each needs its own connection pool (a pool per database, which adds up quickly).

I measured the database-per-tenant cost on a throwaway server: CREATE DATABASE took 47 ms and each empty database used 7.5 MB on disk (against about 0.2 MB for a schema in the previous section, 35 times less), and a minimal idle PostgreSQL server (16 MB of shared buffers) used about 15 MB of memory across its six processes. So memory is not the problem; operational count is. Hundreds of databases are manageable with automation; hundreds of separate servers are a fleet to run.

Use silo for the tenants who need it (the large, the regulated, the ones paying for it) and a cheaper model for the rest. Many products end up with pool or bridge by default and silo as a premium option.

6. Finding the tenant: resolving the request

Every request must be tied to one tenant before any tenant data is touched. Common signals:

Principles that avoid the worst mistakes:

• Resolve to an internal record, not the raw string. Look the host or slug up in the tenants table and use the stored row's slug from then on. The value that reaches the database layer comes from your table, never from the request.
• Authorisation is separate from identification. Knowing a request is for tenant X says nothing about whether this user may act in X. Check the user's membership in X on every request, before opening the tenant's scope, and derive the role from that membership.
• Cache the lookup, because it is on every request, and refresh it on change (the database can notify the application when tenants change; a periodic refresh is the backstop). An unknown host must answer 404, not fall through to a default tenant.
• Custom domains and TLS. If tenants bring their own domains, issue certificates on demand, and have the web server ask your application whether a given hostname is one of yours before it requests a certificate; otherwise anyone can make you request certificates for any name (see TLS and HTTPS).
• Read the platform data before entering the tenant scope. In the bridge model the tenant's role cannot read the shared tables (users, memberships), so resolve user and membership first and pass plain identifiers inside.

7. Noisy neighbours and fairness

Shared resources get shared unfairly unless you divide them. A tenant importing a million rows, a runaway report, or a burst of API calls must not take the others down. The usual controls:

• Rate limits and quotas per tenant at the API edge (rate limiter), with separate limits for expensive operations. Plans (free, paid, enterprise) are just different numbers.
• Per-tenant concurrency limits and a bounded connection pool, so one tenant cannot use every database connection.
• Statement timeouts so one query cannot run for ever. This has a trap I tested: a timeout configured with ALTER ROLE r_beta SET statement_timeout = '300ms' is applied when that role logs in, not when you switch to it with SET ROLE. A 1.5 s query ran to the end (1.50 s) because the application connects as app and then switches. Setting SET LOCAL statement_timeout = '300ms' in the same transaction cancelled a 5 s query after 0.30 s. This is why in_tenant sets the timeout itself.
• Fair queues for background work: a single shared job queue lets a tenant with 100,000 jobs starve everyone else. Use a queue per tenant (or per tenant class) and take from them round-robin (message queues).
• Tenant-aware caches: include the tenant in every cache key. A cache keyed only by URL path (/pages/about) will serve one tenant's page to another, and this bug has caused real data leaks. (Caching strategies.)
• Per-tenant metrics: label requests, errors, query time and storage by tenant, so "the system is slow" becomes "tenant 42 is doing something heavy".

8. Operating many tenants

Operations is where multi-tenant systems are won or lost, because every manual step is multiplied by the number of tenants.

Creating a tenant should be one idempotent command or API call: insert the row, create the schema and role (or database), run all migrations, seed defaults. In the bridge model it is a fast, automatic transaction (about 14 ms of database work per tenant in my test) and a failed attempt leaves nothing half-made. Create each tenant in its own transaction, and never provision many tenants in one. A transaction keeps every lock it takes until it ends, so creating tenants in a loop inside one transaction runs into the same lock table as the pg_dump limit above: with default settings my loop failed after 377 tenants with out of shared memory and the hint to increase max_locks_per_transaction (each tenant created about 17 objects: four tables, seven indexes, four sequences and the schema; 377 × 17 is close to the 6,400 entries available). With one transaction per tenant, 1,000 more were created without trouble. The same rule applies to migrations: run them per tenant, not as one giant transaction.

Migrations. A change must work on every tenant and be safe while the old code still runs:

• Write each migration so it is valid in any tenant: unqualified names and no tenant-specific data.
• Keep a migration-state table per tenant (or the framework's equivalent) so you know which tenants are on which version, and can resume after a partial failure.
• Use expand and contract: first add the new column or table (compatible with old code), deploy the code that uses it, then remove the old one in a later migration. During the rollout different tenants may be at different steps, and the code must tolerate both.
• Never edit a migration that has already run on any tenant; write a new one.
• In the pool model there is a single migration for all, and a long lock affects all tenants at once, so the risk concentrates.

Backups and restore. The question that matters is "can we restore one tenant to yesterday without touching the rest?" In the bridge model it is a schema-level dump and restore (0.36 s for a small tenant in my test, against 2.6 s for the whole database of 1,000). In the pool model it means extracting rows by tenant and merging them back, which is slow and error-prone, so plan it before you need it. Test restores regularly: a backup you have never restored is a hope.

Deleting a tenant. Define it up front, because both sides have obligations: delete data on request (privacy law) but keep backups and audit logs under policy. Bridge or silo: drop the schema or database, delete media, remove the routing entries, and note when backups containing the data expire. Pool: delete rows by tenant across all tables, in dependency order, in a job you can resume. Soft-delete first (disable the tenant) so a mistake can be undone for a grace period.

9. Users, roles and support access

• Users are platform-level; membership ties them to tenants. A memberships(user_id, tenant_id, role) table lets one person work in several tenants with a different role in each.
• Roles are per tenant (for example admin and editor). Authorisation checks the membership of this user in this tenant, on every request.
• Platform staff ("superadmins") can reach every tenant, so treat that power with care: separate accounts, strong authentication, and an audit log of every action, including which tenant was entered and why. If you offer "log in as the customer" for support, record it and show it to the customer.
• API keys belong to a tenant and carry a scope (for example read-only, or "may read drafts"); a key for one tenant must not work on another, and a missing scope is an error, not a quiet fallback.
• The audit log is platform data, written outside the tenant scope, so a tenant's role cannot alter or read it.

10. Customisation without forking

Tenants will ask for differences. Keep them as data, not as branches of code:

• Configuration: per-tenant settings (name, logo, colours, language, time zone) in a settings table, with sensible defaults.
• Feature flags per tenant for gradual rollouts and plan differences.
• Vocabularies in data: lists such as document categories or person types that tenants name differently belong in a settings table, not in CHECK constraints that need a migration per tenant.
• A flexible column for one-offs (a jsonb extra field). When two tenants need the same field, promote it to a real column.
• Different presentation per tenant can still come from one codebase: a theme per tenant, or, if sites must look completely different, a separate presentation project per tenant that talks to the shared backend through a stable, versioned API.
• Do not fork the code per tenant. Every fork multiplies testing and upgrades; configuration scales, forks do not.

11. Growing: moving a tenant, sharding, cells

Eventually one tenant is too big, or too demanding, or moves country. Design for moving a tenant from the start:

• Keep a routing table (tenant -> database/shard/region) that every request consults, instead of a single hard-wired connection string.
• To move a tenant: copy its schema or rows to the new place (a dump and restore, or logical replication for near-zero downtime), switch the routing entry, and delete the old copy after a grace period. The bridge model makes this a file copy; the pool model needs a data extraction.
• At large scale, group tenants into cells: self-contained copies of the whole stack, each serving a set of tenants. A failure or a bad deploy then affects one cell, not everybody, which limits the blast radius (sharding and consistent hashing cover how to assign them).

12. Common mistakes

• Relying on every query remembering a tenant filter with nothing underneath to catch the one that forgets.
• Plain SET on a pooled connection, so the previous request's tenant leaks to the next (measured above).
• Connecting the application as the table owner or a superuser, which bypasses RLS.
• Trusting a tenant id from the client instead of checking the user's membership.
• Cache keys without the tenant.
• Cross-tenant foreign keys because the reference does not include tenant_id.
• A shared job queue, so one tenant starves the rest.
• No plan for per-tenant restore or deletion.
• Forking code for a customer, and then having to maintain it for ever.
• Hard-wiring one connection string, so a tenant can never be moved.
• Testing only with one tenant. Isolation bugs appear only when there are two; tests should create at least two and try to read across.

13. Questions interviewers like to add

"How do you guarantee one tenant can never see another's data?"

Do not rely on application code alone. Put a mechanism in the database that fails closed: separate schemas and roles with grants (bridge), Row-Level Security (pool), or separate databases (silo). Open the tenant scope in exactly one function using transaction-local settings. Run the application as a role without table-owner or superuser rights. And test with two tenants, trying to read across.

"Which model would you choose?"

It depends on the number and size of tenants and on the isolation they demand. A defensible default for a few hundred to a few thousand mixed tenants is a shared database with a schema per tenant; for millions of tiny tenants a shared schema with RLS; for a handful of regulated or very large tenants separate databases. Say that you would make the big ones movable.

"How do you handle a customer much bigger than the rest?"

Move it to its own database or cell behind the routing table, without changing application code, and give it its own limits.

"How do you run schema migrations across 1,000 tenants?"

Automated, resumable, per-tenant migrations written to work on any tenant, tracked in a state table, with expand and contract so old and new code coexist. In my test, adding a column to 1,000 schemas took under half a second; slow or locking migrations are the real risk.

"How do you prevent one tenant from slowing the others?"

Rate limits and quotas, bounded connection pools, statement timeouts set per transaction, fair per-tenant queues for background work, tenant-aware caching, and per-tenant metrics to find the offender.

"Why not just add a tenant_id column everywhere?"

It is the cheapest model and fine for many tiny tenants, but isolation depends on every query (so add RLS and composite foreign keys), per-tenant restore and per-tenant schema changes become hard, and one big tenant shares tables and indexes with everyone.

14. Things to mention in an interview

• Tenant versus user; membership and per-tenant roles.
• Silo, bridge and pool, with the isolation, cost and operations trade-offs.
• Isolation enforced in the database and opened in one function; transaction-local settings; application role without ownership.
• The pooled-connection leak with plain SET.
• RLS fails closed but is bypassed by owners and superusers; composite foreign keys and per-tenant uniqueness.
• Resolve the tenant from the host or a token via your own table, and authorise by membership.
• Noisy neighbours: limits, quotas, timeouts, fair queues, tenant-aware caches.
• Automated tenant creation, resumable migrations with expand and contract, per-tenant backup, restore and deletion.
• A routing table so tenants can move; cells to limit blast radius.

Quick recap

• A tenant is a customer; users belong to tenants through memberships. Sharing code and machines makes it cheap; separating data makes it safe.
• Silo (database per tenant) isolates best and costs most; pool (shared tables) is cheapest and relies on filters; bridge (schema per tenant) sits between.
• RLS made a missing WHERE return only the tenant's rows for a few hundredths of a millisecond per indexed query (more when it scans many rows), but owners and superusers bypass it; composite foreign keys stop cross-tenant references.
• In the bridge model, open the tenant scope in one function with SET LOCAL ROLE, SET LOCAL search_path and a statement timeout; a plain SET on a pooled connection leaked one tenant's data to the next request.
• A schema cost about 0.2 MB and 14 ms; a database cost 7.5 MB and 47 ms; adding a column to 1,000 schemas took 0.37 s; dumping one tenant took 0.36 s against 2.6 s for all 1,000. Create and migrate tenants in separate transactions: one transaction for hundreds of tenants runs out of lock space.
• Find the tenant from your own table, authorise by membership, include the tenant in cache keys, and limit each tenant's resources.
• Keep a routing table so a big tenant can move, and write migrations that work on every tenant.

Appendix: the experiment code

Python 3 with psycopg (version 3) and a PostgreSQL 16 you can create and drop databases in. Edit the connection strings (the experiments used a local server on port 5499 with a superuser, and they delete the databases they create). Timings are from one small machine; the pattern, not the numbers, is what to compare.

mt_scale.py: creating tenants as schemas, and what it costs:

import psycopg, time
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=postgres",autocommit=True)
c.execute("DROP DATABASE IF EXISTS mt"); c.execute("CREATE DATABASE mt")
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt",autocommit=True)
DDL="""CREATE TABLE {s}.pages (id bigserial PRIMARY KEY, slug text NOT NULL UNIQUE, title text, body text, updated_at timestamptz DEFAULT now());
CREATE TABLE {s}.people (id bigserial PRIMARY KEY, name text, email text);
CREATE INDEX ON {s}.people (name);
CREATE TABLE {s}.posts (id bigserial PRIMARY KEY, title text, published_at timestamptz);
CREATE INDEX ON {s}.posts (published_at);
CREATE TABLE {s}.media (id bigserial PRIMARY KEY, key text UNIQUE, size int);"""
def size(): return c.execute("select pg_database_size('mt')").fetchone()[0]/1e6
def cat(): return c.execute("select count(*) from pg_class").fetchone()[0]
made=0; print("tenants | create time (this step) | database size | pg_class rows | ALTER one column in all (own transactions) | one query in a fresh connection")
base=size()
for target in (10,100,1000):
    t=time.perf_counter()
    while made<target:
        s=f"t_{made:04d}"; c.execute(f"CREATE SCHEMA {s}")
        for stmt in DDL.format(s=s).split(";\n"): c.execute(stmt)
        c.execute(f"INSERT INTO {s}.pages(slug,title,body) SELECT 'p'||g,'t','x' FROM generate_series(1,20) g")
        made+=1
    ct=time.perf_counter()-t
    t=time.perf_counter()
    for i in range(made): c.execute(f"ALTER TABLE t_{i:04d}.pages ADD COLUMN IF NOT EXISTS extra_{target} jsonb")
    mt=time.perf_counter()-t
    # a new connection touching one tenant (cold relcache)
    t=time.perf_counter(); c2=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt",autocommit=True)
    c2.execute("SET search_path TO t_0005"); c2.execute("SELECT count(*) FROM pages").fetchone(); q=(time.perf_counter()-t)*1000; c2.close()
    print(f"{made:7d} | {ct:6.1f} s | {size():6.0f} MB | {cat():6d} | {mt:5.2f} s | {q:5.1f} ms")

mt_rls.py: Row-Level Security, the owner and superuser bypass, and its overhead:

import psycopg, time, statistics
S="host=/tmp port=5499 user=postgres dbname=postgres"
adm=psycopg.connect(S,autocommit=True)
adm.execute("DROP DATABASE IF EXISTS mt2"); adm.execute("CREATE DATABASE mt2")
for r in ("app_user","owner_role"):
    adm.execute(f"DROP ROLE IF EXISTS {r}")
adm.execute("CREATE ROLE owner_role LOGIN"); adm.execute("CREATE ROLE app_user LOGIN")
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt2",autocommit=True)
c.execute("CREATE TABLE docs (id bigserial PRIMARY KEY, tenant_id int NOT NULL, title text)")
c.execute("CREATE INDEX ON docs (tenant_id, id)")
c.execute("ALTER TABLE docs OWNER TO owner_role")
c.execute("GRANT SELECT, INSERT ON docs TO app_user"); c.execute("GRANT USAGE ON SEQUENCE docs_id_seq TO app_user")
c.execute("INSERT INTO docs(tenant_id,title) SELECT 1+g%100, 'doc '||g FROM generate_series(1,500000) g"); c.execute("ANALYZE docs")
c.execute("ALTER TABLE docs ENABLE ROW LEVEL SECURITY")
c.execute("CREATE POLICY by_tenant ON docs USING (tenant_id = current_setting('app.tenant_id')::int) WITH CHECK (tenant_id = current_setting('app.tenant_id')::int)")
def conn(user): return psycopg.connect(f"host=/tmp port=5499 user={user} dbname=mt2",autocommit=False)
# 1. the forgotten WHERE
a=conn("app_user")
with a.transaction():
    a.execute("SELECT set_config('app.tenant_id','7',true)")
    n=a.execute("SELECT count(*) FROM docs").fetchone()[0]; t=a.execute("SELECT count(DISTINCT tenant_id) FROM docs").fetchone()[0]
print(f"1. app_user, tenant 7, query with NO WHERE: sees {n} rows from {t} tenant(s) (table has 500000 rows, 100 tenants)")
# 2. no tenant set -> error, not empty/all
a.rollback()
try:
    a.execute("SELECT count(*) FROM docs").fetchone()
except Exception as e: print("2. no tenant set:", type(e).__name__, str(e).splitlines()[0]); a.rollback()
# 3. insert for another tenant is refused by WITH CHECK
try:
    with a.transaction():
        a.execute("SELECT set_config('app.tenant_id','7',true)"); a.execute("INSERT INTO docs(tenant_id,title) VALUES (8,'sneaky')")
except Exception as e: print("3. insert for tenant 8 while tenant 7:", type(e).__name__, str(e).splitlines()[0])
# 4. owner bypasses RLS unless FORCE; superuser always bypasses
o=conn("owner_role"); n=o.execute("SELECT count(*) FROM docs").fetchone()[0]; o.rollback(); print(f"4a. table owner without FORCE: sees {n} rows (RLS bypassed)")
c.execute("ALTER TABLE docs FORCE ROW LEVEL SECURITY")
try: o.execute("SELECT count(*) FROM docs").fetchone()
except Exception as e: print("4b. table owner with FORCE and no tenant set:", type(e).__name__); o.rollback()
print("4c. superuser (postgres):", c.execute("SELECT count(*) FROM docs").fetchone()[0], "rows (always bypasses RLS; connect the app with a plain role)")
# 5. overhead: RLS vs explicit WHERE (same index), median of 200 runs
def bench(sql, setcfg=True, runs=200):
    ts=[]
    with a.transaction():
        if setcfg: a.execute("SELECT set_config('app.tenant_id','7',true)")
        for _ in range(runs):
            t=time.perf_counter(); a.execute(sql).fetchall(); ts.append((time.perf_counter()-t)*1000)
    return statistics.median(ts)
c.execute("ALTER TABLE docs NO FORCE ROW LEVEL SECURITY")
q="SELECT id, title FROM docs {w} ORDER BY id DESC LIMIT 20"
x=bench(q.format(w="WHERE tenant_id = 7"),True)
y=bench(q.format(w=""),True)
print(f"5. page of 20 for one tenant, median of 200: explicit WHERE (RLS also on) {x:.3f} ms; RLS only {y:.3f} ms")
c.execute("ALTER TABLE docs DISABLE ROW LEVEL SECURITY")
z=bench(q.format(w="WHERE tenant_id = 7"),False)
print(f"   explicit WHERE with RLS off {z:.3f} ms")
c.execute("ALTER TABLE docs ENABLE ROW LEVEL SECURITY")

# 6. server-side execution time (EXPLAIN ANALYZE): app_user is subject to RLS, owner_role bypasses it
import re
c.execute("ALTER TABLE docs NO FORCE ROW LEVEL SECURITY")
def exec_ms(user, q, runs=300):
    k=conn(user); ts=[]
    with k.transaction():
        k.execute("SELECT set_config('app.tenant_id','7',true)")
        for _ in range(runs):
            r=k.execute("EXPLAIN (ANALYZE, TIMING OFF, SUMMARY ON) "+q).fetchall()
            ts.append(float(re.search(r"Execution Time: ([\d.]+) ms","\n".join(x[0] for x in r)).group(1)))
    k.close(); return statistics.median(ts)
for label,q in (("page of 20","SELECT id, title FROM docs WHERE tenant_id = 7 ORDER BY id DESC LIMIT 20"),("count of the tenant's 5,000 rows","SELECT count(*) FROM docs WHERE tenant_id = 7")):
    print(f"6. server-side, {label}: with RLS {exec_ms('app_user',q):.3f} ms | without RLS (owner bypasses) {exec_ms('owner_role',q):.3f} ms")

mt_pool.py: roles and grants, the pooled-connection trap, and per-role timeouts:

import psycopg
S="host=/tmp port=5499 user=postgres dbname=postgres"
adm=psycopg.connect(S,autocommit=True)
adm.execute("DROP DATABASE IF EXISTS mt3"); adm.execute("CREATE DATABASE mt3")
for r in ("app","r_alpha","r_beta"): adm.execute(f"DROP ROLE IF EXISTS {r}")
adm.execute("CREATE ROLE app LOGIN NOINHERIT"); adm.execute("CREATE ROLE r_alpha NOLOGIN"); adm.execute("CREATE ROLE r_beta NOLOGIN")
adm.execute("GRANT r_alpha, r_beta TO app")          # app may SET ROLE to either, but has no rights of its own
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt3",autocommit=True)
for t in ("alpha","beta"):
    c.execute(f"CREATE SCHEMA t_{t}"); c.execute(f"CREATE TABLE t_{t}.pages (id serial primary key, title text)")
    c.execute(f"INSERT INTO t_{t}.pages(title) VALUES ('{t} secret page')")
    c.execute(f"GRANT USAGE ON SCHEMA t_{t} TO r_{t}"); c.execute(f"GRANT SELECT ON ALL TABLES IN SCHEMA t_{t} TO r_{t}")
def conn(): return psycopg.connect("host=/tmp port=5499 user=app dbname=mt3",autocommit=True)
# 1. the app role alone sees nothing
a=conn()
try: a.execute("SELECT * FROM t_alpha.pages")
except Exception as e: print("1. app role, no SET ROLE:", type(e).__name__, str(e).splitlines()[0])
# 2. inside a tenant transaction: its own data yes, the other tenant's no
a.execute("BEGIN"); a.execute("SET LOCAL ROLE r_alpha"); a.execute("SET LOCAL search_path TO t_alpha")
print("2a. as alpha, unqualified query:", a.execute("SELECT title FROM pages").fetchall())
try: a.execute("SELECT * FROM t_beta.pages")
except Exception as e: print("2b. as alpha reading t_beta:", type(e).__name__, str(e).splitlines()[0])
a.execute("ROLLBACK")
print("2c. after the transaction the connection is back to:", a.execute("SELECT current_user").fetchone()[0], "and search_path", a.execute("SHOW search_path").fetchone()[0])
# 3. the pooled-connection trap: a pool of ONE connection shared by consecutive requests
pool=[conn()]
def request(tenant, use_local):
    k=pool[0]; k.execute("BEGIN")
    kw="LOCAL " if use_local else ""
    k.execute(f"SET {kw}ROLE r_{tenant}"); k.execute(f"SET {kw}search_path TO t_{tenant}")
    k.execute("COMMIT")
def handler_forgetting_tenant():                       # a code path that never set a tenant
    k=pool[0]; k.execute("BEGIN")
    try: r=k.execute("SELECT title FROM pages").fetchall()
    except Exception as e: r=type(e).__name__
    k.execute("ROLLBACK"); return r
for use_local in (False, True):
    request("alpha", use_local)
    print(f"3{'b' if use_local else 'a'}. {'SET LOCAL' if use_local else 'plain SET'} for alpha's request, then a request that never set a tenant ->", handler_forgetting_tenant())
    pool[0].execute("RESET ROLE"); pool[0].execute("RESET search_path")
# 4. per-role guard rails: ALTER ROLE ... SET applies at LOGIN, not at SET ROLE
c.execute("ALTER ROLE r_beta SET statement_timeout = '300ms'")
import time
b=conn(); b.execute("BEGIN"); b.execute("SET LOCAL ROLE r_beta")
t=time.perf_counter(); b.execute("SELECT pg_sleep(1.5)"); print("4a. ALTER ROLE ... SET statement_timeout, reached with SET ROLE: a 1.5 s query ran to the end (%.2f s)"%(time.perf_counter()-t)); b.execute("ROLLBACK")
b.execute("BEGIN"); b.execute("SET LOCAL ROLE r_beta"); b.execute("SET LOCAL statement_timeout = '300ms'")
t=time.perf_counter()
try: b.execute("SELECT pg_sleep(5)")
except Exception as e: print("4b. SET LOCAL statement_timeout in the same transaction: cancelled after %.2f s (%s)"%(time.perf_counter()-t, type(e).__name__))

mt_helper.py: the one function that opens a tenant scope (run mt_pool.py first, it creates the roles and schemas):

import re, contextlib
import psycopg
from psycopg import sql

SLUG = re.compile(r"^[a-z][a-z0-9_]{1,30}$")

@contextlib.contextmanager
def in_tenant(conn, slug, timeout_ms=5000):
    """The ONE place that opens a tenant scope. Everything the caller runs inside it can only see this tenant."""
    if not SLUG.match(slug):                                   # the slug becomes an identifier: validate it anyway
        raise ValueError("bad tenant slug")
    with conn.transaction():                                   # BEGIN ... COMMIT (or ROLLBACK on error)
        conn.execute(sql.SQL("SET LOCAL ROLE {}").format(sql.Identifier("r_" + slug)))
        conn.execute(sql.SQL("SET LOCAL search_path TO {}").format(sql.Identifier("t_" + slug)))
        conn.execute(sql.SQL("SET LOCAL statement_timeout = {}").format(sql.Literal(f"{int(timeout_ms)}ms")))
        yield conn                                             # unqualified queries now resolve inside t_<slug>

if __name__ == "__main__":
    conn = psycopg.connect("host=/tmp port=5499 user=app dbname=mt3", autocommit=True)
    with in_tenant(conn, "alpha") as c:
        assert c.execute("SELECT title FROM pages").fetchall() == [("alpha secret page",)]
        try: c.execute("SELECT * FROM t_beta.pages"); raise SystemExit("LEAK")
        except psycopg.errors.InsufficientPrivilege: pass     # the other tenant's schema is closed to this role
    for bad in ("alpha; DROP SCHEMA t_beta", "Alpha", "a", 'alpha"', "../x"):
        try:
            with in_tenant(conn, bad): raise SystemExit("accepted " + bad)
        except ValueError: pass
    with in_tenant(conn, "beta") as c:
        assert c.execute("SELECT title FROM pages").fetchall() == [("beta secret page",)]
    assert conn.execute("SELECT current_user").fetchone()[0] == "app"          # nothing leaked to the next user of the connection
    assert conn.execute("SHOW search_path").fetchone()[0] == '"$user", public'
    with in_tenant(conn, "alpha", timeout_ms=200) as c:
        try: c.execute("SELECT pg_sleep(2)"); raise SystemExit("no timeout")
        except psycopg.errors.QueryCanceled: pass
    print("all tenant-scope checks passed")

mt_dump.py: pg_dump of the whole database and of one tenant, and the lock-table limit (it prints the failure at 6,800 tables with default settings):

import psycopg, subprocess, time, os
env=dict(os.environ, PGHOST="/tmp", PGPORT="5499", PGUSER="postgres")
adm=psycopg.connect("host=/tmp port=5499 user=postgres dbname=postgres",autocommit=True)
adm.execute("DROP DATABASE IF EXISTS mt5"); adm.execute("CREATE DATABASE mt5")
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt5",autocommit=True)
def provision(i):
    s=f"t_{i:04d}"; c.execute("BEGIN"); c.execute(f"CREATE SCHEMA {s}")      # one transaction per tenant
    for t in ("pages","people","posts","media","extra"): c.execute(f"CREATE TABLE {s}.{t} (id bigserial PRIMARY KEY, v text)")
    c.execute(f"INSERT INTO {s}.pages(v) SELECT 'row '||g FROM generate_series(1,20) g"); c.execute("COMMIT")
def dump(*args):
    t=time.perf_counter(); r=subprocess.run(["pg_dump","-Fc","-f","/tmp/g5/dump.out",*args,"mt5"],env=env,capture_output=True,text=True)
    return time.perf_counter()-t, os.path.getsize("/tmp/g5/dump.out") if r.returncode==0 else None, r.stderr.strip().splitlines()[:1]
n=0
for target in (1000, 1360):                                    # 1,000 tenants = 5,000 tables; 1,360 tenants = 6,800 tables
    while n<target: provision(n); n+=1
    tables=c.execute("select count(*) from pg_tables where schemaname like 't\\_%'").fetchone()[0]
    t,size,err=dump(); print(f"{n} tenants ({tables} tables): whole database {t:.1f} s, {size/1e6 if size else 0:.1f} MB", err or "")
    if target==1000:
        t,size,err=dump("-n","t_0005"); print(f"  one tenant (-n t_0005): {t:.2f} s, {size/1e3:.1f} KB")

mt_txn.py: provisioning many tenants in one transaction against one transaction per tenant:

import psycopg
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=postgres",autocommit=True)
c.execute("DROP DATABASE IF EXISTS mt4"); c.execute("CREATE DATABASE mt4")
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt4",autocommit=True)
print("max_locks_per_transaction =",c.execute("show max_locks_per_transaction").fetchone()[0],", max_connections =",c.execute("show max_connections").fetchone()[0])
DDL=["CREATE TABLE {s}.pages (id bigserial PRIMARY KEY, slug text UNIQUE, title text)",
     "CREATE TABLE {s}.people (id bigserial PRIMARY KEY, name text)","CREATE INDEX ON {s}.people (name)",
     "CREATE TABLE {s}.posts (id bigserial PRIMARY KEY, title text)","CREATE TABLE {s}.media (id bigserial PRIMARY KEY, key text UNIQUE)"]
def provision(i):
    s=f"t_{i:04d}"; c.execute(f"CREATE SCHEMA {s}")
    for d in DDL: c.execute(d.format(s=s))
# all tenants in ONE transaction
made=0
c.execute("BEGIN")
try:
    for i in range(5000): provision(i); made+=1
    c.execute("COMMIT"); print("one transaction: created all 5000")
except Exception as e:
    print(f"one transaction: failed after {made} tenants with {type(e).__name__}: {str(e).splitlines()[0]}")
    print("  hint:", [l for l in str(e).splitlines() if 'HINT' in l or 'max_locks' in l][:1])
    c.execute("ROLLBACK")
# one transaction per tenant
c.execute("DROP DATABASE IF EXISTS mt4") if False else None
for i in range(1000):
    c.execute("BEGIN"); provision(10000+i); c.execute("COMMIT")
print("one transaction per tenant: created 1000 more without trouble")

mt_fk.py: cross-tenant references and per-tenant uniqueness:

import psycopg
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt2",autocommit=True)
c.execute("DROP TABLE IF EXISTS comments, posts CASCADE")
c.execute("CREATE TABLE posts (tenant_id int NOT NULL, id bigint NOT NULL, title text, PRIMARY KEY (tenant_id, id))")
# naive: comment references posts(id) only; safe: the reference carries tenant_id too
c.execute("CREATE TABLE comments_naive (tenant_id int NOT NULL, id bigserial PRIMARY KEY, post_id bigint NOT NULL, body text)")
c.execute("CREATE UNIQUE INDEX posts_id_only ON posts (id)")
c.execute("ALTER TABLE comments_naive ADD FOREIGN KEY (post_id) REFERENCES posts (id)")
c.execute("CREATE TABLE comments_safe (tenant_id int NOT NULL, id bigserial PRIMARY KEY, post_id bigint NOT NULL, body text, FOREIGN KEY (tenant_id, post_id) REFERENCES posts (tenant_id, id))")
c.execute("INSERT INTO posts VALUES (1, 100, 'tenant 1 post'), (2, 200, 'tenant 2 post')")
c.execute("INSERT INTO comments_naive (tenant_id, post_id, body) VALUES (1, 200, 'tenant 1 comments on a tenant 2 post')")
print("naive FK (post_id only): the cross-tenant comment was ACCEPTED")
try: c.execute("INSERT INTO comments_safe (tenant_id, post_id, body) VALUES (1, 200, 'same attempt')")
except Exception as e: print("composite FK (tenant_id, post_id): refused with", type(e).__name__)
c.execute("INSERT INTO comments_safe (tenant_id, post_id, body) VALUES (1, 100, 'own post')"); print("composite FK: a comment on the tenant's own post is accepted")
# uniqueness per tenant
c.execute("CREATE TABLE pages (tenant_id int NOT NULL, slug text NOT NULL, UNIQUE (tenant_id, slug))")
c.execute("INSERT INTO pages VALUES (1,'about'),(2,'about')"); print("UNIQUE (tenant_id, slug): two tenants can both have 'about'")
try: c.execute("INSERT INTO pages VALUES (1,'about')")
except Exception as e: print("the same tenant twice:", type(e).__name__)

Sources

1. Amazon Web Services, SaaS Tenant Isolation Strategies. The silo, pool and bridge models.
2. PostgreSQL documentation, Row Security Policies. USING, WITH CHECK, FORCE ROW LEVEL SECURITY, and the owner and superuser bypass.
3. PostgreSQL documentation, Schemas, SET (SET LOCAL) and ALTER ROLE (role settings take effect at session start).
4. PostgreSQL documentation, max_locks_per_transaction (the size of the shared lock table, default 64) and pg_dump.
5. Microsoft, Multitenant SaaS database tenancy patterns. Database per tenant, sharded multitenant and hybrid models.
6. Amazon Web Services, Reducing the scope of impact with cell-based architecture. Cells and blast radius.

Practise this topic: System Design SQL & Databases

More guides