Using PSQL as job queue

Using PSQL as job queue

Use-case: Fault Tolerant event queue in Postgres.

Consider the following table:

DROP TABLE IF EXISTS "queue";
CREATE TABLE "queue" (
  "id" SERIAL,
  "dequeued_at" TIMESTAMP NULL,

  -- remaining fields relevant for the use-case. For the outbox pattern, one would be storing the event in JSON format, the event id and the event's timestamp.
);

This table represent a queue in the database. In order to fetch size elements from the queue:

UPDATE event_queue SET dequeued_at = current_timestamp
WHERE id IN (
      SELECT id FROM queue
      WHERE dequeued_at IS NULL OR current_timestamp >= (dequeued_at + interval '1 second' second * :seconds)
      ORDER BY event_timestamp ASC
      LIMIT :size
      FOR UPDATE SKIP LOCKED
)
RETURNING *

Breaking down:

  • UPDATE event_queue SET dequeued_at = current_timestamp + ~FOR UPDATE SKIP LOCKED - Guarantees that multiple nodes reading N elements from the queue will have disjoint sets.
  • WHERE dequeued_at IS NULL - Check if the item was already or not dequeued.
  • current_timestamp >= (dequeued_at + interval '1 second' second * :seconds) - Guarantees that multiple nodes will not process the same elements. Does SKIP LOCKED already safe-guards this?