Comment by CowOfKrakatoa

LISTEN/NOTIFY and SELECT FOR UPDATE SKIP LOCKED serve different purposes in PgQueuer. LISTEN/NOTIFY notifies consumers about changes in the queue table, prompting them to check for new jobs. This method doesn’t inherently lose messages if a process crashes, because it simply triggers a check rather than transmitting data. The actual job handling and locking are managed by SELECT FOR UPDATE SKIP LOCKED, which safely processes each job even when multiple workers are involved.

There are two things:

1. Signaling

2. Messaging

In some systems, those are, effectively, the same. A consumer listens, and the signal is the message. If the consumer process crashes, the message returns to the queue and gets processed when the consumer comes back online.

If the signal and messaging are separated, as in Postgres, where LISTEN/NOTIFY is the signal, and the skip locked query is the message pull, the consumer process would need to do some combination of polling and listening.

In the consumer, that could essentially be a loop that’s just doing the skip locked query on startup, then dropping into a LISTEN query only once there are no messages present in the queue. Then the LISTEN/NOTIFY is just signaling to tell the consumer to check for new messages.

I think the usage of listen/notify is just a mechanism to save you from querying the database every X seconds looking for new tasks (polling). That has some drawbacks, because if the timeout is too small, you are making too much queries that usually may not return any new tasks, and if it's too big, then you may start processing the task long after it was submitted. This way, it just notifies you that new tasks are ready so you can query the database.

[flagged]