A durable job queue in your database

The first version of background work in most codebases is a fire-and-forget call that starts a task and returns. It works in development, where the process never restarts mid-request.

In production it loses work. The process is recycled, the container is rescheduled, the deploy rolls, and whatever was in flight is gone, with no record that it ever existed.

Why not a broker

A dedicated broker is the reflexive answer, and sometimes it is the right one. But it adds a second system to run, monitor, back up and reason about, and it introduces a consistency problem you did not have before: the job and the data it refers to are now in two places that can disagree.

If you already run PostgreSQL, a job table gets you durability, atomicity with your domain writes, and visibility through plain SQL, which matters more than it sounds at 2 a.m. when you want to know what is stuck.

The outbox: enqueue in the same transaction

This is the part that makes the whole thing correct.

BEGIN;
  INSERT INTO invoice (...) VALUES (...);
  INSERT INTO background_job (type, payload, run_after)
    VALUES ('send-invoice-email', $1, now());
COMMIT;

The job and the data are one atomic fact. There is no window where the invoice exists and the email job does not, and none where the job exists for an invoice that rolled back.

Enqueue to an external broker from inside a transaction and you have exactly those two windows, and both of them produce support tickets that are very hard to reproduce.

Claiming work without two workers doing it twice

UPDATE background_job SET
  status      = 'running',
  leased_until = now() + interval '5 minutes',
  attempts    = attempts + 1
WHERE id IN (
  SELECT id FROM background_job
  WHERE status = 'pending' AND run_after <= now()
  ORDER BY run_after
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
RETURNING *;

FOR UPDATE SKIP LOCKED is what makes this work with more than one worker. Rows locked by another worker’s transaction are skipped rather than waited on, so workers never queue up behind each other and never claim the same row.

Without SKIP LOCKED you get a queue of workers all blocked on the same row, which performs worse than a single worker and is a genuinely confusing thing to diagnose.

Leases, not locks

A worker that claims a job and then dies must not hold it forever. So a claim sets leased_until, and a sweeper returns anything past its lease to pending.

The lease has to be longer than the slowest legitimate run of that job, or you will reclaim work that is still in progress and run it twice. Which brings us to the thing that governs every handler you will ever write.

At-least-once is a constraint, not a feature

A lease that expires while work is still running, a worker that completes the job and dies before marking it done, a retry after a network blip, all of these deliver the same job twice. That is not a bug to be fixed; it is the guarantee. Exactly-once delivery does not exist in a distributed system, only exactly-once effects.

So every handler must be idempotent, and that is a design requirement rather than a coding style. The practical version:

• Give the job a natural idempotency key and record it. Before doing the work, check whether that key has already been processed.
• Prefer operations that are naturally idempotent, a SET rather than an increment, an upsert rather than an insert.
• For anything with an external side effect that costs money, a payment, an SMS, a WhatsApp message, write the delivery record first, under a unique constraint, and let the database refuse the duplicate.

The last one matters more than it looks. “At-least-once” plus “costs money per message” equals a refund conversation the first time a lease expires under load.

Backoff and dead-lettering

Retry with exponential backoff and jitter. Without jitter, a transient outage produces a thundering herd of retries all timed identically, which is how a brief outage becomes a long one.

After a bounded number of attempts, move the job to a dead-letter state and stop. A job that retries forever is a job that fills your logs and hides the next real failure. Dead letters should be visible, countable, and alertable, a queue with no dead-letter monitoring is a queue that silently discards work.

Waking up

Polling every second works and costs a query per second per worker. LISTEN / NOTIFY lets you sleep until there is something to do:

NOTIFY background_job_ready;

Keep the poll as a backstop on a longer interval. Notifications are not durable, a worker that is reconnecting misses them, so the poll is what guarantees the job eventually runs, and the notification is what makes it fast.

When to stop doing this

This design carries a lot of load, but it is not infinite. If you need fan-out to many consumers, cross-language consumers, ordered partitions, or millions of messages an hour, you want a real broker.

Until then, the queue that lives in the database you already back up and already monitor is usually the right amount of infrastructure.