Multi-Tenant SaaS on Postgres: Shared Schema + RLS vs Schema-per-Tenant vs Database-per-Tenant

Every B2B SaaS eventually answers the question: how is one customer's data kept apart from another's? The answer shapes your schema, your migrations, your connection pooling, your backup strategy, and your worst-case security incident. It's also the decision that's most expensive to reverse once you have customers.

On Postgres there are three mainstream patterns. This is a practitioner's comparison, followed by the details needed to make the most common choice — shared schema with row-level security — actually hold.

The three patterns

1. Shared schema, tenant_id on every row

All tenants share the same tables. Every tenant-owned table has a tenant_id column, and every query filters on it — enforced in application code, and ideally by row-level security as a second line of defence.

2. Schema per tenant

One Postgres database, one schema per tenant (tenant_acme.orders, tenant_globex.orders), with identical table definitions. The application selects the schema per request, typically via search_path.

3. Database per tenant

Each tenant gets its own database — on a shared cluster, or on dedicated instances for the largest customers.

The comparison

	Shared + RLS	Schema per tenant	Database per tenant
Isolation strength	Logical (policy)	Logical (namespace)	Strong (separate DB/instance)
Blast radius of a query bug	All tenants	Usually one	One
Migrations	One run	N runs	N runs, N connections
Tenant count ceiling	Very high	Hundreds–low thousands	Operationally bounded
Connection pooling	Simple	search_path complications	Pool per database
Cross-tenant analytics	Trivial	UNION across schemas	ETL
Per-tenant restore	Hard	Moderate (pg_dump -n)	Easy
Noisy neighbours	Shared everything	Shared everything	Isolable
Data residency / dedicated infra	Hard	Hard	Natural
Onboarding a tenant	Insert a row	Create schema + migrate	Provision + migrate

A few of these deserve more than a table cell.

Migrations. With N schemas or databases, every migration runs N times. It becomes a fleet operation: what happens when it succeeds for 800 tenants and fails on the 801st? You need idempotent migrations, a per-tenant version table, resumable runners, and tolerance for the fleet being temporarily on mixed schema versions — which means your application must be compatible with both. (What Are Database Migrations?)

Catalog bloat. Schema-per-tenant multiplies catalog entries (tables × indexes × tenants). At thousands of tenants with hundreds of tables each, pg_catalog gets large, planning slows, and tools that introspect the catalog struggle. It's the main reason schema-per-tenant has a practical ceiling.

Pooling. Database-per-tenant needs a connection pool per database; at scale, idle connections across thousands of databases exhaust memory. Schema-per-tenant with a transaction-mode pooler requires SET LOCAL search_path inside every transaction, because a session-level SET leaks to whichever client gets that server connection next. (Postgres Connection Pooling Explained.)

Per-tenant restore. The enterprise customer who deleted a year of data asks you to restore just their data to yesterday. With database-per-tenant, that's a normal restore. With a shared schema, it's a point-in-time restore to a side instance followed by carefully extracting and merging one tenant's rows — possible, but it should be rehearsed before the day it's needed. (Postgres Point-in-Time Recovery.)

The pragmatic default: shared schema + RLS, with an escape hatch

For most SaaS products — many small-to-mid tenants, one product, frequent schema changes — the shared schema wins on operational simplicity by a wide margin. The standard hedge is a hybrid: shared schema for the long tail, and the ability to place a large or regulated tenant in its own database (running the same schema) when a contract requires it. Designing tenant_id into every table from day one keeps that move possible.

The shared schema's weakness is that isolation depends on every query being right. Row-level security moves the guarantee from "every developer and every AI-written query remembered the filter" to "the database enforces it." Done properly, it's a strong backstop. Done naively, it silently does nothing.

Row-level security, done properly

The policy

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING      (tenant_id = (SELECT current_setting('app.tenant_id')::uuid))
  WITH CHECK (tenant_id = (SELECT current_setting('app.tenant_id')::uuid));

• USING filters rows visible to SELECT, UPDATE, and DELETE.
• WITH CHECK validates rows written by INSERT and UPDATE — without it, a tenant could write rows into another tenant.
• Wrapping current_setting() in a scalar subquery lets the planner evaluate it once per statement rather than per row.
• current_setting('app.tenant_id') without the missing_ok argument errors if the setting is absent, which is what you want: failing closed beats silently returning nothing (or everything).

Trap 1: the table owner bypasses RLS

By default, RLS does not apply to the table's owner. If your application connects as the role that ran the migrations — extremely common — your policies do nothing. Two fixes, use both:

• FORCE ROW LEVEL SECURITY (above) applies policies to the owner too.
• Connect the application as a separate, non-owner role with only the DML privileges it needs. Migrations run as the owner; the app never does.

Superusers and roles with BYPASSRLS always bypass RLS. Audit that your application role has neither.

Trap 2: setting the tenant safely under pooling

The tenant must be set per transaction, scoped so it cannot leak:

BEGIN;
SELECT set_config('app.tenant_id', $1, true);   -- true = transaction-local
-- ... queries ...
COMMIT;

set_config(..., true) is equivalent to SET LOCAL: it resets at transaction end, which makes it safe with transaction-mode poolers. A session-level SET under a pooler is a cross-tenant data leak waiting for the right interleaving. Wrap this in your data-access layer so no code path can issue a query without it — for example, a withTenant(tenantId, fn) helper that opens the transaction, sets the context, and is the only way to obtain a query handle.

Derive tenant_id from the authenticated session server-side — never from a request parameter. (Authentication vs Authorization.)

Trap 3: cross-tenant foreign keys

RLS filters reads, but a foreign key check is performed by the system and isn't filtered by your policies. With a plain orders.customer_id → customers.id, a tenant who learns (or guesses) another tenant's customer UUID can attach an order to it, and the FK happily accepts it.

Make tenancy part of the key:

ALTER TABLE customers ADD CONSTRAINT customers_tenant_id_uq UNIQUE (tenant_id, id);

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id);

Now a row can only reference a parent in the same tenant — structurally, not by policy.

Trap 4: global uniqueness

UNIQUE (email) on a tenant-owned table is a cross-tenant information leak ("this email already exists" reveals another tenant's data) and a functional bug. Scope constraints: UNIQUE (tenant_id, email).

Trap 5: views, functions, and side channels

• Views run with the permissions of their owner by default, which can bypass RLS. On Postgres 15+, create them with WITH (security_invoker = true).
• SECURITY DEFINER functions run as their owner. Audit every one; set a safe search_path on them.
• Error messages and timing can leak existence. Return 404 for other tenants' IDs, and don't expose constraint names that reveal data.

Indexing for RLS

Policies become predicates on every query. Lead composite indexes with tenant_id — (tenant_id, created_at), (tenant_id, customer_id) — so the policy predicate is index-served. Check plans with EXPLAIN ANALYZE under a realistic tenant distribution, including your largest tenant; skew can make a plan that's fine for small tenants terrible for big ones. (Database Indexes.)

Test isolation continuously

Isolation must be a tested invariant, not a code review hope:

• An integration test that creates two tenants and asserts, for every tenant-owned table, that tenant A's context sees zero of B's rows and cannot insert or update B's rows.
• A schema test that fails CI if any table with a tenant_id column lacks RLS enabled and forced, or lacks a policy.
• A test that the application role has no BYPASSRLS and owns no tables.

This matters doubly when AI agents write queries. An agent adding a new table or a raw SQL report is exactly the path that forgets the filter; the schema test catches the table, and RLS catches the query. (Getting AI to Write Tests That Actually Catch Bugs.)

When to choose otherwise

Choose database-per-tenant (at least for some tenants) when you have contractual isolation or data-residency requirements, a small number of large tenants with divergent load, per-tenant restore as a product commitment, or customers who demand their own encryption keys. Accept the fleet-migration and pooling costs deliberately.

Choose schema-per-tenant rarely: it takes on most of the operational cost of separate databases while providing weaker isolation than they do. It fits a narrow band — moderate tenant counts needing per-tenant logical separation and pg_dump-level export — and it has a hard ceiling.

---

EasySpawn provisions a managed Postgres per project, so Claude Code can build and test tenancy models — policies, isolation tests, and fleet migrations — against a real database before any of it reaches production. See how it works or join the waitlist.

Related: Supabase Row-Level Security Explained · Docker vs Linux Users for Multi-Tenant Isolation · Soft Deletes and Audit Logs · Postgres Full-Text Search