Is using range on RPC recommended?
Lets say I have a very large table and I am using a database function to query it like so:
Would this construct the result of the whole function first before returning the ranged part from it? Because one of my background tasks keeps failing after the range gets too high with
canceling statement due to statement timeout.
How can I improve this? This is probably less of a supabase question but more related to postgres.11 Replies
Also doing a count exact is very painful.
Look into Postgres limit/offset which is what range is doing.
https://geronimovelasco.medium.com/how-limit-and-offset-works-behind-the-scenes-in-postgresql-a09c2c063405
Make sure you follow the RLS performance tuning tips https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations as you will be running RLS on each row and you want to minimize how much is going on in the policy.
Policy looks simple enough but it is referencing the users table and checking the role:
((( SELECT auth.uid() AS uid) = id_client) OR (( SELECT get_role() AS get_role) = 'admin'::text)).
Do you mean limit offset inside the postgres function or modifiers in the js call like .limit() and .offset()?
Count is painful but necessary as I need to know how much data to loop through.RLS looks optimized.
Range is using limit offset in postgres so it will go thru all rows.
You also need an order but maybe that is in your function.
Is there a .offset() in JS? I'm pretty sure .range() is the way to do that.
There isn't one at least on
rpc()
Order is in my function.So range will be Postgres limit offset.
My question, I think, essentially boils down to this:
Are the following two the same?
SELECT * FROM get_tickets() LIMIT 500 OFFSET 100000; -> This is what I think Supabase does internally.
SELECT * FROM get_tickets(arg_limit:=500, arg_offset:=100000);I did something in my approach to avoid count exact. By reading in groups of say 100. I would get 101. If I get 101 then I know I have at least 1 more time to read (dropping the 101 value).
I will try that too. I am also exploring doing one query with count and the rest without it to save on this.
I think in the end you will do a limit offset in SQL no matter what. But you might try it. I'm not going to try and guess PostgREST's magic in converting things. You could see if there is info at PostgREST.org (note the T).
Okay. I will check their docs too.
Out for the night.