# RLS performance best practices

> Slow queries with Row Level Security are usually fixable. Wrap auth.uid() in a select, index policy columns, name the role, avoid per-row joins, and measure as the real API role.

Source: https://supahost.app/kb/rls-performance · Updated 2026-11-11

Row Level Security adds a condition to every query on a table. Written well, that costs almost nothing. Written badly, the condition runs once per row and a 20 ms query takes several seconds. These are the fixes that matter most, roughly in order of impact. The examples use a bookings app.

## 1. Wrap auth functions in a select

```sql
-- slow: auth.uid() is called for every row
using ( auth.uid() = user_id )

-- fast: evaluated once per statement, then reused
using ( (select auth.uid()) = user_id )
```

Wrapping the call in `(select ...)` lets Postgres run it once as an "initPlan" and compare each row to a constant. The same applies to `auth.jwt()` and to your own helper functions that don't depend on the row. Don't wrap anything that uses the row's own columns, since that has to run per row.

## 2. Index the columns your policies use

A policy like `(select auth.uid()) = user_id` is a filter on `user_id`. Without an index, Postgres reads the whole table to apply it:

```sql
create index bookings_user_id_idx on public.bookings (user_id);
```

Do the same for `team_id`, `org_id` or whatever column your policies compare against.

## 3. Name the role with `to`

```sql
create policy "Users read own bookings"
on public.bookings
for select
to authenticated
using ( (select auth.uid()) = user_id );
```

Without `to authenticated` the policy applies to every role, so even `anon` requests evaluate it. With it, Postgres skips the policy entirely for other roles.

## 4. Avoid joins to the row's table inside policies

This policy looks up the booking's team for every row it checks:

```sql
-- slow: a correlated subquery per row
using (
  (select auth.uid()) in (
    select user_id from public.team_members
    where team_members.team_id = bookings.team_id
  )
)
```

Turn it around: fetch the user's teams once, then test each row against that set.

```sql
-- fast: one lookup, then a simple filter that can use an index on team_id
using (
  team_id in (
    select team_id from public.team_members
    where user_id = (select auth.uid())
  )
)
```

If `team_members` has its own RLS, move the lookup into a `stable security definer` function in a private schema and call it as `team_id in (select private.my_team_ids())`. See [security definer functions and RLS](/kb/security-definer-functions) for a safe version.

## 5. Filter in the query too

RLS is a safety net, not a query plan. Add the same filter in your client call so Postgres can use the index directly:

```ts
const { data } = await supabase
  .from('bookings')
  .select('id, starts_at, room_id')
  .eq('user_id', user.id)
  .order('starts_at')
```

## 6. Keep policies cheap

- Prefer comparing columns to values over calling functions that do their own queries.
- A value you check in many policies, like a user's role, can live in the user's `app_metadata` (set from the server with the admin API). It's copied into the JWT, so a policy can read it with `(select auth.jwt()) -> 'app_metadata' ->> 'role'` instead of querying a table. It only updates when the user's token is refreshed. See [Roles, custom claims and RBAC](/docs/auth/custom-claims-rbac).
- Several permissive policies on the same table and command are combined with `or`, and each is evaluated. Merge them when you can.

## Measure as the API role

Studio's SQL editor connects as `postgres`, which bypasses RLS, so a fast query there proves nothing. Test as `authenticated` with a real user id:

```sql
begin;
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"5d0c8a6e-1b7f-4a7e-9a52-0c3f1d2b9e44","role":"authenticated"}', true);

explain (analyze, buffers)
select * from public.bookings order by starts_at limit 50;

rollback;
```

Look for `InitPlan` near the top (good) and `Seq Scan` with a `Filter:` that contains your policy on a big table (needs an index or a rewrite).

## On SupaHost

All of this is standard Postgres and works the same here. Studio connects as `postgres`, as above. The MCP server's `get_advisors` tool checks for tables without RLS or without policies; it doesn't check policy performance, so use `explain` as shown.

## Related

- [Row Level Security, simplified](/kb/rls-simplified)
- [Row Level Security](/docs/database/row-level-security)
- [Query performance and EXPLAIN](/docs/database/query-performance)
- [Speeding up slow queries with indexes](/kb/improve-query-performance)