Postgres LISTEN/NOTIFY does not scale
28 points by fanf
28 points by fanf
While I think this advice was well intentioned, please do not simply accept it as fact. The blanket statement made by the post title is the sort of thing that forms cultish followings. The reality is more nuanced. NOTIFY can “scale” well, but you don’t want to NOTIFY in large transaction blocks. NOTIFY independently from complex application logic, and you’re unlikely to experience scaling to many concurrent writers.
you’re unlikely to experience scaling to many concurrent writers.
Typo: You’re unlikely to experience problems scaling to many concurrent writers :)
This is good to know! I’ve used LISTEN/NOTIFY to good effect many times in the past, but this will make me more wary of using it in high concurrency situations.
The main lesson seems to make sure you don’t use it in transactions. Outside of transactions, I don’t see why using LISTEN/NOTIFY would cause much trouble, even with scale.
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.
https://www.postgresql.org/docs/current/tutorial-transactions.html
Right, “outside of transactions” is poor terminology. But the issue here is that the transactions are doing both NOTIFY and other work. There’s a global lock that serializes all transactions that use NOTIFY, so the other work also gets serialized. If you can move NOTIFY into its own transaction then the other work can run as concurrently as possible, without interference from NOTIFY. There’s still the issue that your NOTIFY throughput is limited by your serial commit speed, but that might be higher with simpler transactions. (And if you have given up on transactional integrity it’s easier to replace isolated NOTIFY with some other pubsub implementation.)
Yea I imagine that moving the NOTIFY outside of a transaction removes a lot of the benefits (from my perspective anyway) of using a pub/sub system in PG.
also keep in mind that connections that issued LISTEN will only get events delivered outside transactions; at least that was my experience a few years back with postgresql + python + sqlalchemy. in order to immediately wake up sleeping ‘workers’ (with a fallback to polling), i used select.select() on the raw socket, but that had to be done outside a transaction.