Postgresql – Postgres Listen/Notify As Message Queue

event-notificationpostgresqlqueue

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.

Best Answer

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.

Related Question