Multi-tenant Postgres patterns in 2026 — schemas vs RLS vs separate DBs

Pick your tenancy model before you ship row one. Schema-per-tenant is the path of least surprise up to about 500 tenants — after that, Postgres’s own catalog becomes the bottleneck. Row-Level Security scales further but has five known CVEs and a performance trap that costs you 20× on a busy table if you write the policy wrong. Separate databases give the strongest isolation but require a connection pooler from the moment you have more than a hundred tenants. The hybrid approach — RLS for free tenants, dedicated schemas or databases for paying ones — is what most mature SaaS products end up at, not what they start with.

Who this is for

Engineers picking a tenancy architecture before their schema solidifies. If you already have 200,000 tenants on a shared schema, migration costs dominate the calculus — this article doesn’t cover migration paths.

The three models

Model	Isolation unit	What’s shared
Schema-per-tenant	Postgres schema (namespace)	Database, WAL, lock manager, buffer pool, `pg_class`
RLS (shared schema)	Row filter via policy	Everything — same tables, same schema
Separate databases	Postgres database or instance	WAL and lock manager (same instance); nothing (separate instance / Neon project)

One nuance from a March 2026 EDBT benchmarking paper (Erdelt & Rabl, DOI: 10.48786/edbt.2026.46566): schema-per-tenant and separate-databases-on-the-same-instance give similar isolation at the WAL and buffer-pool level. Both share the same lock manager and recovery path. If you need true blast-radius isolation, you need separate instances — or a serverless model where each project is independent infrastructure.

Schema-per-tenant: the ceiling

Where it works

Schema-per-tenant is conceptually clean: each tenant gets its own namespace, migrations run per-schema, and you can restore a single tenant’s schema without touching anyone else. Under 100 tenants, it causes no measurable problems.

The catalog overhead wall

The trouble is pg_class. Every table, index, sequence, and view lands in Postgres’s system catalog. At 1,200 schemas with roughly 200 tables each — around 240,000 catalog entries — a simple information_schema.tables lookup takes 383 ms because it performs a sequential scan over 1.3 million rows. This was measured on Postgres 9.5.7 by Ulf Lohbrügge and posted to the PostgreSQL mailing list in 2017.

Flyway uses information_schema queries internally. At 1,200 schemas, 383 ms multiplied across Flyway’s 10+ queries per tenant pushed migration time to over two hours on that same dataset.

Practical ceilings

Tenant count	Schema-per-tenant health
< 100	Fine, no known bottlenecks
100–500	`information_schema` queries slow; query `pg_class` directly
500–1,000	Catalog overhead measurable; migration tools slow substantially
1,000–10,000	Requires Citus schema-based sharding or equivalent
> 10,000	Unsupported even by Citus

Citus 12 (Microsoft, July 2023) explicitly targets 1–10,000 tenants per cluster for schema-based sharding, contrasting with row-based sharding which it positions for “100–1M+ tenants.”

The PgBouncer session-mode trap

PgBouncer’s transaction pool mode — often the recommended default for performance — discards SET search_path between transactions. Schema-per-tenant relies on search_path to route each connection to the right tenant schema. In transaction mode, that routing silently breaks. You must run session mode, which limits connection reuse and reintroduces the connection count problem that pooling was meant to solve.

Arkency documented this in October 2020 after running into it in production: “Anything else than session mode won’t let you use search_path to switch tenants.”

Migration tooling in 2026

Atlas (v1.0, December 2025) handles multi-schema setups with a for_each meta-argument that iterates over tenants. Fail-fast policy: one tenant migration error stops the whole run. Flyway recommends Pattern 2 (one migration set, one history table per tenant) and explicitly marks Pattern 3 (shared history) as “not recommended.” Neon’s own docs advise against schema-per-tenant for SaaS: “doesn’t reduce operational complexity or costs compared to the many-databases approach, but introduces additional risks.”

RLS: the correctness trap

How it works

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY; -- prevents owner bypass in tests

-- WRONG: function evaluated per row
CREATE POLICY bad_policy ON orders
  USING (tenant_id = auth.uid());

-- CORRECT: (select ...) cached per statement via Postgres's initPlan optimization
CREATE POLICY tenant_isolation ON orders
  AS PERMISSIVE FOR ALL
  TO authenticated
  USING (tenant_id = (select auth.uid()))
  WITH CHECK (tenant_id = (select auth.uid()));

CREATE INDEX ON orders(tenant_id); -- mandatory

Default behavior: RLS is default-deny. No policy means no rows visible or writable. Superusers and BYPASSRLS roles always bypass — FORCE ROW LEVEL SECURITY on the table owner closes that gap in tests. TRUNCATE and foreign-key reference checks are not subject to RLS (Postgres 16/17 docs).

The CVE record

Five CVEs against RLS since 2019, sources at postgresql.org/support/security:

CVE	Fixed	CVSS	Mechanism
CVE-2024-10976	Nov 14, 2024	4.2	CTE/subquery plan reuse across `SET ROLE` — PG 12–17
CVE-2024-0985	Feb 8, 2024	8.0	`REFRESH MATERIALIZED VIEW CONCURRENTLY` privilege drop
CVE-2023-2455	May 11, 2023	4.2	Function inlining + `SET ROLE` applies wrong role’s policies
CVE-2023-39418	Aug 10, 2023	3.1	`MERGE` ignores `UPDATE`/`SELECT` policies (PG 15 only)
CVE-2019-10130	May 9, 2019	3.1	Planner statistics leakage via leaky operators

The recurring pattern: plan reuse across role changes. When a query is planned under one role but executed under another — via security definer functions, SET ROLE, or function inlining — the planning-time role’s policies apply at execution time. CVE-2024-10976, patched November 2024, is the most recent instance. Any code path that combines security definer functions with connection pooling is a potential bypass surface.

The SECURITY DEFINER view trap

Pre-Postgres 15, views ran as the view owner (effectively SECURITY DEFINER) and silently bypassed RLS on the underlying tables. The fix arrived in Postgres 15 (November 2022):

CREATE VIEW tenant_orders WITH (security_invoker = true) AS
  SELECT * FROM orders;
-- or retroactively:
ALTER VIEW tenant_orders SET (security_invoker = true);

Any application on Postgres < 15 with views and RLS should treat every view as an RLS bypass.

Connection pooling and context leakage

PgBouncer in transaction mode reuses server connections across different clients. The current_user approach doesn’t work because all app connections share the same database role. Standard fix: set tenant context inside a transaction using SET LOCAL, which auto-resets on commit.

BEGIN;
SELECT set_config('rls.tenant_id', $1, true); -- true = LOCAL, resets at transaction end
-- execute tenant queries
COMMIT;

If you skip LOCAL and a pooled connection reuses a server connection from a previous client, the previous tenant’s context is still active. No error. Wrong data served silently.

The performance trap

The difference between auth.uid() and (select auth.uid()) in a policy USING clause is not stylistic. Postgres evaluates the bare function call per row. The (select ...) form triggers an initPlan — the result is computed once per statement and cached.

Measured on a 100,000-row table (Supabase Discussion #14576, May 2023):

Policy form	Execution time
`auth.uid() = user_id` (no index)	171 ms
`(select auth.uid()) = user_id` (no index)	9 ms
`(select auth.uid()) = user_id` (with index)	< 0.1 ms

On 1 million rows:

Pattern	Execution time
`=ANY(user_teams())` standard	> 2 minutes (timeout)
`=ANY(ARRAY(select user_teams()))` with index	2 ms

Index the column. Always use (select function()). These aren’t micro-optimizations — they’re the difference between a working product and one that timeouts on any non-trivial tenant.

The real-world failure mode

CVE-2025-48757 (May 2025, CVSS 8.26): Lovable, an AI-powered app generator, shipped Supabase projects where RLS policies did not match business logic (mattpalmer.io). Tables created via SQL have RLS off by default — Supabase’s Table Editor enables it automatically, but the SQL Editor does not (Supabase RLS docs). The tooling doesn’t protect you from omission.

Seven RLS production footguns

SECURITY DEFINER views bypass RLS — use security_invoker = true on Postgres 15+
Connection pool context leakage — skip SET LOCAL and you’ll serve wrong-tenant data
BYPASSRLS and superuser bypass — use FORCE ROW LEVEL SECURITY in test environments
Missing WITH CHECK — allows INSERT of rows you can’t SELECT back
Materialized views — background refresh bypasses RLS entirely
Unique constraints — reveal cross-tenant row existence; use composite (tenant_id, email) uniqueness
Leaky operator statistics — CVE-2019-10130 class; fixed in PG 9.5+ but shows the attack surface

Source: Bytebase RLS footguns, Supabase RLS docs, Postgres official docs.

Separate databases: the operational tax

The connection overhead problem

Postgres uses one backend process per connection. Each idle connection consumes ~2 MiB with huge_pages enabled — but the bigger cost is the snapshot subsystem. Citus Data’s October 2020 benchmark shows TPS degradation from idle connections alone (pgbench, direct connections, 2x Xeon Gold 5215, 192 GiB RAM, Debian Sid, kernel 5.8.5):

Idle connections	TPS (48 active workers)	Drop
0	1,032,435	—
1,000	902,109	−12.6%
5,000	702,680	−31.9%
10,000	521,558	−49.5%

Root cause: GetSnapshotData() burns ~50% of CPU at 5,000 idle connections. This is a fundamental Postgres scalability limit, not a configuration problem. In a 1,000-tenant deployment where each tenant keeps one idle connection, you’re already at −13% throughput. A connection pooler is not optional.

Pooler comparison

Architecture differs where it matters at scale. pgcat is multi-threaded (Rust/Tokio), while PgBouncer is single-threaded — a known ceiling as connection counts climb (pgcat docs, PgBouncer features). Supavisor is purpose-built for multi-tenant clusters and designed for 1M+ connections (Supavisor). For most teams below 500 tenants, PgBouncer is simpler to operate and that ceiling never matters.

# PgBouncer config for per-tenant databases
[databases]
tenant_acme   = host=db.internal dbname=tenant_acme pool_size=5 pool_mode=transaction
tenant_globex = host=db.internal dbname=tenant_globex pool_size=5 pool_mode=transaction
*             = host=db.internal pool_size=3  ; wildcard for dynamic tenant names

[pgbouncer]
pool_mode           = transaction
default_pool_size   = 5
max_db_connections  = 10      ; per-database cap regardless of client count
max_client_conn     = 10000   ; PgBouncer handles this cheaply (~2 kB/client, not 2 MiB)
server_idle_timeout = 60      ; close idle tenant connections quickly

Neon: database-per-tenant at serverless scale

The traditional objection to database-per-tenant — “you’d need a separate server per tenant” — no longer applies with Neon. Neon provisions a complete Postgres project in milliseconds, scales idle projects to zero compute cost, and supports up to 5,000 projects on the Business plan. Storage pricing dropped to $0.35/GB-month following the Databricks acquisition in 2025.

For teams that need per-tenant point-in-time recovery, compliance isolation (HIPAA, SOC 2 scope reduction), or dedicated compute, database-per-tenant on Neon is the 2026 answer. Neon explicitly recommends this model over schema-per-tenant for SaaS.

The operational cost is cross-tenant reporting. Separate databases require dblink, postgres_fdw, or an ETL pipeline to query across tenants. If your analytics layer needs to aggregate across all tenants frequently, factor that in.

For self-hosted separate-DB deployments, Railway offers managed Postgres and works well for smaller tenant fleets. Neither Railway nor Fly.io has native fleet-provisioning automation — you’d script per-tenant provisioning yourself via their APIs. Fly.io caps at 1 TB storage per cluster.

Cross-instance isolation

Separate databases on the same Postgres instance still share WAL, lock manager, and buffer pool (per the EDBT 2026 paper above). A noisy tenant on the same instance can spike latency for others via resource contention. True blast-radius isolation requires separate instances — or Neon, where each project runs on independent infrastructure.

Hybrid approaches

Tier-based sharding with Citus 12

Citus 12 lets you mix tenancy models by tier within one cluster. Free tenants use row-based isolation (shared schema, scales to 100M+ tenants). Paid tenants get schema-per-tenant sharding (up to 10,000 per cluster). Enterprise tenants get dedicated databases. One command activates schema-based sharding: SET citus.enable_schema_based_sharding TO on;. If you’re already running Citus, this is the graduated path as your customer base segments.

Nile’s virtual tenant databases

Nile takes a different approach: a single Postgres-compatible database with logical tenant isolation enforced at the application layer. They built pg_karnak, a Postgres extension using two-phase commit (PREPARE TRANSACTION / COMMIT PREPARED) for atomic DDL across virtual tenant databases. The constraint: primary keys must include tenant_id as the leading column. It’s a managed service with its own operational surface — interesting for teams that want the simplicity of a shared schema but stronger logical boundaries.

ORM support matrix

	Prisma	Drizzle	TypeORM
Multiple schemas	GA in v6.13.0 (Jul 30, 2025)	Native `pgSchema`	`@Entity({ schema })`, PG and MSSQL only
Dynamic schema switching	Not supported	App-layer routing	Not supported; issue #10619 closed “not planned”
RLS in schema definition	No (issue #12735, open since Apr 2022)	First-class since v0.36.0 (Oct 30, 2024)	None
RLS helpers	Client extension (README: “not for production”)	`pgPolicy()`, `pgRole()`, `crudPolicy()`, Neon + Supabase integrations	None
Migration support for RLS	No (raw SQL or Atlas Pro)	Yes — drizzle-kit manages roles + policies	No
TypeScript RLS policy type-safety	No	Yes	No

Drizzle has the strongest multi-tenancy story in 2026. RLS became first-class in v0.36.0 (October 2024), including pgPolicy(), pgRole(), and provider integrations for Neon and Supabase. drizzle-kit generates and manages role and policy migrations. One warning: v1.0.0-beta.2 (February 2025) changed drizzle-kit push/pull to manage all schemas in your code by default. Multi-schema operators must audit their schemaFilter config before upgrading.

import { pgTable, pgRole, pgPolicy } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

const currentTenantId = sql`(select current_setting('rls.tenant_id'))::uuid`;

export const orders = pgTable('orders', {
  id: integer().primaryKey(),
  tenantId: uuid('tenant_id').notNull(),
}, (table) => [
  pgPolicy('tenant_isolation', {
    as: 'permissive',
    for: 'all',
    using: sql`${table.tenantId} = ${currentTenantId}`,
    withCheck: sql`${table.tenantId} = ${currentTenantId}`,
  }),
]);

Prisma multiSchema is GA since v6.13.0 (July 30, 2025) for Postgres, CockroachDB, and SQL Server. RLS remains open issue #12735 labeled “has-stopgap” — the official workaround is a client extension that the README explicitly says is “not intended for production.”

TypeORM 1.0 shipped May 2026. No new multi-tenancy or RLS features. The connection pool problem — new pool per DataSource prevents tenant-routing without N independent pools — is closed as “not planned” in issue #10619.

Verdict

Start with RLS if you’re using Drizzle. It has first-class tooling, scales without catalog overhead, and the CVE risk is manageable if you run current Postgres minor versions, use security_invoker views, and always SET LOCAL for context. Write policies with (select function()) and index the tenant column from day one.

Use schema-per-tenant if you’re on Prisma (multiSchema is GA since v6.13.0), you need per-tenant pg_dump restores, or your compliance requires schema-level separation. Stay under 500 tenants in plain Postgres. Use Atlas or Flyway Pattern 2 for migrations. Run PgBouncer in session mode.

Use separate databases on Neon if:

You need per-tenant PITR, HIPAA/SOC 2 isolation, or dedicated compute
You’re building a product where one tenant’s breach must not be in scope for others
You’re willing to pay the cross-tenant reporting cost

Go hybrid (Citus tier-based sharding) if you already run Citus and your tenant base spans multiple size tiers.

Q1: How many tenants (expected max)?
  ├─ < 500        → Schema-per-tenant or RLS. Go to Q2.
  ├─ 500–10,000   → Schema needs Citus; RLS scales natively. Go to Q2.
  └─ > 10,000     → RLS or Neon database-per-tenant only.

Q2: Compliance isolation required? (HIPAA, SOC 2 scope, per-tenant PITR)
  ├─ Yes → Separate databases on Neon. Done.
  └─ No  → Continue.

Q3: Cross-tenant reporting/analytics needed frequently?
  ├─ Yes → RLS or schema-per-tenant (same database; admin queries bypass RLS).
  └─ No  → Continue.

Q4: ORM?
  ├─ Drizzle  → RLS (first-class; drizzle-kit manages migrations).
  ├─ Prisma   → Schema-per-tenant (multiSchema GA v6.13.0); RLS is workaround-only.
  └─ TypeORM  → Schema-per-tenant via @Entity({schema}); no RLS or dynamic switching.

Q5: Enterprise tenants need isolation from free-tier tenants?
  └─ Yes → Hybrid: RLS for free tier, schema or DB for enterprise (Citus 12).

Caveats

The RLS performance numbers (171 ms vs 9 ms) come from a community benchmark on Supabase’s infrastructure, not a controlled study. They’re directionally reliable; your hardware will differ.
No controlled head-to-head RLS vs. no-RLS benchmark on identical hardware and identical queries appeared in primary literature. The “~30% overhead” claim in community forums is anecdotal — don’t put it in an architecture document.
pgcat’s sharding features are marked “experimental” in the current config docs.
Neon project limits (5,000 on Business) and pricing ($0.35/GB-month storage) are accurate as of August 2025; both can change.
Neon and Railway links above are affiliate links — toolchew earns a commission if you sign up through them, at no cost to you.