17

Is there any way to use Postgres Listen/Notify feature to deliver a message to a channel and have only one listener consume this message?

The purpose for this is that I have multiple 'worker' apps all listening to the same Postgres channel. But I only want the work done once per message received through the notification channel.

If Listen/Notify is not the correct feature in Postgres, is there a seperate feature I should be using?

Ideally I would like to do this without using any additional extensions.

23

According to PostgreSQL documentation about NOTIFY:

The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.

(emphasis mine)

This means you cannot do what you want just with LISTEN/NOTIFY. However, you can have both a table to store queued messages, LISTEN/NOTIFY to notify external applications that "there are new things in the message queue", and use some extra logic from these external applications so that only one consumes the message.

The strategy depicted in the article What is SKIP LOCKED for in PostgreSQL 9.5? is probably the safest/easiest way to implement a message queue within PostgreSQL. Pay special attention to the "How SKIP LOCKED helps" part. Read also carefully one of their caveats:

A queue implemented in the RDBMS will never match the performance of a fast dedicated queueing system, even one that makes the same atomicity and durability guarantees as PostgreSQL. Using SKIP LOCKED is better than existing in-database approaches, but you’ll still go faster using a dedicated and highly optimised external queueing engine.

This is specially important if the queue volume is high.

  • 1
    I wish I could award a bounty to the author of that article. So extremely helpful. – Wildcard Dec 20 '17 at 1:03
3

I did something like this a while back with good success, I used RabbitMQ and this plugin https://github.com/gmr/pgsql-listen-exchange

Basically RabbitMQ Connects to PostgreSQL and listens for the notify event, then you can use RabbitMQ to aggregate that message to as many queues as needed and have an app consuming each queue

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.