RLS Query Optimization: The Fix That Cuts Query Time 90%+

A query that should finish in about a dozen milliseconds instead crawls past 1.8 seconds. That's the kind of jump developers report when a Row Level Security policy on a busy Postgres table gets evaluated the wrong way, running once per candidate row instead of once per query. Most teams respond by rewriting the policy itself, wrapping a function in a subselect or marking it STABLE, and stop there. Real RLS query optimization means going further than that.

That's only one piece of the fix. If you're working through the broader multi-tenant schema design this sits inside, that's worth a read first. Making these queries hold up in production takes three things working together: a lean policy, an explicit tenant filter at the application layer, and an index built for how the query actually runs. Skip any one of the three and the planner falls back to scanning far more of the table than it needs to.

This guide covers why Postgres treats RLS as a security barrier the planner can't fully optimize around by itself, why an "unnecessary" tenant filter in your application code can cut latency by more than 90% even with RLS already active, and how to build indexes for your real query shape instead of just the policy column.

Table of Contents

1. Why Doesn't Optimizing RLS Syntax Alone Fix the Problem?
2. Why Does Postgres Check RLS Before Your WHERE Clause?
3. Pillar 1: Keep the RLS Policy Itself Lean
4. Pillar 2: Filter by Tenant in Your Application Query Too
5. Pillar 3: Build Indexes for Your Real Query Shape
6. What's Actually Causing the Buffer Cache Churn?
7. Putting the Three Pillars Together
8. How Do You Confirm the Fix Actually Worked?
9. Quick Answers About RLS Query Optimization
10. Frequently Asked Questions

Why Doesn't Optimizing RLS Syntax Alone Fix the Problem?

Simply put, Row Level Security itself is usually not the slow part. Independent p95 latency testing on a 1 million row table found the raw evaluation overhead of an active policy added roughly 2%, about 1.6 milliseconds, once the underlying access pattern was already indexed. The syntax tips you'll find everywhere, like wrapping the function in a subselect or marking it STABLE, matter, but they only fix how expensive the policy check is per row. They don't fix how many rows Postgres ends up checking in the first place.

That second number is what actually blows up your latency. A policy that evaluates in microseconds still hurts if it's running against a full sequential scan of a multi-tenant table, because the planner had no index range or explicit filter to narrow the search first. That's the gap most RLS advice skips: query optimization has to cover what happens around the policy, not just inside it.

Fix the policy syntax and you'll shave milliseconds. Fix the query shape and indexing around it and you'll usually cut execution time by an order of magnitude or more.

Why Does Postgres Check RLS Before Your WHERE Clause?

Simply put, Postgres treats every RLS-protected table as security-barrier protected, and the planner isn't allowed to run your own query conditions ahead of the security policy unless it can prove those conditions are safe. When you enable row security, the query rewriter injects the policy expression into the query at the rewrite stage, before the planner ever builds an execution plan. That's documented directly in PostgreSQL's row security documentation.

Here's the part that catches people off guard. The planner is normally free to reorder WHERE clause conditions to run first, except on RLS-protected tables. There, it can only push a user-supplied condition ahead of the security barrier if that condition's function is explicitly marked LEAKPROOF, meaning it's guaranteed not to leak information through side channels like error messages or timing.

Most application-level filters, a plain equality check on a UUID column for instance, don't carry that marking. So when you query status = 'pending' on a table with an active tenant policy, the engine still has to evaluate the RLS condition across every candidate row before it applies your status filter.

In practice, this means the planner can end up scanning wide index ranges or falling back to a sequential scan just to satisfy the security barrier, even when your own filter would have narrowed the result set to a handful of rows. Understanding this ordering is what makes the next two pillars click: you're not fighting Postgres, you're working with how it's designed to keep row security safe.

Pillar 1: Keep the RLS Policy Itself Lean

Before the policy even reaches the security barrier problem above, make sure it's cheap to evaluate. Wrap any call to an auth function in a scalar subselect so Postgres generates an InitPlan node and evaluates it once per query instead of once per row:

-- Slow: function re-evaluated for every candidate row
CREATE POLICY "tenant_isolation" ON orders
  USING (auth.uid() = user_id);

-- Fast: InitPlan caches the scalar result once per query
CREATE POLICY "tenant_isolation" ON orders
  USING ((SELECT auth.uid()) = user_id);

In Supabase's own benchmark testing on a 100,000 row table, this single change took a policy check from 179ms down to 9ms, about a 20x improvement. For cross-table lookups, like checking team membership in a separate table, wrap the logic in a function marked STABLE and, where the access pattern allows it, SECURITY DEFINER so the lookup can bypass the target table's own RLS instead of triggering a second, recursive policy check. The same benchmark set found this cut a join-heavy policy from 11 seconds to 7 milliseconds at the same table size.

Role and permission checks are the most common cross-table lookup this applies to. For a complete has_permission() implementation backing a full hierarchical RBAC schema, not just a single team-membership check, see our guide to Postgres RBAC for B2B SaaS.

This part of the fix is well covered ground. If your policies aren't wrapped this way yet, start with the full diagnosis and fix here before moving on. A lean policy is necessary, but the numbers above show it isn't sufficient by itself once a table passes a few thousand rows.

Pillar 2: Filter by Tenant in Your Application Query Too

Simply put, adding a tenant filter to your application query on top of an RLS policy that already enforces the same rule is not redundant, it's what lets the planner use your indexes properly. Because of the security barrier ordering covered above, RLS alone can't always give the planner a tight enough starting point. An explicit, parameterized filter does.

The numbers back this up directly. In Supabase's own testing, adding .eq('user_id', id) to a query that already had a matching RLS policy took the same query from 171ms down to 9ms, a 94% reduction, purely from giving the planner a literal value to bound its index scan with instead of relying on the security barrier alone.

// RLS-only: the policy enforces the rule, but the planner
// has less to work with up front
const { data } = await supabase.from('orders').select('*');

// RLS + explicit filter: same security guarantee,
// but the planner gets a literal bound immediately
const { data } = await supabase
  .from('orders')
  .select('*')
  .eq('tenant_id', tenantId);

This is the same principle behind the tenant_id IN() pattern for cases where a user belongs to multiple teams or tenants at once. There's also a security argument for keeping this filter even after RLS is confirmed working: it gives you defense in depth. If a policy is ever misconfigured, disabled during a migration, or bypassed by a service role key that ends up somewhere it shouldn't, an explicit application-level filter is still there enforcing the boundary. That's a distinction auditors reviewing SOC2 controls will ask about directly: is tenant isolation enforced at one layer or two?

Treat RLS as the backstop and the application filter as the primary access control, not the other way around, and you get both the performance and the safety net.

Pillar 3: Build Indexes for Your Real Query Shape

Simply put, a single-column index on your tenant or policy column is necessary but rarely sufficient once queries filter or sort on a second column too. Get the tenant index in place first. In Supabase's benchmarks, adding a plain B-tree index on the policy column took an unindexed 171ms query down to under 0.1ms, a swing of more than 1,700x. If you haven't indexed the columns your policies reference yet, that's the single highest-leverage change available.

But that index alone stops paying off once your query adds a second condition. In independent testing, adding a secondary status filter on top of a tenant-indexed table only improved latency by about 5%, because Postgres still had to fetch the actual table row from disk or page cache for every candidate that matched the tenant index, just to check the second condition. A single-column index gets you to the right rows fast; it doesn't help you filter within them.

The fix is a composite index ordered to match how the query actually filters:

-- Matches WHERE tenant_id = $1 AND status = $2
CREATE INDEX idx_orders_tenant_status
  ON orders (tenant_id, status);

Column order matters here. Put the column with the highest selectivity, usually your tenant or account ID, first, then the column your query actually filters or sorts on next. That's what turns a partial win into an index-only scan that satisfies both the security barrier and your business logic from the same lookup, without a separate heap fetch per row.

The same logic applies to less trivial access patterns, like a user belonging to multiple teams. An unindexed set-based lookup structured as team_id = ANY(user_teams()) timed out entirely past 120 seconds on a 1 million row table in Supabase's testing. Rewriting the lookup and adding an index brought that down to 2 to 3 milliseconds, even at 500 teams:

-- Slow: planner can't bind a function call to an index scan
-- (times out past 120s on 1M+ rows)
USING (team_id = ANY(user_teams()));

-- Fast: the ARRAY(SELECT ...) wrapper gives the planner
-- a set it can bind directly to the index below
USING (team_id = ANY(ARRAY(SELECT user_teams())));

CREATE INDEX idx_orders_team_id ON orders (team_id);

The pattern matters as much as the index. The planner needs a set it can actually bind to an index scan, not a function it has to call once per row.

What's Actually Causing the Buffer Cache Churn?

Simply put, when RLS forces a wide index scan or sequential scan across a shared multi-tenant table, Postgres pulls pages belonging to other tenants into the buffer cache along the way, and that churn shows up in EXPLAIN (ANALYZE, BUFFERS) output even when the query eventually returns the right rows. This is the part that rarely gets mentioned alongside RLS performance advice, because it doesn't show up as a wrong result, only as unexplained latency and memory pressure under load.

One developer's production incident report is a good illustration. A PostgREST call that should have finished in roughly 12 milliseconds instead took 1.86 seconds, about 155 times slower than expected. Profiling traced it to a permission-check function firing once per candidate row, more than 8,000 separate calls, instead of once for the whole query. The resulting plan showed a sequential scan whose filter step alone racked up close to 25,000 shared buffer hits before the matching rows ever came back.

Every one of those buffer hits is a page that had to be located in shared memory or pulled in from disk, and on a busy multi-tenant table, a lot of those pages belong to tenants that have nothing to do with the current query. Under concurrent load, that's cache space and I/O bandwidth taken away from the queries that should be fast. Pillar 2 and Pillar 3 both attack this directly: an explicit tenant filter combined with a matching index means the planner can go straight to the relevant pages instead of walking through everyone else's data to get there.

Putting the Three Pillars Together

Each pillar solves a different part of the problem, and none of them substitute for the other two:

• Database security layer: a lean, InitPlan-cached RLS policy that acts as the backstop, not the primary access-control mechanism.
• Application scoping layer: an explicit, parameterized tenant filter on every query, so the planner gets a literal bound before it ever reaches the security barrier.
• Physical storage layer: composite indexes ordered to match how the application actually filters, not just the column the policy references.

Stacked together, the effect compounds rather than just adding up. Here's how the individual fixes compare on a 100,000 to 1 million row multi-tenant table, based on Supabase's own benchmark testing:

None of these numbers are from a toy dataset. They're the same order of magnitude you should expect once all three pillars are in place on a real multi-tenant table. The security layer stays cheap, the application layer gives the planner a starting point, and the storage layer makes sure that starting point resolves to an index-only scan instead of a stack of heap fetches.

How Do You Confirm the Fix Actually Worked?

Simply put, run EXPLAIN (ANALYZE, BUFFERS) on the query before and after, and check three things: scan type, buffer hits, and execution time. A fix that's actually working shows up as an Index Scan or Index Only Scan instead of a Seq Scan, a shared buffer hit count in the hundreds instead of the thousands, and execution time in single-digit milliseconds instead of hundreds.

1. Run EXPLAIN (ANALYZE, BUFFERS) on the exact query your application sends, not a simplified version.
2. Check the scan node type on the table with the RLS policy. A Seq Scan or a wide Bitmap Heap Scan means the planner still isn't getting a tight enough bound from either your filter or your index.
3. Check the Buffers line for shared hit and shared read counts. A high number here, even with a fast total time, is an early warning sign under concurrent production load.
4. If you're on Supabase and querying through PostgREST, the .explain() modifier on a request gives you the same plan output without needing direct database access.

If the plan still shows a sequential scan after adding the filter and the index, double check that the index actually matches your filter's column order and that the application query is sending the tenant value as a literal parameter, not something the planner has to resolve at execution time.

Quick Answers About RLS Query Optimization

What Causes Slow RLS Queries?

Simply put, slow RLS queries almost always come down to the planner scanning far more rows than necessary, not the policy check itself being expensive. The security barrier ordering means Postgres evaluates the policy before most user-supplied filters, so without an explicit tenant filter and a matching index, the engine falls back to a sequential or wide index scan across the whole table. That matters most once a table passes roughly 100,000 rows, where the gap between a scan and an index-only lookup becomes the difference between milliseconds and seconds.

RLS Query Optimization at a Glance

When Does This Apply to You?

This matters most for multi-tenant B2B SaaS tables that have grown past roughly 100,000 rows, or smaller tables under heavy concurrent load. If your table is small and read volume is low, a correctly indexed RLS policy alone is usually enough. Once you're seeing timeouts, connection pool pressure, or support tickets about slow dashboards, all three pillars are worth implementing together.

Frequently Asked Questions

Does Row Level Security affect performance in PostgreSQL?

Yes, but usually far less than teams expect. Independent benchmarking found the raw overhead of an active RLS policy adds around 2% to query time once the underlying columns are indexed. Most of the latency people blame on RLS actually comes from missing indexes or missing application-level filters.

Why is Supabase RLS slow on large tables?

Usually because the policy triggers a sequential or wide index scan instead of an index-only lookup, either from an unindexed policy column, a policy function that isn't marked STABLE, or a query with no explicit tenant filter for the planner to bound against.

Should I filter by tenant_id in queries if RLS is already enabled?

Yes. RLS enforces the rule, but an explicit tenant_id filter gives the query planner a literal value to bound its index scan with immediately, often cutting latency by 90% or more on the same query. It also adds a second enforcement layer if the policy is ever misconfigured.

Can RLS cause sequential scans when indexes exist?

Yes, if the query's secondary conditions aren't part of the index. A single-column tenant index gets the planner to the right rows, but if the query also filters on a second column that isn't in that index, Postgres still fetches each candidate row from the heap to check it.

Does PostgreSQL cache RLS policy function results?

Only if the function is marked STABLE and wrapped so the planner can generate an InitPlan. A VOLATILE function, the default, gets re-evaluated for every row. Wrapping a call like auth.uid() in a subselect is what triggers the caching behavior.

Why should I index foreign keys used in RLS policies?

Because the planner needs an access path to satisfy the security barrier without scanning the whole table. Benchmark testing found adding a plain B-tree index on an unindexed policy column took a 171ms query down to under 0.1ms on a 100,000 row table.

What is the difference between SECURITY DEFINER and SECURITY INVOKER in RLS?

A SECURITY DEFINER function runs with the privileges of whoever created it, which can bypass RLS on tables it queries. SECURITY INVOKER, the default, runs with the calling user's own permissions and still respects RLS on every table it touches.

How do I optimize RLS policies in Supabase?

Wrap auth function calls in subselects for InitPlan caching, mark cross-table lookup functions STABLE, add explicit tenant filters in every application query, and build composite indexes matching your actual filter columns, not just the policy column.

The Real Fix for RLS Query Optimization

RLS query optimization stops being a guessing game once you stop treating it as a single policy-syntax problem. A lean, InitPlan-cached policy keeps the security check itself cheap. An explicit tenant filter in your application code gives the planner a literal bound to work with instead of relying on the security barrier alone. A composite index built for your actual query shape, not just the policy column, is what turns that bound into an index-only scan instead of a stack of heap fetches.

Each pillar fixes a different failure mode. Skip one and the other two usually only get you partway there. Run the fix on your slowest tenant-scoped query first, check the plan with EXPLAIN (ANALYZE, BUFFERS) before and after, and you should see the same order-of-magnitude drop the benchmarks above show.

Postgres and Supabase both keep shipping planner improvements, so if you're reading this well after publication, it's worth re-running the numbers rather than assuming they still hold exactly. The three-pillar shape of the fix isn't going anywhere though, and if it saves you the debugging session it saved everyone quoted above, it's worth passing on to whoever else owns your schema.