What is a Postgres SKIP LOCKED job queue and who should use one?
A Postgres SKIP LOCKED job queue is a jobs table in your application's existing database. Background workers claim rows from it with SELECT ... FOR UPDATE SKIP LOCKED. Each worker locks the rows it selects. Any row already locked by another worker is passed over instead of waited on, so many workers can pull from the same table without blocking each other or processing the same job at the same time.
Teams adopt the pattern for three reasons:
- No extra infrastructure. The queue lives in a database you already run.
- One operational story. Backups, failover, access control and monitoring for jobs are the ones you already have for application data.
- Transactional enqueue. A job inserted in the same transaction as the business write commits or rolls back with it. You never enqueue work for an order that was never saved.
The natural fit is a monolith already on Postgres, with moderate job volume and a small platform team that would rather not run a separate message broker.
It is a sound default only if you design for at-least-once delivery, short transactions and table hygiene from day one. It is not a drop-in broker. The development cost shifts rather than disappears. You skip provisioning, securing and monitoring a broker, and you skip building an outbox to keep it consistent with the database. In exchange, you write and own the queue mechanics yourself: claiming, leases, retries, backoff, dead-lettering and cleanup.
How does SELECT FOR UPDATE SKIP LOCKED let workers claim jobs safely?
Start with one table. Each row carries id, queue, a payload stored as jsonb, a status (pending, running, done, failed), run_at for scheduling and backoff, attempts and max_attempts, locked_at and locked_by to record which worker holds the job, last_error, and created_at.
Index it with a partial index on (queue, run_at) whose WHERE clause matches the runnable condition, typically status = 'pending'. Completed and failed rows are left out of the index, so a claim scans only pending work. Without this, a table holding months of finished jobs makes every claim slower, even when only a few jobs are waiting.
The claim is a single statement. A CTE selects ids from the target queue where the job is pending and run_at <= now(), ordered by run_at, with LIMIT n FOR UPDATE SKIP LOCKED. An UPDATE joined to that CTE sets status = 'running', locked_at = now(), and locked_by to the worker's identifier. RETURNING hands the claimed rows back to the worker.
FOR UPDATE takes a row-level lock on each selected row. A plain FOR UPDATE would make a second worker wait until the first transaction ends. SKIP LOCKED changes that: the second worker ignores any row that another transaction has already locked and moves on to the next candidate. Workers neither block on each other nor claim the same job twice.
Do not depend on ordering across workers. Each worker claims jobs in roughly run_at order, but skipped rows, different transaction timing, and retries mean jobs can finish in a different order than they were enqueued. If a handler needs strict sequencing, enforce it separately.
-- Minimal jobs table: one row per job, history kept in place.
CREATE TABLE jobs (
id bigserial PRIMARY KEY,
queue text NOT NULL,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'running', 'done', 'failed')),
run_at timestamptz NOT NULL DEFAULT now(),
attempts int NOT NULL DEFAULT 0,
max_attempts int NOT NULL DEFAULT 5,
locked_at timestamptz,
locked_by text,
last_error text,
created_at timestamptz NOT NULL DEFAULT now()
);
-- Partial index: only runnable rows are indexed, so claim cost
-- tracks pending work, not months of done/failed history.
CREATE INDEX jobs_runnable_idx ON jobs (queue, run_at)
WHERE status = 'pending';
-- Claim up to 10 jobs in one statement.
-- FOR UPDATE locks the selected rows; SKIP LOCKED makes concurrent
-- workers pass over rows already locked instead of waiting on them.
WITH claimable AS (
SELECT id
FROM jobs
WHERE queue = 'emails'
AND status = 'pending'
AND run_at <= now()
ORDER BY run_at
LIMIT 10
FOR UPDATE SKIP LOCKED
)
UPDATE jobs AS j
SET status = 'running', locked_at = now(), locked_by = 'worker-host1-pid4242'
FROM claimable c
WHERE j.id = c.id
RETURNING j.id, j.payload, j.attempts, j.max_attempts;
A minimal jobs table, a partial index covering only pending rows, and a single-statement claim that uses FOR UPDATE SKIP LOCKED so concurrent workers each take different jobs without blocking.
Should a worker hold the row lock or claim and commit?
Once a worker has selected a row with FOR UPDATE SKIP LOCKED, it can either keep the transaction open until the job finishes or commit the claim right away.
Hold the lock. The row lock is the claim. If the worker crashes, Postgres rolls the transaction back and the job becomes visible again with no extra code. The cost is one pinned connection per running job. Each long transaction also holds back the xmin horizon, so vacuum cannot remove dead tuples anywhere in the database until it ends. Slow jobs can also trip idle_in_transaction_session_timeout or statement_timeout.
Claim and commit. The worker sets status = 'running' and lease_until = now() + interval, commits, and runs the job with no transaction open. It then opens a short transaction to mark the job done or failed. A reaper requeues jobs whose lease has expired. This adds code, but transactions stay short, connections return to the pool, and an HTTP call that hangs for minutes holds no database resources. For slow jobs and jobs that call external services, this is usually the better model.
enqueue ──> [queued] ──claim+commit──> [running, lease_until]
^ │
│ run (no txn)
│ │
│ ┌───────────┴───────────┐
│ v v
│ [done] [failed / retry at run_at]
│
└──── reaper: running AND lease_until < now()
| Aspect | Hold the lock | Claim and commit |
|---|---|---|
| Crash recovery | Automatic rollback | Reaper after lease expiry |
| Connection use | One per running job | Brief, pooled |
| Vacuum impact | Holds back xmin horizon | Negligible |
| Timeout risk | High for slow jobs | Low |
┌──────────┐ 1. INSERT status='queued' ┌────────────────────────┐
│ Producer │ ───────────────────────────────> │ jobs table │
└──────────┘ │ │
│ queued │
┌──────────┐ 2. txn A: FOR UPDATE SKIP LOCKED │ │ claim (txn A) │
│ Worker │ SET running, lease_until; │ v │
│ │ <──────────────────────────────> │ running, lease_until │
│ │ COMMIT │ │ │ │
│ │ │ v v │
│ │ 4. txn B: SET done or failed, │ done failed/retry │
│ │ ───────────────────────────────> │ │
└──────────┘ run_at; COMMIT └────────────────────────┘
│ ^ ^
│ │ 3. run job: no txn open, │ 5. requeue
v │ connection back in pool │
┌──────────────────┐ ┌────────────────────────┐
│ External service │ │ Reaper (periodic) │
│ (HTTP, may hang) │ │ SET status='queued' │
└──────────────────┘ │ WHERE status='running' │
│ AND lease_until < now()│
└────────────────────────┘
Claim-and-commit lifecycle: the worker commits its claim in a short transaction, runs the job and any external calls with no transaction open, records the outcome in a second short transaction, and a reaper requeues running jobs whose lease has expired.
How do you implement leases, retries, and idempotent handlers?
With claim-and-commit, a worker that crashes leaves its row in running forever unless something reclaims it. A reaper runs periodically and updates rows where status = 'running' and locked_at < now() - lease_timeout, setting them back to queued and clearing locked_by. This gives you at-least-once delivery, not exactly-once. The reaper cannot tell a dead worker from a slow one. If a job's runtime exceeds the lease, the reaper requeues it while the original worker is still running, and the job executes twice. Set the lease well above your slowest job's expected runtime, or have long jobs extend locked_at periodically as a heartbeat.
Because duplicates will happen, handlers must be idempotent:
- Idempotency keys: Store a key derived from the job, such as
order_idplus the action, in a table with a unique constraint. A second insert fails, and the handler treats that failure as "already done." - Upserts: Use
INSERT ... ON CONFLICT DO UPDATEorDO NOTHINGso that writing the same state twice has no additional effect. - External side effects: Emails and payments cannot be rolled back. Record a sent-marker before calling the provider, and pass your idempotency key to the provider when its API accepts one.
On failure, increment attempts, set run_at = now() + base * 2^attempts (add jitter so retries don't all fire together), and write the exception to last_error. Once attempts reaches max_attempts, set the status to failed so the job stops retrying and is kept for inspection.
SKIP LOCKED does not preserve order. If jobs for the same entity must run strictly in sequence, you need per-key serialization, such as an advisory lock on the entity ID or a rule that only claims the oldest pending job per key.
-- Reaper: run periodically. Requeues rows whose lease expired.
-- It cannot tell a dead worker from a slow one, so delivery is at-least-once.
UPDATE jobs
SET status = 'queued', locked_by = NULL, locked_at = NULL
WHERE status = 'running'
AND locked_at < now() - interval '15 minutes'; -- lease > slowest job
-- Heartbeat: long jobs extend their lease while still owning the row.
UPDATE jobs SET locked_at = now()
WHERE id = $1 AND locked_by = $2 AND status = 'running';
-- Idempotency key, in the same transaction as the handler's writes.
-- Zero rows returned means the action already ran, so skip the work.
CREATE TABLE IF NOT EXISTS processed_actions (
idempotency_key text PRIMARY KEY, -- e.g. 'order:4821:send_receipt'
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO processed_actions (idempotency_key)
VALUES ($1)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;
-- Failure path: back off exponentially with jitter, record the error,
-- and stop retrying after max_attempts. SET expressions read the old row,
-- so attempts + 1 is the new count. The locked_by guard stops a worker
-- whose lease was reaped from overwriting the row.
UPDATE jobs
SET attempts = attempts + 1,
last_error = $2,
status = CASE WHEN attempts + 1 >= max_attempts
THEN 'failed' ELSE 'queued' END,
run_at = now()
+ interval '10 seconds' * power(2, attempts + 1)
+ interval '5 seconds' * random(),
locked_by = NULL,
locked_at = NULL
WHERE id = $1 AND locked_by = $3;
Shows the lease reaper, a heartbeat that extends the lease, an idempotency-key insert that detects already-completed work, and a failure update that applies exponential backoff with jitter, records last_error, and marks the job failed after max_attempts.
How does transactional enqueue save engineering time?
The biggest saving has nothing to do with locking. It comes from where the job row lives. When the job table sits in the same database as your business data, you insert the job inside the same transaction as the write it depends on. Suppose an order insert and its send_confirmation job commit together. The job becomes visible to workers at the same moment the order does. If the transaction rolls back, the job disappears with it. No worker ever picks up a job that points at an order that doesn't exist, and no committed order goes without its job.
An external broker can't join your database transaction. To get the same guarantee, you have to build it yourself:
- An outbox table written in the business transaction.
- A relay process that reads the outbox and publishes to the broker.
- Dedup logic in consumers, because the relay can publish and then crash before marking the row as sent.
Each of these is a component you have to build, deploy, monitor and debug. With a Postgres queue, the outbox is the queue.
Cutting latency with LISTEN/NOTIFY
Polling every few seconds adds delay and generates load even when the queue is idle. Instead, issue NOTIFY in the enqueue transaction and have workers LISTEN on that channel. Notifications are delivered only on commit, so a worker never wakes for a job that doesn't exist yet.
Keep a slower periodic poll as the correctness path anyway, for three reasons:
- Notifications are not durable.
- A worker that is reconnecting misses any notification sent while it was disconnected.
LISTENneeds a dedicated session. It generally doesn't work through a pooler in transaction mode.
Use notifications to make the queue fast, and rely on polling to make it correct.
-- Enqueue: the job commits or rolls back together with the order.
BEGIN;
INSERT INTO orders (id, customer_id, total_cents)
VALUES (1042, 77, 4999);
INSERT INTO jobs (kind, payload)
VALUES ('send_confirmation', jsonb_build_object('order_id', 1042));
-- Queued now, delivered to listeners only if this transaction commits.
NOTIFY jobs_ready;
COMMIT; -- a ROLLBACK here would discard the order, the job and the notification
-- Worker, on a dedicated (non-pooled) session:
LISTEN jobs_ready;
-- Run this claim query when a notification arrives AND on a slow timer
-- (e.g. every 30s). The timer covers notifications missed during reconnects.
WITH next AS (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', started_at = now()
FROM next
WHERE j.id = next.id
RETURNING j.id, j.kind, j.payload;
The order row, its job row and the NOTIFY commit as one unit, while workers LISTEN for fast wakeups and keep running the same SKIP LOCKED claim query on a timer as the correctness fallback.
How do you keep a Postgres job queue healthy in production?
Clean up completed rows
Every claim, heartbeat, and completion is an UPDATE, which in Postgres writes a new row version and leaves a dead tuple behind. A busy queue table can churn through its live rows many times an hour. Delete or archive finished jobs in small batches, for example a few thousand rows per transaction, so each pass stays short. At higher volume, partition by time and drop old partitions. Dropping a partition removes the data without creating dead tuples.
Keep vacuum ahead of churn
Dead tuples bloat the table and its indexes. The claim query then scans past garbage before it finds a runnable row. The default autovacuum thresholds scale with table size, so they fire too late for a small, hot table. Set lower per-table values with ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = ...), and watch n_dead_tup in pg_stat_user_tables. Vacuum can't remove tuples that any open transaction might still see. One idle-in-transaction session or a long report anywhere in the database blocks cleanup for every table. Check xact_start in pg_stat_activity.
Measure age, not just depth
Track four numbers:
- Job counts grouped by status.
now() - min(run_at)for runnable jobs.- Running jobs whose lease has expired.
- Failed jobs as a share of finished jobs over a recent time window.
Alert on the age of the oldest runnable job. A deep queue that drains quickly is healthy. A shallow queue with a job waiting twenty minutes means something is wrong.
Size workers against the pool
Each worker loop holds a database connection while it claims a job. Under hold-the-lock, it keeps that connection for the full duration of the job. Count the total number of worker slots across all processes, add your web traffic, and make sure the sum fits within the pool and within max_connections.
When should you avoid a pg job queue, and what are the alternatives?
The pattern stops paying off once the queue's writes become a meaningful share of the primary's load. Every job costs at least three writes: the insert, the claim update, and the completion update or delete. Each one produces WAL, leaves dead tuples behind, and checks out a connection, all on the same primary that serves user-facing queries.
It is also the wrong tool in several other cases:
- Multiple consumers. If you need fan-out, pub/sub, consumption by other services, or replay, a table does not fit. A row that one worker claims and deletes is gone for every other consumer.
- Strict ordering. SKIP LOCKED exists so that workers can jump past locked rows. Global FIFO therefore holds only if you run a single worker.
- Shared failure. A primary outage or failover stops the application and the job system at the same time. When the database comes back, the retry backlog arrives with it.
| Option | Operational cost | Delivery guarantee | Transactional enqueue | Feature set |
|---|---|---|---|---|
| Jobs table in app database | Lowest; nothing new to run | At-least-once via leases | Yes, native | Whatever you build |
| Separate queue database | One more Postgres to operate | At-least-once | No; needs an outbox | Same as above, isolated load |
| Broker plus outbox | Highest; broker and relay | At-least-once; consumers dedupe | Yes, via the outbox table | Fan-out, routing, replay on log-based brokers |
| Managed queue service | Low ops, per-message billing | Typically at-least-once | No; needs an outbox | Visibility timeouts, dead-letter queues, elastic scaling |
Consider migrating when you see any of these signals:
- Jobs tables rank among your top sources of I/O or WAL.
- Autovacuum falls behind on the jobs table.
- You add connections or hardware mainly to support workers.
- A second service asks to consume the same events.
- Incident reviews trace user-facing latency back to job throughput.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to postgres skip locked job queue, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- The dependency graph answers "what does this touch?" in seconds instead of a manual audit.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.