The migration that took our API down shipped at 17:40 on a Tuesday, and it was eleven lines of SQL. We run a multi-tenant SaaS on PostgreSQL — one schema, every table carrying a tenant_id, about 40 million rows in the invoices table — and we had decided to move tenant isolation out of the application layer and into row-level security. The policy our engineer wrote was reasonable on its face: a user can see rows whose tenant appears in their memberships. It deployed cleanly. By 18:15 the p95 on our dashboard endpoint had gone from 9 milliseconds to 1.4 seconds, and by 19:00 the connection pool was queuing because every request was holding a session for a hundred times longer than usual. The database CPU graph looked busy but not alarming. Nothing was "wrong" except the plans — every query on invoices had silently swapped an index scan for a per-row nested-loop lookup against memberships, 40 million times over.
What follows is what I wish someone had told us before that deploy.
How does PostgreSQL actually apply an RLS policy?
It doesn't filter rows after the fact. Policy expressions are inlined into the query as quals — additional WHERE clauses — during query planning, before the planner chooses indexes or join strategies. This is the single most important mental model, and it's the one most people get wrong. RLS is not a post-processing step that runs over your result set; it rewrites your query.
That has two consequences. First, a well-written policy can be a performance feature: a simple tenant_id = ... qual gives the planner a selective predicate it can push into an index scan or use for partition pruning. Second, a badly-written policy doesn't just add a filter — it changes the entire optimization problem. When our policy was inlined, the planner was no longer optimizing "find these invoices by date"; it was optimizing "find these invoices by date, where each row's tenant passes a membership check," and it made a different, catastrophically worse choice.
You can see the rewriting directly: run EXPLAIN as the restricted role and the policy expression shows up in the Filter or index condition, right alongside your own predicates. Stop thinking of policies as configuration and start reading them as SQL that runs inside every query on that table — because that is literally what they are. For a refresher on reading those plans, see our EXPLAIN (ANALYZE, BUFFERS) deep dive.
Why did our subquery policy turn indexed lookups into nested loops?
Because a policy containing a subquery or a volatile function can't be flattened into the scan the way a simple comparison can, and the planner is forced to evaluate it per row. Our original policy looked like this:
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id IN (
SELECT m.tenant_id
FROM memberships m
WHERE m.user_id = current_setting('app.user_id')::uuid
));Read it the way the planner does: for every candidate row of invoices, decide whether tenant_id is in the set of that user's memberships. In the best case the planner can hash the subquery once and probe it. In our case — with the surrounding query shape and the size of memberships — it chose a nested loop: for each of the 40 million invoice rows it considered, a fresh index probe into memberships. Even at microseconds per probe, that math only ends one way.
Volatile functions make it worse, and in a more absolute way. A function marked VOLATILE can't be assumed to return the same answer twice, so the planner must call it for every row and can't cache, pre-evaluate, or reorder it around other work. If your policy calls a helper function — say user_can_see_tenant(tenant_id) — and that function is volatile (the default if you never declared a volatility), you have serialized your plan around it. The fix for the function case is usually trivial — declare it STABLE or IMMUTABLE if it genuinely is — but you have to know to look. The fix for the subquery case is usually to not have the subquery, which brings us to the rule that decides what the planner is even allowed to do with your predicates.
What is the leakproof rule, and why does it block your index?
PostgreSQL will only evaluate a function or operator before the security quals if it's marked LEAKPROOF. The reasoning is about information disclosure: RLS exists to hide rows, and if a non-leakproof function runs on a row before the security check, it could leak information about hidden data — through an error message like "division by zero" that reveals a value, or through timing. So the planner plays it safe: security quals go first, and anything not proven leakproof waits behind them.
The performance cost is that "waiting behind them" means a non-leakproof custom function or operator in your policy can't be pushed down into an index condition. The built-in operators on ordinary types — = on uuid, integer, text, the standard comparison operators — are leakproof, which is why the simple pattern below works so well. But the moment your policy calls my_tenant_check(tenant_id), where my_tenant_check is a plpgsql function you wrote and never marked leakproof (and only superusers can mark functions leakproof, precisely because a wrong marking is a security hole), the planner can no longer use that expression to drive an index scan. Rows get fetched, then filtered. You can audit your own functions quickly:
SELECT p.proname, p.proleakproof, p.provolatile
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'my_tenant_check';If proleakproof is f and that function sits in a policy, you've found your pushdown blocker. The honest tradeoff: you usually can't just mark your function leakproof — the bar is "provably leaks nothing, ever, including via errors," and most functions with real logic don't meet it. The better answer is to not need a function in the hot path at all.
What does a fast RLS policy look like in production?
A plain equality between an indexed column and a per-transaction setting, with nothing else in the expression. This is what we shipped as the replacement, and it took p95 back under 15 milliseconds:
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.current_tenant')::uuid);Every piece of this earns its place. current_setting('app.current_tenant') is a STABLE function, so the planner evaluates it once per query as a constant — not once per row — and the qual collapses to tenant_id = 'a-literal-uuid', which any btree index on tenant_id can serve directly. The equality operator is leakproof, so pushdown is allowed. There is no subquery, so there is nothing to nest-loop. The application sets the GUC once per transaction (SET LOCAL app.current_tenant = '...' or via set_config in the transaction block), and because the value comes from the session rather than from a table lookup, the plan is stable and cache-friendly.
FORCE ROW LEVEL SECURITY matters because the table owner bypasses RLS by default — and in most deployments the application's role owns the tables, which means your policy is silently not applied at all until you force it. Note that superusers always bypass RLS regardless; if your app connects as a superuser you have bigger problems than plan shape.
Two supporting tools worth knowing. security_barrier views exist for the same leakproofing reason: a view defined WITH (security_barrier = true) forces quals on the view to be evaluated in an order that prevents leaking rows through user-defined functions, which is the view-layer equivalent of the RLS guarantee. And when you need to verify what a policy actually does to a plan, verify it as the real role, not as yourself — your superuser session skips RLS entirely and will happily show you a beautiful plan that no tenant ever gets:
BEGIN;
SET ROLE app_readonly;
SET LOCAL app.current_tenant = '6f3c9a2e-1b7d-4e5a-9c0f-2d8a4b6e1c3d';
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, status
FROM invoices
WHERE created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;
ROLLBACK;What you want to see in that output: an index scan (or bitmap scan) using tenant_id, with the tenant predicate as an Index Cond rather than a Filter, low Rows Removed by Filter, and shared hit counts that match the row counts you actually touch. Rows Removed by Filter in the millions against 50 returned rows is the smoking gun we should have looked for before that Tuesday. If you're weighing this single-schema-plus-RLS architecture against per-tenant schemas in the first place, the tradeoffs are covered in our multi-tenant schema design post, and the skew side of the problem in the tenant-skew follow-up.
When is row-level security the wrong tool?
When the policy overhead dominates the query itself, or when your isolation requirements are stronger than "the planner is careful." RLS is the right tool when policies are simple equalities on indexed columns, tenants share a schema naturally, and queries touch a small slice of the table. It's the wrong tool in three situations we've now hit.
First, when the access rule genuinely requires a join — "users see invoices for tenants they belong to, and membership changes constantly" — and you can't denormalize that into a session variable. You can make subquery policies work with careful sizing, but you're permanently one planner decision away from the Tuesday I described. Second, when queries scan most of the table (analytics, reporting, exports): a policy qual on a full sequential scan adds real CPU per row across tens of millions of rows, and an application-level filter or a separate reporting replica is often cheaper and simpler. Third, when compliance demands hard isolation — separate credentials, separate backup boundaries, the ability to say "tenant X's data cannot be read with tenant Y's session." RLS is a software boundary inside one database; per-tenant schemas or per-tenant databases are a stronger one, at real operational cost. The cost sheet cuts both ways: dropping RLS means your application layer must never forget a WHERE clause, ever, on any code path. We kept RLS — but we kept the simple RLS, and we test it as the restricted role in CI.
How do you catch RLS regressions with MonPG?
RLS regressions are nasty to spot because nothing errors — the queries return correct results, just a hundred times slower, and the database looks healthy. What gives it away is plan-shape drift and filter waste, which is what we watch in MonPG: per-query mean execution time from pg_stat_statements so the migration cliff shows up as a step change minutes after deploy, not as a support ticket; filter waste visible when you pull a live plan from the slow-query view; buffer hit ratios per query so a policy that suddenly forces heap fetches appears as a shared-reads spike; and session counts so the secondary symptom — slow queries holding connections until the pool queues — gets caught before the API does. The habit that would have saved us that evening: baseline the top queries per table, alert on mean-time regressions, and treat any policy change as a migration that needs a before/after EXPLAIN (ANALYZE, BUFFERS) as the real role, attached to the PR.