---
title: "PostgreSQL RLS at Scale: Index Traps and Policy Pitfalls in Multi-Tenant Systems"
published: true
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: postgresql, architecture, security, performance
canonical_url: https://mvpfactory.co/blog/postgresql-rls-scale-index-traps-policy-pitfalls
---
## What We Are Building
By the end of this tutorial, you will understand how PostgreSQL's query planner sees your RLS policies — and why that misunderstanding will silently kill performance under load. We will walk through policy inlining, fix the `current_setting()` selectivity trap, design composite indexes that survive realistic tenant data, and audit views for the leaky bypass most teams miss entirely.
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.
---
## Prerequisites
- PostgreSQL 14+
- A multi-tenant schema with a `tenant_id` column on your key tables
- Basic familiarity with `EXPLAIN (ANALYZE, BUFFERS)`
- An understanding of why application-layer `WHERE tenant_id = $1` is fragile (one missed clause, one ORM abstraction, and tenant A reads tenant B's data)
---
## Step 1 — Understand How Policy Inlining Actually Works
When you write this:
sql
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::uuid);
PostgreSQL inlines that expression into every query that touches `orders`. A simple `SELECT * FROM orders WHERE status = 'open'` becomes:
sql
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 is a runtime value with no statistics. It also cannot 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 is the policy trap, live in production.
---
## Step 2 — Fix Selectivity Estimation With a STABLE Wrapper
Here is the pattern I use in every project. The planner behavior varies significantly 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 = get_current_tenant()` (STABLE fn) | Inlineable, foldable | Yes — with correct declaration |
The fix is a `STABLE PARALLEL SAFE` wrapper function:
sql
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. It can now treat the policy predicate more like a parameter, enabling consistent index scan selection. Verify with your own `EXPLAIN` output — generic plan caching still depends on additional planner thresholds beyond function volatility alone.
---
## Step 3 — Design Composite Indexes That Survive RLS
Here is the gotcha that will save you hours: a single-column index on `tenant_id` does not serve real production workloads with large tenants.
sql
-- 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);
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.
---
## Step 4 — Audit Every View for security_barrier
The docs do not emphasize this enough, but `SECURITY DEFINER` views run as the view owner, not the calling user. RLS does not apply by default unless you set `security_barrier = true`:
sql
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 your RLS policy entirely. Leaky views are the most commonly missed RLS footgun — especially when views are generated by ORM migrations or scaffolding tools.
Real tradeoff: `security_barrier = true` prevents predicate pushdown into the view, so you may evaluate more rows before outer filters apply. Benchmark against your actual query shapes before treating it as a free fix.
---
## Gotchas
- **Seq scans are silent.** You will not see a policy violation — queries return correct data, just slowly. Profile under realistic tenant data distributions, not just small test datasets.
- **ORM-generated views skip security_barrier.** Audit your migration history. Any view created by a scaffold tool almost certainly needs this set manually.
- **STABLE alone is not magic.** Verify your `EXPLAIN` output after adding the wrapper function. Generic plan caching thresholds can still interfere with what you expect.
- **Single-column tenant indexes degrade under large tenants.** The planner calculates that a seq scan is cheaper when a tenant owns 30% of the table. Composite indexes narrow that math.
---
## Conclusion
Here is the minimal setup to get this working correctly: replace raw `current_setting()` with a `STABLE PARALLEL SAFE` wrapper, lead every relevant index with `tenant_id`, and set `security_barrier = true` on every view. Miss any one of these and you have correct isolation with catastrophic throughput under load.
(If you are the kind of developer who runs long `EXPLAIN ANALYZE` sessions at the desk — HealthyDesk is worth a look for scheduling stretch breaks between those deep query-tuning rabbit holes: [Google Play](https://play.google.com/store/apps/details?id=com.healthydesk).)
RLS is the right primitive for multi-tenant isolation at the database layer. The planner just needs a little help understanding your intent.
Top comments (0)