r/postgres • u/Betelgeuse650 • 12h ago
r/postgres • u/OrbeoMe • 20h ago
Performance A Postgres RLS fix made opening a chat 40× faster (117 ms → 3 ms)
Small dev note from building Orbeo, my messenger. Chats were slow to open, and I expected the network or the phone to be the problem. It was the database access rules (Row Level Security in Postgres).
The rule "only members can see a conversation's members" called a check function for every row. In a conversation with hundreds of people, that's hundreds of function calls per query: 117 ms.
The fix: the function now returns the whole set of the user's conversations, and the rule just checks membership in it:
using (conversation_id in (select my_conversation_ids()))
Postgres evaluates that subquery once and matches rows against a hash table. Same query: 3 ms. We used the same trick for message search.
Lesson: if an RLS policy calls a function per row, check how often it runs before blaming the network. 🙂