Stop, Collaborate, and Listen/Notify

I had no idea. Why isn't this well more well known? Is there some known quirks/limitations why this isn't more widely used for very simple needs?

Passing basic messages around like this is a perfect job for LISTEN/NOTIFY because we can solve the job of message passing without the operational overhead of setting up an additional queuing system.

I am a big fan of using as little pieces of tech as possible for a project to keep the overall complexity down and this could be very useful for simple needs.

the way in which notify/listen interacts with transactions is interesting; from the docs:

http://www.postgresql.org/docs/9.5/static/sql-notify.html

"... if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY.... Secondly, if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed (either committed or aborted). Again, the reasoning is that if a notification were delivered within a transaction that was later aborted, one would want the notification to be undone somehow — but the server cannot "take back" a notification once it has sent it to the client. So notification events are only delivered between transactions."

There is also PgQ from the https://wiki.postgresql.org/wiki/SkyTools package, which is fantastic and mature. It's like Kafka inside your PostgreSQL. When I was at Instagram, we implemented a multi-region cache invalidation system using PgQ that took advantage of atomicity between PgQ and writes to the source-of-truth data table. http://engineering.instagram.com/posts/548723638608102/

On the off chance that someone reading wasn't subjected to 1990's song "Ice Ice Baby" by Vanilla Ice, that's where the title of the article comes from :)

Redis also has these operations. See http://redis.io/topics/pubsub

Note that these simplistic operations (both in Redis and Postgres) don't handle many important parts of message passing (e.g. handling retransmission of failed messages, ensuring the application has processed the messages after receiving it, and so on).

People really need to know more about LISTEN/NOTIFY. It's the primary reason that PG has ruined me for other databases.

It's great, unless you are using JDBC.

How do you clean the queue table?

Do you use SKIP LOCKED introduced in PostgreSQL 9.5?