3 ms·

I can't speak for the author, but having used RLS for authentication I would not recommend it. It complicates nearly every EXPLAIN plain, and it can cause perfo

I can't speak for the author, but having used RLS for authentication I would not recommend it. It complicates nearly every EXPLAIN plain, and it can cause performance headaches in even the simplest of queries. We had to mark many internal functions as LEAKPROOF in order to get them to perform. It was not possible to move our database into RDS, so in the end we had to abandon the RLS authentication layer.

ndarilek 7y ago
Thanks for this. Lots of developers fall into the trap of discarding concepts because they aren't Facebook/Google scale solutions. The app on which I used Postgraphile/RLS was likely to have only a handful of users, so I thought more about solving the problem effectively than about whether the solution would scale, so RLS seemed like an airtight system. Knowing that it kills performance puts things into perspective. It at least suggests which tool might be best for which job. :)
- BenjieGillam 7y ago
  I suspect many of these concerns were with PostgreSQL 9.5 and 9.6 where RLS did indeed have many performance issues. PG 10 fixed a large number of these issues, and PG11 even more. I would recommend re-evaluating RLS performance on these latest PostgreSQL versions.
  - ndarilek 7y ago
    Oh good, glad RLS performance got a boost. Unfortunately my Postgraphile implementation for that project was replaced with Drupal, but I'm glad RLS performance is less of a concern under version 10 and up.
  - aidos 7y ago
    In Hasura’s use case it’s a little different. They’re working on the assumption that you have loads of identical queries that you want to run that differ only in the session variables (and hence the rows they see). Because they’re outside Postgres, they can collate it all and execute a single query for multiple users. Within Postgres you don’t have that luxury. It’s a trade off, but not using RLS suits some use cases better.
cdaringe 7y ago
I'm yet to observe high perf hits on account my RLS impl. I captured data at some point for a 1M record domain with a couple thru tables, and query perf with and without RLS was indiscernible. Perhaps your check clauses we're expensive? I made sure to keep them as light as possible.

ndarilek 7y ago

Thanks for this. Lots of developers fall into the trap of discarding concepts because they aren't Facebook/Google scale solutions. The app on which I used Postgraphile/RLS was likely to have only a handful of users, so I thought more about solving the problem effectively than about whether the solution would scale, so RLS seemed like an airtight system. Knowing that it kills performance puts things into perspective. It at least suggests which tool might be best for which job. :)

BenjieGillam 7y ago
I suspect many of these concerns were with PostgreSQL 9.5 and 9.6 where RLS did indeed have many performance issues. PG 10 fixed a large number of these issues, and PG11 even more. I would recommend re-evaluating RLS performance on these latest PostgreSQL versions.
- ndarilek 7y ago
  Oh good, glad RLS performance got a boost. Unfortunately my Postgraphile implementation for that project was replaced with Drupal, but I'm glad RLS performance is less of a concern under version 10 and up.
- aidos 7y ago
  In Hasura’s use case it’s a little different. They’re working on the assumption that you have loads of identical queries that you want to run that differ only in the session variables (and hence the rows they see). Because they’re outside Postgres, they can collate it all and execute a single query for multiple users. Within Postgres you don’t have that luxury. It’s a trade off, but not using RLS suits some use cases better.

cdaringe 7y ago

I'm yet to observe high perf hits on account my RLS impl. I captured data at some point for a 1M record domain with a couple thru tables, and query perf with and without RLS was indiscernible. Perhaps your check clauses we're expensive? I made sure to keep them as light as possible.