Postgres SKIP LOCKED Queue: Build a Job Queue with Row-Level Locking

What FOR UPDATE SKIP LOCKED does and why naive dequeues fail

FOR UPDATE SKIP LOCKED is a row-level locking clause that tells Postgres to lock the rows a SELECT returns and to leave out any row another transaction has already locked. For a job queue, this lets many workers ask for "the next job" at the same time without claiming the same job or waiting on each other.

The naive dequeue: duplicate claims

A common first version reads the oldest pending job, then marks it running in a second statement. Nothing stops two workers from reading the same row between those steps:

Both workers run job 42.

Plain FOR UPDATE: correct but serialized

Adding FOR UPDATE to the select makes the second reader wait for the first one's lock:

Every worker queues behind the same head row. Adding workers adds waiting, not throughput.

SKIP LOCKED: different rows, no waiting

With SKIP LOCKED, worker B ignores job 42 at t2 and locks job 43 straight away. The cost is that each worker sees a deliberately incomplete view of the table, because locked rows are treated as if they weren't there. That suits a queue, where any free job will do. For reports, balances, or uniqueness checks it gives wrong answers.

Copy-- Example table: one row per job.
CREATE TABLE jobs (
  id         bigserial PRIMARY KEY,
  status     text NOT NULL DEFAULT 'pending',
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 1. Naive dequeue (racy): two statements, no lock between them.
--    Two workers can both read job 42 before either updates it.
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1;
-- ...later, in a separate statement:
UPDATE jobs SET status = 'running' WHERE id = 42;

-- 2. FOR UPDATE: no duplicate claims, but a second worker
--    blocks on the same head row until the first commits.
BEGIN;
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE;
-- ...process, mark running/done, then COMMIT.
COMMIT;

-- 3. FOR UPDATE SKIP LOCKED: each worker claims a different
--    row without waiting. Locked rows are simply left out.
UPDATE jobs SET status = 'running'
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;  -- no row returned means no free job right now

The three dequeue patterns side by side: a racy select-then-update, a correct but serializing FOR UPDATE, and a single-statement FOR UPDATE SKIP LOCKED claim that lets concurrent workers take different jobs.

Schema and the canonical dequeue query

Shape the table and index around the dequeue

A minimal jobs table needs id (a bigint identity), payload jsonb, status text (pending, running, done, failed), priority int, run_at timestamptz defaulting to now(), attempts int defaulting to 0, locked_at timestamptz, locked_by text, and last_error text. Keeping run_at separate from creation time lets you delay a job or back off a retry with a single column update.

Index only what workers search: a partial index on (priority, run_at) WHERE status = 'pending'. Completed rows are never in it, so the index stays roughly the size of the backlog, and dequeues stay index scans even as finished rows pile up. The planner uses a partial index only when the query's own predicate implies the index predicate, so the dequeue must filter on status = 'pending' literally.

The canonical dequeue is a single statement: UPDATE jobs SET status = 'running', locked_at = now(), locked_by = $1, attempts = attempts + 1 WHERE id = (SELECT id FROM jobs WHERE status = 'pending' AND run_at <= now() ORDER BY priority, run_at LIMIT 1 FOR UPDATE SKIP LOCKED) RETURNING *. Order by both indexed columns so the index also supplies the sort.

The lock has to live in the subquery because that is where the row is chosen. If the subquery doesn't lock, two workers can read the same id. The second then blocks on the outer UPDATE and either claims a job that is already running or updates nothing. With FOR UPDATE SKIP LOCKED on the inner SELECT, each worker skips rows that another worker has already locked and moves on to the next one.

Enqueue by inserting into jobs in the same transaction as the business change, for example the INSERT that creates an order. They commit or roll back together, so you never get a job for an order that doesn't exist, and you never get an order with no job. Workers can't see the job until that transaction commits.

Copy-- Jobs table: run_at is separate from creation time so delays/backoff are one UPDATE.
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', 'running', 'done', 'failed')),
  priority   int         NOT NULL DEFAULT 0,
  run_at     timestamptz NOT NULL DEFAULT now(),
  attempts   int         NOT NULL DEFAULT 0,
  locked_at  timestamptz,
  locked_by  text,
  last_error text
);

-- Only pending rows are indexed, so the index tracks the backlog, not history.
CREATE INDEX jobs_pending_idx ON jobs (priority, run_at) WHERE status = 'pending';

-- Dequeue: the lock is taken in the subquery, where the row is chosen.
-- Rows locked by other workers are skipped instead of waited on.
UPDATE jobs
SET status = 'running', locked_at = now(), locked_by = 'worker-1',
    attempts = attempts + 1
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'pending' AND run_at <= now()  -- literal predicate matches the partial index
  ORDER BY priority, run_at                     -- index also supplies the sort
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING *;

-- Transactional enqueue: the job exists only if the business change commits.
-- Assumes an existing orders table with an identity id column.
BEGIN;
WITH new_order AS (
  INSERT INTO orders (customer_id, total_cents) VALUES (42, 1999)
  RETURNING id
)
INSERT INTO jobs (payload)
SELECT jsonb_build_object('type', 'send_receipt', 'order_id', id) FROM new_order;
COMMIT;

The jobs table with its partial index on pending rows, the single-statement SKIP LOCKED dequeue, and an enqueue that commits atomically with the order insert.

Choosing a worker model: held lock vs. lease

Once a worker has claimed a row, the next question is how long it keeps that claim and what happens when it dies mid-job.

Model one: hold the lock for the whole job

The worker opens a transaction, runs the FOR UPDATE SKIP LOCKED dequeue, does the work, marks the row done (or deletes it), and commits. If the process crashes, the connection drops, Postgres rolls back the transaction, and the row lock disappears. The next worker picks the job up with no extra code. The cost is a transaction that stays open for as long as the job runs. That transaction holds back the xmin horizon, so vacuum cannot remove newer dead tuples anywhere in the database, not only in the queue table. Each in-flight job also pins a connection.

Model two: commit a lease

The worker claims the job and commits right away: UPDATE jobs SET status = 'running', locked_until = now() + interval '5 minutes' WHERE id IN (…SKIP LOCKED subquery…) RETURNING *. It runs the job outside any transaction, then issues a separate UPDATE to set status = 'done'. Transactions stay short, but a crash leaves the row stuck in running. A periodic reclaim fixes that: UPDATE jobs SET status = 'queued' WHERE status = 'running' AND locked_until < now().

The risk is a slow job that outlives its lease. The reclaim requeues it while the first worker is still running, so a second worker runs it concurrently. To limit this, long jobs should heartbeat by extending locked_until. The final UPDATE should check ownership, for example with a worker_id or attempt counter in its WHERE clause.

Copy-- Model one: hold the row lock for the whole job (one transaction)
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'queued'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- ... application runs the job; a crash rolls back and frees the row ...
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

-- Model two: claim with a lease and commit immediately (autocommit)
UPDATE jobs
SET status = 'running', worker_id = $1, attempts = attempts + 1,
    locked_until = now() + interval '5 minutes'
WHERE id = (SELECT id FROM jobs WHERE status = 'queued'
            ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1)
RETURNING id, payload, attempts;  -- keep id and attempts as the claim token

-- Heartbeat: extend the lease only if this claim still owns the row
UPDATE jobs SET locked_until = now() + interval '5 minutes'
WHERE id = $1 AND status = 'running' AND worker_id = $2 AND attempts = $3;

-- Finish: zero rows updated means the lease was lost and the job was requeued
UPDATE jobs SET status = 'done', locked_until = NULL
WHERE id = $1 AND status = 'running' AND worker_id = $2 AND attempts = $3;

-- Reclaim (run periodically): requeue rows whose lease has expired
UPDATE jobs SET status = 'queued', worker_id = NULL, locked_until = NULL
WHERE status = 'running' AND locked_until < now();

Sketches of the held-lock worker transaction, the lease-based claim, heartbeat and ownership-checked finish, and the periodic reclaim query. The $1/$2/$3 placeholders are parameters bound by the application driver.

Batching, ordering, retries and idempotency

Batch dequeue and ordering

Claiming one row per round trip wastes most of each query on network and planning overhead. Adding LIMIT n to the claim lets a worker lock several rows in one statement. Keep n modest: a worker that grabs fifty slow jobs holds them while idle workers find nothing to claim.

ORDER BY decides which rows a worker tries first, but it does not give you strict FIFO. Two workers can scan concurrently, and the second skips rows the first has locked, so jobs commit out of order. Treat ordering as a preference. Sorting on priority, run_at with an index matching that order is the practical version. It is cheap to scan and pushes urgent work ahead. If two jobs for the same customer must never run in parallel or out of sequence, you need extra design. Two common options are a per-key advisory lock taken inside the handler, or a partial unique index that allows only one running row per entity key.

Retries and delivery guarantees

On failure, increment attempts, record the error, and set run_at to now plus an exponential backoff with jitter so retries don't cluster. Once attempts reaches a maximum, set status = 'failed' so the row leaves the claimable set and a human can inspect it.

Delivery is at-least-once. A worker can finish the side effect and then crash before it commits. Handlers therefore need idempotency keys checked at the destination, or operations such as upserts that are naturally safe to repeat.

Copy-- Partial index matching the claim's ORDER BY, limited to claimable rows.
CREATE INDEX jobs_claim_idx ON jobs (priority, run_at) WHERE status = 'queued';

-- Single-flight per entity: at most one running job per customer.
-- A claim that would break this raises unique_violation, so catch it and retry.
CREATE UNIQUE INDEX jobs_one_running_per_key ON jobs (entity_key) WHERE status = 'running';

-- Batch claim: lock up to 10 due jobs, lower priority number first.
-- This is a preference, not strict FIFO, because rows locked by others are skipped.
WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY priority, run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', locked_at = now()
FROM next
WHERE j.id = next.id
RETURNING j.id, j.entity_key, j.payload, j.attempts;

-- On handler failure ($1 = job id, $2 = error text).
-- In SET, "attempts" is the pre-update value in every expression.
UPDATE jobs
SET attempts   = attempts + 1,
    last_error = $2,
    locked_at  = NULL,
    -- Terminal state once the attempt budget is spent, so the row leaves the claimable set.
    status     = CASE WHEN attempts + 1 >= max_attempts THEN 'failed' ELSE 'queued' END,
    -- Exponential backoff capped at 1 hour, with +/-25% jitter to spread retries.
    run_at     = CASE WHEN attempts + 1 >= max_attempts THEN run_at
                      ELSE now() + make_interval(secs => least(3600, power(2, attempts + 1))
                                                         * (0.75 + random() * 0.5))
                 END
WHERE id = $1;

A batch claim with FOR UPDATE SKIP LOCKED ordered by priority and run_at, a partial unique index that allows one running job per entity, and a failure update that applies jittered exponential backoff and moves rows to 'failed' after max_attempts.

Running it in production: wakeups, connections and table health

Wakeups

Polling with a short sleep, say every second, is simple and correct, but it trades latency against idle query load. LISTEN/NOTIFY cuts latency: the enqueueing transaction issues NOTIFY, and Postgres delivers it to listeners only after that transaction commits. Treat the notification as a hint, not a guarantee. A worker that is disconnected, restarting, or busy can miss it, and one notification may wake several workers when only one job is available. Keep a fallback poll so jobs are never stranded.

Connections

Each worker usually holds a connection, often inside an open transaction while it works. Your total worker count across all hosts is therefore capped by max_connections, and each Postgres connection is a backend process with its own memory. A pooler helps, but transaction-level pooling hands the server connection to another client after each transaction. LISTEN and session-level advisory locks are tied to a session, so they can break silently through such a pooler. Use session pooling or a direct connection for listeners.

Table health

Every update or delete leaves a dead tuple. Delete completed jobs or move them to an archive table. Tune autovacuum per table so it runs more often on the queue table. Watch for long-running or idle-in-transaction sessions anywhere in the database, because they hold back cleanup on every table, including this one.

SKIP LOCKED does not remove scan cost. With a large backlog or no partial index on eligible rows, the dequeue still reads past many locked, scheduled-for-later, or dead rows before it finds one it can claim.

When to use a Postgres queue and when to avoid it

Good fits

A Postgres queue works best for teams that already run Postgres, process jobs at moderate rates, and want fewer moving parts. The main benefit is transactional enqueue. If you insert the job row in the same transaction as the business write, both commit or neither does. You don't need an outbox, a dual-write reconciliation job, or a second system to monitor, back up, and secure. Your existing tooling also covers the queue: plain SQL for inspection and the same backups, access controls, and on-call runbooks you already use.

Poor fits

Avoid this design when the requirements describe a log rather than a work queue:

• Several consumers must each see the same message. SKIP LOCKED gives a row to exactly one worker. Fan-out means duplicating rows per consumer or building subscription tracking yourself.
• You need replay. Once a job is deleted or marked done, re-reading history from an arbitrary point is not a native operation.
• Ordering and offsets matter. Per-partition ordering and consumer offsets come with streaming platforms. A locking queue intentionally gives up strict order to get parallelism.
• Write load becomes the limit. Every enqueue, claim, and completion is a write to the primary. Each one generates WAL and I/O, and each worker typically holds a connection. At high rates, that load competes with your application traffic, and adding workers makes it worse.

Write down your fan-out, replay, ordering, and peak-throughput requirements before you choose. If none of them apply, Postgres is usually enough.

Alternatives and impact on cost and engineering time

The comparison below is what you are trading against.

Staying in Postgres saves the most engineering time where it matters most. You add no new infrastructure, so backups, failover, access control and monitoring stay on one path. You also avoid the dual-write problem, where a committed row has no matching message or a sent message has no row. Every other option needs an outbox table and a relay to close that gap, which means building half of a Postgres queue anyway.

The cost moves into code you own. That includes lease expiry, retry backoff, dead-lettering and pruning of finished rows. Each needs tests and on-call knowledge. An existing Postgres job library absorbs most of that work, so prefer one unless you have a specific reason not to.

Keep the cost of migrating later bounded. Put enqueue and dequeue behind a narrow interface, and make your handlers idempotent from the start. If your requirements later outgrow the database, the broker becomes a swap behind that interface instead of a rewrite.

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 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.
• Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

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.
• Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
• 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.