Row-level security performance in PostgreSQL, measured

Row-level security costs nothing — if the policy is a simple comparison and the query only asks for what the policy already restricts. We measured that earlier: with the right index, the policy is the index lookup. But a policy can be written in many ways, and a query can ask for more than the policy knows about. One of the variants we measured made every query take 80 milliseconds or more; another took nearly two seconds; and one rule that is easy to overlook turned a 0.2 ms lookup into 34 to 45 ms — mostly for the largest customer.

What does a row-level security policy cost?

Nothing measurable, in its simplest form. On a table of 2 million invoices, the same queries with the policy tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid were as fast as without row-level security and an explicit WHERE tenant_id = …: 11.0 against 11.0 ms to count the largest tenant’s 267,023 invoices, 0.30 against 0.34 ms for a median tenant’s latest 50. The cost comes from three other places: how the policy gets the tenant, whether it looks up memberships, and which functions the query itself uses.

The setup

PostgreSQL 17.10 in a throwaway container, default settings, warm cache, median of five runs. The table from PostgreSQL indexes for multi-tenant SaaS: 1,998,794 invoices over 1,000 tenants of very unequal size — the largest has 267,023, the median 534 — with an index on (tenant_id, issued_on). We added a customer_email column with two indexes, (tenant_id, lower(customer_email)) and (tenant_id, customer_email text_pattern_ops), and later a third on a generated column. The application connects as a role without BYPASSRLS; the baseline without row-level security runs as the table owner with the tenant in the WHERE. We recreated the policy before every series, which throws away cached plans, so every number below is for a plan made for that tenant — why that matters is at the end of the next section.

Does it matter how the policy gets the tenant?

Only when the function cannot be inlined. Many teams put the lookup in a helper function, tenant_id = app.current_tenant(). We measured that in five forms, for two queries: count all invoices, and the latest 50.

The SQL function was fast whether it was declared VOLATILE or STABLE: PostgreSQL inlined it, and EXPLAIN showed the nullif(current_setting(…)) expression itself as the Index Cond, without the function. The PL/pgSQL function cannot be inlined. Declared VOLATILE, which is what you get if you declare nothing, it has to be called once for every row, so the index cannot be used and the query becomes a sequential scan over 2 million rows — for a tenant with 534 invoices too. STABLE fixes it, and so does wrapping the call in (select …), which turns it into a value computed once per query. That is the advice you often read; it is right, and for an inlinable SQL function it is unnecessary.

Inlining has conditions, and two common hardening habits break it. An SQL function declared SECURITY DEFINER, or with a SET search_path clause, is no longer inlined. Declared STABLE that did not matter — 14.6, 0.31, 0.18 and 0.31 ms — but a VOLATILE SQL function took 3,804 ms with SECURITY DEFINER and 4,600 ms with SET search_path — a sequential scan, like the PL/pgSQL one. So declare the helper STABLE whatever language it is in; then you do not depend on inlining.

The count for the largest tenant stayed at about 15 ms instead of 11 with every function. That is parallelism: a function is PARALLEL UNSAFE unless you declare otherwise, and the documentation is explicit that such a function “forces a serial execution plan”. Declared STABLE PARALLEL SAFE, the helper got its parallel plan back: 10.2 to 10.9 ms for the SQL function, 11.2 to 11.8 ms for the PL/pgSQL one.

That parallel plan has a price on a shared connection. psycopg prepares a query automatically once it has run more than five times on a connection, and a statement without parameters keeps the plan of its first tenant. With that default, on one direct connection, counting the median tenant’s invoices right after the largest tenant’s took 7.3 ms instead of 0.2 — with the parallel-safe helper and with the plain current_setting() policy alike, because both now had a parallel plan to hand down. With the serial plan of a parallel-unsafe helper it stayed at 0.15 ms. Behind a connection pooler this gets worse, and we measured it separately in PgBouncer and RLS.

What does a membership subquery in the policy cost?

About 80 milliseconds on every query, on this table. When users can belong to several tenants, a common policy asks the membership table directly:

using (tenant_id in (
  select m.tenant_id from memberships m
  where m.user_id = nullif(current_setting('app.user_id', true), '')::int))

With in (select …) the planner no longer had a single value to look up in the index. It checked the policy row by row against the membership list, and the median tenant’s 534 invoices cost as much as the largest tenant’s 267,023: 80 ms for a list that takes 0.30 ms. Rewriting it as = any (array(select …)) gives the planner a value list computed once, and that fixed the counts — but the largest tenant’s latest 50 got worse, 158 ms, because the plan now fetched all its rows through a bitmap and sorted them instead of walking the index backwards.

The version that was fast everywhere is the first row: resolve the membership once, when the request starts, and put the chosen tenant in the setting. The policy then compares against one value. Checking that the user belongs to that tenant moves into the code that sets the tenant — one query per request instead of one per row.

Why does row-level security stop my index from being used?

Because of a rule for functions that are not leakproof. The PostgreSQL documentation: “The system will enforce conditions from security policies and security barrier views before any user-supplied conditions from the query itself that contain non-leakproof functions, in order to prevent the inadvertent exposure of data.” A function that could reveal something about a row — through an error message, for instance — must not see rows the policy has not yet approved.

lower() is not leakproof. Neither is LIKE. So a lookup that is instant without row-level security:

select count(*) from invoices
where lower(customer_email) = lower($1);

— under the policy uses the index only for the tenant, then evaluates lower() on every one of that tenant’s rows. EXPLAIN for the largest tenant showed Index Cond on tenant_id only, with the email as a Filter and Rows Removed by Filter: 89,005 in each of three parallel processes: all 267,015 other rows.

This is the trap in multi-tenant form: the median tenant hardly notices, because scanning 534 rows is quick. The cost grows with the size of the tenant, so it lands entirely on your largest customer, and a test with small tenants will not show it.

Adding the tenant to the query yourself does not help: uuid equality was already being used; the problem is the other condition. What helps is making the condition itself leakproof. The equality operator on text is, so a stored generated column holding lower(customer_email), with an index on (tenant_id, customer_email_lower), brought the lookup back to 0.25 ms. lower($1) on the parameter side is fine: the documentation adds that functions “which are not passed any arguments from the security barrier view or table do not have to be marked as leakproof”. For prefix searches, the text_pattern_ops operators ~>=~ and ~<~ are leakproof, and writing the range yourself brought 22 ms back to 0.30 ms.

Marking lower() itself as leakproof is possible, but only for a superuser, and it is a promise about someone else’s function. We would not do it.

How do you find these in your own database?

Two checks. Policies whose expression calls a volatile function:

select p.tablename, p.policyname, pr.oid::regprocedure as function
from pg_policies p
join pg_proc pr
  on position(pr.proname || '(' in coalesce(p.qual, '') || ' ' || coalesce(p.with_check, '')) > 0
where pr.provolatile = 'v'
  and pr.pronamespace not in ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
order by 1, 2;

This matches on the function name in the policy text, so two functions with the same name in different schemas can give a false positive. On a test table with seven policies it reported exactly the four that use a VOLATILE function: the PL/pgSQL one, the SECURITY DEFINER one, the inlined SQL one — fast, but only as long as nobody adds SECURITY DEFINER — and the one wrapped in (select …), which is fast. Every hit is worth declaring STABLE. And for the leakproof rule: run EXPLAIN on your slowest screens as the application role, not as the owner. A condition that is an Index Cond as the owner and a Filter as the application is this problem.

Why counting the largest tenant can get five times slower without any policy change — the visibility map, and an autovacuum threshold no single tenant reaches — is in counting per tenant in PostgreSQL.

What we did not measure

PostgreSQL 18, a cold cache, tables larger than memory, policies with WITH CHECK on writes, and more complex membership models with roles per tenant. The timings come from one machine with default settings; read them as proportions. The proportions are consistent: every slow case above lost an index condition or an index order the query needed, and every fix gave it back.

Why the simplest policy is also the safest — and the five ways row-level security is silently not working — is in row-level security in PostgreSQL: where tenants leak.

Sources: the PostgreSQL 17 documentation on CREATE FUNCTION (LEAKPROOF, PARALLEL UNSAFE and VOLATILE as defaults), read on 30 September 2026. All measurements are our own, on PostgreSQL 17.10, on 30 September and 1 October 2026.