Comment by andrewstuart
2 days ago
There’s lots of ways to invoke NOTIFY without doing it from with the transaction doing the work.
The post author is too focused on using NOTIFY in only one way.
This post fails to explain WHY they are sending a NOTIFY. Not much use telling us what doesn’t work without telling us the actual business goal.
It’s crazy to send a notify for every transaction, they should be debounced/grouped.
The point of a NOTIFY is to let some other system know something has changed. Don’t do it every transaction.
Agreed, I am struggling to understand why "it does not scale" is not "we used it wrong and hit the point where it's a problem" here.
Like if it needs to be very consistent I would use an unlogged table (since we're worried about "scale" here) and then `FOR UPDATE SKIP LOCKED` like others have mentioned. Otherwise what exactly is notify doing that can't be done after the first transaction?
Edit: in-fact, how can they send an HTTP call for something and not be able to do a `NOTIFY` after as well?
One possible way I could understand what they wrote is that somewhere in their code, within the same transaction, there are notifies which conditionally trigger and it would be difficult to know which ones to notify again in another transaction after the fact. But they must know enough to make the HTTP call, so why not NOTIFY?
Agreed.
They’re using it wrong and blaming Postgres.
Instead they should use Postgres properly and architect their system to match how Postgres works.
There’s correct ways to notify external systems of events via NOTIFY, they should use them.
Yeah, the way I've always used LISTEN/NOTIFY is just to tell some pool of workers that they should wake up and check some transactional outbox for new work. False positives are basically harmless and therefore don't need to be transactional. If you're sending sophisticated messages with NOTIFY (which is a reasonable thing to think you can do) you're probably headed for pain at some point.
Assuming you skip select transaction, or require logging on it because your regulated industry had bad auditors, then every transaction changes something.