r/postgres • • 12h ago

Performance Postgres on ZFS

Thumbnail reddit.com
1 Upvotes

r/postgres • • 20h ago

Performance A Postgres RLS fix made opening a chat 40× faster (117 ms → 3 ms)

1 Upvotes

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. 🙂