What are the multi-tenant database models, and what does each one isolate?
Multi-tenant data models sit on a spectrum, and the useful question is what each boundary actually separates.
- Shared schema: every tenant's rows live in the same tables, distinguished by a
tenant_idcolumn. Isolation exists only in the query predicates and policies you write. One missed filter exposes another tenant's data. - Schema-per-tenant: one database holds a separate schema (namespace) per tenant, with identical table definitions in each. Visibility is separated by name resolution and grants. CPU, memory, I/O, connections and the write-ahead log are still shared.
- Database-per-tenant: each tenant gets its own database, or its own cluster. The engine enforces the boundary, and with a dedicated cluster the resources are separate too.
| Isolated property | Shared schema | Schema-per-tenant | Database-per-tenant |
|---|---|---|---|
| Data visibility | Predicates and policies | Schema grants | Database or cluster boundary |
| Performance | None | None | Partial if co-hosted, full if a dedicated cluster |
| Failure blast radius | All tenants | All tenants in the database | One tenant, or one host's tenants |
| Backup and restore unit | Whole database | Whole database, with per-schema logical dumps possible | Per tenant |
| Encryption scope | Application-level, per tenant | Usually database-wide | Per database or cluster |
Schema-per-tenant looks like a middle ground, but it has drawbacks from both ends. Thousands of schemas multiply the tables and indexes in the system catalog, which slows planning, metadata queries and tooling. Every migration fans out across every schema, and a failure partway through leaves schemas on different versions. You take on that operational cost and still get no resource isolation in return.
Who should use each model?
The right model depends on who your tenants are and who will operate the system.
- Shared schema with row level security fits self-serve SaaS products with many small tenants, a single product team without dedicated database operations staff, and customers whose isolation requirements are satisfied by logical separation and contractual commitments. Engineers who write most of the queries benefit most, because the database enforces the tenant filter they might forget.
- Schema-per-tenant fits a narrow case: a modest number of tenants (tens to low hundreds) that need per-tenant logical dumps or per-tenant schema customization, where the team accepts migration fan-out and does not need resource isolation.
- Database-per-tenant fits products with few, large or regulated tenants: enterprise customers who require data residency, customer-managed keys, per-tenant restore, or audited physical separation. It suits teams that already automate provisioning, migrations, backups and monitoring, because every one of those tasks is multiplied by tenant count.
- A hybrid fits products with both profiles: a long tail of small tenants plus a handful of large or regulated ones. Platform teams that own a tenant catalog and routing layer are the natural owners.
How does multi-tenancy work at request time?
Every model follows the same four steps on each request. They differ in where the isolation is enforced.
- Identify the tenant. The service extracts the tenant ID from a verified credential, such as a signed token's claims, never from an unverified client header.
- Resolve the data location. In a shared schema, every tenant maps to the same database. In schema-per-tenant, the tenant maps to a schema name, typically applied through
search_pathor schema-qualified table names. In database-per-tenant, the tenant maps to a connection target from a catalog. - Establish tenant context. In a shared schema, the application sets a transaction-scoped setting such as
app.tenant_id, and row level security policies filter every query against it. In the other two models, the connection or schema itself is the context. - Run the query and release the connection. Context must not outlive the transaction, or a pooled connection carries one tenant's identity into another tenant's request.
The sections below cover how each step is implemented and where it fails: RLS for step 3 in a shared schema, pooling for step 4, operational costs of database-per-tenant, and routing for step 2 in a hybrid.
How does row level security enforce tenant isolation in a shared schema?
In a shared schema, the usual isolation mechanism is a WHERE tenant_id = ? clause in every query. It works until one query omits it, such as a new report endpoint, an ORM eager load, or an ad hoc admin script. Nothing errors. The query returns rows from every tenant, and nobody notices until a customer does.
PostgreSQL row level security moves that filter into the database. You enable RLS on each tenant-scoped table and attach a policy whose predicate compares tenant_id to current_setting('app.tenant_id', true). At the start of each transaction, the application calls set_config('app.tenant_id', $1, true). The final true scopes the value to the transaction. A query with no filter now sees only the current tenant's rows.
Some roles skip policies:
- Table owners bypass RLS unless the table has
FORCE ROW LEVEL SECURITY. The application should connect as a separate, non-owning role. - Superusers and roles with
BYPASSRLSskip policies even when RLS is forced.
Treat RLS as defense in depth, not a guarantee. A table created without a policy is unprotected. A migration or backfill run as a bypassing role touches every tenant's data. Missing tenant context makes current_setting return NULL, so the predicate matches nothing and data silently disappears instead of leaking.
Policy predicates also become part of every query plan. Run EXPLAIN on your hot queries to confirm that indexes leading with tenant_id are still used.
Add a CI check that queries pg_class for every table carrying a tenant_id column. The check should fail the build if relrowsecurity or relforcerowsecurity is false, or if the table has no entry in pg_policies.
-- Schema owned by a migration role; the app connects as app_user, which does not own the table.
CREATE TABLE invoices (
id bigserial PRIMARY KEY,
tenant_id uuid NOT NULL,
amount numeric(12,2) NOT NULL
);
CREATE INDEX invoices_tenant_id_idx ON invoices (tenant_id, id);
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY; -- apply policies to the owner too
-- nullif guards against '' (the value left behind after a transaction-local setting ends).
-- No tenant context means NULL, which matches no rows.
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);
CREATE ROLE app_user LOGIN PASSWORD 'change-me-placeholder'; -- NOSUPERUSER, NOBYPASSRLS by default
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;
GRANT USAGE ON SEQUENCE invoices_id_seq TO app_user;
-- Per request, as app_user: the third argument true scopes the value to this transaction.
BEGIN;
SELECT set_config('app.tenant_id', '7f3c2a10-5b6e-4d2a-9c1f-0e8b4a6d2c11', true);
SELECT id, amount FROM invoices; -- no WHERE clause, but only this tenant's rows return
EXPLAIN SELECT id, amount FROM invoices WHERE id > 1000; -- confirm the tenant_id index is used
COMMIT;
-- CI check: any row returned means a tenant-scoped table is unprotected; fail the build.
SELECT c.oid::regclass AS table_name, c.relrowsecurity, c.relforcerowsecurity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped
WHERE c.relkind IN ('r', 'p')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity
OR NOT EXISTS (SELECT 1 FROM pg_policies p
WHERE p.schemaname = n.nspname AND p.tablename = c.relname));
A forced RLS policy keyed on a transaction-scoped app.tenant_id setting, a non-owning application role, and a catalog query that CI can run to flag any tenant_id table missing RLS, FORCE, or a policy.
How do you keep tenant context safe with connection pooling?
A session-level SET app.tenant_id = '...' stays attached to the server connection, not to the client that issued it. A pooler in transaction mode returns that connection to the pool as soon as the transaction ends and hands it to the next client. That client's queries then run with the previous tenant's context, and row level security filters correctly for the wrong tenant. Nothing errors, so the leak goes unnoticed.
The fix is to tie tenant context to the transaction. Open an explicit transaction, then call SET LOCAL app.tenant_id or set_config('app.tenant_id', $1, true) as its first statement. The third argument, true, makes the setting transaction-local, so PostgreSQL discards it at COMMIT or ROLLBACK before the pooler can reuse the connection. set_config is easier to use from drivers because the tenant ID can be passed as a bound parameter. Outside a transaction block, SET LOCAL only emits a warning and has no lasting effect, so a missing BEGIN silently drops the context.
Policies should fail closed. After a transaction-local value is discarded, current_setting('app.tenant_id', true) can return an empty string rather than NULL. Wrap it in NULLIF(..., '') so a missing context matches no rows.
Write a test that runs as the application role, not as a superuser or the table owner, since either can bypass policies. Pin the test to a single pooled connection. Set tenant A in one transaction and commit. Then query on the same connection with no context and assert that zero rows come back.
import os
import psycopg
import pytest
DSN = os.environ["APP_ROLE_DSN"] # application role, not a superuser or the table owner
TENANT_A = "11111111-1111-1111-1111-111111111111"
@pytest.fixture
def conn():
# One connection for the whole test simulates a pooler reusing a server connection.
with psycopg.connect(DSN, autocommit=True) as c:
yield c
def run_as_tenant(conn, tenant_id, sql):
# conn.transaction() wraps the block in BEGIN ... COMMIT.
# is_local=true discards the setting when the transaction ends.
with conn.transaction():
conn.execute("SELECT set_config('app.tenant_id', %s, true)", (tenant_id,))
return conn.execute(sql).fetchall()
def test_app_role_is_subject_to_rls(conn):
# Superusers, BYPASSRLS roles and table owners skip policies, which would hide leaks.
su, bypass = conn.execute(
"SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = current_user").fetchone()
owner = conn.execute("SELECT tableowner FROM pg_tables WHERE tablename = 'invoices'").fetchone()[0]
assert not su and not bypass and owner != conn.execute("SELECT current_user").fetchone()[0]
def test_tenant_context_does_not_outlive_transaction(conn):
rows = run_as_tenant(conn, TENANT_A, "SELECT id FROM invoices")
assert rows, "no fixture rows for tenant A, so the leak check would pass vacuously"
# Same server connection, next caller, no tenant context set.
leaked = conn.execute("SELECT count(*) FROM invoices").fetchone()[0]
assert leaked == 0, f"{leaked} rows visible without tenant context"
A pytest suite using psycopg 3 that confirms the application role is subject to row level security, then sets tenant context with set_config(..., true) inside an explicit transaction and fails if any rows are visible on the same connection after commit.
What does database-per-tenant cost to operate as tenant count grows?
Database-per-tenant moves complexity from query code into operations, and that cost grows with every tenant you add.
Connections multiply. Each service needs its own pool per tenant database, so connection count is the product of tenants, services and pool size.
Illustrative estimate (assumed numbers, not a benchmark): potential connections = tenants × services × connections per pool. With 2,000 tenants, 6 services and 5 connections each: 2,000 × 6 × 5 = 60,000. A shared schema with one 20-connection pool per service needs 6 × 20 = 120. Plug in your own numbers. The gap comes from multiplying by tenant count.
Small and inactive tenants make it worse. A tenant issuing one query an hour still holds idle connections, needs a backup job, emits metrics and must be migrated. Fixed per-tenant overhead dominates when most tenants are small, and you pay it whether or not they generate traffic. Lazily opening pools and evicting idle ones reduces connection count, but it adds cold-start latency.
Migrations become a distributed rollout. A schema change runs against thousands of databases, and some will fail from lock timeouts, unexpected data or unreachable hosts. A practical orchestrator:
- records each tenant's applied schema version in a central table
- applies migrations in batches with bounded concurrency
- marks failures and continues instead of aborting the whole run
- retries idempotently from the recorded version
Application code must tolerate both versions during the rollout.
Drift and observability. Manual hotfixes on one tenant's database produce silent schema drift, so compare each schema against the expected version on a schedule. Backup verification, alerting and dashboards also scale per tenant. You need aggregated views, because nobody can watch 2,000 dashboards.
from concurrent.futures import ThreadPoolExecutor, as_completed
TARGET_VERSION = 42 # placeholder: latest migration number
MAX_CONCURRENCY = 20 # bound load on shared database hosts
def migrate_tenant(tenant_id, registry, migrations, connect):
# Resume from the centrally recorded version, so retries skip finished steps.
current = registry.get_version(tenant_id)
with connect(tenant_id) as conn:
for version in range(current + 1, TARGET_VERSION + 1):
with conn.transaction(): # each step commits or rolls back on its own
migrations[version](conn)
# Record progress after every step. If we crash before this line,
# the idempotent migration is safe to re-run on retry.
registry.set_version(tenant_id, version)
def run(tenant_ids, registry, migrations, connect):
failed = {}
with ThreadPoolExecutor(max_workers=MAX_CONCURRENCY) as pool:
futures = {
pool.submit(migrate_tenant, t, registry, migrations, connect): t
for t in tenant_ids
}
for fut in as_completed(futures):
tenant = futures[fut]
try:
fut.result()
except Exception as exc:
# Lock timeout, bad data, unreachable host: mark it and keep going.
registry.mark_failed(tenant, repr(exc))
failed[tenant] = exc
# Re-run run(list(failed), ...) later; each tenant resumes from its recorded version.
return failed
A minimal migration orchestrator that reads and records each tenant's schema version in a central registry, applies migrations with bounded concurrency, and marks failed tenants for an idempotent retry instead of aborting the whole rollout.
When should you avoid a shared schema? Noisy neighbors and blast radius
A shared schema means every tenant shares the same tables, indexes, buffer cache and maintenance processes, so one large tenant can degrade service for all of them. A bulk import from that tenant generates dead rows that vacuum must process across the whole table. Its long-running report holds locks and snapshots that delay cleanup for everyone. Its data inflates index size until the hot pages for small tenants no longer fit in memory.
Failures spread the same way. A migration that locks a table, or a bad backfill that rewrites rows incorrectly, affects every tenant at once. Recovering from corruption usually means restoring the whole database, which rolls back tenants who were never affected.
| Mitigation | What it fixes | What it does not fix |
|---|---|---|
Composite indexes with tenant_id leading |
Keeps each tenant's lookups to its own index range | Total index size, vacuum load, shared cache pressure |
| Per-tenant rate limits in the service | Caps request volume from one tenant | Expensive individual queries, background jobs |
| Statement timeouts | Kills runaway queries before they hold locks for long | Many cheap queries that add up to high load |
Partitioning by tenant_id |
Allows per-partition vacuum, reindex and detach for very large tables | Migrations still run across all partitions; the cluster is still shared |
None of these mitigations creates physical separation. If a contract or regulator requires that a tenant's data live in its own database or on dedicated hardware, a shared schema is ruled out. Well-written RLS policies do not change that, because the requirement is about where the data is stored, not about who can query it.
How does tenant routing enable a hybrid model across microservices?
A tenant catalog lets one codebase support both models. It is a small control-plane service, or a table it owns, that maps each tenant ID to a database location: cluster, database name, and whether that database is pooled or dedicated. On each request, a service reads the tenant ID from a verified token and asks the catalog where that tenant's data lives. It caches the answer briefly. The tenant ID must not come from a client-supplied header. If it did, any caller could pick another tenant's database by editing one value, and routing would become the security hole.
This supports a tiered model. Most tenants sit in pooled shared-schema databases, where per-tenant cost is lowest. Large, high-traffic, or contractually isolated tenants get dedicated databases that use the same schema and the same code path. Because the application only sees "a connection for this tenant," you avoid maintaining a separate enterprise branch.
Moving one tenant out of a pool follows a fixed sequence:
- Copy the tenant's rows into the new database.
- Keep both copies in sync. You can dual-write, or you can freeze the tenant's writes for a short, announced window.
- Verify the copy by comparing row counts and checksums per table.
- Flip the catalog entry and invalidate cached routes.
- Delete the tenant's rows from the pool once a rollback is no longer needed.
Move tooling that runs twice a year will break silently as the schema changes. Run test migrations on a schedule against synthetic tenants so you find the failures before a customer migration depends on the tool.
+---------------------------+
| Client |
+---------------------------+
| request + signed token
v
+---------------------------+
| Auth: verify token, |
| tenant_id from claims |
+---------------------------+
v
+---------------------------+ +---------------------------+
| Service (any) |--------->| Tenant catalog |
| one code path |<---------| tenant -> cluster, db, |
| short route cache | location | tier: pooled or dedicated |
+---------------------------+ +---------------------------+
| | ^
| pooled | dedicated | 4. flip entry,
v v | drop caches
+-------------+ +-------------+ +----------------+
| Pool DB 1 | | Dedicated | | Move tool |
| t1 t2 t3 t9 | | DB: t9 |<------------| runs on a |
+-------------+ +-------------+ 1. copy | schedule with |
^ 2. sync | synthetic |
| 3. verify | tenants |
| counts + | |
| checksums +--------+-------+
| |
+--- 5. delete t9 rows from pool once safe ---+
Services route each request using the tenant ID from a verified token and a catalog lookup, and moving tenant t9 from a pooled database to a dedicated one follows copy, sync, verify, catalog flip, then cleanup.
How do per-tenant restore, export, deletion and encryption differ between models?
Lifecycle operations are where the two models diverge most sharply. With database-per-tenant, the database is the unit of every operation. You restore it to a point in time, dump it for export, drop it for a deletion request, and encrypt it with a key no other tenant shares. Each operation is simple because the storage boundary and the tenant boundary are the same.
A shared schema gets none of these for free. Backups capture every tenant at once, so recovering one tenant after a bad write means restoring the full backup to a separate instance and copying that tenant's rows back. The copy has to follow foreign-key order and must not overwrite newer data. Export and deletion depend on a maintained inventory of every table that carries a tenant key, and deletion also has to reach caches, search indexes and backups. Storage-level encryption uses one key for everyone, so per-tenant keys mean encrypting in the application. That work pays off for deletion: destroying a tenant's key makes their data unreadable even in old backups.
| Operation | Database per tenant | Shared schema |
|---|---|---|
| Single-tenant restore | Native point-in-time restore | Restore elsewhere, copy rows back |
| Export | Database dump | Custom per-table extraction |
| Deletion | Drop database | Scripted deletes plus backup handling |
| Per-tenant keys | Key per database | Application-level encryption |
| Cross-tenant analytics | ETL or replication pipeline | Ordinary queries |
Cross-tenant concerns run the other way. Global uniqueness, such as login emails, and shared reference data, such as plans and currencies, need an explicit strategy in either model. That usually means a global registry and a decision about whether reference data is centralized or replicated.
What are the alternatives to these models?
The three database models are not the only ways to separate tenants. Other options trade isolation, cost and operational effort differently.
- Application-only filtering without RLS. Every query carries
WHERE tenant_id = ?, enforced by convention, code review, or a data-access layer that injects the filter. It has no database-side safety net, so a single bypass of the data-access layer leaks data. It is simpler to start with, but RLS is cheap to add on top. - Per-tenant views. Each tenant gets views filtered to its rows, and grants restrict access to those views. This gives database-enforced visibility without RLS, but view count grows with tenants and every table change must be reflected in every view set.
- Sharding a shared schema across several databases. Tenants are distributed across multiple shared-schema databases by tenant ID, using a routing layer like the catalog described above. This limits blast radius to one shard and spreads load, while keeping per-tenant overhead low. It is the hybrid model without dedicated tiers.
- Partitioning by tenant within one database. Declarative partitioning on
tenant_idgives large tenants their own partitions for maintenance and detach, while staying in a single shared database. It improves maintenance, not isolation. - Full stack per tenant (single-tenant deployments). Each customer gets a separate deployment of the application and its database. This gives the strongest isolation, including application-level faults, but multiplies deployment, upgrade and monitoring work by tenant count, well beyond database-per-tenant.
How can the right model save engineering time?
Choosing deliberately saves time in three places: day-to-day feature work, incident and support work, and future migrations.
- Less review burden on every query. With forced RLS and a CI check for unprotected tables, reviewers no longer have to verify the tenant filter on every new query, report or script. The class of bug becomes a build failure instead of a production incident.
- Fewer pooling incidents. Transaction-scoped context plus the same-connection test removes a leak that is otherwise hard to reproduce, because it only shows up when a pooler reuses a connection across tenants.
- Faster lifecycle requests where they matter. Putting regulated or large tenants on dedicated databases turns single-tenant restore, export and deletion into routine database operations, instead of custom extraction scripts written under deadline.
- Avoided rewrites. A
tenant_idon every tenant-owned table and a routing abstraction from day one mean promoting a tenant later is a data move and a catalog change, not a code change across every service. - Automation paid for once. A migration orchestrator with recorded versions and idempotent retries replaces manual reruns and drift investigations across a fleet.
Illustrative estimate (assumed inputs, not a measured result): assume a team ships 40 pull requests a month that touch tenant-scoped queries, and reviewers spend an assumed 10 extra minutes on each confirming the tenant filter. That is about 400 minutes, roughly 6.7 hours, of review time a month. If forced RLS and the CI check let reviewers drop that step, the saving is of that order, before counting any avoided incident. Substitute your own pull request volume and review time.
Which multi-tenant model should you choose? A decision checklist
Score your situation on four inputs before committing to a model. The goal is to pick the cheapest model that satisfies your strictest tenant.
| Input | Points toward shared schema with RLS | Points toward database per tenant |
|---|---|---|
| Expected tenant count | Thousands or more, mostly self-serve | Tens to low hundreds, sales-led |
| Size distribution | Long tail of small tenants | A few tenants dominate load or storage |
| Compliance needs | Contractual isolation is enough | Data residency, customer-managed keys, audited physical separation |
| Team capacity | No dedicated platform team | Automation for migrations, backups and provisioning already exists |
Default recommendation
For most SaaS products, start with a shared schema hardened by RLS and keep a path open to dedicated databases for the tenants that outgrow it. Self-serve products with a long tail of small tenants should stay shared, because per-tenant overhead would exceed what those tenants pay. Products with a few dominant tenants, or with regulated tenants that demand physical separation, should move those tenants onto dedicated databases. Pure database-per-tenant is the right default only when every tenant is large or regulated and the team can automate operations across the whole fleet.
Why decide early
Moving between models after launch usually takes several quarters. The schema change itself is small. The cost comes from backfilling tenant ownership into every table, rewriting queries that assume a single tenant, moving data without downtime, and verifying that isolation holds at every step.
Two things are cheap on day one and expensive later:
- A
tenant_idcolumn on every tenant-owned table. It lets you add RLS or extract a tenant's data without archaeology. - A routing abstraction that resolves tenant to connection. Even if every tenant maps to the same database today, services never hardcode where data lives. Promoting a tenant to a dedicated database then becomes a data move plus a routing change, with no change to application code.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to multi-tenant database per tenant vs shared schema, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.