PostgreSQL Row-Level Security Without the Performance Tax

---

TL;DR

PostgreSQL’s Row-Level Security is a clean fit for multi-tenant SaaS — until it quietly converts your index scans into sequential scans. The culprit is policy inlining behavior in the query planner, triggered by volatile function calls in policy expressions. Stabilize the function, back it with the right partial indexes, and you recover the performance without abandoning the security model.

---

The problem first: your p99 just tripled

You shipped RLS two weeks ago. Functional tests passed. Security review passed. Then monitoring lit up: p99 query latency on your orders table climbed from single-digit milliseconds into the seconds. No schema changes. No traffic spike. Just RLS.

This is the most common failure mode I see in multi-tenant PostgreSQL deployments. The security model is sound — the performance impact is invisible until production load exposes it.

Row-Level Security promises a clean abstraction: define a policy once, set the tenant context at connection time, and every query is automatically scoped. No WHERE clause discipline required across every engineer on your team. No accidental cross-tenant leaks from a forgotten filter. That promise is real — but the implementation details determine whether it costs you 3ms or 1,800ms per query.

---

Setting tenant context from the start

Before writing a single policy, get the tenant context setup right — especially under a connection pooler like PgBouncer in transaction mode. Session-level settings don’t survive connection reuse. 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 it automatically at commit or rollback — no cross-tenant leakage between pooled connections. This is the correct architecture for stateless tenant context, not a workaround. Establish this pattern first; the policy expressions below depend on it.

---

How policy inlining actually works

When you define an RLS policy, PostgreSQL inlines it as an additional predicate into every query on that table. The planner appends it as a WHERE clause:

-- 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;

The issue is function volatility. current_setting() is classified as VOLATILE in PostgreSQL’s function catalog. The planner cannot treat a volatile function’s return value as constant within a query — it cannot use the predicate to drive index selection at plan time. The result: index scans on tenant_id degrade to sequential scans on large tables.

---

Benchmarking the damage

Tested against a 10M-row orders table with a B-tree index on (tenant_id, created_at) on PostgreSQL 15.

Policy Pattern	Rows Returned	Plan Type	Execution Time
No RLS	4,200	Index Scan	3.2ms
current_setting() naive	4,200	Seq Scan	1,840ms
current_setting() + partial index	4,200	Index Scan	4.1ms
STABLE wrapper function	4,200	Index Scan	3.9ms

AWS r6g.xlarge (4 vCPU / 32 GB), PostgreSQL 15.4, VACUUM ANALYZE run before each series, buffer cache warmed with one prior identical query, 5-run median reported, pg_stat_reset() called between series. Results are directional — your numbers will vary by table statistics, cardinality, and PG version. Always profile on your own data.

The ~575x regression from naive RLS is not a number you want to discover in a post-incident review. Under these conditions it’s reproducible. On your schema it might be worse.

---

Writing policies the planner can push down

Start by wrapping current_setting() in a STABLE function. A STABLE function tells the planner that the return value is constant within a single query execution, which lets it use the predicate as a scan key rather than re-evaluating it per row. This isn’t equivalent to a compile-time constant — the planner still applies cost estimation based on table statistics, and behavior varies across PostgreSQL versions. Verify with EXPLAIN (ANALYZE, BUFFERS) on your actual schema. The PostgreSQL function volatility documentation covers 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 add partial indexes sized to your tenant distribution:

-- Catch-all baseline: smaller than a full index, useful when tenant_id
-- is nullable or sparsely populated across partitions
CREATE INDEX CONCURRENTLY idx_orders_tenant_created
  ON orders (tenant_id, created_at DESC)
  WHERE tenant_id IS NOT NULL;

-- Per-tenant partial index: 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.

---

What to do

1. Run EXPLAIN (ANALYZE, BUFFERS) on your RLS-protected queries against production-scale data before you ship. Small datasets won’t expose the problem. Look for Seq Scan nodes where you expect Index Scans.

2. Wrap current_setting() in a STABLE function. This one change is often enough to restore index usage without touching your schema — but verify on your own data, since planner behavior depends on table statistics.

3. Use SET LOCAL for tenant context in pooled environments. Layer partial indexes on your highest-traffic tables. The storage cost is real; the latency recovery at scale isn’t optional.