3 ms·
Few things. 1. The main downside to using PostgreSQL as a pub/sub bus with LISTEN/NOTIFY is that LISTEN is a session feature, making it incompatible with state
by jpgvm 3y ago
Few things.
1. The main downside to using PostgreSQL as a pub/sub bus with LISTEN/NOTIFY is that LISTEN is a session feature, making it incompatible with statement level connection pooling.
2. If you are going to do this use advisory locks [0].
Other forms of explicit locking put more pressure on the database while advisory locks are deliberately very lightweight.
My favorite example implementation is que [1] which is ported to several languages.
[0] https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS https://www.postgresql.org/docs/current/explicit-locking.htm...
[1] https://github.com/que-rb/que https://github.com/que-rb/que
- MrPowerGamerBR 3y agoOne reason that makes me dislike NOTIFY/LISTEN is that issues with it are hard to diagnose. Recently I had to stop using it because after a while all NOTIFY/LISTENS would stop working, and only a database restart would fix the issue https://dba.stackexchange.com/questions/325104/error-could-not-access-status-of-transaction-could-not-open-file-pg-xact-0029 https://dba.stackexchange.com/questions/325104/error-could-n...
- runeks 3y agoAt my previous company, they switched from using NOTIFY/LISTEN for Postgres notifications to a custom solution built on top of logical replication. As I understand it, part of the reason was reliability. I never touched that part of the code, but I believe the idea was to subscribe to logical replication updates and send out notifications based on these.
- cpursley 3y agoThis is what we do. Even put together a library for it: https://github.com/cpursley/walex https://github.com/cpursley/walex
- teaearlgraycold 3y agoIs there a Node version of Que?