Supabase RLS performance optimization: measure slow policies with EXPLAIN, and make them 100× faster with (select) wrapping, indexes, TO, and JWT

First, let me state the most important thing outright. An RLS (row-level security) policy is an "implicit WHERE clause" automatically added to every query against the table (Supabase: Row Level Security). So if the predicate (the contents of USING / WITH CHECK) is slow, that slowness rides equally on every query and every row that touches that table. The way one policy is written can decide the whole app's latency — this is why RLS performance can't be taken lightly.

And the tricky part is that RLS gets slow even written "correctly." The seemingly innocuous predicate auth.uid() = user_id suddenly becomes 100× slower in production once row count grows. The cause isn't a mistake in the authorization logic but a query-plan problem: PostgreSQL evaluates that predicate "per row."

This article explains the 6 RLS performance optimizations Supabase (PostgreSQL) officially presents, with query-plan-level reasons for "why it works" and a procedure to measure in your own environment with EXPLAIN ANALYZE. The material is my single-handedly-built real-time match-recording app with simultaneous multi-user editing (an Expo + Next.js + Supabase monorepo), where I enable RLS on all 69 tables and operate about 280 policies in production. At this scale of policies, the difference of one way of writing directly ties to perceived speed.

This article's position: how to design RLS (multi-tenant separation, tenant_id, membership) is in the Supabase RLS production-design guide, the correctness of USING and WITH CHECK is in the write-bypass article, and the tests that stop regressions are in the pgTAP article. This article doesn't overlap with those and concentrates only on "how to make correctly-written RLS fast." It's a performance talk on the premise that design, correctness, and tests are in place.

honesty contract: the benchmark figures in the text (179ms→9ms, etc.) are measured values on the synthetic tables Supabase's official documentation presents. They likely won't reproduce with your row counts, distribution, hardware, and index situation. Read the figures as a guide to "the order (digits) of effect" and always measure with your own data using the EXPLAIN ANALYZE below. The specs are based on Supabase official and PostgreSQL official (both as of June 2026).

0. Why RLS gets slow: in 3 lines

Before optimizing, decompose the slowness into three. Grasp this and the 6 measures become visible not as "scattered tricks" but as "prescriptions for three diseases."

The PostgreSQL official defines the evaluation order of policy expressions like this — "this expression is evaluated for each row before conditions or functions from the user's query" (PostgreSQL: Row Security Policies). That is, the RLS predicate runs on the innermost hot path. So if this is slow, no other optimization works. The exception is only leakproof (guaranteed not to leak information) functions, in which case the optimizer can apply the user condition before the RLS check.

The point is the order. First measure (§1), then in order of effect (§2→§7). Erecting indexes by guesswork is a bad move, in RLS as in ordinary query tuning.

1. First measure: EXPLAIN ANALYZE and pg_stat_statements

Before saying "RLS is slow," grasp which policy's which predicate is how many ms in numbers. Since RLS works transparently, run a normal EXPLAIN ANALYZE and the policy appears in the execution plan as a filter condition.

1-1. Measure by impersonating an authenticated user

RLS results change by "who runs it." Measuring with service_role (RLS bypass) is meaningless. Impersonate the authenticated role and put sub (the user ID) in request.jwt.claims before measuring.

-- セッションを「特定の認証済みユーザー」に偽装して計測する
begin;
  -- このトランザクションだけ authenticated ロールで実行する
  set local role authenticated;
  -- auth.uid() が返すユーザーIDを注入する（本番のJWTと同じ経路）
  set local request.jwt.claims = '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}';

  explain (analyze, buffers, verbose)
  select * from public.matches where game_id = '...';
rollback; -- 計測は副作用を残さない（冪等）

Closing with rollback is so the measurement doesn't change state no matter how many times it runs (idempotency). A line like Filter: (matches.user_id = auth.uid()) appears in the plan, and looking at the Rows Removed by Filter below it and the actual time tells you how much work the policy is doing.

1-2. Spot "per-row evaluation" in the execution plan

Grasp, in plan terms, what changes before and after optimization.

• The slow form: the predicate is applied to all rows as a Filter, and auth.uid() is called per row. Seq Scan + a large Rows Removed by Filter.
• The fast form: (select auth.uid()) appears once at the head of the plan as InitPlan 1, and the body can be fetched by index like Index Cond: (user_id = $0).

So the goal is clear — move the predicate from Filter (per row) to Index Cond (one index lookup). The 6 below are all means to that.

1-3. In production, find "the slowness that matters" with pg_stat_statements

A single EXPLAIN is for hypothesis verification. In production, find "which query's cumulative cost is large" with pg_stat_statements (Supabase enables it by default).

select calls, mean_exec_time, total_exec_time, query
from pg_stat_statements
order by total_exec_time desc
limit 20;

The query whose mean_exec_time × calls = total_exec_time is large is the target with the highest optimization ROI. Fix "a somewhat-slow query called a lot" before "a query that's slow once" — this is a tuning iron rule beyond RLS.

2. The strongest move: wrap auth.uid() with (select auth.uid())

This has the biggest effect and the lowest cost. Just wrap the function call in the policy with an uncorrelated scalar subquery (select ...).

-- ❌ 遅い：auth.uid() が行ごとに評価される
create policy "rls_select" on public.test_table
  for select to authenticated
  using ( auth.uid() = user_id );

-- ✅ 速い：(select auth.uid()) は一度だけ評価され、InitPlanに畳まれる
create policy "rls_select" on public.test_table
  for select to authenticated
  using ( (select auth.uid()) = user_id );

In the official benchmark, this one-character-level difference is 179ms → 9ms (about 95% improvement). In an example with a heavy function like is_admin(), 11,000ms → 7ms; in an example involving a security definer function, a difference of orders of magnitude is reported: 178,000ms → 12ms (Supabase: RLS Performance).

Why it works: InitPlan, "constant folding"

auth.uid() is, in substance, a function that reads the user ID from current_setting('request.jwt.claims'). Its value is the same throughout one query execution — it doesn't change per row.

Yet PostgreSQL, if auth.uid() is written bare in the policy predicate, dutifully re-calls it for each row. Wrap it in a subquery as (select auth.uid()) and PostgreSQL recognizes it as an expression uncorrelated with the outer rows (an uncorrelated subquery) and evaluates it once at the start of execution as an InitPlan, reusing the result like a constant. In the plan it appears at the head as InitPlan 1 (returns $0), and the body just references $0 (the evaluated constant).

This is less "caching" than constant folding. So as a side effect, if user_id has an index, user_id = $0 can be fetched by index, drawing out §3's effect too.

Applicability condition (important): this optimization is correct only when the result of the wrapped expression is the same for all rows. auth.uid(), auth.jwt(), and functions depending on the current user are OK. Conversely, don't wrap an expression containing a value that depends on the row, like row.column, with (select ...) — the meaning changes. Remember to fold only "values constant per user."

3. Index the columns the policy references

An RLS policy is an implicit WHERE. Then, just like an ordinary query, the columns used for narrowing need an index. If you narrow by user_id = (select auth.uid()), index user_id.

create index if not exists idx_test_table_user_id
  on public.test_table using btree (user_id);

In the official bench, 171ms → under 0.1ms (about 99.9% improvement). With the predicate in a form that can ride on Index Cond (per §2) plus an index, it can be fetched in one index lookup, so it becomes dramatically faster.

Index-design guidance in practice:

• Single-tenant ownership: a btree single-column index on user_id.
• Multi-tenant: tenant_id becomes the common predicate of all policies, so place tenant_id at the head of a composite index (e.g., (tenant_id, created_at)). The query's order by created_at can be served by the index too.
• membership table (used heavily in §5): a composite index (user_id, team_id) and a (team_id) index for reverse lookup.

Indexes aren't omnipotent. An index on a low-cardinality column with many writes (e.g., a boolean like is_active) can be not worth the update cost. Always confirm "did it change to an Index Scan" with §1's EXPLAIN ANALYZE before keeping it. The details of PostgreSQL core index design are left to the 'PostgreSQL core / performance design' cluster.

4. Narrow roles with TO and erase wasteful policy evaluation

Without writing TO authenticated on a policy, that policy is evaluated for all roles including anon (unauthenticated). Even an unauthenticated user's request runs the auth.uid() comparison, wastefully slowing it.

-- ❌ TO なし：anon のリクエストでもこの述語が評価される
create policy "rls_select" on public.rls_test
  using ( (select auth.uid()) = user_id );

-- ✅ TO authenticated：認証済みにだけ評価され、anon は即スキップ
create policy "rls_select" on public.rls_test
  for select to authenticated
  using ( (select auth.uid()) = user_id );

In the official bench, in the case where anon accesses, 170ms → under 0.1ms (about 99.8% improvement). Since this policy doesn't apply to anon from the start, the predicate evaluation itself disappears.

This is a good case where performance and correctness coincide. As an RLS design principle, making "who it runs as (anon / authenticated / service_role)" explicit on all policies is the pattern recommended in the production-design guide too. The habit of writing TO works for both speed and safety.

5. Rewrite JOINs in the policy into "set references"

Write complex authorization — "is this user a member of the team this row belongs to?" — as a table join (JOIN) in the policy, and that join runs per row of the target table, becoming sharply slow.

-- ❌ 遅い：source(対象表) と target(team_user) を結合している
create policy "rls_select" on public.test_table
  for select to authenticated
  using (
    (select auth.uid()) in (
      select user_id from team_user
      where team_user.team_id = test_table.team_id  -- 外側の行に相関＝行ごとに実行
    )
  );

-- ✅ 速い：先に「自分が所属するteam_idの集合」を作り、行はそれに含まれるか確認するだけ
create policy "rls_select" on public.test_table
  for select to authenticated
  using (
    team_id in (
      select team_id from team_user
      where user_id = (select auth.uid())  -- 外側に相関しない＝一度だけ実行
    )
  );

In the official bench, 9,000ms → 20ms (about 99.8% improvement).

Why it works: cut the correlation

The first form, because the subquery references test_table.team_id, is correlated with the outer rows and re-fetches team_user per row (N lookups).

The latter, because the subquery depends only on (select auth.uid()), is uncorrelated with the outside and executed once, building "the set of team IDs I belong to." After that it just checks with in whether each row's team_id is in that set. O(N joins) changes to O(1 set construction + N membership checks). Not "JOIN the target table with the authorization table" but "fetch the authorization data as a set first" — this is the most important rewrite pattern in RLS.

6. Offload complex authorization to a security definer function

Multi-stage authorization that even in (select ...) can't fully express (roles, time-limited permissions, item-level disclosure, etc.) is confined to a security definer function. This function runs not with the caller's privileges but with the function owner's privileges and internally bypasses RLS, so the policy's predicate evaluation completes there.

-- 認可ロジックを private スキーマ（API非公開）の関数に閉じ込める
create or replace function private.has_team_access(target_team_id uuid)
returns boolean
language sql
security definer
set search_path = ''            -- search_path注入を塞ぐ（必須）
stable                          -- 同一トランザクション内で結果が変わらないと明示
as $$
  select exists (
    select 1 from public.team_user
    where user_id = (select auth.uid())
      and team_id = target_team_id
  );
$$;

revoke execute on function private.has_team_access(uuid) from anon, authenticated;
grant  execute on function private.has_team_access(uuid) to authenticated;

-- ポリシーからは (select ...) で包んで呼ぶ（§2の最適化を効かせる）
create policy "rls_select" on public.test_table
  for select to authenticated
  using ( (select private.has_team_access(team_id)) );

In the official bench, an extreme improvement of 178,000ms → 12ms is reported for the case of wrapping a security definer function with (select ...).

But security definer is also a blade that bypasses RLS. Always keep these 3.

1. Don't put it in a public schema. As the Supabase official explicitly says, you must not create a security definer function in an "Exposed schema (a schema published in the API settings)." Put it in a non-public schema like private.
2. Always add set search_path = '' and fully-qualify the objects inside the function like public.team_user. Neglecting this becomes an injection path where a malicious search_path makes it grab a different object.
3. Least privilege. Strip execute from anon and grant it only to the necessary role.

The design to not make this function a hole that slips past RLS, and the pitfall of search_path, are detailed in the security definer function and search_path article. A function introduced for performance becoming a hole in authorization defeats the purpose, so be sure to read it together.

7. A higher-tier optimization: erase the lookup itself with JWT claims

Up to here was about "making the DB query fast." The last one changes the idea — don't query the DB at all.

If a user's team membership or role doesn't change often, put it in the JWT's app_metadata and the policy can judge with only the token contents without querying the DB at all. Even the lookup to the team_user table (§5, §6) disappears.

-- JWTのapp_metadataに格納された teams 配列で判定する（DBルックアップなし）
create policy "user is in team" on public.my_table
  for select to authenticated
  using (
    team_id in (
      select jsonb_array_elements_text(
        (select auth.jwt() -> 'app_metadata' -> 'teams')
      )::uuid
    )
  );

There are two important notes.

• Use app_metadata (not user_metadata). Supabase official explicitly states that raw_user_metadata is writable by the user themselves, so it must not be used for authorization; for authorization, use the non-writable raw_app_metadata. Get this wrong and it becomes a fatal hole where users can escalate their own permissions.
• The trade-off with token freshness. A JWT is a snapshot at issue time. Even after leaving a team, the old permission remains until the token expires (until refresh). Use DB lookups (§5/§6) for permissions needing immediate revocation, and JWT for permissions that change slowly — this is the classic trade-off of performance and consistency.

Similarly, requiring MFA (multi-factor authentication) strength with RLS is an application of JWT claims. With a restrictive (AND-combined) policy, you can enforce "no update unless aal2" on all paths.

-- 二要素認証を通したセッションでなければ更新を拒否する（全UPDATEにANDで効く）
create policy "require mfa for updates" on public.profiles
  as restrictive            -- 既存のpermissiveポリシーとANDで結合される
  for update to authenticated
  using ( (select auth.jwt()->>'aal') = 'aal2' );

A restrictive policy is combined with other permissive policies (OR-combined) with AND (PostgreSQL official). "Adding permission" is permissive, "constraining the whole" is restrictive — fix absolutely-non-removable conditions like tenant boundaries and MFA requirements with restrictive.

8. The order of production rollout: apply in order of effect, protect with tests

Lining up the 6 (+JWT) optimizations in order of high ROI is as follows. Apply from the top in order and confirm the effect each time with §1's EXPLAIN ANALYZE — that's the shortest path.

And there's something to always do last.

Re-verify: is the now-fast RLS still correct?

Performance optimization is the act of rewriting predicates. The (select) wrap, JOIN rewrite, and functionize always carry the risk of inadvertently changing the meaning of authorization. An RLS that's "fast but lets others' rows be seen" is far more dangerous than a slow one. So before and after optimization, run the automated tests that check both "what should be allowed is allowed" and "what should be denied is denied" as shown in the pgTAP article, and stop regressions in CI. Performance and correctness — you can't ship to production with only one.

In my real-time match-recording app, I wove these 6 into all 280 policies from the design stage, verified allow/deny with pgTAP, and run a screen where multiple people simultaneously touch the same data with different views at a practical latency. RLS isn't "done when written" or "done when made fast"; it endures production only with the 3-set of design → performance → verification.

Conclusion: RLS slowness is "a disease of the execution plan," not a mistake in authorization

• An RLS policy is an implicit WHERE added to every query. A slow predicate hits all rows, so performance is part of the design.
• The true identity of slowness is three — per-row function evaluation, absence of an index, wasteful policy evaluation. The 6 optimizations are prescriptions for these three.
• The biggest move is the (select auth.uid()) wrap. An uncorrelated scalar subquery is folded into an InitPlan, per-row evaluation disappears, and it rides on an index too.
• Apply indexes, explicit TO, JOIN set-referencing, security definer functions, and JWT-claim-izing in order of effect while measuring with EXPLAIN ANALYZE.
• Figures are environment-dependent. After optimizing, re-verify allow/deny with pgTAP and absolutely don't produce a "fast but leaky" regression.

Primary sources (always confirm the latest)

• Supabase: Row Level Security (including the performance section)
• PostgreSQL: Row Security Policies
• PostgreSQL: CREATE POLICY
• PostgreSQL: EXPLAIN