We tried to use LISTEN/NOTIFY for notification purposes, or rather, a queue in which the order mattered (well, kind of, we were aiming for the best scenario where a customer would not perform a specific task in a matter of milliseconds, because we could afford this expectation), in a .NET application using Npgsql.
The listeners were scattered between replicas, so we took advantage of Advisory Locks (https://www.postgresql.org/docs/current/explicit-locking.htm...) by choosing a unique key (unique to the data being sent) and before performing any task on the JSON payload, that the notification would send, we would first check the lock and continue the routine.
The NOTIFY routine was triggered after an insert in an Outbox table, so we could replay it if it failed for some reason.
Unfortunately I don't remember the exact reason we didn't use it, but I was bit sceptical for 2 reasons:
- I had a feeling that I was use this feature in a wrong way; and
but to this day I'm still unsure. Anyway, since it was a complementary system, it didn't hurt to leave it out, we had another background job that would process the outbox table regardless, but I felt it could/would give something closer to "real time" in our system.
The listeners were scattered between replicas, so we took advantage of Advisory Locks (https://www.postgresql.org/docs/current/explicit-locking.htm...) by choosing a unique key (unique to the data being sent) and before performing any task on the JSON payload, that the notification would send, we would first check the lock and continue the routine.
The NOTIFY routine was triggered after an insert in an Outbox table, so we could replay it if it failed for some reason.
Unfortunately I don't remember the exact reason we didn't use it, but I was bit sceptical for 2 reasons:
- I had a feeling that I was use this feature in a wrong way; and
- I read both the article and comments from this HN entry: https://news.ycombinator.com/item?id=44490510, and my first point felt validated;
but to this day I'm still unsure. Anyway, since it was a complementary system, it didn't hurt to leave it out, we had another background job that would process the outbox table regardless, but I felt it could/would give something closer to "real time" in our system.