What is the difference between shared tables with RLS and schema per tenant?
In a shared-table design, every tenant's rows live in the same tables, and each table carries a tenant_id column. Postgres row level security policies compare that column against a per-session value, such as a custom setting the application sets at the start of each request. The SQL your application writes doesn't change: SELECT * FROM invoices WHERE status = 'open' returns only the current tenant's rows because the policy adds the predicate. Indexes are shared across tenants, so they usually lead with tenant_id, as in (tenant_id, status), so the planner can narrow to one tenant's slice before filtering further. The ORM needs one schema and one set of models. The only extra wiring is a hook that sets the tenant context when a connection is checked out or a transaction begins.
In schema per tenant, each tenant gets its own Postgres schema holding an identical copy of every table. A request selects its tenant by setting search_path to that schema, so unqualified table names resolve to that tenant's copy. Tables need no tenant_id column, and indexes are per tenant, so each one covers only a single tenant's data. The ORM must avoid hard-coding schema names in model metadata and must set search_path per connection or per transaction.
Routing happens at the same point in both models. Middleware resolves the tenant from the subdomain, token claim, or header, then puts that identity on the database session before any query runs. The difference is the mechanism. Shared tables filter rows inside each table. Schema per tenant changes which tables a name refers to.
+--------------------------------------------------------------------------+
| HTTP request (tenant in subdomain, token claim, or header) |
+------------------------------------+-------------------------------------+
|
v
+--------------------------------------------------------------------------+
| Middleware: resolve tenant -> 'acme' |
| then bind it to the DB session before any query runs |
+-----------------+--------------------------------------+-----------------+
| shared + RLS | schema per tenant
v v
+-----------------------------------+ +-----------------------------------+
| SET app.tenant_id = 'acme' | | SET search_path = tenant_acme |
| SELECT * FROM invoices | | SELECT * FROM invoices |
| WHERE status = 'open' | | WHERE status = 'open' |
| policy appends: | | name resolves to: |
| AND tenant_id = 'acme' | | tenant_acme.invoices |
+-----------------+-----------------+ +-----------------+-----------------+
| |
v v
+-----------------------------------+ +-----------------------------------+
| public.invoices (all tenants) | | tenant_acme.invoices |
| cols: tenant_id, status, ... | | cols: status, ... (no tenant_id) |
| index (tenant_id, status) | | index (status), one per schema |
| one index, shared by all tenants | | other schemas: tenant_globex, ... |
+-----------------------------------+ +-----------------------------------+
filters rows within shared tables swaps which tables names resolve to
Both models route the tenant at the same point, in middleware, before any query runs; shared tables with RLS then filter rows through a policy predicate, while schema per tenant changes which table an unqualified name resolves to via search_path.
How does it work end to end?
Both models follow the same lifecycle: provision a tenant, bind each request to it, enforce the boundary inside Postgres, and eventually remove the tenant. The steps differ in what each one touches.
Provisioning a tenant.
- Shared tables + RLS: insert a row into a
tenantstable. Every tenant-scoped table references it through atenant_idforeign key. No DDL runs. - Schema per tenant: run
CREATE SCHEMA tenant_<id>and apply every migration to that schema, so its tables match all the others. Provisioning is DDL, and it can fail partway through like any migration.
Binding a request.
- Middleware authenticates the caller and reads the tenant from the verified token.
- It opens a transaction on a pooled connection.
- It sets the tenant context for that transaction only. The RLS model uses
set_config('app.tenant_id', ..., true). The schema model usesSET LOCAL search_path. - The handler runs ordinary, unqualified SQL.
- The transaction commits or rolls back, and Postgres discards the context.
Enforcing the boundary.
- Shared tables + RLS: when the query is rewritten, Postgres attaches the table's policy expressions. The
USINGclause limits which rows are visible. TheWITH CHECKclause rejects writes tagged for another tenant. Enforcement holds even if application code leaves out thetenant_idfilter, as long as the app role is not the table owner and does not haveBYPASSRLS. - Schema per tenant: enforcement is name resolution. Postgres looks up
invoicesin the schemas listed insearch_pathand uses the first match. Nothing checks rows, so isolation depends entirely onsearch_pathbeing correct. For a second layer, give each tenant its own role and grant itUSAGEon that tenant's schema only. Note that this adds roles and grants that must also be managed for every tenant.
Offboarding.
- Shared tables + RLS: delete the tenant's rows across all tables, or cascade the delete from the
tenantsrow, then let vacuum reclaim the space. - Schema per tenant: drop the schema.
The sections below cover each step in detail: how policies are written, how to scope context under a pooler, and what migrations cost as tenant count grows.
How does Postgres row level security work?
Row level security is off by default. Running ALTER TABLE invoices ENABLE ROW LEVEL SECURITY turns it on. From then on, every query from a role subject to RLS is filtered by the table's policies. If a table has RLS enabled but no policies, those roles see no rows at all. Postgres denies by default.
A policy created with CREATE POLICY has two clauses. USING is a boolean expression that decides which existing rows are visible to SELECT, UPDATE, and DELETE. WITH CHECK validates rows being written by INSERT and UPDATE, so a session for tenant A cannot write a row tagged for tenant B. Both clauses usually compare tenant_id to current_setting('app.tenant_id', true), cast to the column's type. The second argument makes the call return NULL instead of raising an error when the setting is missing. A comparison with NULL matches nothing, so a session with no tenant set fails closed.
Superusers and roles with BYPASSRLS skip policies entirely. Table owners also skip them, because ownership is treated as full authority over the table. For that reason, the application must connect as a separate role that owns nothing and lacks BYPASSRLS. If the owner role has to stay subject to policies, for example in tests, use ALTER TABLE ... FORCE ROW LEVEL SECURITY.
RLS is a safety net, and the net has gaps:
- A new table created without RLS enabled has no protection.
- A
SECURITY DEFINERfunction runs as its owner, which is often the table owner, so its queries bypass policies.
Neither gap produces an error. Add an automated test that queries pg_class and pg_policies and fails the build when any tenant-scoped table lacks RLS or policies.
-- Turn on RLS; without policies, non-owner roles see zero rows.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
-- Make the owner subject to policies too (useful in tests).
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
-- USING filters visible rows; WITH CHECK rejects writes for other tenants.
-- missing_ok = true returns NULL when unset, so no rows match (fail closed).
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
-- App role: owns nothing, no BYPASSRLS, so policies always apply.
CREATE ROLE app_user LOGIN PASSWORD 'change-me' NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;
-- Per request, inside a transaction: SET LOCAL scopes the tenant to it.
BEGIN;
SET LOCAL app.tenant_id = '6f1c2a7e-0000-4000-8000-000000000001';
SELECT id, amount FROM invoices; -- only this tenant's rows
COMMIT;
-- CI check: tenant-scoped tables missing RLS or policies.
-- An empty result passes; any row should fail the build.
SELECT c.relname,
c.relrowsecurity AS rls_enabled,
count(p.policyname) AS policy_count
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
LEFT JOIN pg_policies p
ON p.schemaname = n.nspname AND p.tablename = c.relname
WHERE c.relkind IN ('r', 'p') AND n.nspname = 'public'
GROUP BY c.relname, c.relrowsecurity
HAVING NOT c.relrowsecurity OR count(p.policyname) = 0;
Enabling and forcing RLS, a tenant policy with USING and WITH CHECK, a non-owner app role, per-transaction tenant context, and a CI query that flags tenant tables lacking RLS or policies.
How do you set tenant context safely with a connection pooler?
On a pooled connection, session state outlives the request that set it. A plain SET app.tenant_id = '42' or SET search_path = tenant_42 stays on the backend connection after your request returns it to the pool. The next request that borrows that connection inherits tenant 42's context. In transaction-mode pooling, consecutive statements can even land on different backends, so session-level settings are unreliable in both directions.
Scope tenant context to a transaction instead. SET LOCAL app.tenant_id = '42', or set_config('app.tenant_id', '42', true) when the value is a bind parameter, lasts only until COMMIT or ROLLBACK. After that, Postgres restores the previous value, even if the request failed halfway through. The per-request middleware pattern looks like this:
- Authenticate the request and derive the tenant ID from the verified token. Never take it from a header the client controls.
- Open a transaction.
- Call
set_config(..., true). - Run the handler.
- Commit, or roll back on any exception.
Schema per tenant needs the same discipline with SET LOCAL search_path, but its failure mode is worse. If an RLS policy is written to reject a missing tenant ID, a leftover or missing setting returns no rows or raises an error. A stale search_path raises nothing. Unqualified table names resolve against whichever schema is first in the path. The query reads or writes another tenant's tables, or falls through to public, and it succeeds without error.
Test this directly. On a single held connection, open a transaction, set the context, and commit. Then assert that current_setting('app.tenant_id', true) is NULL or an empty string (once the setting has been used in the session, Postgres can report it as empty instead of NULL). Also assert that SHOW search_path returns the role's default.
import psycopg
from psycopg import sql
DSN = "postgresql://app_user:example-password@localhost:5432/appdb"
def run_for_tenant(conn, tenant_id: int, handler):
# tenant_id must come from the verified auth token, never a client header.
# conn.transaction() commits on success and rolls back if handler raises.
with conn.transaction():
# is_local=true scopes the value to this transaction (RLS approach).
conn.execute("SELECT set_config('app.tenant_id', %s, true)", (str(tenant_id),))
# Schema per tenant: same scoping, with the identifier quoted safely.
conn.execute(sql.SQL("SET LOCAL search_path TO {}").format(
sql.Identifier(f"tenant_{tenant_id}")))
return handler(conn)
def assert_no_tenant_context(conn):
# NULL if never set in this session, '' once it has been set and reverted.
tid = conn.execute("SELECT current_setting('app.tenant_id', true)").fetchone()[0]
assert tid in (None, ""), f"tenant context leaked: {tid!r}"
path = conn.execute("SHOW search_path").fetchone()[0]
assert "tenant_42" not in path, f"search_path leaked: {path!r}"
def test_context_does_not_survive_transaction():
# One held connection stands in for a reused pooled backend.
with psycopg.connect(DSN, autocommit=True) as conn:
run_for_tenant(conn, 42, lambda c: c.execute("SELECT 1"))
assert_no_tenant_context(conn) # cleared at COMMIT
def failing_handler(c):
raise RuntimeError("handler failed mid-request")
try:
run_for_tenant(conn, 42, failing_handler)
except RuntimeError:
pass
assert_no_tenant_context(conn) # cleared at ROLLBACK
A psycopg 3 sketch that scopes app.tenant_id and search_path to a single transaction with set_config(..., true) and SET LOCAL, then tests on one held connection that neither setting survives a commit or a rollback.
How do migrations and catalog size scale with tenant count?
On shared tables, a migration is one change against one set of tables. It applies or it doesn't, and a single row in your migrations table records the version. Schema per tenant turns that into a loop over N schemas, which causes three problems:
- Partial failure. If schema 3,412 fails on a lock timeout or on tenant data that violates a new constraint, some tenants are on version 42 and the rest are on 41. The application must work with both until you fix the cause and resume. The runner needs per-schema version tracking and must be safe to re-run.
- Schema drift. A hotfix applied to one tenant, or an index added by hand for a large customer, makes schemas diverge without anyone noticing. You need a job that compares each schema against the expected definition.
- Deploy time. Deploy time grows linearly with tenant count. Illustrative estimate: with 2,000 schemas and an assumed 1.5 seconds each for lock acquisition, DDL and bookkeeping, a serial run takes about 50 minutes. Running in parallel shortens this but adds contention for locks and connections.
The catalog also multiplies. Illustrative estimate: assume 40 tables per schema, each with 3 indexes. That is 160 relations per schema before sequences and TOAST tables, or 320,000 relations across 2,000 schemas. This has several effects:
pg_classandpg_attributegrow, so anything that scans them slows down.pg_dumpreads metadata for every object and takes a lock on every table it dumps, so you may need to raisemax_locks_per_transaction.- Each backend caches catalog entries for the relations it touches. A pooled connection that serves many tenants keeps growing that cache.
- ORM introspection and
information_schemaqueries slow down.
The practical ceiling depends on tables per schema and on your hardware. Find it by loading synthetic schemas and measuring before you commit to this model.
#!/usr/bin/env bash
set -euo pipefail
DB="postgres://migrator@db.internal:5432/app" # placeholder DSN
VERSION=42
: > failed_schemas.txt
for s in $(psql "$DB" -XAt -c "SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant\_%' ORDER BY 1"); do
# Each schema tracks its own version, because a partial run leaves tenants on 41 and 42.
current=$(psql "$DB" -XAt -c "SELECT coalesce(max(version), 0) FROM \"$s\".schema_version")
[ "$current" -ge "$VERSION" ] && continue
# Short lock_timeout: fail fast on a busy tenant instead of queueing behind its traffic.
if ! psql "$DB" -X -q -v ON_ERROR_STOP=1 <<SQL
BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL search_path = "$s";
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS due_date date;
INSERT INTO schema_version (version) VALUES ($VERSION);
COMMIT;
SQL
then
echo "$s" >> failed_schemas.txt # fix the cause, then re-run the script
fi
done
psql "$DB" -X -c "
SELECT count(*) AS tenant_relations,
pg_size_pretty(pg_total_relation_size('pg_catalog.pg_attribute')) AS pg_attribute_size
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname LIKE 'tenant\_%'"
This shell runner loops one migration over every tenant schema. It uses a separate transaction for each schema and tracks the version inside each schema, so a partial failure can be resumed by re-running it. It then reports how many relations the tenant schemas have added to the catalog.
How do you keep shared-table queries fast under RLS?
Every index on a tenant-scoped table should lead with tenant_id, for example (tenant_id, created_at) or (tenant_id, status, id). The policy adds a tenant_id = … predicate to every query, so a leading tenant_id lets one index scan satisfy both the policy and the query's own filter or sort. Without it, the planner can walk the right column range across every tenant and then discard most rows.
Keep the policy a plain equality check, such as tenant_id = current_setting('app.tenant_id')::uuid. current_setting is stable, so the planner can treat the value as a constant for the scan and match it to the index. Policies that call a membership subquery or a volatile function cost more. That work runs inside every statement on the hot path, and it can stop the predicate from being used as an index condition. RLS also evaluates policy quals before user quals that call non-leakproof functions. Those user filters cannot be pushed ahead of the policy, so they end up as post-scan filters instead of index conditions.
Check plans as the application sees them. Superusers and table owners bypass RLS unless the table has FORCE ROW LEVEL SECURITY. Run SET ROLE to the app role, set the tenant setting, then run EXPLAIN (ANALYZE, BUFFERS). Confirm that the plan shows an index scan on the tenant_id index rather than a filter.
When a few tenants dominate a table, consider partitioning by tenant_id. Use list partitions for the large tenants and hash partitioning for the long tail. This keeps per-partition indexes small and lets you vacuum, detach or drop one tenant's data independently. The costs are more objects to manage and queries that must include tenant_id so partitions can be pruned. Partitioning is worth this only once you have measured a single large tenant degrading index or vacuum behavior for everyone else.
What tenant isolation does each model actually give you, and what are the alternatives?
Neither model isolates resources. Every tenant shares the same cluster, shared buffers, WAL stream, autovacuum workers, and disk I/O. A tenant running a heavy report evicts other tenants' pages from cache, and a bad migration, a full disk, or a failover takes down every tenant at once. Schemas and RLS only control which rows a query can see. They do not limit how much of the server a tenant can use.
The models do differ in day-to-day operations:
| Task | Shared tables + RLS | Schema per tenant |
|---|---|---|
| Restore one tenant | PITR is cluster-wide, so you restore to a side instance and copy that tenant's rows back | pg_dump of one schema, restored on its own |
| Export a tenant | Filtered COPY on every table by tenant_id |
Dump the schema |
| Delete a tenant | DELETE across all tables, then wait for vacuum |
DROP SCHEMA ... CASCADE |
| Cross-tenant analytics | One query | UNION across N schemas, or load into a warehouse |
| Move a large tenant out | Copy filtered rows and keep them in sync during cutover | Dump and restore one schema, then cut over |
Schema per tenant makes per-tenant lifecycle tasks easier. Shared tables make anything that spans tenants easier.
For real resource isolation you need a physical boundary. With database per tenant, each tenant gets separate catalogs and connection limits, but tenants still share the instance's memory and I/O. With cluster per tenant, each tenant has its own CPU, memory, and failure domain. Both bring back fleet management: you provision, monitor, upgrade, back up, and migrate every instance yourself, and connection pooling no longer happens in one place. Most teams use this only for the few tenants that pay for it or need it to meet compliance requirements.
Who should use each model?
Shared tables with RLS suit teams building a multi-tenant SaaS application on a single Postgres database with one codebase. That covers a monolith or a small number of services that share a data model. It fits best when:
- You have many tenants, or expect to, and most of them are small or medium-sized.
- Every tenant uses the same tables and columns.
- You want the database to enforce isolation as a second layer behind application code, so a forgotten
WHERE tenant_id = ...cannot leak data. - You control which role the application connects as, so it can be a non-owner without
BYPASSRLS. - You run reporting, billing, or support queries that span tenants. These can use a separate, audited role that bypasses RLS.
Schema per tenant suits teams with a small, slow-growing set of large customers, where some of them need one of the following:
- Contractual or audit-driven separation that is easy to show to a reviewer.
- Their own columns, indexes, or extensions.
- Restore, export, or deletion that does not touch other tenants.
It also assumes someone owns the migration runner, drift detection, and catalog monitoring described above.
Database or cluster per tenant is for tenants that need resource isolation or a separate failure domain, usually because of regulation, data residency, or a workload heavy enough to hurt other tenants. Most teams use it for a handful of tenants and keep everyone else on shared tables.
RLS is a poor fit when tenant boundaries are not expressed as a column on each row, or when every connection runs as a superuser or table owner and you cannot change that. In both cases the policies would not protect anything.
How can it save engineering time?
The time savings come from moving tenant isolation out of individual queries and into a single mechanism that is set up once and checked automatically.
With shared tables and RLS:
- Fewer places to get isolation wrong. Developers write ordinary queries. The policy adds the tenant predicate, so reviewers do not need to check every query, ORM scope, and raw SQL string for a
tenant_idfilter. - Failures fail closed. A missing tenant context returns no rows instead of everyone's rows. The bug shows up as an empty page in testing rather than a data leak in production.
- Guardrails run without anyone remembering them. The
pg_policiesCI check catches new tables that lack RLS. The pooled-connection test catches context that leaks between requests. Both run on every build. - One migration path. A schema change runs once, with ordinary migration tooling and one version row. No fleet runner, per-schema version tracking, or drift job is needed.
- Onboarding is a data operation. Creating a tenant is an insert that the application can do itself, with no DDL and no deploy step.
With schema per tenant, the savings land elsewhere. Restoring, exporting, or deleting one tenant is a dump, restore, or DROP SCHEMA rather than a filtered copy across every table. Per-tenant customizations do not need conditional code paths in a shared schema. Those savings are paid for with migration tooling, so they only pay off when per-tenant operations are frequent and the tenant count is small.
The hybrid approach saves time later. Putting tenant_id on every row from the start means that moving a large tenant to its own schema or database is a filtered copy plus a routing change. The alternative is a backfill-and-rewrite project, which costs much more.
When should you use schema per tenant instead of shared tables, and when should you avoid it?
For most monoliths, start with shared tables and RLS. You run one schema, one migration path, and one pool, and onboarding a tenant is just an insert. Schema per tenant makes sense only when the tenant list is short, known in advance, and grows slowly, and when at least one of these holds: a contract requires hard separation, tenants need their own columns or indexes, or you must restore one tenant without touching the others.
| Factor | Shared tables + RLS | Schema per tenant |
|---|---|---|
| Tenant count | Hundreds to very large | Small and stable |
| Onboarding | Insert a row | Create schema, run all migrations |
| Migration cost | Once | Once per tenant, with partial-failure handling |
| Per-tenant customization | Hard | Natural |
| Single-tenant restore | Filtered export, manual | Dump and restore one schema |
| Team size needed | Small | Enough to own fleet tooling |
Avoid schema per tenant if you have many tenants or expect fast growth, ship schema changes often, or have a small team. In those cases its cost lands on development time. Each migration becomes a fleet operation that needs retries, drift detection, and a way to handle tenants stuck halfway through.
The hybrid path keeps both options open. Put tenant_id on every row from the first day, including tables that seem global at the moment, and include it in keys and indexes. When a large or regulated tenant later needs separation, you copy its rows into a dedicated schema or database by filtering on that column, and the application changes only how it routes connections. Adding tenant_id to an existing data model after the fact means backfilling data and rewriting queries, which is a project in its own right.
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 design, 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.
- Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
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.
- Consumers learn about a breaking change at review time rather than from production errors.
- 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.