Postgres Multitenancy in 2025: RLS vs Schemas vs Separate DBs—Performance, Isolation, and Migration Playbook

Choosing the right tenancy model in Postgres isn’t a one-time decision—it’s a sequence of choices you’ll refine as your product grows. In 2025, the ecosystem around Postgres (PgBouncer, Citus, logical replication filters, managed services) is mature enough that you can start simple and evolve with a dependable migration path. But the trade-offs across performance, isolation, and operational complexity remain very real.

This article is an opinionated, technical guide for engineering leaders and staff-plus ICs deciding between:

• RLS (row-level security) in shared tables
• Schema-per-tenant (multiple schemas in one database)
• Database-per-tenant (one DB per tenant)

We’ll compare the models on connection pooling, query plans and statistics, per-tenant indexing, sharding with Citus, noisy-neighbor control, encryption, and safe migrations. We’ll end with a pragmatic decision checklist and a migration playbook for moving between models.

My short take:

• Most SaaS teams should start with RLS on shared, hash-partitioned tables and add Citus or native partitioning as they scale. It balances simplicity, performance, and operational sanity, while keeping a clean path to peel off heavy tenants later.
• Schema-per-tenant makes selective per-tenant indexing and versioning easy, but it does not scale well to large tenant counts, and it complicates migrations and connection pooling.
• Database-per-tenant is the cleanest for isolation and customer promises (like key destruction and bespoke SLAs). It’s also the most operationally expensive. Reserve it for high-value tenants or strict regulatory demands.

The models in one minute

• RLS (Row-Level Security): All tenants share the same tables; a tenant_id column plus RLS policies restrict visibility. Operationally simplest for many tenants. Requires careful indexing and planner awareness. Per-tenant indexing is limited.
• Schema-per-tenant: One logical copy of the schema per tenant (tenant_a.users, tenant_b.users). Lets you tailor indexes and run per-tenant migrations. Overhead grows with every schema and object; metadata bloat and migration fan-out become pain points.
• Database-per-tenant: Each tenant gets its own database (or even cluster). Strong isolation, independent extensions/indexes/config, easier noisy-neighbor control—at the cost of more connections, more pool fragmentation, and more orchestration.

Connection pooling, concurrency, and the cost of backends

Each Postgres session is a process with non-trivial memory (~5–10+ MB typical baseline before work_mem). At scale, direct connections do not work; you need a pooler like PgBouncer.

• PgBouncer modes: session, transaction, statement. Transaction pooling yields the highest density but places constraints on session-local state.
• RLS and pooling: RLS policies often reference current_setting('app.tenant_id') or CURRENT_USER. In transaction pooling, you must ensure the tenant context is set at the beginning of the transaction and reset reliably.
  • Good pattern: wrap each request in a transaction and use SET LOCAL or set_config('app.tenant_id', '<id>', true) at the start. The third parameter true makes it LOCAL to the current transaction.
  • Avoid relying on per-session state (e.g., SET without LOCAL) in transaction pooling; it can bleed across requests.
• Schema-per-tenant and search_path: You’ll set search_path to tenant_schema, public per request. In transaction pooling, do SET LOCAL search_path = ... inside a transaction. Always validate and quote schema names to prevent SQL injection via search_path.
• DB-per-tenant: PgBouncer pools are per (db, user) pair. Many small databases multiply pools and idle connections. With thousands of tenants, you’ll either shard across many Postgres clusters or accept higher pooling overhead.

Rule of thumb:

• If you expect 10k–100k+ concurrent tenants with modest traffic each, RLS performs best with a pooler in transaction mode.
• If you manage 10–500 high-value tenants and want per-tenant customizations, schemas or databases can be feasible.

Query plans, statistics, and the RLS filter

The planner’s view of your data distribution determines performance. RLS adds a tenant_id filter to every query on protected tables. The overhead of the extra predicate is usually small if you have appropriate composite indexes and, ideally, partition pruning.

Key considerations:

• Composite indexes: For RLS, you almost always want indexes like (tenant_id, created_at), (tenant_id, email), or (tenant_id, status, created_at). This allows the planner to use the tenant filter and still satisfy selective predicates. A lone index on created_at will not help a tenant-scoped query.
• Statistics skew: With one giant table for all tenants, planner statistics are global by default. Heavy skew (one tenant much larger than others) can lead to suboptimal plans. Two mitigation patterns:
  • Native partitioning by hash on tenant_id. Each partition maintains its own statistics, and the planner can prune partitions at plan time if the tenant_id is a constant or a stable current_setting.
  • Extended statistics (multivariate stats) for correlated columns can help, but partitioning tends to be more robust.
• Prepared statements and plan caching: The server may use generic plans after a few executions, which can mask per-tenant selectivity differences. Consider:
  • Using plan_cache_mode = force_custom_plan in hot paths where tenant-specific selectivity matters, or
  • Adding stable constants (e.g., set_config('app.tenant_id', ...) and referencing current_setting in queries) so that partition pruning can happen at plan time. Ensure the value is known to the planner as stable/const within the transaction.

Example: partitioning plus RLS

sql
-- One logical table, hash partitioned by tenant_id, with RLS
CREATE TABLE invoices (
  tenant_id uuid NOT NULL,
  invoice_id uuid PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  status text NOT NULL,
  amount_cents bigint NOT NULL,
  -- other columns
  CHECK (tenant_id IS NOT NULL)
) PARTITION BY HASH (tenant_id);

-- Create N partitions to start; you can ATTACH more later
CREATE TABLE invoices_p0 PARTITION OF invoices FOR VALUES WITH (MODULUS 32, REMAINDER 0);
-- ... repeat p1..p31

-- RLS Policy
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- ensures owner sees RLS too

-- Policy uses a GUC set per-request/transaction
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Critical composite indexes per partition
CREATE INDEX ON invoices_p0 (tenant_id, created_at DESC);
CREATE INDEX ON invoices_p0 (tenant_id, status, created_at DESC);
-- repeat on each partition (consider a helper function or template)

With this setup, a query like SELECT ... FROM invoices WHERE status = 'open' ORDER BY created_at DESC LIMIT 50 will prune to a single partition and use the composite index for the current tenant.

Per-tenant indexing strategies

• RLS (shared tables): You cannot create a separate index just for tenant ABC without impacting the global namespace. You can create partial indexes (WHERE tenant_id = 'ABC'), but this does not scale if you consider thousands of tenants. A pragmatic approach:

  • Use generic composite indexes that cover most tenant workloads.
  • Partition by tenant_id (hash) to reduce bloat and keep index sizes healthy.
  • Selectively add partial indexes for top-1 or top-5 heavy tenants—only as temporary relief while planning to peel them off to a dedicated schema/DB if they keep growing.

• Schema-per-tenant: You can add a custom index in tenant_abc.users without affecting others. This is powerful but increases operational complexity: you need tooling to roll out or remove indexes per tenant and to keep track of drift.

• DB-per-tenant: Full freedom. You can tune tables, indexes, and even extensions per tenant. Use this advantage when your top customers have unique, heavy workloads.

Citus and sharding: multi-tenant at scale

Citus (open source and managed variants) lets you distribute tables across workers and route queries by a distribution column.

• The canonical multi-tenant pattern is to distribute by tenant_id:

sql
-- On the Citus coordinator
SELECT create_distributed_table('invoices', 'tenant_id');
SELECT create_reference_table('currencies');  -- small tables replicated everywhere

• Co-location: When all tenant-scoped tables are distributed by tenant_id, joins within a tenant are shard-local, so queries route to a single worker and avoid cross-shard repartitioning.
• RLS with Citus: You can still use RLS policies referencing tenant_id. Ensure the coordinator can push down predicates to workers. Keep policies simple and deterministic.
• Schema-per-tenant + Citus: Feasible but fiddlier; each tenant schema has tables that all must be distributed identically. Routing by search_path can work, but routing by tenant_id is simpler.
• DB-per-tenant + Citus: Typically unnecessary; you’ve already achieved isolation at the DB boundary.

If you anticipate many tenants and want to scale writes/reads horizontally, RLS + distributed-by-tenant_id is the most natural path. It also keeps migrations more uniform.

Noisy-neighbor control and isolation

Security isolation, as well as resource isolation, varies by model.

• RLS security isolation:

  • Use ALTER TABLE ... FORCE ROW LEVEL SECURITY so even table owners are subject to policies.
  • Prefer simple USING predicates like tenant_id = current_setting('app.tenant_id')::uuid.
  • Avoid leaky functions in policies; only superusers can declare LEAKPROOF, and you should rely on built-in operators.
  • Test RLS thoroughly with regression tests that try cross-tenant exfiltration via joins and subqueries.

• Schema-per-tenant security isolation:

  • Restrict each tenant’s role to its schema and revoke default PUBLIC privileges on the database. Do not rely solely on search_path; also use GRANT/REVOKE on schemas/tables.
  • Be careful with SECURITY DEFINER functions which can bypass privilege checks. Consider SECURITY INVOKER for tenant-facing functions.

• DB-per-tenant isolation:

  • Strongest security boundary within one cluster. For even stronger isolation (CPU/IO), place big tenants on their own VM/container or managed instance.

• Resource isolation (CPU, memory, IO) in Postgres isn’t first-class per tenant. Practical tools:

  • Per-role GUCs with ALTER ROLE SET (e.g., work_mem, statement_timeout) and ALTER ROLE IN DATABASE ...
  • statement_timeout, idle_in_transaction_session_timeout, and idle_session_timeout to prevent bad-behaving sessions.
  • PgBouncer limits (max_user_connections) to cap concurrency per tenant.
  • OS/container-level cgroups if you run your own clusters.
  • If you need hard isolation, DB-per-tenant on separate hardware (or a managed instance) is the reliable answer.

Encryption: at rest, in transit, and per-tenant keys

• In transit: Always use TLS. Managed services provide this by default.
• At rest: Cloud Postgres typically encrypts storage per cluster using a KMS-managed key. Per-database keys are uncommon; per-tenant keys at the storage layer are not generally supported in vanilla Postgres.
• Application or row-level encryption for sensitive columns:

sql
-- Example with pgcrypto
-- Key management stays in your app/KMS; pass a per-tenant key to the DB only when needed
SELECT pgp_sym_encrypt('4111111111111111', :tenant_key, 'cipher-algo=aes256') AS enc_cc;
SELECT pgp_sym_decrypt(enc_cc, :tenant_key) FROM payments WHERE tenant_id = :tenant;

If you must support customer-managed keys with provable key destruction, DB-per-tenant plus app-layer encryption is the straightforward path. Dropping the database plus revoking the KMS key is a cleaner story than trying to rotate row-level keys across millions of rows in a shared table.

Safe migrations in each model

The same golden rules apply across models:

• Never block writes for long. Prefer online changes: CREATE INDEX CONCURRENTLY, NOT VALID constraints followed by VALIDATE CONSTRAINT, and adding nullable columns first.
• Use the expand-and-contract pattern: deploy code that can read/write old and new shapes; backfill; switch reads; then remove old fields/indexes.
• Backfill in small batches with checkpoints and progress tracking. Consider synchronous_commit = off for the backfill session (not globally) to trade small durability windows for speed.
• For large objects, throttle to protect the primary and avoid replication lag.

Specifics per model:

• RLS (shared tables):

  • Changes are global and applied once, which is simpler. But you must keep RLS policies in sync with schema changes.
  • When introducing a new tenant context (e.g., moving from user-scoped to tenant-scoped), stage policies carefully and test with FORCE ROW LEVEL SECURITY enabled.

• Schema-per-tenant:

  • Migrations fan out across N schemas. Write migration code that loops over schemas, e.g.:

sql
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT nspname FROM pg_namespace WHERE nspname LIKE 't_%'
  LOOP
    EXECUTE format('ALTER TABLE %I.accounts ADD COLUMN IF NOT EXISTS locale text', r.nspname);
  END LOOP;
END$$;

• Build idempotent migrations. Record per-tenant migration versions to resume safely.

• DB-per-tenant:

  • Migrations are fully independent; you need an orchestrator to roll out changes in waves, monitor failures, and rollback.
  • CREATE INDEX CONCURRENTLY across thousands of DBs can saturate I/O—stagger and limit concurrency.

Provisioning and routing patterns

• RLS: On request start, set the tenant context and begin a transaction.

sql
BEGIN;
SET LOCAL app.tenant_id = '0d9d...';
-- queries here see only that tenant
COMMIT;

• Schema-per-tenant: Resolve the schema and set search_path safely.

sql
-- server-side sanitization
SELECT format('%I,public', 't_' || replace(:tenant_slug, '-', '_')) AS sp;
SET LOCAL search_path = :sp;

• DB-per-tenant: The app routes to a connection string derived from the tenant mapping. For PgBouncer, ensure pools are sized appropriately per tenant and capacity-plan for headroom.

Migration playbook: moving between tenancy models

The highest-value capability you can build is the ability to move tenants between isolation levels with minimal downtime. Postgres now gives you solid primitives: logical replication with row filtering (Postgres 15+), FDWs, and robust online DDL.

RLS (shared) to schema-per-tenant

Goal: Peel off a heavy tenant to its own schema without stopping the world.

Steps:

1. Create schema and tables

sql
CREATE SCHEMA t_acme;
CREATE TABLE t_acme.invoices (LIKE public.invoices INCLUDING ALL);
-- repeat for other tables; keep the same indexes where useful

2. Start logical replication from shared tables to the new schema using row filters. For a one-database move, use a staging database or FDW if you must remain inside one DB. With logical replication across DBs:

sql
-- On source DB
CREATE PUBLICATION pub_acme FOR TABLE public.invoices WHERE (tenant_id = 'acme-uuid');
-- Add other tables similarly, respecting FK order for initial copy

-- On target DB (could be same cluster, different DB)
CREATE SUBSCRIPTION sub_acme
  CONNECTION 'host=... dbname=target ...'
  PUBLICATION pub_acme
  WITH (copy_data = true);

3. Dual-write or briefly quiesce writes. If you cannot pause writes, add triggers on source to mirror writes to target during cutover.
4. Flip routing for ACME to the new schema (by setting search_path in the app for that tenant).
5. Verify counts and checksums; drop row subset from shared tables once you’re confident.

Notes:

• If staying inside one DB without logical replication, you can move rows with INSERT ... SELECT WHERE tenant_id = ... in batches and use triggers to catch stragglers during the move window.

Schema-per-tenant to DB-per-tenant

1. Provision a new database for the tenant; apply base schema.

sql
CREATE DATABASE acme_db TEMPLATE template_postgis;  -- if you need extensions
-- or run your migration tool to create schema

2. Dump and restore the tenant schema only:

bash
pg_dump --schema=t_acme --no-owner --no-privileges source_db | \
  psql acme_db

3. Fix ownerships, GRANTs, and search_path. Recreate sequences with correct values if needed (pg_dump handles this generally).
4. Point the application routing for ACME at acme_db.
5. Optionally set per-DB GUCs (e.g., work_mem) for that tenant’s workload.

RLS (shared) to DB-per-tenant

Prefer logical replication with row filters (PG 15+):

1. Create the tenant DB and base schema.
2. Create publications on source with WHERE tenant_id = ... clauses.
3. Create subscriptions on the tenant DB with copy_data = true for initial snapshot.
4. Once caught up, briefly pause writes for the tenant, cut over application traffic, then drop their rows from the shared tables.

This approach minimizes downtime and avoids heavy locks on the source.

DB-per-tenant back to shared (RLS)

Sometimes consolidation reduces cost and simplifies operations.

1. Create a consolidated RLS table partitioned by tenant_id.
2. Use postgres_fdw from the consolidated DB to each tenant DB:

sql
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER t_acme_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (...);
CREATE USER MAPPING FOR app_user SERVER t_acme_srv OPTIONS (...);
IMPORT FOREIGN SCHEMA public LIMIT TO (invoices) FROM SERVER t_acme_srv INTO staging_acme;

INSERT INTO public.invoices
SELECT 'acme-uuid'::uuid AS tenant_id, *
FROM staging_acme.invoices
ON CONFLICT DO NOTHING;  -- if you arranged keys globally unique

3. Validate row counts, create indexes (CONCURRENTLY) as needed, then re-point routing for ACME to the shared DB and drop their dedicated DB if desired.

Operational pitfalls and hard-won tips

• RLS and SECURITY DEFINER: SECURITY DEFINER functions run with the owner’s privileges. If the owner bypasses RLS, these functions may, too. FORCE ROW LEVEL SECURITY helps, but be explicit and test. Prefer SECURITY INVOKER unless you need elevated privileges.
• Search path safety: Treat tenant identifiers as untrusted input. Quote identifiers with format('%I', ...) and never concatenate raw tenant strings into search_path.
• Many schemas: Thousands of schemas increase pg_catalog size and can slow planning and introspection. Operations like pg_dump, schema-wide grants, and extension updates take longer.
• Many databases: Each DB multiplies connection pools and maintenance jobs (VACUUM/ANALYZE). You’ll also contend with global limits like max_wal_senders for replication.
• VACUUM and bloat: Shared hot tables under RLS can get large. Partitioning reduces dead tuple accumulation per partition and makes VACUUM/REINDEX more surgical.
• Index-only scans: Ensure your RLS predicate aligns with the index. An index on (tenant_id, col) plus a covering INCLUDE (...) can make hot queries index-only if the visibility map cooperates.
• Timeouts as seatbelts: Set statement_timeout low in OLTP paths. For background jobs, override it higher via SET LOCAL.

Recommended defaults by stage

• Pre–product-market fit (few tenants, small data):

  • RLS on shared tables, no partitioning yet. Get the composite indexes right. Use PgBouncer in transaction mode.

• Growth (hundreds to thousands of tenants, GBs–TBs data):

  • Add hash partitioning on tenant_id (or move to it). Keep composite indexes per partition.
  • If you outgrow one node or want read scale, introduce Citus and distribute by tenant_id.
  • Peel off the top 1–3 heavy tenants to their own schema/DB if they dominate cost or need bespoke features.

• Enterprise scale (top-tier tenants with SLAs/compliance):

  • DB-per-tenant for the largest accounts; optionally dedicated hardware.
  • App-layer encryption with tenant-specific keys if you need key revocation guarantees.
  • Orchestrated migrations and per-tenant maintenance windows.

Concrete code snippets

RLS policy with transaction-scoped tenant ID

sql
-- Set once per request (inside a transaction) in the app
BEGIN;
SET LOCAL app.tenant_id = '9a1e7a8b-...'::text;
-- Now all RLS USING clauses can see the tenant UUID
COMMIT;

Safer search_path switching for schema-per-tenant

sql
-- Compute a safe search_path value server-side
WITH x AS (
  SELECT format('%I,public', 't_' || regexp_replace(:tenant_slug, '[^a-zA-Z0-9_]', '_', 'g')) AS sp
)
SELECT set_config('search_path', (SELECT sp FROM x), true);

Global-to-tenant hash partitioning migration

sql
-- Create the partitioned table
CREATE TABLE accounts_new (
  tenant_id uuid NOT NULL,
  account_id uuid PRIMARY KEY,
  email citext NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY HASH (tenant_id);

-- Attach partitions
DO $$
DECLARE i int;
BEGIN
  FOR i IN 0..31 LOOP
    EXECUTE format('CREATE TABLE accounts_p%s PARTITION OF accounts_new FOR VALUES WITH (MODULUS 32, REMAINDER %s);', i, i);
    EXECUTE format('CREATE INDEX ON accounts_p%s (tenant_id, email);', i);
  END LOOP;
END$$;

-- Backfill in batches
INSERT INTO accounts_new
SELECT tenant_id, account_id, email, created_at
FROM accounts
ORDER BY created_at
LIMIT 10000;  -- repeat until done

-- Swap names atomically in a transaction with short locks
BEGIN;
ALTER TABLE accounts RENAME TO accounts_old;
ALTER TABLE accounts_new RENAME TO accounts;
COMMIT;

Logical replication with row filtering (PG 15+)

sql
-- On source
CREATE PUBLICATION pub_tenant FOR TABLE public.orders WHERE (tenant_id = '9a1e...');

-- On target
CREATE SUBSCRIPTION sub_tenant
CONNECTION 'host=... dbname=... user=... password=... sslmode=require'
PUBLICATION pub_tenant WITH (copy_data = true);

PgBouncer transaction pooling and tenant context

ini
[databases]
app = host=postgres port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
ignore_startup_parameters = extra_float_digits
server_reset_query = DISCARD ALL

# In app code: always BEGIN; SET LOCAL app.tenant_id = ...; COMMIT per request

Decision checklist

Answer these and you’ll have your default:

• How many tenants today? In 12 months? In 36 months?
• What’s your top-tenant Pareto curve? Are the top 1–3 tenants >50% of load?
• Do you need customer-managed keys or evidence of key destruction? If yes, lean DB-per-tenant + app-layer encryption.
• Are you planning for horizontal scale-out? If yes, RLS + Citus/distribution by tenant_id is the cleanest.
• How much operational weight can your team carry? Schema-per-tenant and DB-per-tenant need a migration/orchestration platform.
• Do you need per-tenant custom indexes or extensions? Schema/DB models make that straightforward.

My default recommendation for most SaaS teams in 2025:

• Start with RLS on shared, hash-partitioned tables with composite indexes, PgBouncer in transaction mode, and a clear convention for app.tenant_id.
• Keep the door open to peel off heavy tenants. Build a small toolkit for: (a) logical replication with row filters, (b) schema templating, (c) migration orchestration.
• Add Citus when you need horizontal write/read scaling.
• Use app-layer encryption for sensitive fields; don’t rely on per-DB storage keys for tenant isolation.

References and further reading

• PostgreSQL Row Security Policies (RLS): https://www.postgresql.org/docs/current/ddl-rowsecurity.html
• PostgreSQL Partitioning: https://www.postgresql.org/docs/current/partitioning.html
• PostgreSQL Logical Replication (row filtering in PG 15+): https://www.postgresql.org/docs/current/logical-replication.html
• PgBouncer docs: https://www.pgbouncer.org/config.html
• Citus distributed tables: https://docs.citusdata.com/
• Postgres constraints and VALIDATE: https://www.postgresql.org/docs/current/sql-altertable.html
• Extended statistics: https://www.postgresql.org/docs/current/planner-stats.html

---

You can build a multitenant Postgres that grows with your business if you plan for migrations from the start. RLS gives you velocity and scale, partitioning protects the planner, Citus gives you sharding, and logical replication lets you move the few tenants who need more. That’s a robust, low-drama path through 2025 and beyond.