Topic: PostgreSQL

PostgreSQL SKIP LOCKED: Job Queue, Not Magic Broker

SKIP LOCKED can claim jobs without worker contention, but reliable delivery still needs short transactions, leases, retries, idempotency, and an exit plan.

“Just use Postgres” is unusually good advice for a job queue—until it quietly stops being good advice.

The appeal is real. An application can insert a business record and its background job in one transaction. There is no second service to deploy, no dual-write gap to reconcile, and no new durability model to learn. PostgreSQL even documents SKIP LOCKED as useful for multiple consumers of a queue-like table.

The trap is treating that clause as the whole queue.

SKIP LOCKED solves one narrow concurrency problem: when one worker has locked a candidate row, another worker can move past it instead of waiting. It does not decide what happens when a worker crashes, an HTTP request succeeds twice, a job is abandoned forever, or the queue’s write churn overwhelms autovacuum.

My rule is:

Use PostgreSQL for a job queue when transactional enqueue and operational simplicity matter more than broker features. Treat delivery as at-least-once, make side effects idempotent, and decide the migration signal before traffic decides it for you.

What SKIP LOCKED actually guarantees

A normal SELECT ... FOR UPDATE waits when another transaction already owns the row lock. Add SKIP LOCKED, and PostgreSQL omits rows that cannot be locked immediately. Several workers can therefore claim different jobs without coordinating through application code.

That behavior also explains two limits:

• The result is intentionally inconsistent. PostgreSQL says it is unsuitable for general-purpose queries, but useful for queue-like tables.
• It is not strict FIFO. An older locked job may be skipped while a newer available job runs first.

Neither is a bug. A work queue usually values progress over a perfect snapshot. But if a product promises globally ordered processing, this is already the wrong primitive unless work is partitioned into independently ordered streams.

Claim atomically, then end the transaction

The useful pattern is a short claim transaction, not a transaction that stays open while the job runs:

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY priority DESC, run_at, id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs AS j
SET status = 'running',
    locked_at = clock_timestamp(),
    locked_by = $1,
    attempts = attempts + 1
FROM next_job
WHERE j.id = next_job.id
RETURNING j.*;

The CTE selects and locks one eligible row; the UPDATE ... RETURNING records ownership in the same statement. Commit immediately after receiving the row. Perform the email, upload, model call, or report generation outside that transaction, then mark the job complete in a separate short transaction.

Holding the row lock throughout external work is tempting because it looks like ownership. It actually couples database lock duration to network latency and third-party failures. PostgreSQL’s locking guidance explicitly warns against keeping transactions open for long periods.

This is the important turn: claiming exactly once is not the same as producing a side effect exactly once.

Suppose a worker charges a card, then crashes before updating status = 'completed'. A rescue process will eventually retry that job. If the payment provider did not receive a stable idempotency key, the customer may be charged twice.

The practical contract is at-least-once delivery. Build around it:

1. Give externally visible operations a stable idempotency key, usually derived from the job or business operation ID.
2. Make completion updates conditional on the current owner when stale workers must not overwrite a rescued job.
3. Store attempts, last_error, and run_at so retries have a limit and exponential backoff instead of becoming a hot loop.
4. Move exhausted jobs to a visible failed state. A dead-letter state without inspection and replay tooling is only a quieter data graveyard.
5. Run a reaper that returns running jobs with an expired locked_at lease to pending, or marks them failed.

The lease must be longer than ordinary job duration and observable when exceeded. For genuinely long jobs, add an explicit heartbeat or split the work into smaller resumable units. Do not solve uncertainty with a six-hour database transaction.

Keep the hot path small

A queue table is updated far more aggressively than an ordinary business table. Its physical design should reflect that.

Start with a partial index that matches the claim query:

CREATE INDEX jobs_claim_idx
ON jobs (priority DESC, run_at, id)
WHERE status = 'pending';

This keeps completed and failed rows out of the hot index. The query predicate must match the partial-index predicate closely enough for PostgreSQL’s planner to recognize it; a partial index is not magic pattern matching.

Then keep the table boring:

• Avoid indexes the worker does not use; every state change must maintain them.
• Archive or delete completed jobs in bounded batches instead of letting history share the hot table forever.
• Watch dead tuples, table and index size, autovacuum progress, claim latency, queue age, retry rate, and stuck leases.
• Tune autovacuum from measurements. UPDATE and DELETE leave old row versions that VACUUM must reclaim, so a healthy queue can still create substantial maintenance work.

A recent daily.dev discussion surfaced a deeper failure mode at very high concurrency: MultiXact SLRU contention, WAL volume, snapshot overhead, and bloat can make a simple query fall off a cliff. The original article is a useful warning, but its rough worker thresholds are not universal capacity limits. Row width, indexes, transaction length, connection count, storage, job duration, and cleanup policy all move the boundary. Benchmark the real workload.

LISTEN/NOTIFY is a doorbell, not the queue

Polling every few seconds is simple and often sufficient. If idle latency matters, NOTIFY can wake listeners after an enqueue transaction commits.

Keep the durable job in the table and treat the notification as “please check the table.” PostgreSQL describes NOTIFY as a simple interprocess signal, folds duplicate notifications with identical channel and payload inside one transaction, and limits payload size. A worker that reconnects must still discover pending jobs by querying the table.

That division is clean:

• Table: durable state, retries, ownership, inspection.
• NOTIFY: low-latency hint.
• Periodic poll: recovery when a hint or connection is missed.

When PostgreSQL is the right queue

It is a strong default when:

• Enqueue must commit atomically with nearby relational data.
• Jobs have one consumer path, modest fan-out, and tolerable polling latency.
• The team can operate one PostgreSQL database better than two distributed systems.
• Queue traffic is small relative to the database’s primary workload.
• At-least-once processing and application-level idempotency are acceptable.

Reach for a purpose-built broker when you need multiple independent consumer groups, replay, partitioned ordering, very high sustained dispatch throughput, long retention, or isolation that prevents queue traffic from hurting transactional queries.

The best migration trigger is not a fashionable number of jobs per second. It is evidence that queue work is consuming the database’s reliability budget: growing claim latency, autovacuum falling behind, lock or SLRU waits, replica lag, WAL pressure, or business queries competing with workers.

A small production checklist

Before calling the table a queue, answer these questions:

• Can enqueue happen in the same transaction as the business change?
• Is the claim a single atomic statement followed by an immediate commit?
• What recovers an expired lease?
• Which side effects are idempotent, and what key enforces that?
• How are retries delayed, capped, inspected, and replayed?
• Does the partial index match the exact claim predicate and ordering?
• How are completed rows removed without creating an unbounded maintenance problem?
• Which metrics tell us this workload has outgrown PostgreSQL?

If those answers fit on one page, PostgreSQL may be the least complicated reliable queue you can run. If they require rebuilding consumer groups, replay logs, partition ownership, and flow control, the database is asking to stop impersonating a broker.

Conclusion: one clause removes contention, not responsibility

FOR UPDATE SKIP LOCKED is valuable precisely because it is small. It lets PostgreSQL workers distribute available rows with very little machinery, while transactional enqueue eliminates an entire class of dual-write failures.

Keep that advantage by being honest about the rest: short claim transactions, at-least-once delivery, idempotent effects, leases, bounded retries, a focused index, vacuum-aware cleanup, and observable exit conditions.

PostgreSQL can be an excellent job queue. It becomes a bad one when “we already have a database” is allowed to substitute for a delivery design.

External references

• PostgreSQL: SELECT locking clause and SKIP LOCKED
• PostgreSQL: explicit locking
• PostgreSQL: partial indexes
• PostgreSQL: routine vacuuming
• PostgreSQL: NOTIFY
• daily.dev: Potential Consequences of Using Postgres as a Job Queue
• Richard Yen: Potential Consequences of Using Postgres as a Job Queue
• Reddit: Postgres message queue discussion