The Queue You Already Have: PostgreSQL, Retries, and Failure Recovery

A background job creates a second timeline inside an application. The request can finish before the work does. The system must preserve that work, distribute it among workers, and recover when a worker disappears.

In paystable, my payment-state stabilization project, gateway verification needs that separation. A webhook triggers work that can require repeated status checks before the application has enough evidence to act.

PostgreSQL is already responsible for durable state. Using it for scheduled work keeps job creation close to the data that requires the job. The important design question is what happens at each failure boundary.

FOR UPDATE SKIP LOCKED solves one part: concurrent workers can claim different available rows. Recovery, retries, and external effects still need explicit rules.

Claiming work is a state transition

A plain SELECT does not claim a job. Two workers can both read a pending row before either changes it.

FOR UPDATE locks selected rows until the transaction ends. Adding SKIP LOCKED lets a worker pass over rows another transaction has locked.

The claim must also change the row's state before commit. Otherwise, releasing the lock makes the same pending job available again.

The following schema is a teaching example, separate from paystable's schema. It uses five total attempts and a five-minute lease to make the recovery policy concrete.

CREATE TABLE jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'processing', 'done', 'dead')),
    attempts integer NOT NULL DEFAULT 0 CHECK (attempts BETWEEN 0 AND 5),
    run_after timestamptz NOT NULL DEFAULT now(),
    lease_until timestamptz,
    CHECK ((status = 'processing') = (lease_until IS NOT NULL))
);
 
CREATE INDEX jobs_ready_idx ON jobs (run_after, id)
WHERE status = 'pending';

The partial index covers pending work in scheduling order. Completed jobs stay outside that index, although their rows still need a retention policy.

This statement claims one due job and returns the attempt number as an ownership token:

WITH candidate AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
      AND run_after <= now()
      AND attempts < 5
    ORDER BY run_after, id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs AS j
SET status = 'processing',
    attempts = j.attempts + 1,
    lease_until = now() + interval '5 minutes'
FROM candidate
WHERE j.id = candidate.id
RETURNING j.id, j.payload, j.attempts;

Commit the claim before starting slow work. In autocommit mode, this single statement forms its own transaction. If it returns no row, wait before polling again.

The ordering selects the earliest due unlocked job, with id as a tie-breaker. It does not promise strict FIFO execution or completion. SKIP LOCKED also avoids row-lock waits only, not every possible database wait.

A lease needs an ownership check

After the claim commits, a row lock no longer protects the worker. The processing state prevents another normal claim, while the lease defines when recovery may begin.

Suppose worker A stalls. Recovery makes the job available, and worker B claims it. If A resumes, it must not mark B's attempt complete.

Require the attempt number returned by the claim on every worker-driven state change. In the parameterized statement below, $1 is the job ID and $2 is that attempt number:

UPDATE jobs
SET status = 'done', lease_until = NULL
WHERE id = $1
  AND status = 'processing'
  AND attempts = $2
  AND lease_until > now()
RETURNING id;

No returned row means the worker no longer holds a valid claim. It must treat completion as unacknowledged.

The attempt token protects this database transition. It cannot cancel an HTTP request that the old worker has already sent. That limitation matters whenever the job affects another system.

Recovery cannot distinguish a dead worker from a slow one

A periodic recovery task can reschedule expired claims:

UPDATE jobs
SET status = CASE WHEN attempts >= 5 THEN 'dead' ELSE 'pending' END,
    lease_until = NULL,
    run_after = now() + interval '30 seconds'
WHERE status = 'processing'
  AND lease_until <= now();

The delay prevents immediate retries after recovery. The attempt limit stops a failing job from cycling forever. Alert on dead jobs and keep enough failure context to investigate them.

For a failure reported by a live worker, use the same ID, attempt, and lease guards as completion. Apply bounded backoff with jitter and stop retrying permanent failures.

Five minutes is an example lease, not a safe default for every workload. Set task timeouts with margin inside the lease. Longer tasks need guarded lease renewal or a different execution model.

A timeout expresses suspicion, not proof of death. Recovery can overlap with work from an old attempt. Any design that allows retries must account for that possibility.

The external-effect boundary determines correctness

Consider a job that sends a callback and then records completion:

The third case is the critical gap. PostgreSQL cannot atomically commit a row update and an arbitrary HTTP effect at another service.

Use a stable idempotency key for the logical operation across retries. The receiving system must atomically record that key with its business effect. A check followed by an unrelated write can race too.

The ownership token and idempotency key have different jobs. The attempt token changes on each claim. The operation's idempotency key remains stable across those attempts.

This is an at-least-once processing design with a finite retry budget. It permits duplicates and can end in a dead state. It does not guarantee eventual successful delivery.

Keep database effects within one transaction

When both the business change and the job use the same database, insert them in one transaction. A rollback then removes both, and a commit preserves both.

For a job whose entire effect is local, a short transaction can validate ownership, apply the effect, and mark completion together. Use a unique operation key where logical duplicates can enter the system.

For external notifications, a transactional outbox preserves the intent to send alongside the business change. A separate worker delivers the outbox row. Delivery still needs retries and receiver-side deduplication.

Paystable's documented design uses PostgreSQL for verification work and callback delivery. Its callback contract requires merchant deduplication. That is the relevant boundary: durable intent in the database, retry-safe effects at the receiver.

Database durability is also a configuration property. PostgreSQL's reliability documentation explains the storage assumptions behind WAL. Queue code cannot compensate for a storage stack that does not honor durable writes.

Verify the claim protocol under contention

Use a disposable database for this two-session check. After creating the schema, insert two jobs:

INSERT INTO jobs (payload) VALUES ('{"task":"a"}'), ('{"task":"b"}');

1. In session A, run BEGIN, then the claim statement. Leave the transaction open.
2. In session B, run the claim statement. It should return the other job without waiting for A's row lock.
3. In session A, run ROLLBACK. Its claim should disappear, including the attempt increment.
4. In session B, run the claim statement again. The rolled-back job should be available.

Also exercise stale completion. Expire a lease in the disposable database, run recovery, and claim the job again after its retry delay. Completion with the old attempt number must return zero rows. Completion with the new attempt number and a valid lease must return one.

Those checks test the protocol's boundaries. A successful single-worker run does not exercise them.

Measure the cost to the primary database

Queue traffic shares storage, connections, and CPU with application queries. Polling frequency and job duration can matter more than a headline jobs-per-second number.

Monitor the age of the oldest due job, retry and dead-job counts, claim latency, database load, and expired leases. Queue depth alone cannot show whether one old job has stopped progressing.

Frequent updates create obsolete row versions. PostgreSQL's vacuum documentation describes how those versions are reclaimed and why long transactions can delay cleanup. Retain completed jobs deliberately and tune autovacuum from observed behavior.

A separate queue service becomes useful when measured contention, delivery features, or isolation requirements justify it. There is no universal job-volume threshold that makes that decision for every application.

PostgreSQL is a good starting point when jobs belong to existing database transactions and the workload fits the database's capacity. The design becomes dependable when claims, retries, ownership, and external effects each have a clear contract.