Skip to content
Database

Row Level Security performance

Measure and tune Postgres Row Level Security policies.

Measure the cost of Row Level Security (RLS) and tune policies that are already correct. To learn how to write correct policies, see Row Level Security.

Postgres evaluates a policy expression against each candidate row, so the cost scales with the rows a query scans. This matters most for queries that scan every row in a table, like many select operations, including those using limit, offset, and ordering.

Three policy rules affect performance enough that they belong with the policy itself rather than here. Apply them first:

Diagnose whether RLS is the bottleneck#

Confirm that policies are the cost before you rewrite one. Run the query with RLS enabled, then again with it disabled, and compare. If the times are similar, the query itself is the problem.

To reproduce an API request, set the JWT claims and switch to the role the request runs as:

1
set session role authenticated;
2
set request.jwt.claims to '{"role":"authenticated", "sub":"5950b438-b07c-4012-8190-6ce79e4bd8e5"}';
3
4
explain analyze select count(*) from rlstest;
5
6
set session role postgres;

The output shows the policy expression as a filter, and the execution time is the number to compare:

1
Seq Scan on rlstest  (cost=0.00..4334.00 rows=1 width=35) (actual time=170.999..170.999 rows=0 loops=1)
2
  Filter: ((COALESCE(NULLIF(current_setting('request.jwt.claim.sub'::text, true), ''::text), ((NULLIF(current_setting('request.jwt.claims'::text, true), ''::text))::jsonb ->> 'sub'::text)))::uuid = user_id)
3
  Rows Removed by Filter: 100000
4
Planning Time: 0.216 ms
5
Execution Time: 171.033 ms

Rows Removed by Filter is the signal to watch. A policy that removes most of the table on every read is a policy whose filter column needs an index.

Measure through the Data API#

PostgREST can return the query plan to a Supabase client. Enable it first:

1
alter role authenticator set pgrst.db_plan_enabled to true;
2
notify pgrst, 'reload config';

Then add the .explain() modifier to a query:

1
const { data, error } = await supabase
2
  .from('projects')
3
  .select('*')
4
  .eq('id', 1)
5
  .explain({ analyze: true })
6
7
console.log(data)
1
Aggregate  (cost=8.18..8.20 rows=1 width=112) (actual time=0.017..0.018 rows=1 loops=1)
2
  ->  Index Scan using projects_pkey on projects  (cost=0.15..8.17 rows=1 width=40) (actual time=0.012..0.012 rows=0 loops=1)
3
        Index Cond: (id = 1)
4
        Filter: false
5
        Rows Removed by Filter: 1
6
Planning Time: 0.092 ms
7
Execution Time: 0.046 ms

Filter in the client query too#

Policies are implicit where clauses, so it's common to run select statements without any filters. That's a bad pattern for performance. Instead of this:

1
const { data } = supabase
2
  .from('table')
3
  .select()

Always add a filter:

1
const { data } = supabase
2
  .from('table')
3
  .select()
4
  .eq('user_id', userId)

Even though this duplicates the contents of the policy, Postgres can use the filter to construct a better query plan.

Avoid joins in policy expressions#

You can often rewrite a policy to avoid a join between the source and the target table. Fetch the relevant data from the target table into an array or set instead, then use an in or any operation in your filter.

This policy joins the source test_table to the target team_user:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  (select auth.uid()) in (
5
    select user_id
6
    from team_user
7
    where team_user.team_id = team_id -- joins to the source "test_table.team_id"
8
  )
9
);

Rewriting it selects the filter criteria into a set instead:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  team_id in (
5
    select team_id
6
    from team_user
7
    where user_id = (select auth.uid()) -- no join
8
  )
9
);

You can also use a security definer function to bypass RLS on the join table.

More resources#