---
title: "PostgreSQL Row-Level Security Without the Performance Tax"
published: true
description: "RLS silently converts index scans to seq scans in multi-tenant Postgres. Here's the planner behavior and the policy patterns that recover performance."
tags: postgresql, performance, architecture, security
canonical_url: https://mvpfactory.co/blog/postgresql-rls-performance
---
## 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:
sql
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:
sql
-- 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:
| Policy Pattern | Plan Type | Execution Time |
|---|---|---|
| No RLS | Index Scan | 3.2ms |
| `current_setting()` naive | Seq Scan | 1,840ms |
| `current_setting()` + partial index | Index Scan | 4.1ms |
| `STABLE` wrapper function | Index Scan | 3.9ms |
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](https://www.postgresql.org/docs/current/xfunc-volatility.html) cover the formal semantics.
sql
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:
sql
-- 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](https://play.google.com/store/apps/details?id=com.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.
Top comments (0)