9 · Performance without weakening

The question this chapter answers: how do I make policies fast enough that nobody reaches for the off switch?

Every team that disables RLS "temporarily" tells the same story: the policy was correct, the table grew, a list view got slow, and someone noticed the query was instant with row security off. Performance work on policies is therefore security work — it removes the incentive to weaken them. Three techniques cover most cases, and all three make queries faster without making them more permissive.

Technique 1: wrap authentication calls

Called bare inside a policy, auth.uid() may be evaluated once per candidate row. Wrapped in a scalar subquery, the planner evaluates it once per statement and caches the result as an InitPlan (Supabase RLS guide):

-- Per-row risk:
using (auth.uid() = owner_id)

-- Once-per-statement:
using ((select auth.uid()) = owner_id)

The semantics are identical because the value cannot vary by row; only the evaluation count changes. The same wrapping works for auth.jwt() and for stable helper functions — but note the docs' caution: only when the call's result does not depend on the current row. private.is_member(workspace_id) takes a row value and stays per-row; Technique 3 is its cure.

Technique 2: index every predicate column

Postgres evaluates policies against each candidate row, so every column your predicates touch needs an index whose leading column it occupies — a composite primary key covers only its first column (Supabase guide). TaskHarbor's membership check filters members by user, but the primary key leads with workspace_id:

create index members_user_id_idx on public.members (user_id);

Audit rule: take every policy condition you own, list the columns it reads (owner_id, workspace_id, join targets), and confirm each appears first in some index. Chapter 5's catalog walk plus this list closes the gap that makes correct policies slow.

Technique 3: give the planner a tenant column

TaskHarbor's task policies reach tenancy through a join — correct, but executed per row. Denormalizing the tenant id onto tasks turns the boundary into a plain indexed predicate:

alter table public.tasks add column workspace_id uuid references public.workspaces(id);

update public.tasks t
set    workspace_id = p.workspace_id
from   public.projects p
where  p.id = t.project_id;

alter table public.tasks alter column workspace_id set not null;

create index tasks_workspace_id_idx on public.tasks (workspace_id);

Now replace the joined select policy with a membership-set form:

drop policy "tasks: members read" on public.tasks;

create policy "tasks: members read (fast)" on public.tasks
  for select to authenticated
  using (
    workspace_id in (
      select m.workspace_id from public.members m
      where m.user_id = (select auth.uid())
    )
  );

Reading it inside-out: the inner lookup finds the caller's workspaces once (index-assisted), and each task's eligibility is a single indexed comparison against that set. Then mirror the same expression through the other three task policies, in the clause each command actually uses (Chapter 2's table): with check for insert, both using and with check for update, using for delete. Leaving any of them on the old join form is not a bug, but it does mean two expressions must stay in agreement forever. Two disciplines keep the denormalized column truthful:

revoke update on public.tasks from authenticated;
grant update (title, done) on public.tasks to authenticated;

Column-level grants let members edit content while making project_id and workspace_id unchangeable through the API — the row cannot be moved out of its tenant, and the copy cannot disagree with the original. Any backend process that legitimately re-parents tasks runs as a trusted role and maintains both columns itself.

Measuring, not guessing

Impersonate a real user and look at the plan (EXPLAIN docs):

begin;
set local role authenticated;
set local request.jwt.claim.sub = 'c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3';

explain (analyze) select * from public.tasks;
rollback;

What to look for, in order:

1. InitPlan containing your wrapped auth.uid() — proof Technique 1 landed. A nested SubPlan repeating per row means something stayed unwrapped or row-dependent.
2. Index usage on the tenant column — Index Scan using tasks_workspace_id_idx, not a sequential scan with a filter line beneath it.
3. rows removed by filter — a large number here is the policy doing brute-force work that an index should have done.

Your plans will differ in numbers and naming from any printed in a book; trust the three shapes above rather than the exact text. Run the plan before and after each technique and keep the after version in the migration's comment — future maintainers deserve to know why the shape is what it is.

The standing rule

None of this ends in alter table ... disable row level security. When a legitimate need genuinely requires seeing across tenants — nightly aggregation, support tooling — the answer is a trusted server-side path with a scoped query and a one-sentence justification, the same discipline as Pattern 4. Disabling row security on a table in an exposed schema hands the anon key a readable surface and waits patiently for someone to notice. Slow-and-correct has a fix; disabled-and-fast has an incident.

A useful closing habit: whenever a policy gets optimized, rerun Chapter 6's proofs immediately. Techniques 1–3 rewrite expressions, and rewritten expressions are exactly where isolation quietly breaks. Fast and provably correct beats fast alone.

Check on your project

1. Grep your policies for unwrapped auth.uid() / auth.jwt() calls and wrap each row-independent one in (select ...).
2. Build the column list from every predicate you own and verify each column leads an index; create what is missing in one reviewed migration.
3. Pick your largest multi-tenant table, run the impersonated explain (analyze) before and after Techniques 1–2, and record which plan shapes changed.
4. Confirm no exposed table has row security disabled anywhere — environments included — and write down who is authorized to ever run such a statement (the ideal answer: a migration review rule from Appendix D, not a person).