What is a Postgres SKIP LOCKED job queue and why does it work?
A Postgres SKIP LOCKED job queue is a jobs table in your application's existing Postgres database. Workers claim rows from it with SELECT ... FOR UPDATE SKIP LOCKED. Each row is a unit of work. A worker locks a row, runs the job, and marks it done or deletes it. Libraries such as pg-boss (Node.js) and good_job (Rails) package Postgres-backed queues, but the underlying idea is small enough to understand and build yourself.
The pattern works because of how row locks behave under concurrency. With plain FOR UPDATE, several workers that select "the oldest pending job" all target the same row. The first worker locks it, and the others block until that transaction commits or rolls back. Adding workers adds waiting, not throughput. SKIP LOCKED changes that behavior: any row another transaction has already locked is skipped instead of waited on. Each worker immediately gets a different available row, so ten workers can claim ten jobs at once without contending.
The cost is strict FIFO ordering. Workers still pick roughly the oldest jobs first. However, a job enqueued later can start or finish before an earlier one that is locked, slow, or rolled back and retried. If your jobs need exact sequencing, you have to enforce it some other way.
This pattern fits backend and platform teams that run a monolith on Postgres with moderate job volume. The main gain is one fewer system to deploy, monitor, and back up. The second gain is transactional enqueue: a job can be inserted in the same transaction as the business data that triggered it. That is covered later.
How do you design the jobs table and indexes?
Start with one table and resist adding columns until a query needs them:
id: abigintidentity. It is cheap to index and gives a stable handle for logs and admin tooling.queue: a short text name such asemailsorbilling. It lets workers pull from specific queues so a flood of one job type cannot starve another.payload:jsonbholding the job's arguments. Store IDs, not whole objects, so a job reads current data when it runs.status: one ofpending,running,succeeded,failed.run_at: the earliest time the job may run. Default it tonow()so immediate jobs and scheduled jobs use the same code path.attempts: default0. Incremented on each claim.max_attempts: a non-null default such as5. Every job gets a retry ceiling without callers having to remember one.locked_atandlocked_by: nullable. They record when a job was claimed and which worker claimed it, which matters for recovering stalled jobs.last_error: nullable text for the most recent failure.created_at: defaultnow(). It is used for latency metrics and retention.
For status, use text with a CHECK constraint rather than an enum type. Both reject typos. Changing a CHECK is a plain constraint swap, but an enum value cannot easily be removed or renamed once rows depend on it.
The key index is partial: (queue, run_at) WHERE status = 'pending'. Succeeded and failed rows never enter it, so the index stays roughly the size of the current backlog no matter how much history accumulates. The dequeue query filters on queue, orders by run_at, and reads only the few entries it needs.
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
queue text NOT NULL DEFAULT 'default',
payload jsonb NOT NULL DEFAULT '{}'::jsonb, -- store IDs, not whole objects
-- text + CHECK instead of an enum: easy to change later with a constraint swap
status text NOT NULL DEFAULT 'pending'
CONSTRAINT jobs_status_check
CHECK (status IN ('pending', 'running', 'succeeded', 'failed')),
run_at timestamptz NOT NULL DEFAULT now(), -- immediate and scheduled jobs share one path
attempts integer NOT NULL DEFAULT 0,
max_attempts integer NOT NULL DEFAULT 5 CHECK (max_attempts > 0),
locked_at timestamptz, -- set on claim, used to find stalled jobs
locked_by text, -- worker identifier, e.g. hostname:pid
last_error text,
created_at timestamptz NOT NULL DEFAULT now()
);
-- Only pending rows are indexed, so the index tracks the backlog,
-- not the full history of succeeded and failed jobs.
CREATE INDEX jobs_dequeue_idx
ON jobs (queue, run_at)
WHERE status = 'pending';
-- Enqueue: defaults fill in status, run_at, attempts and max_attempts.
INSERT INTO jobs (queue, payload)
VALUES ('emails', '{"user_id": 42, "template": "welcome"}');
-- Schedule a job for later by overriding run_at.
INSERT INTO jobs (queue, payload, run_at)
VALUES ('billing', '{"invoice_id": 1001}', now() + interval '1 hour');
-- Changing allowed statuses later is a plain constraint swap:
-- ALTER TABLE jobs DROP CONSTRAINT jobs_status_check,
-- ADD CONSTRAINT jobs_status_check
-- CHECK (status IN ('pending', 'running', 'succeeded', 'failed', 'cancelled'));
A minimal jobs table with a CHECK-constrained text status, defaults that let callers enqueue with just a queue name and payload, and a partial index on (queue, run_at) covering only pending rows.
How does the dequeue query claim jobs safely?
Build the claim in two parts. The inner part is a CTE that selects id from jobs where status = 'pending' and run_at <= now(), orders by run_at, limits to n rows, and adds FOR UPDATE SKIP LOCKED. The outer part is an UPDATE that joins on those ids, sets status = 'running', locked_at = now() and locked_by to the worker's identifier, and ends with RETURNING id, payload.
FOR UPDATE takes a row lock on each selected row. SKIP LOCKED tells other transactions to pass over any row that is already locked instead of waiting for it. Concurrent workers therefore partition the backlog among themselves without coordinating:
jobs (pending, run_at <= now())
+----+----+----+----+----+----+
| 1 | 2 | 3 | 4 | 5 | 6 |
+----+----+----+----+----+----+
Worker A [lock][lock] -> claims 1, 2
Worker B skip skip [lock][lock] -> claims 3, 4
The select and the status change must run in one statement, or at least in one transaction. Suppose you run the SELECT ... FOR UPDATE SKIP LOCKED in autocommit mode and issue the UPDATE afterwards. The row lock is released when the select's transaction commits. During the gap before the update, another worker sees the row as unlocked and still pending, and claims it as well. Both workers then run the job. Combining the two steps in a single statement closes that window.
On the worker side, commit the claim, run the handler, and then mark the job finished with a separate update: status = 'completed' where id matches and locked_by is still this worker. The locked_by check stops a worker whose claim has been taken over by another worker from overwriting that worker's result.
import os, socket
import psycopg2
WORKER_ID = f"{socket.gethostname()}-{os.getpid()}"
# Claim and status change in ONE statement: rows stay locked until commit,
# so no other worker can see them as unlocked and still 'pending'.
CLAIM_SQL = """
WITH next_jobs AS (
SELECT id FROM jobs
WHERE status = 'pending' AND run_at <= now()
ORDER BY run_at
LIMIT %(n)s
FOR UPDATE SKIP LOCKED -- pass over rows other workers hold
)
UPDATE jobs
SET status = 'running', locked_at = now(), locked_by = %(worker)s
FROM next_jobs
WHERE jobs.id = next_jobs.id
RETURNING jobs.id, jobs.payload
"""
# Only the current claim holder may complete the job.
COMPLETE_SQL = """
UPDATE jobs SET status = 'completed'
WHERE id = %(id)s AND locked_by = %(worker)s
"""
def run_batch(conn, handle, n=10):
with conn, conn.cursor() as cur: # 'with conn' commits on success
cur.execute(CLAIM_SQL, {"n": n, "worker": WORKER_ID})
jobs = cur.fetchall()
for job_id, payload in jobs:
handle(payload) # runs after commit: no row locks held during work
with conn, conn.cursor() as cur:
cur.execute(COMPLETE_SQL, {"id": job_id, "worker": WORKER_ID})
if cur.rowcount == 0:
print(f"job {job_id}: claim taken over, result not recorded")
run_batch(psycopg2.connect("dbname=app user=worker host=localhost"), print)
A psycopg2 worker that claims a batch with a single CTE-plus-UPDATE statement using FOR UPDATE SKIP LOCKED, commits the claim, runs the handler, and then marks each job completed only if locked_by still matches this worker.
Should you hold the lock for the whole job or use a lease?
That claim-then-update pattern is one of two ways to run a job, and the choice affects how the queue behaves under load.
Model A: hold the lock for the whole job. The worker runs SELECT … FOR UPDATE SKIP LOCKED, executes the job, and marks it complete inside the same transaction. This is crash-safe with no extra code. If the worker dies, Postgres rolls back its transaction, releases the row lock, and the job becomes claimable again. The costs grow with job duration:
- A transaction that stays open for minutes keeps vacuum from removing dead tuples newer than its snapshot, across the whole database.
- Each running job occupies one connection.
- Transaction-mode connection poolers cannot hand that connection to anyone else until the job finishes.
Model B: claim, commit, run, complete. The claim sets status = 'running', locked_by, and locked_until = now() + interval '5 minutes', then commits. The job runs with no transaction open, and a second short transaction records the result. Because nothing rolls back on a crash, you need a reaper. The reaper runs UPDATE jobs SET status = 'queued', locked_by = NULL WHERE status = 'running' AND locked_until < now() on a schedule.
The lease length is a tradeoff:
- Too short: the lease expires while the job is still running, the reaper requeues it, and a second worker runs it in parallel.
- Too long: a job held by a crashed worker sits idle until its lease expires.
A common approach is to size the lease above the job's realistic worst-case runtime. For long jobs, the worker can periodically extend locked_until as a heartbeat.
| Model A: lock held | Model B: lease | |
|---|---|---|
| Crash recovery | Automatic rollback | Reaper after lease expiry |
| Transaction length | Job duration | Milliseconds |
| Vacuum impact | Holds back cleanup | Negligible |
| Connections | One per running job | Only during claim/complete |
| Transaction poolers | Poor fit | Works |
| Duplicate risk | Low | If lease < runtime |
-- Model B: each statement runs in its own short autocommit transaction.
-- 1. Claim: take one queued job and stamp a 5-minute lease, then commit.
UPDATE jobs
SET status = 'running',
locked_by = 'worker-7',
locked_until = now() + interval '5 minutes'
WHERE id = (
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED -- concurrent claimers skip this row
)
RETURNING id, payload;
-- (job runs here with no transaction open)
-- 2. Heartbeat for long jobs: extend the lease only if we still own it.
-- 0 rows updated means the lease was lost, so the worker should stop.
UPDATE jobs
SET locked_until = now() + interval '5 minutes'
WHERE id = 42 AND status = 'running' AND locked_by = 'worker-7';
-- 3. Complete: the ownership check keeps a worker whose lease expired
-- from overwriting a run that another worker now owns.
UPDATE jobs
SET status = 'done', locked_by = NULL, locked_until = NULL
WHERE id = 42 AND status = 'running' AND locked_by = 'worker-7';
-- 4. Reaper, run on a schedule: requeue jobs whose lease has expired.
UPDATE jobs
SET status = 'queued', locked_by = NULL, locked_until = NULL
WHERE status = 'running'
AND locked_until < now()
RETURNING id;
The Model B lease lifecycle in SQL: claim with SKIP LOCKED and a lease, extend the lease with a heartbeat, complete only while still owning the job, and requeue expired leases with a reaper.
How do transactional enqueue, retries and scheduling work in practice?
Transactional enqueue is the main reason to use a Postgres queue in a monolith. When the order row and its "send receipt" job are inserted in the same transaction, they commit or roll back together. With an external broker you have two writes that can diverge: the database commits and the process crashes before publishing, or the message is published and the transaction rolls back. The usual fix is an outbox table plus a relay that forwards rows to the broker. Here the jobs table is the outbox and the worker is the relay. You still need a real outbox when consumers live outside your system, such as another team's service subscribing to events, because something has to publish across that boundary.
Retries and dead jobs
On failure, increment attempts, write the error to last_error, and set run_at to now plus base × 2^attempts with random jitter. The jitter keeps retries from many workers from hitting a recovering dependency at the same moment. When attempts reaches max_attempts, move the row to a failed state and keep last_error.
Delivery is at-least-once. A worker can finish the side effect and die before marking completion, so handlers must be idempotent. If two jobs for the same account must not overlap, your application has to enforce that serialization.
Delayed and recurring jobs
A delayed job is a row with a future run_at. For cron, every node computes the same tick. A unique constraint on (job_name, scheduled_at) turns duplicate inserts into no-ops. Alternatively, a pg_advisory_lock can elect a single node to enqueue each tick.
This approach saves engineering time. You don't build a relay or write broker reconciliation logic, and every stuck or failed job can be found with a SELECT.
-- 1. Transactional enqueue: the order and its job commit or roll back together.
BEGIN;
INSERT INTO orders (id, customer_id, total_cents)
VALUES (42, 1001, 4999);
INSERT INTO jobs (kind, payload, run_at)
VALUES ('send_receipt', jsonb_build_object('order_id', 42), now());
-- Delayed job: same insert with a future run_at.
INSERT INTO jobs (kind, payload, run_at)
VALUES ('review_reminder', jsonb_build_object('order_id', 42), now() + interval '3 days');
COMMIT;
-- 2. Failure handling ($1 = job id, $2 = error text).
-- In SET, column references read the pre-update values.
UPDATE jobs
SET attempts = attempts + 1,
last_error = $2,
locked_at = NULL,
status = CASE WHEN attempts + 1 >= max_attempts
THEN 'failed' ELSE 'queued' END,
-- Exponential backoff from a 5s base, scaled by a random 0.5x-1.5x jitter.
run_at = CASE WHEN attempts + 1 >= max_attempts THEN run_at
ELSE now() + make_interval(
secs => 5 * power(2, attempts + 1) * (0.5 + random()))
END
WHERE id = $1;
-- 3. Recurring jobs: each tick may be enqueued only once.
CREATE UNIQUE INDEX jobs_cron_tick_uniq
ON jobs (kind, scheduled_at) WHERE scheduled_at IS NOT NULL;
-- Every node runs this each hour and computes the same tick.
-- Inserts after the first one become no-ops.
INSERT INTO jobs (kind, payload, run_at, scheduled_at)
VALUES ('nightly_report', '{}'::jsonb, now(), date_trunc('hour', now()))
ON CONFLICT (kind, scheduled_at) WHERE scheduled_at IS NOT NULL DO NOTHING;
-- 4. Failures stay visible in SQL.
SELECT id, kind, attempts, last_error FROM jobs WHERE status = 'failed';
This example inserts a job in the same transaction as the business write, retries failures with jittered exponential backoff until the row moves to a failed state, and deduplicates cron ticks with a unique index.
How do you wake workers and keep the table healthy?
Polling is the simplest wake-up mechanism. Each idle worker runs the dequeue query every second or two. That is cheap with a partial index, and it bounds worst-case latency to the interval. To cut latency further, have the enqueuing transaction issue NOTIFY and have workers LISTEN. Postgres delivers the notification only when that transaction commits, so a worker never wakes for a row it cannot yet see.
Treat NOTIFY as a hint, never as the source of truth:
- Notifications are not durable. They are not stored in the table, and a backlog of jobs never produces a replay of past notifications.
- They are lost when no listener is connected. If a worker reconnects after a crash, it misses everything sent in between.
- They need a session-level connection.
LISTENis tied to one backend session, and poolers in transaction mode hand that session to other clients, so listeners need a dedicated direct connection.
Keep the poll loop as a fallback. A notification just shortens the sleep.
Housekeeping
Postgres updates create new row versions. Every claim, completion and retry therefore leaves a dead tuple behind. A busy queue table churns faster than default autovacuum thresholds expect, because those thresholds scale with table size. Lower autovacuum_vacuum_scale_factor for this table alone, or set a fixed autovacuum_vacuum_threshold. Also watch for long-running transactions anywhere in the database, since they stop vacuum from reclaiming those tuples.
You have three options for removing finished rows. You can delete them in small batches, or move them to a history table. You can also partition by time and drop old partitions, which avoids creating dead tuples at all.
Monitor four things:
- queue depth
- age of the oldest pending job
n_dead_tupfrompg_stat_user_tables- table and index size
When should you avoid a Postgres queue, and what are the alternatives?
The queue shares a blast radius with your application. A burst of jobs competes with user-facing queries for CPU, disk I/O, connection slots and autovacuum capacity on the same primary. A backlog of 200,000 retries can slow down checkout. There is also a throughput ceiling. Very high job rates, fan-out to many consumers, or the need to replay a stream are better served by a dedicated message broker. Where that ceiling sits depends on your hardware, row width, job duration and the rest of your workload. Load-test your own setup at expected peak plus margin instead of trusting a published number.
If the pattern fits, decide whether to build or adopt. pg-boss (Node.js) and good_job (Ruby on Rails) already handle retries with backoff, cron-style scheduling, concurrency limits and cleanup of finished jobs. good_job also ships a web dashboard. Hand-rolled SQL is cheap on day one. Over time you own lease reaping, cron deduplication across workers, metrics and the edge cases from earlier sections, and each of those needs ongoing maintenance. A library removes that work if your stack matches. In exchange, you adopt its schema, its migrations and its operating model, and leaving later means a data migration.
Once you outgrow the database, a separate broker is the next step. You give up transactional enqueue and need an outbox or similar pattern instead.
| Option | Setup effort | Operational burden | Transactional enqueue | Throughput headroom | Ecosystem fit |
|---|---|---|---|---|---|
| Hand-rolled SQL | Low | High (you own everything) | Yes | Bounded by primary | Any language |
| pg-boss | Low | Medium | Yes, when sharing the connection | Bounded by primary | Node.js |
| good_job | Low | Low–medium | Yes, same database | Bounded by primary | Rails / ActiveJob |
| Separate broker | High | High (new system) | No, needs outbox | Highest | Language-agnostic |
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.
- Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
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.
- Consumers learn about a breaking change at review time rather than from production errors.
- 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.