Schema-per-Tenant vs RLS: When to Use Which

There are three common ways to isolate tenant data in PostgreSQL: a discriminator column, Row-Level Security (RLS), and schema-per-tenant. I've implemented all three. The first one is too simple to get wrong. The other two are where the real trade-offs live.

This post compares RLS and schema-per-tenant from direct experience building nest-tenant — a multi-tenant isolation library for NestJS that supports both strategies through pluggable adapters. Not theory. Not benchmarks from a blog post. Actual production patterns and the pain points that come with each.

The Three Common Strategies

Before diving into the comparison, here's the landscape:

Discriminator column — Every table has a tenant_id column. Every query includes WHERE tenant_id = ?. Simple, no PostgreSQL-specific features needed. But every query must remember the filter, and a single missed WHERE clause leaks data across tenants.

Row-Level Security (RLS) — PostgreSQL enforces tenant filtering at the database level. You define policies, and the database adds the WHERE clause for you. Even if your application code forgets, the database won't.

Schema-per-tenant — Each tenant gets their own PostgreSQL schema. Tables are identical across schemas, but completely isolated. Queries target the tenant's schema via search_path.

The discriminator column approach works fine for simple cases, but it puts the burden of isolation entirely on the application. RLS and schema-per-tenant push that burden to the database — which is where it belongs.

How RLS Works

RLS uses three PostgreSQL features together: session variables, policies, and forced enforcement.

-- 1. Create a policy that filters by tenant
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
 
-- 2. Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
-- 3. Force RLS even for table owners
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

At query time, the application sets the tenant context and the database handles filtering:

BEGIN;
-- SET LOCAL scopes to this transaction only (pool-safe)
SET LOCAL app.tenant_id = 'tenant-abc';
 
-- PostgreSQL automatically adds: WHERE tenant_id = 'tenant-abc'
SELECT * FROM orders;
COMMIT;

The FORCE ROW LEVEL SECURITY is critical. Without it, table owners bypass RLS policies entirely — a common misconfiguration that silently disables isolation.

Using SET LOCAL instead of SET is equally critical. SET persists on the connection and poisons shared pools. SET LOCAL is scoped to the transaction and auto-clears on commit or rollback.

How Schema-per-Tenant Works

Schema isolation uses PostgreSQL's search_path to route queries to tenant-specific schemas:

-- Create a schema for each tenant
CREATE SCHEMA tenant_abc;
CREATE SCHEMA tenant_xyz;
 
-- Create identical tables in each schema
CREATE TABLE tenant_abc.orders (id uuid, amount numeric, created_at timestamptz);
CREATE TABLE tenant_xyz.orders (id uuid, amount numeric, created_at timestamptz);

At query time, switch the search path:

BEGIN;
SET LOCAL search_path TO tenant_abc, public;
 
-- This hits tenant_abc.orders — no tenant_id column needed
SELECT * FROM orders;
COMMIT;

No policies. No session variables (besides search_path). Each tenant's data is physically separated into different schemas. A query in one schema literally cannot see tables in another.

When RLS Wins

RLS is the better choice when:

You have a shared schema. All tenants share the same tables. Migrations run once. Indexes are shared. Connection pooling is simpler because every connection can serve any tenant — just change the session variable.

Your queries are simple. RLS policies add a filter to every query. For straightforward SELECT, INSERT, UPDATE, DELETE operations, this overhead is negligible. PostgreSQL's query planner handles it well, especially with a proper index on tenant_id.

You have many tenants. If you have 10,000 tenants, you do not want 10,000 schemas. Each schema means separate tables, separate indexes, separate statistics. pg_dump, vacuuming, and catalog queries all slow down as schema count grows.

You want simpler operations. One schema means one set of migrations, one backup strategy, one monitoring setup. RLS adds complexity at the policy level but keeps everything else simple.

-- One migration, applied once, works for all tenants
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;
 
-- One index, shared across all tenants
CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status);

When Schema-per-Tenant Wins

Schema isolation is the better choice when:

You need regulatory separation. Some industries (healthcare, finance, government) require that tenant data be provably isolated — not just filtered, but physically separated. Schema-per-tenant gives you that proof. Each schema can even live on a separate tablespace or be backed up independently.

You need per-tenant migrations. If different tenants are on different versions of your schema (common in enterprise SaaS with staged rollouts), schema isolation lets you migrate tenant-by-tenant. With RLS, every migration affects every tenant simultaneously.

You need independent scaling. Schema isolation lets you move individual tenants to dedicated databases when they outgrow shared infrastructure. The application code doesn't change — you just point the tenant's connection to a different host.

You have few, large tenants. If you have 50 enterprise customers each with millions of rows, separate schemas give you independent vacuuming, independent index statistics, and independent query plans. PostgreSQL's planner works better when it has accurate statistics per-tenant rather than aggregated across all tenants.

-- Per-tenant migration: only tenant_abc gets the new column
ALTER TABLE tenant_abc.orders ADD COLUMN priority integer DEFAULT 0;
 
-- Per-tenant index: optimize for tenant_abc's specific query patterns
CREATE INDEX idx_orders_priority ON tenant_abc.orders (priority) WHERE priority > 0;

How nest-tenant Supports Both

nest-tenant doesn't force a choice. It provides a pluggable TenantIsolationStrategy interface that lets you configure isolation per-module:

// RLS strategy — sets session variable per transaction
@Module({
  imports: [
    TenantModule.forRoot({
      isolation: {
        strategy: 'rls',
        variable: 'app.tenant_id',
      },
    }),
  ],
})
export class AppModule {}
 
// Schema strategy — switches search_path per transaction
@Module({
  imports: [
    TenantModule.forRoot({
      isolation: {
        strategy: 'schema',
        schemaPrefix: 'tenant_',
      },
    }),
  ],
})
export class AppModule {}

Under the hood, both strategies use the same pattern: middleware extracts the tenant identity, AsyncLocalStorage propagates it through the request, and the ORM adapter applies isolation inside a transaction using SET LOCAL. The only difference is what gets set — a session variable for RLS, or search_path for schema isolation.

This matters because the choice between RLS and schema-per-tenant isn't always clear at the start of a project. Teams that start with RLS sometimes need to migrate specific tenants to dedicated schemas as they grow. A pluggable strategy makes that migration a configuration change, not a rewrite.

Decision Framework

Use this checklist when choosing:

Start with RLS unless you have a specific reason for schema isolation. It's simpler to operate, simpler to scale horizontally, and covers the vast majority of multi-tenant use cases. Move to schema-per-tenant for specific tenants that need it — don't over-engineer the isolation layer before you know you need it.

The Trade-Off Nobody Talks About: Debugging

RLS is harder to debug than schema isolation. When a query returns unexpected results, you have to check:

1. Is app.tenant_id set correctly?
2. Is the RLS policy correct?
3. Is FORCE ROW LEVEL SECURITY enabled?
4. Are you connected as a role that bypasses RLS (superuser, table owner without FORCE)?

With schema isolation, you can connect to a specific schema and run queries directly. The data is right there, unfiltered. No policies to reason about, no session variables to verify.

This debugging advantage matters more than you'd think. In production incidents, the difference between "query the schema directly" and "reconstruct the exact session state to reproduce the RLS behavior" can be the difference between a 5-minute fix and a 2-hour investigation.

The Rule

For multi-tenant PostgreSQL:

• Default to RLS — simpler to operate, scales to thousands of tenants, one migration path
• Use schema-per-tenant when you need regulatory isolation, per-tenant migrations, or independent scaling
• Never mix strategies without a clean abstraction layer — a pluggable adapter saves you from coupling application code to isolation mechanics
• Always use SET LOCAL — regardless of strategy, session-scoped SET is unsafe on pooled connections
• Test both paths — isolation bugs are silent until they're catastrophic

nest-tenant provides both RLS and schema-per-tenant strategies as pluggable adapters, with ORM integration for Drizzle and Prisma, AsyncLocalStorage context propagation, and CLI tooling. See the project page or the source on GitHub.