What RLS costs: performance and how to index for it

Row-level security adds a hidden WHERE clause to every query that touches a protected table. This article shows exactly where that clause executes, why the (select auth.uid()) wrapper exists, and which indexes keep a well-protected table as fast as an unprotected one — for developers shipping Supabase backends.

A policy is code that runs. It runs inside your queries, on every execution, against every candidate row, forever. Most of the time that cost is trivial — a constant compared to an indexed column. But the gap between "trivial" and "sequential scan on every request" is one misplaced function call wide, and nothing in the dashboard tells you which side you are on. This piece gives you the mechanical picture and the exact plans to look for, so performance review becomes part of policy review rather than a separate discipline you discover during an outage.

Policies are predicates, and predicates cost scan time

When Postgres plans a query against a row-security-enabled table, it appends each applicable policy's expression to the query's own conditions. The PostgreSQL documentation describes this directly: policies are applied to queries as filtering conditions, exactly like a WHERE clause the author never wrote.

That framing immediately sorts policies into cost classes:

The last two rows are the interesting pair. The fastest possible policy is true, which is also the most expensive mistake in the product sense. Performance work on RLS is therefore never about making policies cheaper in isolation; it is about making correct policies cheap. The tautology rule exists because these goals diverge.

Two structural facts complete the model:

Policies evaluate before your query's own filters can prune work in some plans. The planner treats policy conditions and user conditions together when choosing scans and indexes, so a good policy predicate can share an index with your query — but a bad one forces extra scanning regardless of how selective your own WHERE is.

Every surface that reads the table pays. PostgREST requests, GraphQL queries, generated views, background jobs through the pooled connection, and Realtime subscribers evaluating read policies all append the same predicates. A policy that costs 40ms is not 40ms once; it is 40ms times every consumer, which is why policy review and load review belong to the same meeting.

The auth.uid() wrapper: one evaluation instead of N

Supabase policies almost universally call auth.uid() — the helper that reads the JWT's sub claim from session state. The single most impactful habit in Supabase policy writing is wrapping that call in a scalar subquery:

-- Evaluated per row scanned:
using (auth.uid() = owner_id)

-- Evaluated once per statement, then reused:
using ((select auth.uid()) = owner_id)

The mechanism is visible in the query plan. Unwrapped, Postgres inlines the function body into the scan's filter condition:

Seq Scan on docs_perf
  Filter: ((NULLIF((current_setting('request.jwt.claims'::text, true)::jsonb
          ->> 'sub'::text), ''::text))::uuid = owner_id)

That entire expression — JSON parse, text extraction, cast — is evaluated for every row the scan touches. Wrapped, the same logical condition compiles into an init plan:

InitPlan 1
  ->  Result
Seq Scan on docs_perf
  Filter: ((InitPlan 1).col1 = owner_id)

InitPlan is PostgreSQL's name for a subquery evaluated once before the main scan, its result passed in as a parameter. The JWT cannot change mid-request, so evaluating it once is semantically identical — the PostgreSQL documentation on subquery expressions covers this execution strategy, and Supabase's RLS performance guide recommends the wrapper explicitly.

The difference compounds with table size: the wrapped form turns a per-row interpreter walk into a single constant, and — as the next section shows — it is also what lets the planner hand the value to an index.

This is common enough that RowShield carries a dedicated detection for it: unwrapped auth calls. It is a performance finding, not a security one — the policy behaves identically either way.

Index the columns your policies filter on

An init-plan parameter is a runtime constant, and runtime constants are exactly what B-tree indexes are for. Continuing the same fixture — a fabricated example table with 20,000 rows, half owned by our test user, whose full DDL is in the worked tuning pass below — adding one index changes the plan from filtered sequential scan to index scan:

create index docs_perf_owner_id_idx on docs_perf (owner_id);
analyze docs_perf;

Bitmap Heap Scan on docs_perf
  Recheck Cond: ((InitPlan 1).col1 = owner_id)
  ->  Bitmap Index Scan on docs_perf_owner_id_idx
        Index Cond: (owner_id = (InitPlan 1).col1)

The policy predicate moved out of the Filter line — which discards rows after reading them — and into the Index Cond line, which never reads excluded rows at all. On a ten-million-row table those are different universes of I/O.

Practical indexing rules for policy columns, in priority order:

1. Every column appearing alone in a USING/WITH CHECK comparison gets an index. owner_id, user_id, tenant_id — whichever your model uses.
2. Membership subqueries want the inner key indexed. For the standard workspace-membership policy, the pair (workspace_id, user_id) should carry an index — a composite primary key on the members table provides it for free.
3. Composite indexes follow the equality chain. If a policy tests workspace_id = X AND status = Y, an index led by workspace_id serves both; one led by status serves neither well.
4. Foreign keys used in policies benefit doubly. The same index that accelerates the policy also protects parent-row deletes from full scans during constraint checks.

RowShield flags tables whose policy predicate columns lack indexes — see the unindexed RLS predicate rule — because this is the single most common cause of the "worked fine in staging, degraded in production" arc traced in slow queries after adding RLS.

One composite case deserves its own template because it appears in almost every multi-tenant schema: a policy that tests both tenancy and state.

create policy tasks_member_open
  on tasks for select to authenticated
  using (
    exists (
      select 1 from workspace_members m
      where m.workspace_id = tasks.workspace_id
        and m.user_id = (select auth.uid())
    )
  );

Here the outer table wants an index led by workspace_id, and the membership table wants its (workspace_id, user_id) key indexed — which its primary key supplies. Two indexes, one per table the predicate touches; policies that reference two tables always imply this pairing.

Also worth knowing: when several permissive policies apply to one command, Postgres evaluates their conditions with OR semantics and can satisfy them with a BitmapOr over each policy's index. Multiple permissive policies therefore need not degrade into sequential scans if each branch has its own supporting index — but every branch you add is another index to maintain and another access path to keep honest. Since permissive accumulation widens access anyway (policy sprawl), the performance answer and the security answer converge: fewer, well-indexed policies.

When indexes silently don't apply

Indexes are picky about expression shape. Two patterns defeat them while producing identical results:

Casting the column side. AI-generated schemas frequently store identifiers as text. A policy then written as:

using (owner_ref::uuid = (select auth.uid()))

forces Postgres to convert every row's value before comparing — the plain index on owner_ref matches no expression the planner can use:

Seq Scan on docs_txt
  Filter: ((owner_ref)::uuid = (InitPlan 1).col1)

Reorienting the cast to the parameter side restores index usage without changing semantics:

using (owner_ref = (select auth.uid())::text)

Bitmap Index Scan on docs_txt_owner_btree
  Index Cond: (owner_ref = ((InitPlan 1).col1)::text)

Cast the constant, never the column. The deeper fix is type discipline: keep identifiers as uuid end to end so no cast exists to misplace. This failure mode is invisible in application tests — the query returns correct results either way — and only shows up under load, which is why it belongs in static review. Our scanner checks policy expressions for it during catalog analysis.

Functions wrapped around indexed columns. The same law generalizes: lower(email) = $1 cannot use an index on email; it needs an index on lower(email). Whenever a policy applies any function to a column, either create a matching expression index or remove the transformation.

A quieter variant: stale statistics. After bulk-loading data or adding an index, run ANALYZE on the table. The planner chooses between "index probe" and "sequential scan plus filter" using distribution estimates; wrong estimates produce wrong plans for perfectly indexed schemas.

Reading EXPLAIN as an RLS reviewer

You do not need to become a planner expert. You need to recognize four shapes. Run your query as the affected role — impersonation mechanics are in our testing guide — with:

explain (analyze, costs off, timing off) <your query>;

(timing off keeps output stable and avoids anchoring you on machine-specific milliseconds — the shapes, not the clock, are the diagnosis.)

One honest caveat about scope: EXPLAIN shows the policy cost in the context of one query. What it does not show is aggregate load across every consumer of the table — dashboards, cron jobs, third-party integrations, Realtime subscriptions re-evaluating read policies per broadcast. The plan tells you whether each visit is cheap; keeping total visits and drift under watch is the monitoring problem RowShield exists for.

Where the cost lands, command by command

Different commands spend policy effort differently, and knowing the shape prevents both over-engineering and blind spots:

Notes worth internalizing:

Bulk inserts amortize well when the check is constant-shaped. A WITH CHECK comparing against an init-plan parameter costs the same logic per row but no repeated JWT parsing; batch loads stay fast on correctly written policies.

Deny-by-default is cheap. Tables locked down pending policy work impose essentially no overhead — there is no reason to delay enabling RLS for performance reasons, whatever the folklore says. The cost myth attaches to the wrong half: protection is nearly free; badly shaped protection is what bills you.

Bypassing roles pay nothing. service_role skips policy evaluation entirely, so server-side paths do not fund the policy budget — one more reason to keep heavy operations server-side rather than loosening client-facing policies, a boundary we draw precisely in the role boundaries reference.

A worked tuning pass

Everything above, compressed into one runnable sequence against a fabricated example project. Fixtures: 20,000 document rows split evenly between two owners.

create table docs_perf (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null,
  title text not null,
  owner_id uuid not null,
  payload text
);
alter table docs_perf enable row level security;

create policy dp_wrapped on docs_perf for select to authenticated
  using ((select auth.uid()) = owner_id);

insert into docs_perf (id, workspace_id, title, owner_id)
select gen_random_uuid(), 'aaaaaaaa-0000-0000-0000-00000000000a', 'doc ' || g,
       case when g % 2 = 0 then '11111111-1111-1111-1111-111111111111'::uuid
            else '22222222-2222-2222-2222-222222222222'::uuid end
from generate_series(1, 20000) g;
analyze docs_perf;

Step 1 — measure as the affected user:

begin;
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"11111111-1111-1111-1111-111111111111"}', true);
explain (analyze, costs off, timing off)
  select count(*) from docs_perf;
rollback;

Observed: sequential scan, Filter: ((InitPlan 1).col1 = owner_id), Rows Removed by Filter: 10000. Correct result, worst shape — the database reads everything to return half.

Step 2 — index the predicate column:

create index docs_perf_owner_id_idx on docs_perf (owner_id);
analyze docs_perf;

Re-run step 1. Observed: Bitmap Index Scan on docs_perf_owner_id_idx with Index Cond: (owner_id = (InitPlan 1).col1). Same answer, no discarded-row work.

Step 3 — regression-check the other commands. The SELECT policy improved, but if this table also carries update/delete ownership policies, repeat the explain for those statements; they compile separately. Then commit the migration with the index included, because a policy whose supporting index lives only in your session is a slow-motion regression — the drift pattern our monitoring guide watches for.

Three statements, two plans recognized, one index. That is the whole discipline: policies reviewed with the plan reader open ship fast and sealed.

Common questions

Does wrapping auth.uid() in (select ...) change what the policy allows?

No. The JWT is fixed for the duration of a request, so one evaluation and N evaluations produce the same value. The wrapper changes when the value is computed, not what it computes to — it is purely an execution-strategy improvement, which is why we classify findings about it as performance rather than access control.

My table has an index on the policy column and still seq-scans. Why?

In rough order of likelihood: the policy expression transforms the column (casts, functions), the comparison types don't match the index (text versus uuid), statistics are stale after a bulk load, or the table is small enough that the planner correctly prefers a scan. Read the Index Cond line specifically — if the policy parameter appears there, the index is engaged; if the condition only appears under Filter:, it is not.

Should every policy column have an index?

Columns compared against per-user constants — owners, tenants — almost always, because selectivity is high. Low-selectivity flags (a is_public boolean admitting half the table) may legitimately stay unindexed; a bitmap-or across a couple of wide branches can beat maintaining another index. Decide per table using the Rows Removed by Filter evidence, not as a blanket ritual.

Do policies measurably slow inserts?

A write-only path pays for WITH CHECK once per row and nothing for read visibility. With the standard wrapped shape, the check reduces to comparing a pre-computed constant against the new row — measurable only in high-throughput ingestion, and dwarfed by the cost of the exposure you would take on by removing it.

---

Run the free scan on your own project: paste your app URL and get findings that include unindexed policy predicates and unwrapped auth calls, each with remediation SQL proposed for review.

RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.