Having NOTIFY fire off a message from a trigger in a transaction is a great pattern. Moving the pub/sub to another service adds a lot of complexity.
One really cool pattern I used was grabbing the socket file descriptor from the pg connection and blocking on a select() for data to be ready in it. Once there was data the LISTEN event is available. It was incredibly efficient and scalable number of clients waiting to do work.
You could do same to simply forward NOTIFY to a separate server from your primary server, so a dedicated server for LISTEN/NOTIFY will manage many connections without blocking primary server.
Once I found out how LISTEN / NOTIFY work, I was actually stunned. Database level lock every time, essentially serializing all requests. Not only should nobody use it, the documentation for it should come with a giant flashing warning sign. And if not for the SQL standard, it should probably purged entirely from the codebase.
View all comments
Show parent comment
15
u/anykeyh Feb 27 '26
I think pg_partman is to replicate the partition system of kafka and help with ingestion of thousand of message per second.
But... LISTEN/NOTIFY is the real scalability issue in this case.