RLS patterns that survive millions of rows

Start with the two mechanical fixes

Before any subtle tuning, confirm the foundations. Every column a policy filters on needs a valid index, ideally leading a composite that also serves the table's real queries. Every auth.uid() and auth.jwt() call needs the scalar-subquery wrap, so evaluation happens once per statement rather than per candidate row. On large tables neither omission is survivable; together they account for most of what people describe as "RLS is slow", when RLS itself is merely exposing the table's missing index strategy.

-- The two defaults, done properly:
CREATE INDEX CONCURRENTLY idx_events_org_id_created_at
  ON public.events (org_id, created_at DESC);

ALTER POLICY "org_reads_events"
  ON public.events
  FOR SELECT
  TO authenticated
  USING ((SELECT auth.jwt() ->> 'org_id') = org_id);

Keep predicates sargable

An index helps only when the condition is shaped so the planner can seek into it. Wrap functions around values, never around the column: lower(:email) = email_canonical seeks, while lower(email) = :value must evaluate the function for every row and cannot use a plain index on email. The same applies to casts on the column side — comparing a text identifier against a uuid column forces a cast per row and abandons the index; store identifiers in the type the provider emits, or index the cast expression deliberately.

-- Seeks: function on the parameter side
WHERE email = lower($1);

-- Scans: function on the column side
WHERE lower(email) = $1;

-- Deliberate: an index that matches the expression
CREATE INDEX idx_users_email_lower ON public.users (lower(email));

Prefer narrow grants and hard boundaries

Permissive policies on one table combine with OR: the caller needs only one arm to admit a row, and the planner must evaluate arms until one succeeds, so each additional broad arm is paid on every query. Prefer several narrow policies targeting distinct roles over one wide policy with OR chains inside. Where a condition must never be relaxed — tenant isolation, deletion flags — declare it as a RESTRICTIVE policy, which ANDs against the permissive set and gives the reviewer a single obvious place where the hard boundary lives.

Membership checks deserve the same discipline. A policy that asks EXISTS (SELECT 1 FROM members WHERE ...) is a join against another table on every candidate row unless the planner can cache it; keep the inner query indexed on both sides of its join column and wrap constant parts of it, or better, denormalise the membership decision onto the row itself at write time so the read-time policy is a plain column comparison.

Test at realistic volume, read plans not vibes

Policies feel free at ten thousand rows and are anything but at ten million, so seed staging with production-shaped volume before judging any pattern. Then read the plan rather than guessing: run EXPLAIN (ANALYZE, BUFFERS) against representative queries and look for sequential scans on the policy table, the policy expression appearing among the scan's filters, and large removed-by-filter counts — the signature of a policy admitting few rows after considering many. Comparing plans before and after a change settles arguments that speculation cannot.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM public.events
WHERE org_id = $1
ORDER BY created_at DESC
LIMIT 50;

Where the escalation path ends

Partitioning, materialised summary tables and replica routing each solve real problems, but none of them is an RLS fix, and reaching for them before the basics are confirmed usually adds a second system to tune. Partitioning can even complicate policy management, since policies attach per partition. Exhaust indexes, wraps and predicate shape first; measure again; escalate only what measurably remains.

RowShield does not automate this holistic review — no scanner reasons about sargability or membership-query shape today. What it does continuously is the mechanical layer: UNINDEXED_RLS_PREDICATE and RLS_UNWRAPPED_AUTH_CALL run on every scheduled scan, so the foundations stay solid while you spend review time on the patterns above. RowShield reads pg_catalog only and is an independent product, not affiliated with or endorsed by Supabase. Run a free audit at rowshield.dev/audit — no account needed.

Frequently asked

Does Row Level Security slow down every query?

RLS adds its predicate evaluation to every query touching the table, including reads from your own application. Whether that is noticeable depends almost entirely on whether the predicate columns are indexed and the auth calls are wrapped.

Should I move filtering into the application instead?

No. Application filtering is a convenience layered on top of enforcement, never a replacement for it — anything holding the anon key can query without your application. Keep RLS as the boundary and make it fast; do not make it optional.

Is there such a thing as too many policies?

Cost follows the complexity of policy expressions far more than the count of policies — a dozen narrow, indexed, wrapped policies outperform one wide OR-chain. Review what each expression does per row, not how many rows the catalog lists.