Supabase RLS: when (select auth.uid()) helps, measured

Supabase’s documentation tells you to write (select auth.uid()) instead of auth.uid() in a row-level security policy. We measured when that matters. On a table of a million rows in PostgreSQL 17.11, a policy user_id = auth.uid() made select count(*) take 923 ms; with (select auth.uid()) it took 42 ms. With an index on user_id, both took 0.1 to 0.3 ms. And when the query filtered on user_id itself, both took 40 ms. In our measurement the wrapper helped only where the condition could not use an index; that holds for an inlined function like auth.uid(). There the expression is evaluated for every row, and a non-inlinable copy of the function was called 1,000,001 times. The biggest lever was the index on user_id: with it, every variant took 0.1 to 0.3 ms.

What is auth.uid()?

An SQL function that reads the user ID from the JWT claims that PostgREST puts in a setting for every request. The current definition, from the supabase/auth repository, with the template namespace filled in as auth:

create or replace function auth.uid()
returns uuid
language sql stable
as $$
  select
  coalesce(
    nullif(current_setting('request.jwt.claim.sub', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
  )::uuid
$$;

auth.jwt() is built the same way and returns the claims as jsonb. Both are plain SQL functions without security definer or a set clause, so PostgreSQL does not call them as functions at all: it inlines the body into the query. In EXPLAIN you see the coalesce(nullif(current_setting(…))) expression where the policy said auth.uid(). The same migration that added auth.jwt() marks uid() as deprecated in a comment on function: “Use auth.jwt() -> ‘sub’ instead.”

Supabase’s advice, in their row-level security guide: “Wrapping the function causes an initPlan to be run by the Postgres optimizer, which allows it to ‘cache’ the results per-statement, rather than calling the function on each row.” And in the same guide: “Indexing the filter columns and wrapping helper calls keeps policies fast as a table grows.” Their performance page shows the case without an index: a sequential scan over 100,000 rows with the function body as a filter, 171 ms.

The setup

PostgreSQL 17.11 in a throwaway container, default settings, median of five interleaved runs after one warm-up, timed client-side with \timing; the core case repeated on 18.6. A table todos (id, user_id uuid, title, done) with 1,000,000 rows, exactly 100 for each of 10,000 users. The two functions verbatim as above, in a schema auth. Queries ran as a role authenticated without BYPASSRLS, after select set_config('request.jwt.claims', '{"sub":"…","role":"authenticated"}', false). PostgREST sets the same setting per transaction rather than per session. Every policy was created for select to authenticated. request.jwt.claim.sub was not set, so auth.uid() took its second branch, through jsonb, every time.

Three queries: (A) select count(*) from todos, where only the policy filters; (B) the latest 20 open todos, where done = false order by id desc limit 20; (C) select count(*) from todos where user_id = '…', where the application also filters on the user.

When does (select auth.uid()) make a difference?

Without an index on user_id, on the queries where only the policy decides which rows come back:

The line under each number shows its size against the slowest, 1,592 ms. Writing auth.uid() = user_id instead of user_id = auth.uid() gave the same plan and the same times. On PostgreSQL 18.6, query (A) took 921 ms without the wrapper and 39 ms with it.

With create index on todos (user_id), every one of these combinations took between 0.1 and 0.3 ms, with the wrapper and without.

Why did it matter only without an index?

Because of where the condition ends up in the plan. With an index, the inlined expression became the Index Cond of an index-only scan. An index condition is computed once per scan (again on each rescan, inside a nested loop for instance), not per row: our count found two calls per query without the wrapper and one with it. For a function that cannot be inlined, the wrapper helps with an index too: in our row-level security performance measurements a VOLATILE PL/pgSQL helper took 1,877 ms with an index, and 14.6 ms wrapped.

Without an index, the condition becomes a Filter on a sequential scan. A filter is evaluated for every row, and an inlined stable expression is no exception:

Filter: (todos.user_id = (COALESCE(NULLIF(current_setting('request.jwt.claim.sub'::text, true), ''::text), ((NULLIF(current_setting('request.jwt.claims'::text, true), ''::text))::jsonb ->> 'sub'::text)))::uuid)

That is two current_setting calls, a jsonb parse and a uuid cast per row, a million times. With (select auth.uid()) the plan gets an InitPlan, executed once, and the filter compares each row to its result: Filter: (todos.user_id = (InitPlan 1).col1). Both plans read the same 8,334 buffers, so the difference of 881 ms is evaluation, not reading.

Because an inlined function never runs as a function, track_functions counts no calls for auth.uid(). To count them we used a PL/pgSQL copy with the same body, which cannot be inlined:

Query (B), with the uid check alone, walked the primary key backwards and stopped after 20 matches; it checks NOT done first, so the function ran only for the open todos it passed.

Does filtering on user_id in the query help too?

Yes, and here it did as much as the wrapper. With where user_id = '…' in the query, the planner combined it with the policy into a One-Time Filter that compares the function’s result with the literal once, and then filtered the table on the literal. That took 40 to 41 ms with every policy, wrapped or not. Supabase’s performance page recommends this filter as well. But you cannot rely on every query doing it: the policy is there for the query that forgets.

What about the role check with auth.jwt()?

That one is the most expensive pattern, and the wrapper fixes it too. A policy that also checks auth.jwt() ->> 'role' = 'authenticated' took 1,592 ms without wrappers, against 923 for the uid check alone. The role check uses no column, and as the table owner with the same condition in the WHERE, the planner turned it into a One-Time Filter, evaluated once. Under row-level security it stayed in the per-row Filter, next to the uid check. We did not look into why. With both functions in (select …) the role check still sat in the per-row filter, extracting role from the InitPlan’s jsonb for every row, and that cost nothing measurable: 41.6 ms, against 42.0 for the uid check alone.

Is a literal faster still?

A little, and not because of row-level security. As the table owner with the uid as a literal in the WHERE, query (A) took 22 ms, because that plan ran as a parallel sequential scan with two workers. The plans with auth.uid() in the policy or in the WHERE did not get a parallel plan, with or without the wrapper. auth.uid() is not declared PARALLEL SAFE, and in our row-level security performance measurements that alone cost an inlined helper its parallel plan; we did not verify that it is the cause here.

What we would do

Put an index on the column the policy compares, user_id or tenant_id. That made every variant fast, including the slowest. Then wrap auth.uid() and auth.jwt() in (select …) anyway: with the index it cost nothing measurable, and it covers the table where the index is missing or the planner does not use it. Write auth.jwt() ->> 'role' checks wrapped as well, or drop them in favour of the to clause, which Supabase’s guide also recommends: “Always name the role a policy applies to, using the to clause.” All our policies already had to authenticated; the role check on top of it only added cost.

Reproduce the benchmark

setup.sql creates the schema auth with both functions verbatim, the role and the table. run.sh starts a container itself (IMAGE=postgres:17 by default), runs every policy against every query with and without the index, saves every EXPLAIN and counts calls with the PL/pgSQL copy. summarize.py turns the timings into medians. A run removes its container at the end unless KEEP=1 is set.

What we did not measure

Why a Supabase policy returns nothing or fails, rather than how fast it is, is in Supabase RLS policy not working? Ten causes, measured. Beyond that: PostgREST itself, with set_config(…, true) per transaction; Supabase’s hosted platform; policies that join a membership table, which have their own cost, measured in the RLS performance article; auth.jwt() -> 'sub', the replacement the deprecation comment names; and tables where the index exists but the planner prefers a sequential scan because one user owns a large share of the rows.

Sources: Supabase, Row Level Security and RLS performance, read on 18 October 2026; auth.uid() from supabase/auth, migration 20220224000811 and auth.jwt() from migration 20220531120530, read on 18 October 2026. All measurements are our own, on PostgreSQL 17.11 and 18.6 in throwaway containers, on 18 October 2026.