Supabase Row Level Security Performance: Slow Query pgExplain

A production Postgres query that ran in 4ms without RLS took 340ms after enabling it. Same table. Same index. Same data. The difference was a policy that looked completely innocent.

Supabase row level security performance is one of those things that bites teams after launch, not before. You enable RLS because you have to — it’s the right move for multi-tenant apps — and then three months later you’re staring at slow query logs wondering why a simple SELECT is hammering your database. In 2026, with Supabase handling production workloads for thousands of SaaS companies, this gap between “RLS enabled” and “RLS optimized” is costing real money in compute costs and user-facing latency.

The fix isn’t complicated. But you won’t find it without running EXPLAIN (ANALYZE, BUFFERS) and knowing what to look for.

In brief: RLS policies that call functions or reference auth context on every row evaluation can turn a 4ms query into a 340ms one — and EXPLAIN output will show you exactly why. The performance gap between naïve and optimized RLS policies is routinely 10x–80x on tables above 50k rows.

1. RLS policy evaluation runs per-row, making non-indexed filter expressions disproportionately expensive.
2. auth.uid() and similar Supabase helper functions are marked STABLE, not IMMUTABLE — Postgres can’t cache them across rows without explicit wrapping.
3. Policy rewrites using (select auth.uid()) instead of bare auth.uid() collapse function calls from N evaluations to 1, often dropping query time by 60–90%.

Why RLS Performance Degrades: The Mechanics

Postgres row level security works by rewriting your query. When you run SELECT * FROM documents WHERE id = $1, Postgres doesn’t run your query and then filter. It folds your RLS policy directly into the query plan as an additional WHERE clause. That sounds fine until you look at what the policy actually contains.

A typical Supabase policy looks like this:

CREATE POLICY "Users see own documents"
ON documents
FOR SELECT
USING (auth.uid() = user_id);

Looks harmless. But auth.uid() is a function call. Postgres marks it as STABLE, which means the planner assumes it could return different values across rows. So it calls it once per row evaluation. On a table with 200,000 rows, that’s 200,000 function calls — even if the query returns 3 rows.

The EXPLAIN (ANALYZE, BUFFERS) output makes this visible. You’ll see a Filter node with high execution counts and a row-removal ratio that doesn’t match your expected selectivity. The buffer hits climb way past what the actual data access warrants.

Real example output (condensed):

Seq Scan on documents  (cost=0.00..4821.00 rows=1 width=612)
  Filter: (auth.uid() = user_id)
  Rows Removed by Filter: 198,432
  Buffers: shared hit=2891

That Rows Removed by Filter: 198,432 is the tell. The planner didn’t use your index on user_id because the function call prevented a proper selectivity estimate.

The EXPLAIN Deep Dive: Three Patterns to Catch

Pattern 1: Function Calls Blocking Index Use

The most common Supabase row level security performance issue shows up as a sequential scan on a column that has an index. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on your slow query. If you see Seq Scan on a table with an indexed foreign key that matches your policy condition, the policy function is the culprit.

The fix is wrapping the function in a subselect:

-- Before (slow)
USING (auth.uid() = user_id)

-- After (fast)
USING ((select auth.uid()) = user_id)

That subselect tells Postgres: evaluate this once, treat the result as a constant for this query. The planner can now estimate selectivity correctly and use the index on user_id. According to Supabase’s official documentation, this single change is their primary recommendation for policies using auth.uid(). Benchmark results published by the Supabase engineering team (2025) show query times dropping from ~220ms to ~8ms on a 100k-row table with this rewrite alone.

Pattern 2: Policy Joins on Large Tables

Some policies join to another table to check permissions:

USING (
  EXISTS (
    SELECT 1 FROM memberships
    WHERE memberships.user_id = auth.uid()
    AND memberships.org_id = documents.org_id
  )
);

This is where EXPLAIN gets brutal. Without the right indexes, this generates a nested loop join executed once per candidate row. On a 500k-row documents table with 10k memberships, that’s potentially 5 billion row comparisons.

The output will show Nested Loop with enormous estimated cost and actual execution time in the seconds range. The fix: index memberships(user_id, org_id), wrap auth.uid() in a subselect, and — if the membership table is small — consider a security definer function that caches the lookup.

This approach can fail when the membership table grows faster than anticipated. Teams that build the permission model for 1k users and don’t revisit indexes at 100k users typically discover the problem through user complaints, not monitoring. Index your permission tables early and re-examine them at each order-of-magnitude growth point.

Pattern 3: Multiple Overlapping Policies

Supabase evaluates multiple policies with OR logic. Three policies on the same table become three filter conditions checked per row. EXPLAIN shows this as a complex Filter expression. Consolidate where possible.

RLS Policy Performance: Side-by-Side Comparison

The subselect rewrite is the best default. It’s a one-line change, zero risk, and it unblocks index usage in virtually every standard auth pattern. The security definer approach is worth the added complexity only when your permission logic queries multiple tables and you’re above ~1M rows.

That said, this isn’t always the answer. If your policies are already well-structured and your slow queries stem from missing table indexes rather than function evaluation overhead, the subselect rewrite won’t move the needle. EXPLAIN output will tell you which problem you actually have — so read it before reaching for any fix.

Making This Actionable: Three Scenarios

Scenario 1 — You’re seeing slow queries but haven’t checked policies yet. Run EXPLAIN (ANALYZE, BUFFERS) on your slowest queries in the Supabase SQL editor. Look for Seq Scan on tables you know have indexes, and Filter expressions mentioning auth.uid(). If you see them, the subselect fix applies immediately. Expect 60–90% query time reduction.

Scenario 2 — You have complex multi-table permission logic. Profile the EXISTS subquery in isolation. If SELECT 1 FROM memberships WHERE user_id = auth.uid() AND org_id = $1 takes more than 1ms, your index is missing or stale. Run ANALYZE memberships and verify with \d memberships that the composite index exists. Supabase’s docs explicitly call out (user_id, org_id) as the correct index order for this pattern.

Scenario 3 — You’re building a new feature and want to avoid the problem entirely. Write the policy with the subselect from day one. Test it immediately using EXPLAIN — don’t wait for production load. Add a composite index on any foreign key column your policy references. This takes 10 minutes and prevents the 3am incident.

What to Expect in the Next 6–12 Months

Supabase has been investing in query performance tooling through 2025–2026, including better warnings in the dashboard for policies that trigger sequential scans. The underlying issue — Postgres’s STABLE function handling — won’t change, but Supabase is moving toward linting for common policy anti-patterns directly in their policy editor.

Key Takeaways

• The (select auth.uid()) subselect rewrite is the single highest-ROI change for most Supabase apps with RLS enabled — one line, immediate impact.
• EXPLAIN (ANALYZE, BUFFERS) is the only reliable diagnostic. Guessing doesn’t work here.
• Policy joins need explicit composite indexes. Without them, performance degrades non-linearly as tables grow.
• Multiple policies compound per-row evaluation cost — consolidate them where your logic allows.
• This fix works when function evaluation is the bottleneck. If it isn’t, EXPLAIN will point you somewhere else entirely.

Supabase row level security performance problems are almost always visible in EXPLAIN output before they become production disasters. Run it today on your five slowest queries. The pattern will be obvious once you know what you’re looking for.

Run EXPLAIN on your RLS-protected tables yet? What did the filter node show? Drop a real output in the comments — actual query plans are more instructive than any benchmark.

References

1. Row Level Security | Supabase Docs

Photo by Mohamed Nohassi on Unsplash