PostgreSQL RLS at scale: index traps and policy pitfalls
Meta description: PostgreSQL RLS policy inlining, current_setting() selectivity gaps, leaky views, and composite index design to prevent full-table scans in multi-tenant workloads.
Tags: backend architecture microservices saas productengineering
TL;DR
RLS is the right primitive for multi-tenant isolation at the database layer, but PostgreSQL’s query planner doesn’t understand your policy expressions the way it understands column predicates. It inlines the policy, struggles to estimate selectivity for runtime values, and either ignores your tenant index or picks a seq scan. Fix: composite indexes with tenant_id first, a STABLE wrapper function instead of raw current_setting(), and an explicit audit of every view for security_barrier. Miss any one of these and you’ll have correct isolation with catastrophic throughput under load.
The case for database-layer tenant isolation
At 10M rows and 5,000 tenants, a missed index on a policy expression can turn a 2ms lookup into a 4-second seq scan — under every concurrent request, for every tenant, simultaneously. That is the failure mode most teams discover in production rather than in load testing.
Application-layer multi-tenancy — filtering every query with WHERE tenant_id = $1 — is fragile in a different way. One missed WHERE clause, one ORM abstraction that silently drops the filter, and tenant A reads tenant B’s data. The breach is silent and the stack trace points nowhere useful.
Row-Level Security moves the enforcement into the database engine itself. The policy fires regardless of what the application sends. In production systems I’ve worked on, this has caught query regressions that would have leaked data in pre-RLS architectures. The tradeoff: you have to understand how the planner sees your policies, or you’ll pay for the isolation in query time.
How policy inlining works — and where it breaks
When you write:
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::uuid);PostgreSQL inlines this expression into every query that touches orders. A simple SELECT * FROM orders WHERE status = 'open' becomes:
SELECT * FROM orders
WHERE status = 'open'
AND tenant_id = current_setting('app.tenant_id')::uuid;The planner sees this compound predicate, but it cannot estimate the cardinality of current_setting(...) because it’s a runtime value with no statistics. It also can’t confirm the value is stable within a query, which degrades index selection confidence.
Run EXPLAIN (ANALYZE, BUFFERS) on any tenant-scoped query and watch for Seq Scan where you expect Index Scan. That’s the policy trap, live in production.
The selectivity estimation problem
The table below shows how planner behavior varies by predicate type:
| Predicate Type | Planner Behavior | Reliable Index Usage |
|---|---|---|
tenant_id = $1 (bind parameter) | Uses column statistics | Yes |
tenant_id = current_setting(...)::uuid | Opaque expression, no statistics | No — unpredictable |
tenant_id = current_user::uuid | Session-stable, partial signal | Partial |
tenant_id = get_current_tenant() (STABLE fn) | Inlineable, foldable | Yes — with correct declaration |
The fix is a STABLE PARALLEL SAFE wrapper function that PostgreSQL can inline and fold into the plan:
CREATE OR REPLACE FUNCTION current_tenant_id()
RETURNS uuid LANGUAGE sql STABLE PARALLEL SAFE AS $$
SELECT current_setting('app.tenant_id', true)::uuid
$$;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_tenant_id());STABLE tells the planner the function returns the same value within a single query execution. The planner can now treat the policy predicate similarly to a parameter, enabling more consistent index scan selection. Generic plan caching still depends on additional planner thresholds beyond function volatility alone, so verify with your own EXPLAIN output.
Index design that survives RLS
The common mistake is a single-column index on tenant_id with the expectation that it serves all tenant-scoped queries. Under RLS on tables with high tenant counts and millions of rows, the planner will frequently choose a seq scan for large tenants because the single-column index does nothing to narrow the secondary predicate.
The production answer is composite indexes with tenant_id as the leading column:
-- Too generic — planner may prefer seq scan for large tenants
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
-- Correct — composite supports both isolation and query predicates
CREATE INDEX idx_orders_tenant_status
ON orders(tenant_id, status, created_at DESC);For SaaS systems with thousands of tenants and mixed query patterns, build your index set from EXPLAIN output on your five most frequent query shapes — always leading with tenant_id, followed by the columns in your WHERE and ORDER BY clauses.
Leaky views: the silent bypass
SECURITY DEFINER views run as the view owner, not the calling user, which means RLS policies don’t apply by default unless you set security_barrier = true at view creation:
CREATE VIEW active_orders
WITH (security_barrier = true)
AS
SELECT * FROM orders WHERE status = 'open';Without security_barrier, PostgreSQL may push predicates from outer queries inside the view definition, bypassing the RLS policy entirely. In my experience, leaky views are the most commonly missed RLS footgun — especially when views are generated by ORM migrations or scaffolding tools that don’t set this option.
There’s a real tradeoff here: security_barrier = true prevents predicate pushdown into the view, so the database can’t optimize queries by filtering early inside the view definition. Security is preserved, but you may evaluate more rows before outer filters apply. Benchmark against your actual query shapes before treating it as a free fix.
After any schema audit, verify every view has explicit security_barrier intent documented and confirm whether SECURITY DEFINER is intentional.
Three things to do before you ship
Replace raw current_setting() in policy expressions with a STABLE PARALLEL SAFE wrapper function. This single change does the most to restore index selectivity — the planner stops treating your tenant ID as an opaque runtime value and starts making consistent decisions about index scans.
Design composite indexes with tenant_id as the leading column, followed by your most selective query predicates. A single-column tenant index rarely survives query plan analysis under realistic production data distributions with large tenants.
Audit every view for security_barrier = true, then benchmark the predicate pushdown cost. Default PostgreSQL view behavior can silently bypass RLS, and the fix carries a query performance tradeoff that has to be measured against your workload before it goes to production.