enabling rls took a 40ms select to 6.2s on a 2.1m row table Performance

Wrap the function call in a subselect:

using ( user_id = (select auth.uid()) )

That is the whole fix and it is worth understanding why. Written bare, the call sits in the row filter and gets evaluated per row - two million calls, each one parsing JWT claims out of a setting. Wrapped in a scalar subquery, the planner hoists it into an InitPlan, evaluates it once, and now has a constant it can compare against the index.

While you are in there, add the role to the policy:

create policy docs_own on documents
  for select to authenticated
  using ( user_id = (select auth.uid()) );

Without to authenticated the expression is also evaluated for anonymous requests that could never match anything.

The same trick applies to anything expensive in a policy, not just auth.uid(). If your policy joins a memberships table, put the whole lookup in a security definer function and call it inside a subselect so it runs once. A policy that joins on every row is a nested loop over your tenant table that nobody ever profiles because it never appears in application code.

Rule of thumb I use: a policy predicate should compare a column against a constant. Anything more than that and it should be behind a function you can benchmark on its own.

After the subselect fix, check the sort. A list endpoint that orders by created_at desc limit 20 wants (user_id, created_at desc) as one index, not two separate ones. That took my equivalent query from 41ms to 3ms because it stopped sorting 8,000 rows to show 20.

Related habit worth building: keep the filter in your query too. RLS is a backstop, not a query plan. select * from documents with a policy is asking the database to look at every row and then throw most away; select * from documents where user_id = $1 with the same policy asks it to look at twelve. Same result, wildly different work, and if the policy is ever wrong you still return the right rows.

Honestly at 2m rows I would turn RLS off and do the filtering in the API layer. You control the queries anyway and you get the plan you expect every time.