Postgres as a Queue

PostgreSQL can be an excellent job queue when the work belongs close to transactional data and the scale is understood. It can also become an expensive imitation of a streaming platform when its limits are ignored. The useful question is not whether Postgres can queue work. It is whether its trade-offs match the job.

Why keep work in the database

The strongest reason is transactional consistency. An application can update business state and insert a job in the same transaction. Either both commit or neither does. There is no gap where an order exists but the event intended to process it was never published.

Operational simplicity matters too. A team already running PostgreSQL gets backups, access control, observability, and familiar query tools without introducing another distributed system.

Claim jobs without collisions

Multiple workers need to claim different rows safely. A common pattern selects available jobs inside a transaction using FOR UPDATE SKIP LOCKED, updates the selected rows to a running state, records a lease expiry, and commits. Locked rows are skipped, so workers do not wait behind each other for the same job.

Keep the claim transaction short. The actual job should run after the transaction commits; otherwise a slow external call holds locks and consumes a database connection.

Use leases, not permanent ownership

Workers crash. Containers disappear. A job marked “running” forever is lost work. Record a lease expiration and allow another worker to reclaim the job after that time. If jobs may legitimately run longer than the lease, the worker can heartbeat to extend it.

The lease only provides delivery mechanics. The handler still needs idempotency because a job may complete externally and fail before recording success locally.

Make retries intentional

Store the attempt count, the next eligible time, and a compact error reason. Exponential backoff with jitter prevents a failed dependency from receiving a synchronized retry storm. Classify permanent errors separately from transient ones; invalid input will not become valid after the seventh attempt.

After the retry limit, move the job to a failed state that operators can inspect and replay. A dead-letter view without context is just a slower way to lose work, so preserve the job type, relevant identifiers, and final error.

Keep the table healthy

A queue table experiences frequent inserts and updates. Index the columns used to find eligible work, usually status and scheduled time, but avoid collecting indexes that every state change must maintain. Archive or delete completed jobs in bounded batches. Watch table bloat, autovacuum behavior, lock waits, and connection usage.

Polling should be adaptive. Empty workers can back off; busy workers can claim small batches. Notifications may reduce idle polling, but the durable query should remain the source of truth.

Know when to graduate

Postgres is a poor fit when you need long retention with many independent consumers, replay across large histories, very high write throughput, or partitioned ordering at scale. It is also risky when queue traffic competes with the primary database workload that serves customers.

Start with the simplest system that meets the reliability contract. Measure queue depth, oldest-job age, claim latency, retry rate, and database cost. Move to dedicated infrastructure when those measurements—not fashion—show that the queue has become a different kind of system.

A complete claim-and-lease design

CREATE TABLE jobs (
  id uuid PRIMARY KEY,
  queue text NOT NULL, payload jsonb NOT NULL,
  status text NOT NULL DEFAULT 'ready',
  run_at timestamptz NOT NULL DEFAULT now(),
  attempts integer NOT NULL DEFAULT 0,
  leased_until timestamptz, last_error text
);
CREATE INDEX jobs_claim ON jobs (queue, run_at, id) WHERE status = 'ready';

WITH candidate AS (
  SELECT id FROM jobs
  WHERE queue = $1 AND status = 'ready' AND run_at <= now()
  ORDER BY run_at, id
  FOR UPDATE SKIP LOCKED
  LIMIT $2
)
UPDATE jobs j
SET status = 'running', leased_until = now() + interval '60 seconds', attempts = attempts + 1
FROM candidate c WHERE j.id = c.id
RETURNING j.*;

PostgreSQL explicitly notes that SKIP LOCKED is unsuitable for a general consistent view but useful for multiple consumers of a queue-like table. See the official SELECT documentation.

Recovery, retries and dead letters

A reaper returns expired leases to ready. Retryable failures set run_at using exponential backoff with jitter; validation and policy failures go directly to dead-letter review. Handlers remain idempotent because a worker can complete an external side effect and crash before marking the job successful.

Benchmark and migration thresholds

Measure completed jobs per second, claim latency, lock wait, dead tuples, autovacuum, connection usage, retry storms, and impact on OLTP queries. Move to SQS, NATS, Kafka, or Redis Streams when queue load competes with primary transactions, independent scaling is required, or durable replay and fan-out become central product requirements.

See the broader real-time systems architecture guide.