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.
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
-- 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:
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
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:
-- 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.
-- 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 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:
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. - 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:
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.