Performance issues with RLS
Hi,
I am quite new with working with RLS, while I do have a background with Postgres I am having a hard time making queries run fast with RLS. I have to admit I have quite a complex app, but perhaps RLS is not meant to be as fast as I expected it to be.
I for example have a function get_users_paginated, it gets 20 users at the same time, when having the RPC as invoker it took around 200ms on a loaded table 100k records. Doing some explain analyze showed me that each row was checking the invoking user context (cant do JWT for roles and stuff). When making a very fast function that does caching within the transaction I got it down to 60ms. However when making the RPC DEFINER and checking upfront I get like 11ms. Is this 49ms slower just a accepted cost of RLS. Or am I doing something wrong?
17 Replies
Please check the performance guide here: https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations
In particular you don't seem to be wrapping your functions in selects.
Also if you can use security definer functions with filters to avoid RLS on sub tables if that can be done helps.
If you skim thru that doc you can see RLS can make huge performance impacts if not done correctly (or in some cases may have to be reworked).
Hmm I did forget some select in this function, will messure if that makes a difference, since the function it calls is already heavily cached.
It will end up running the function on each row versus caching the result to compare.
Ah so security definer RPCs with filters is accepted, and expected to have better performance. Would you say 49ms is a expected cost of RLS, ofcourse accepted depends on the app.
It makes a difference even on auth.uid() which is just looking at a JWT.
Yeah true, will change this and messure again
Look at some of the times in the doc. RLS can be seconds of added time or hardly any at all.
No easy way to just say what it will be.
Some things may not help also depending on indexing, but rarely does anything in the guide hurt times.
Yes true, a simple policy that just checks against a claim was instant for me. Thats why I put that one on top since its 95% of the case and postgres circuit breaks
or statementsYou are not guaranteed they will run in "order"
Its quite well indexed, I notice that planning time is sometimes 2x the execution time. Since some policies get really complex.
Ah forgot about that. In my testing cases it happened to be in order according to the path from analyze.
It is all up to what the analyzer decides.
I also inlined most of the policies, just in case the functions are black boxes. So in most cases the missing select is not a issue since I dont call functions (only call functions when I get a infinte recursion). And with the inlined policies I also have like 3x slower queries.
If you can write your functions you use in RLS so that they are secure as a security definer (or put them in a schema that is safe) then that can be a big win. But you have to make sure you don' t depend on the table they are accessing RLS.
And everything is really well indexes, no seq scans
Inlining can be much slower than a well written function as shown in the minimize join section. Surrounded by a select.
Ahh okay thanks
Well I am going to play around with this some more. Thanks!!
AI's are not much help with this. RLS in postgres is not used in the past like it gets used in Supabase (PostgREST). So not much history.