Queries got slower the day RLS went on

What the predicate does, and why nothing warns you

A policy like USING ((SELECT auth.uid()) = user_id) is spliced into every SELECT, UPDATE and DELETE against the table, for every role it applies to. The planner treats the predicate as one more filter; when the filtered column has no usable index, satisfying that filter means reading every row in the table and testing each one against the condition.

At a thousand rows the waste is invisible, which is how the pattern survives development. At a million it dominates: latency climbs, connection pools queue behind slow queries, and compute metered by work grows in step. Nothing errors at any point — queries merely get slower, which is the least alarming symptom in computing and the easiest to misattribute.

Correctness tooling and performance tooling barely overlap. Advisors and linters check whether policies exist and whether they grant sensibly; none of them model the query plan a predicate produces. Slowdowns therefore surface where performance problems always have — user reports, dashboards, invoices — disconnected from the change that caused them by days or weeks.

Name the expensive policies

Start from the catalog: list every policy on the public tables and read the columns its conditions reference — each referenced column without a covering index is a candidate explanation for the slowdown. Focus first on the tables your hottest endpoints touch:

SELECT tablename, policyname, cmd, qual, with_check
FROM pg_catalog.pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Measure rather than infer

Then measure. Running a representative query with EXPLAIN ANALYZE as an affected role shows the plan the policy produces; a Sequential Scan whose filter matches the policy expression is the signature, and the row estimate beside it tells you how much work every call performs:

EXPLAIN ANALYZE
SELECT * FROM public.messages
WHERE user_id = '00000000-0000-0000-0000-000000000000';

The fix is one index

Give the filtered column an index and the predicate turns from a scan into a lookup. Build it concurrently so the live table keeps serving while the index fills — the statement cannot run inside a transaction block, so issue it on its own:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_user_id
  ON public.messages (user_id);

Keeping predicates cheap

Composite indexes count: a column anywhere in a valid index key covers the predicate for detection purposes, though a leading position serves best. Re-run the EXPLAIN afterwards and expect an index scan where the sequential scan stood; the difference on large tables is dramatic enough to be unmistakable, and latency follows the plan change down.

New policies arrive without their indexes the way new tables arrive without their policies — as an omission nobody notices until load reveals it. RowShield evaluates UNINDEXED_RLS_PREDICATE on every scan, reporting one finding per table and column rather than per policy, because three policies filtering the same column need one index between them and three alerts would be noise around a single fix.

RowShield is an independent product, unaffiliated with and not endorsed by Supabase; it reads pg_catalog metadata only, never rows, and references to Supabase and Postgres are descriptive. Run a free audit — no account — at rowshield.dev/audit to see which of your policies filter unindexed columns today.

Frequently asked

Is this a security problem?

No — it is a performance and cost problem, which is why the rule carries medium severity rather than critical. The policies grant exactly what they should; they simply make every query pay for the privilege.

Why CONCURRENTLY on the index build?

It avoids taking a write lock on a live table for the duration of the build. The trade-off is that the statement cannot run inside a transaction block, so it goes to the database on its own.

Should I just remove the policy instead?

Removing it restores speed by restoring exposure. The predicate is doing necessary work; the index is what makes that work affordable, and adding one costs minutes against a problem that otherwise compounds with every row.