PostgreSQL Row-Level Security Without the Performance Tax

What You Will Build

By the end of this tutorial, you will understand why naive RLS policies silently destroy index usage on multi-tenant PostgreSQL tables - and you will have the specific policy patterns and index strategies that recover that performance without abandoning the security model.

Here is the situation: you shipped Row-Level Security two weeks ago. Functional tests passed. Security review passed. Then p99 latency on your orders table climbed from single-digit milliseconds into the seconds. No schema changes. No traffic spike. Just RLS. Let me show you exactly why this happens and how to fix it.

Prerequisites

• PostgreSQL 15+ (patterns are directional for earlier versions - verify with EXPLAIN)
• A multi-tenant schema with a tenant_id column
• A connection pooler like PgBouncer in transaction mode
• Basic familiarity with EXPLAIN (ANALYZE, BUFFERS)

Step 1: Get Tenant Context Right Before Anything Else

Session-level settings do not survive connection reuse under a pooler. The correct pattern is SET LOCAL inside an explicit transaction:

BEGIN;
SET LOCAL app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';
SELECT * FROM orders WHERE status = 'pending';
COMMIT;

SET LOCAL scopes the setting to the current transaction and resets automatically at commit or rollback - no cross-tenant leakage between pooled connections. Establish this first. The policies below depend on it.

Step 2: Understand What the Planner Actually Sees

PostgreSQL inlines your RLS policy as an additional predicate into every query on the table:

-- Your policy
CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- What the planner sees for: SELECT * FROM orders WHERE status = 'pending'
SELECT * FROM orders WHERE status = 'pending'
AND tenant_id = current_setting('app.current_tenant_id')::uuid;

Here is the gotcha that will save you hours: current_setting() is classified as VOLATILE in PostgreSQL's function catalog. The planner cannot treat its return value as constant within a query - it cannot use the predicate to drive index selection at plan time. Index scans on tenant_id degrade to sequential scans.

Here is the benchmark on a 10M-row orders table with a B-tree index on (tenant_id, created_at), PostgreSQL 15, AWS r6g.xlarge - VACUUM ANALYZE before each series, 5-run median:

A ~575× regression. Results are directional - your numbers will vary by schema and table statistics. Always profile on your own data. But you do not want to discover this number in a post-incident review.

Step 3: Write Policies the Planner Can Push Down

Wrap current_setting() in a STABLE function. A STABLE function tells the planner the return value is constant within a single query execution, which lets it use the predicate as a scan key rather than re-evaluating per row. The PostgreSQL function volatility docs cover the formal semantics.

CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS uuid
LANGUAGE sql STABLE AS $$
SELECT current_setting('app.current_tenant_id')::uuid;
$$;

CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_tenant_id());

Then back it with the right partial indexes:

-- Baseline: useful when tenant_id is nullable or sparsely populated
CREATE INDEX CONCURRENTLY idx_orders_tenant_created ON orders (tenant_id, created_at DESC) WHERE tenant_id IS NOT NULL;

-- Per-tenant: smallest possible, fastest for high-volume tenants
CREATE INDEX CONCURRENTLY idx_orders_tenant_acme_created ON orders (created_at DESC) WHERE tenant_id = '550e8400-e29b-41d4-a716-446655440000';

Per-tenant partial indexes are dramatically smaller and faster for your largest tenants but carry maintenance overhead as tenant count grows. Profile first, then index precisely.

Gotchas

• Small datasets hide this completely. Run EXPLAIN (ANALYZE, BUFFERS) against production-scale row counts before you ship. Look for Seq Scan nodes where you expect Index Scans.
• STABLE is not a compile-time constant. The planner still applies cost estimation based on table statistics, and behavior varies across PostgreSQL versions. Confirm with EXPLAIN on your actual schema - do not assume the function change alone is sufficient.
• SET LOCAL is not optional under PgBouncer. Session-level SET works in dedicated connections but not in transaction-mode pooling. The docs do not make this obvious enough. SET LOCAL inside an explicit transaction is the only correct pattern.
• Per-tenant indexes have a storage cost. They pay off for high-traffic tenants at scale, but do not add them speculatively. Profile first.

Conclusion

Row-Level Security is the right abstraction for multi-tenant PostgreSQL. The security model holds - the performance impact is invisible until production load exposes it. The fix is three steps: use SET LOCAL for tenant context in pooled environments, wrap current_setting() in a STABLE function, and layer partial indexes on your highest-traffic tables.

Between long debugging sessions like this one, I have been using HealthyDesk to get break reminders - it is easy to spend three hours staring at query plans and forget to move. Worth having running in the background.

Run EXPLAIN (ANALYZE, BUFFERS) on your RLS-protected queries today. You may find a seq scan you did not know you had.