Making Fóir fast
Every read in Fóir runs under Postgres row-level security. You declare who can see what, and Demesne, our open-source authorisation engine, writes the policies and the functions they call. Making those reads fast turned up a few things about RLS I hadn't seen written down. If your policies call functions, they probably apply to you too.
JIT compiles five-row reads
Postgres has a JIT compiler, and it's on by default. It's meant for expensive queries, so it only compiles once a query's estimated cost passes jit_above_cost. A set-returning function with no ROWS clause is costed at 1,000 rows, though, and some of our policy functions return sets from inside subqueries. Multiplied out, a read of five rows was estimated at millions, and Postgres compiled about 70 functions for it every time it ran. That read took anywhere from 55 ms to just over a second, depending on the plan. With JIT off it took 2.4 ms.
In production, JIT was 18.7% of all the time our database spent, in six statements, and none of them ran faster for it. We switched it off for the whole cluster, which took the database time behind each foir.io page view from 211 ms to 75 ms. A job that wants JIT can SET LOCAL jit = on for its own transaction. We also gave ROWS 1 to the functions that return exactly one row by construction, and left the rest alone, because a guessed estimate misleads the planner too.
EXPLAIN ANALYZE puts JIT in its own section at the very end, so it's easy to miss: the node timings just won't add up to the execution time. pg_stat_statements has jit_ columns for it.
Policy functions are invisible by default
pg_stat_statements only records the statements your application sends. The statements inside a policy's functions run as part of the query that triggered them, so their cost is folded into that query's execution time, planning included. Set pg_stat_statements.track = all to see them as statements of their own, and pg_stat_statements.track_planning = on to see how long each spends being planned. Neither is the default.
The first time we turned both on, on a product page in our development environment, the policy functions spent 80 ms a page view being planned and 6 ms running.
A CASE on an argument is planned on every call
Our policy functions took an argument saying which access to check, read, write or delete, and picked the check with a CASE. It's a tidy way to share one function between a table's policies, and an expensive one. On Postgres 17 the busiest of those functions was planned about 24 times a page view, at nearly 3 ms a plan, and every plan covered all three checks.
You'd expect Postgres 18 to help here, since it gives SQL functions the plan cache PL/pgSQL has always had. It made things worse. The plan cache compares a generic plan with plans made for the actual arguments, and a plan that knows which access you asked for can drop the other two branches, so it always looks cheaper. Postgres makes a fresh plan on every call.
We timed the same read, the latest versions of five products for a visitor who isn't signed in. The original functions took 13.4 ms on Postgres 17 and 77.5 ms on 18. Moved into PL/pgSQL with the CASE kept, they took 68.6 ms on 17. Rewritten in PL/pgSQL with an IF for each access, each returning only its own check, and marked STRICT (next section), they took 6.4 ms on 17 and 6.3 ms on 18. Each arm is planned once per connection and stays cached. On the product page, re-planning the policy functions went from 80 ms a view to 3.5 ms.
If you're moving to Postgres 18 and your policies call a function that branches on an argument, time it first.
STRICT skips the call
A function marked STRICT returns null without running when any argument is null. Under AND and OR, a policy treats null the same as false and admits nothing. So a check that needs a customer, an admin or an organisation never runs for a visitor who has none of those. On the same product page, one organisation check went from 61 calls a view to none.
It's only safe when a null argument really does mean no. Demesne marks a function strict only when every argument is compared with = and nothing else, so a null could never have matched.
The plan decides how often your policy runs
Under RLS, every row a plan reads goes through the policy, including rows that never reach the answer. One of our reads finds a record's latest version. At Postgres's default random_page_cost of 4, the planner sorted every version under the policy and took the first. At 1.1 it walked the index backwards and checked one. With 300 versions that was 13 ms against 1.5 ms, and with 1,000 it was 122 ms against 1.5.
1.1 is the usual setting for SSDs. If yours is still at 4 and your policies call functions, it's worth checking which of your plans change.
Fóir's current numbers, and how we measured them, are on foir.io/performance.