Supabase RLS was slow at 100k rows. One change fixed it.

My warehouse app worked fine with test data. Then I loaded it with 100,000 purchase orders.

Two screens stopped loading. The receiving list and the dashboard metrics both went past the 8 second database timeout.

The cause

The app uses Supabase Row Level Security. Every table has policies that check who the user is and what role they have.

Most of my policies looked like this:

create policy "inventory_stock_update_ops_roles" on inventory_stock
for update
using ( current_user_has_any_role(array['super_admin', 'operations_manager']) );
 
create policy "Users can read own profile" on profiles
for select using ( id = auth.uid() );

Nothing wrong with the rule. The problem is how Postgres runs it.

A function call like current_user_has_any_role(...) or auth.uid() inside a policy can be run once for every row the query checks. With 500 rows you never feel it. With 100k, it adds up to seconds.

The fix

Wrap the call in (select ...):

alter policy "inventory_stock_update_ops_roles" on inventory_stock
  using ( (select current_user_has_any_role(array['super_admin', 'operations_manager'])) );
 
alter policy "Users can read own profile" on profiles
  using ( id = (select auth.uid()) );

Now Postgres runs it once per query and reuses the result for every row. Same rule, same security. alter policy only replaces the condition, so each policy keeps its command and roles.

In my case 20 of 26 role-checking policies had this pattern. I rewrote 27 policies across 9 tables in one migration, generated from the live policies so the commands and roles stayed exactly the same.

Faster is not enough

With security code, a faster query that returns different rows is a bug, not a fix.

So before shipping, I compared what every role could see, before and after, across 13 tables and views. The rows were identical.

The result

Check your own policies

Search your migrations for policies that call a function directly:

grep -rniE "auth\.uid\(\)|auth\.jwt\(\)|has_any_role\(" supabase/migrations | grep -vi "(select"

Change the function names to match your own helpers. Every match is a candidate. Wrap it, then check two things before you ship: the query is faster, and every role still sees the same rows.

Supabase documents this in its RLS performance guide (opens in new tab).