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?
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?
