Supabase RLS tenant_id: The IN() Fix for Slow Queries

Your multi-tenant Supabase app worked fine in staging. Then a customer with a few thousand rows showed up in production, and a query that used to return in 20 milliseconds started taking two or three seconds. Nothing in your schema changed. What changed is how many rows Postgres has to check your policy against on every single call.

This is the most common Supabase RLS tenant_id problem out there: a policy that reads perfectly fine but forces Postgres to re-run a subquery or join once per row instead of once per query. The fix isn't a bigger Postgres instance or a plan upgrade. It's rewriting the policy so the list of tenant IDs a user can see gets computed once, then checked with a plain tenant_id IN (...) filter that Postgres can push straight through an index.

This sits inside the same multi-tenant architecture we cover in our complete guide to multi-tenant database design in Supabase, and it picks up right where a slow, correctly written RLS policy leaves off. By the end, you'll know why the naive tenant_id check falls apart at scale, how to rewrite it with tenant_id IN(), which composite index it actually needs, and where the pattern itself starts to strain once a single user belongs to hundreds of tenants.

Table of Contents

1. Why tenant_id Checks Slow Down Supabase RLS
2. InitPlan vs SubPlan: What Postgres Actually Does
3. The tenant_id IN() Fix, Step by Step
4. Composite Indexes and the LEAKPROOF Trap
5. Quick Answers About Supabase RLS tenant_id Performance
6. What Happens When the tenant_id IN List Gets Too Large?
7. Common Mistakes That Undo the tenant_id IN() Fix
8. Frequently Asked Questions

Why tenant_id Checks Slow Down Supabase RLS

A tenant_id check gets slow because Postgres evaluates it separately for every row a query touches, instead of once for the whole query. Simply put, when a policy checks tenant membership through a correlated subquery or a join, Postgres can't compute that check once and reuse it. It runs the check again for row one, again for row two, and again for every row after that.

This is easy to miss because the policy usually looks completely reasonable:

-- Slow: Postgres re-checks this join for every row in "projects"
create policy "tenant_can_read_projects"
on projects
for select
to authenticated
using (
  exists (
    select 1
    from tenant_members
    where tenant_members.tenant_id = projects.tenant_id
      and tenant_members.user_id = auth.uid()
  )
);

On a small table in development, this runs fine. Once the table crosses tens of thousands of rows, that per-row join forces the planner into a sequential scan instead of an index lookup, and query time climbs in direct proportion to table size. If you want to confirm this is actually what's happening in your own database before changing anything, EXPLAIN ANALYZE and the pg_stat_statements extension are the two tools for that.

The practical takeaway: any RLS policy that has to look something up per row, rather than check a value that was already computed once, is a candidate for the tenant_id IN() rewrite below.

InitPlan vs SubPlan: What Postgres Actually Does

The difference between a slow policy and a fast one usually comes down to a single query planner decision: does your function call become a SubPlan or an InitPlan? A bare function call like auth.uid() inside a policy's USING clause gets evaluated as part of the row-by-row filter. Postgres has no reason to assume the result won't change from row to row, so by default it plans to call it again, and again, for every row it inspects.

Wrap that same call in a subquery, like (select auth.uid()), and Postgres treats it as a self-contained, uncorrelated subquery instead. That qualifies it for an InitPlan: a plan node that runs exactly once at the start of the query and caches the result for every row that follows. This is the exact mechanism behind Supabase's own auth_rls_initplan Performance Advisor warning, which exists specifically to flag policies missing this wrapper.

The effect isn't subtle. In Supabase's own published RLS benchmarks, wrapping a bare auth.uid() = user_id check took a 179 millisecond query down to 9 milliseconds, and a security definer role check involving a table join dropped from 11 full seconds to 7 milliseconds, according to the Supabase Row Level Security documentation. If you want the formal definition of how Postgres reports these plan nodes, the PostgreSQL EXPLAIN documentation covers how subplan nodes get counted and timed.

tenant_id checks are exactly this kind of function call, just wearing a different name. Whether you're calling a raw function or a security definer helper that looks up tenant membership, the same InitPlan-versus-SubPlan rule decides whether it runs once or once per row.

The tenant_id IN() Fix, Step by Step

The fix has two parts: a STABLE security definer function that returns the tenant IDs a user belongs to, and a policy that filters with a plain tenant_id IN (...) instead of joining per row. Here's the full rewrite.

1. Create a security definer function that returns the tenant set. It runs once, bypasses RLS on the join table itself, and hands back a simple set Postgres can cache.
2. Mark it STABLE, not the VOLATILE default. STABLE tells Postgres the function returns the same result for the same arguments within one statement, which is what makes caching legal in the first place. Leave a function VOLATILE and the planner won't cache it no matter how you wrap the call.
3. Wrap the call in the policy so it becomes an InitPlan. Filtering with tenant_id IN (select get_user_tenant_ids()) runs the function once and reuses the result for every row.
4. Drop and recreate any existing policy that used the join pattern. Supabase policies can't be altered in place, so the old one needs to be dropped and the new one created in the same migration.

-- Runs once per query, not once per row.
-- SECURITY DEFINER bypasses RLS on tenant_members so this
-- doesn't trigger its own recursive policy check.
create or replace function get_user_tenant_ids()
returns setof uuid
language sql
security definer
stable
set search_path = public
as $$
  select tenant_id
  from tenant_members
  where user_id = (select auth.uid())
$$;

revoke all on function get_user_tenant_ids() from public, anon;
grant execute on function get_user_tenant_ids() to authenticated;

-- Fast: tenant_id is checked against a cached IN() list,
-- not a per-row join against tenant_members.
drop policy if exists "tenant_can_read_projects" on projects;

create policy "tenant_can_read_projects"
on projects
for select
to authenticated
using (
  tenant_id in (select get_user_tenant_ids())
);

Notice the function is security definer, meaning it runs with the privileges of whoever created it, not the calling user. That's what lets it read tenant_members directly without triggering that table's own RLS and starting the recursive-join problem all over again. Keep functions like this out of any schema exposed to the API, or a client could call them directly and read data they shouldn't.

get_user_tenant_ids() only answers which tenants a user belongs to, not what they're allowed to do inside each one. If you also need role-level permissions on top of tenant membership, Owner, Admin, Manager, Member, see our guide to hierarchical Postgres RBAC for B2B SaaS.

One nuance worth flagging: if you're rewriting an UPDATE policy, Postgres requires a matching SELECT policy on the same table for the UPDATE to work at all. It's an easy detail to miss the first time, since the error you get back doesn't mention SELECT anywhere.

That's worth flagging in the other direction too: rewriting the read side into a tenant_id IN() filter does nothing for the write side of the same table. A SELECT policy scoped this way and a WITH CHECK clause on the matching UPDATE policy are two separate checks, and a correctly scoped read filter can sit right next to an UPDATE policy that lets a user reassign a row's tenant_id to someone else's. We cover that specific gap, along with four other silent ways RLS setups fail in production, in 5 Silent RLS Mistakes That Leave Your Database Exposed.

Composite Indexes and the LEAKPROOF Trap

Indexing tenant_id by itself only helps if your query filters on tenant_id alone, which it almost never does. A real query is closer to "this tenant's pending orders," not "every row for this tenant." If the index only covers tenant_id, Postgres still has to scan every one of that tenant's rows to find the ones with the right status.

-- tenant_id first (always filtered), then the column
-- your application actually queries on most often.
create index idx_projects_tenant_status
on projects (tenant_id, status);

Column order matters here. Put tenant_id first since every query filters on it, then whatever column your application filters on most, like status or created_at.

There's a second, less obvious way an index gets bypassed even when it exists: LEAKPROOF. Postgres has to evaluate RLS security conditions before any user-supplied predicate that isn't marked leakproof, so a malicious query can't use error messages or timing to infer data it shouldn't see. Most built-in comparison operators are leakproof. Custom functions and some pattern-matching operators, including current_setting(), generally aren't. When a non-leakproof operator shows up in your query, Postgres treats it as a security barrier and can't freely push it down into the index scan alongside your policy, even if the index itself is perfect.

For a plain tenant_id IN() setup this rarely bites you, since the comparison is a straightforward equality check. It matters most once you introduce a GUC-based approach using current_setting(), which is covered in the next section. For the full mechanics of how Postgres orders this security barrier check against your own application filters, see our breakdown of RLS query optimization.

Quick Answers About Supabase RLS tenant_id Performance

What Causes Slow tenant_id RLS Queries?

Simply put, a tenant_id RLS policy gets slow when Postgres can't compute tenant membership once and reuse it, so it recomputes the check for every row instead. This happens with a raw auth.uid() comparison, a join against a membership table, or any function call that isn't wrapped in a subquery. It matters most on tables with tens of thousands of rows or more, where a per-row check turns into a full sequential scan.

Supabase RLS tenant_id Performance at a Glance

When Does This Apply?

This fix applies to any Supabase or self-hosted Postgres table with row level security enabled, where access is scoped by a tenant, team, or organization ID rather than a single user_id. If your table only ever filters on auth.uid() = user_id with no join or set membership involved, the plain InitPlan wrapper from earlier is enough on its own and you don't need the full rewrite.

Pros and Cons of tenant_id IN() vs a Direct Join

• Pro: Runs once per query instead of once per row, regardless of table size
• Pro: Works with a plain composite index, no exotic query patterns required
• Pro: The security definer function is reusable across every policy that needs tenant scoping

• Con: Adds a function you have to maintain and keep correctly marked STABLE
• Con: Degrades again if a single user's tenant list grows into the thousands

What Happens When the tenant_id IN List Gets Too Large?

The tenant_id IN() pattern holds up well for the vast majority of users, but it starts to strain once someone belongs to an unusually large number of tenants. Think enterprise admins, agency accounts, or any role that spans dozens or hundreds of organizations instead of one or two.

Supabase's own guidance draws the line in two places depending on which pattern you're using. For a set built from a security definer function and filtered with IN, expect to reassess your approach once the list crosses roughly 1,000 items. For a plain IN subquery, Supabase's RLS performance and best practices guide puts the threshold closer to 10,000 items before extra analysis is warranted. Either way, once you're past the low thousands, the array itself, not the join, becomes what the planner has to work through.

So which one should you reach for once you hit that ceiling? Two alternative architectures show up at this point.

Custom JWT Claims

Instead of looking tenant membership up in the database at query time, you bake the tenant ID array directly into the user's JWT under app_metadata, which is only writable server-side. A policy can then read it straight from the token with no database round trip at all:

-- Reads tenant membership straight from the JWT app_metadata claim.
-- jsonb_array_elements_text() expands the array so ANY can compare
-- against each tenant ID individually, not the array as a whole.
create policy "tenant_can_read_projects_jwt"
on projects
for select
to authenticated
using (
  tenant_id = any(
    select jsonb_array_elements_text(
      (select auth.jwt() -> 'app_metadata' -> 'tenant_ids')
    )::uuid
  )
);

This is fast because it skips the lookup entirely, but it comes with two real trade-offs. Browsers commonly cap cookies at 4,096 bytes, so a JWT carrying hundreds of tenant IDs can blow past that limit. And a JWT isn't live: if you remove someone from a tenant, that change won't take effect until their token refreshes, which complicates anything built on Supabase Realtime where a user might switch tenant context mid-session.

Our guide to custom JWT claims for RLS walks through the hook setup, a fix for that staleness problem, and the WITH CHECK gap this pattern needs on writes.

GUC Session Variables

Native Postgres also supports scoping a transaction with a session variable, set once per request through current_setting(), instead of relying on auth.uid() at all:

-- Scopes the query to a session variable set once per request,
-- independent of Supabase Auth's auth.uid().
create policy "tenant_can_read_projects_guc"
on projects
for select
to authenticated
using (
  tenant_id = current_setting('app.tenant_id', true)::uuid
);

This works outside Supabase's auth layer entirely, which is useful if you're managing identity yourself. The catch is connection pooling. Behind PgBouncer, the pool mode has to be session or transaction, since SET LOCAL doesn't survive in statement mode, and a variable that isn't cleared at the end of a transaction can leak tenant context into the next request that reuses the same connection.

For most B2B SaaS teams, the tenant_id IN() function pattern from earlier is the right default. These two alternatives are worth reaching for only once you've confirmed the IN list itself is the bottleneck, not before.

Common Mistakes That Undo the tenant_id IN() Fix

Even a correctly rewritten policy gets undone by a handful of repeat mistakes, and most of them show up as confusing symptoms rather than clear errors.

Treating an Empty Result as a Bug Instead of RLS Working

Here's the thing: RLS doesn't throw a permission error when a row fails a policy. It silently filters that row out of the result set instead. A query that should return five rows and returns zero usually means the policy is correctly blocking access, not that something is broken. Check the policy and the JWT claims first, before you start debugging the client.

Dropping the Client-Side Filter

Once RLS is in place, it's tempting to stop passing .eq('tenant_id', currentTenant) from the client, since the policy already restricts the rows. Keep the explicit filter anyway. RLS policies function as an implicit WHERE clause the planner can't always see ahead of time, and an explicit filter gives it a concrete value to plan around, which measurably helps performance even though the two filters are logically redundant.

Reaching for a Bigger IN List Instead of BYPASSRLS

Admin dashboards and internal tools sometimes try to solve "let this one role see every tenant" by stuffing every tenant_id into the allowed list. Don't. Postgres roles can be granted the bypassrls privilege directly, which is the correct tool for a role that genuinely needs cross-tenant visibility, and it keeps that exception explicit and auditable instead of buried inside a policy meant for regular tenant scoping.

-- For roles that genuinely need cross-tenant access,
-- like an internal admin service. Never share these credentials.
alter role "internal_admin_role" with bypassrls;

Get these three right and the tenant_id IN() fix holds up the way the benchmarks suggest it should. Get any one of them wrong and you're back to debugging a policy that looks correct but performs like it isn't.

Frequently Asked Questions

Does RLS slow down Postgres queries?

Yes, RLS policies act as an implicit WHERE clause evaluated per row, which can slow queries measurably. Wrapping identity functions in a subquery to force an InitPlan, adding a composite index, and filtering with tenant_id IN (...) instead of a join brings that overhead down to a few milliseconds on most tables.

Why does my Supabase query return an empty array instead of an error?

RLS doesn't throw a permission error when a row fails a policy check. It silently filters the row out of the result set instead. An empty array from a query that should return rows almost always means the policy is working as intended, not that something broke.

Should I still filter by tenant_id on the client if RLS already handles it?

Yes. RLS is your security boundary, but an explicit .eq('tenant_id', ...) filter on the client helps the query planner build a better plan, even though the two filters are logically redundant. Skipping it doesn't create a security gap, but it does leave performance on the table.

What's the difference between tenant_id IN() and a security definer function?

They usually work together. The security definer function computes which tenant IDs a user can see, bypassing RLS on the membership table itself. The tenant_id IN (...) clause is the policy filter that uses that result. One without the other still leaves you with a slow or recursive policy.

Does the tenant_id IN() fix work the same way for UPDATE and DELETE policies?

The pattern is the same, but Postgres requires a working SELECT policy for an UPDATE policy to function at all. If you only rewrite the UPDATE policy and forget the matching SELECT policy, updates will fail in ways that don't obviously point back to a missing SELECT rule.

How do I switch tenants without forcing a full re-login?

If tenant membership is read from a JWT claim, switching tenants means refreshing the token, not logging out. Most Supabase client libraries expose a session refresh method for this. If you're using the security definer function pattern instead, there's no token to refresh, since the check runs live against the database.

Can tenant context leak across requests when using GUC session variables?

Yes, if a connection pooler reuses a session without clearing the variable. This is a known risk with PgBouncer when current_setting() is used for tenant scoping. Keep the pool mode set to session or transaction, and always reset the variable at the end of every transaction.

Does this fix apply if I'm not using Supabase Auth?

Yes. The InitPlan caching behavior and the tenant_id IN() pattern are native Postgres query planner mechanics, not Supabase-specific features. If you're using Clerk, Auth0, or a custom identity layer, the same rewrite works with a GUC session variable or a custom JWT claim instead of auth.uid().

Wrapping Up

A slow Supabase RLS tenant_id query almost always comes down to the same root cause: a policy that has to recompute tenant membership for every row instead of once per query. The fix is consistent too. Wrap the check so Postgres can cache it as an InitPlan, move tenant lookups into a STABLE security definer function, filter with a plain tenant_id IN (...), and back it with a composite index that matches your actual query pattern.

None of this is exotic, and that's the point. It's the same InitPlan-versus-SubPlan logic Postgres uses everywhere, applied specifically to the tenant scoping every B2B SaaS schema needs, the same tenant isolation that shows up in SOC 2 and GDPR access-control reviews, not just in your query times.

If you haven't confirmed yet that tenant_id filtering is actually what's slowing your queries down, start with our guide on why Supabase RLS gets slow, which walks through diagnosing it with EXPLAIN ANALYZE before you touch a single policy. Once you've made this change, run your own numbers through EXPLAIN ANALYZE and compare them against the benchmarks above. This pattern earns its place in your schema. It doesn't need to be taken on faith.