SKIP LOCKED vs FOR UPDATE vs Advisory Locks in Postgres Queues

A PostgreSQL job queue lives or dies at one statement: the claim. Every worker runs the same query against the same table at the same time, trying to take a row no other worker has taken. Job execution can scale horizontally; claiming cannot, because the claim is the single shared contention point.

There are three common claim patterns: plain FOR UPDATE, FOR UPDATE SKIP LOCKED, and advisory locks. Most articles assert which one is faster. This one takes a different approach: it explains the locking mechanism behind each pattern, then provides a complete, runnable pgbench harness so the comparison can be measured on your PostgreSQL version, your hardware, and your worker counts — because the shape of the results, not someone else's absolute numbers, is what should drive the decision.

For the full production queue design around the claim — schema, retries, stuck-job recovery, monitoring — see PostgreSQL Job Queues with SKIP LOCKED. This article focuses on the claim itself and how to measure it.

Why the Claim Statement Decides Queue Throughput

A queue table looks like ordinary storage, but under concurrency it behaves like a shared claim surface. If ten workers each process jobs in 500 milliseconds, the system can in principle complete twenty jobs per second. Whether it actually does depends on how fast workers can take ownership of distinct rows without stepping on each other.

The claim has three jobs to do at once:

1. find the next runnable row in order
2. take ownership so no other worker processes it
3. avoid blocking other workers who want a different row

Every claim strategy is a different trade-off among those three. The differences are invisible with one worker and dominant with fifty.

Strategy 1: Plain FOR UPDATE

The claim that most queues start with:

WITH picked AS (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE
)
UPDATE jobs j SET status = 'running', locked_at = now()
FROM picked WHERE j.id = picked.id
RETURNING j.id;

This is correct: two workers can never claim the same row, because FOR UPDATE locks the selected row until the transaction ends.

The problem is which row every worker selects. The query orders by run_at, id and takes the first runnable row — so every concurrent worker tries to lock the same row. One wins. The rest do not move on to the second row; they queue up behind the row lock and wait.

When the winner commits, PostgreSQL re-evaluates the row for the next waiter: the row no longer matches status = 'queued', so the waiter re-fetches the next candidate, locks it, and the remaining workers pile up behind that one. The mechanism (called EvalPlanQual re-checking in PostgreSQL internals) keeps the result correct, but the effect is that claims execute roughly one at a time.

The symptom in practice: adding workers does not increase queue throughput. Claim latency grows with worker count while claims per second stay nearly flat, because the workers are not claiming in parallel — they are taking turns.

Strategy 2: FOR UPDATE SKIP LOCKED

One clause changes the contention model:

WITH picked AS (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j SET status = 'running', locked_at = now()
FROM picked WHERE j.id = picked.id
RETURNING j.id;

With SKIP LOCKED, a worker that encounters a locked row does not wait — it skips to the next candidate. Worker A locks row 1, worker B skips row 1 and locks row 2, worker C skips both and locks row 3. Claims proceed in parallel instead of in line.

The PostgreSQL SELECT documentation is explicit about the trade-off: skipping locked rows returns an inconsistent view of the data, which makes it unsuitable for general queries — and then names the exact use case where that is fine: avoiding lock contention among multiple consumers of a queue-like table.

Two consequences follow from the mechanism:

• Ordering becomes approximate. A skipped row is processed slightly later than strict run_at order would demand. For almost all job queues this is acceptable; if the system needs strict global ordering, a queue with parallel consumers is the wrong shape regardless of claim strategy.
• Throughput scales until a real resource limit. Once claims stop blocking each other, the ceiling moves to CPU, index maintenance, and write throughput — which is where you want the ceiling to be.

Strategy 3: Advisory Locks

Before SKIP LOCKED existed (it arrived in PostgreSQL 9.5), high-throughput queues used advisory locks — application-defined locks keyed by a 64-bit integer that PostgreSQL tracks but never interprets:

CREATE OR REPLACE FUNCTION claim_advisory() RETURNS bigint AS $$
DECLARE
  candidate bigint;
BEGIN
  FOR candidate IN
    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY run_at, id
    LIMIT 32
  LOOP
    IF pg_try_advisory_xact_lock(candidate) THEN
      UPDATE jobs SET status = 'running', locked_at = now()
      WHERE id = candidate AND status = 'queued';
      IF FOUND THEN
        RETURN candidate;
      END IF;
    END IF;
  END LOOP;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

The pattern: read a batch of candidates without locking any rows, then try-lock each candidate's id with pg_try_advisory_xact_lock, which never blocks — it returns false immediately if another worker holds the lock. The first successful try-lock is followed by a re-check of status, because between the candidate read and the lock acquisition another worker may have already processed the row through a different code path.

Details that matter when running or evaluating this pattern:

• The candidate batch size is a tuning knob. With LIMIT 32, a worker gives up after 32 contended candidates and returns NULL — an empty-handed claim the worker must retry. Under high contention, small batches produce misses; large batches produce longer candidate scans.
• Advisory locks live in a fixed-size shared memory table, not in row headers. Thousands of concurrent locks are fine; the practical costs are the extra query logic and the two-step claim.
• The distinctive capability is session-level locking. pg_advisory_lock (the session variant) can hold ownership across transaction boundaries — something row locks cannot do. Queue systems that keep a job locked for the whole multi-transaction execution (rather than marking status) are the historical reason this pattern persists.

For claim-and-mark queues on modern PostgreSQL, advisory locks add complexity that SKIP LOCKED made unnecessary. The benchmark below lets you verify whether that complexity buys anything on your workload.

A Runnable Claim Benchmark Harness

Everything needed to compare the three strategies is below — no framework, just psql and pgbench (both ship with PostgreSQL).

Schema and claim functions (setup.sql):

CREATE TABLE jobs (
  id bigserial PRIMARY KEY,
  status text NOT NULL DEFAULT 'queued',
  run_at timestamptz NOT NULL DEFAULT now(),
  locked_at timestamptz,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX jobs_runnable_idx ON jobs (run_at, id) WHERE status = 'queued';

CREATE OR REPLACE FUNCTION claim_skip_locked() RETURNS bigint AS $$
  WITH picked AS (
    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY run_at, id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
  )
  UPDATE jobs j SET status = 'done', locked_at = now()
  FROM picked WHERE j.id = picked.id
  RETURNING j.id;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION claim_for_update() RETURNS bigint AS $$
  WITH picked AS (
    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY run_at, id
    LIMIT 1
    FOR UPDATE
  )
  UPDATE jobs j SET status = 'done', locked_at = now()
  FROM picked WHERE j.id = picked.id
  RETURNING j.id;
$$ LANGUAGE sql;

-- claim_advisory() as defined in the previous section

The benchmark functions mark jobs done directly instead of running, so a run measures pure claim throughput without simulating execution.

Reseed between runs (reseed.sql) — every run must start from an identical queue state, or earlier runs pollute later ones with dead tuples and shrunken candidate sets:

TRUNCATE jobs RESTART IDENTITY;
INSERT INTO jobs (run_at)
SELECT now() - interval '1 hour' + (i * interval '1 millisecond')
FROM generate_series(1, 800000) i;
VACUUM ANALYZE jobs;

One-line workload files for pgbench:

echo "SELECT claim_skip_locked();" > skip_locked.sql
echo "SELECT claim_for_update();"  > for_update.sql
echo "SELECT claim_advisory();"    > advisory.sql

The measurement matrix — each strategy at increasing worker counts, reseeding before every run:

for STRATEGY in skip_locked for_update advisory; do
  for C in 1 4 16 64; do
    psql -q -f reseed.sql
    pgbench -n -c $C -j 6 -T 20 -f $STRATEGY.sql -l --log-prefix=lat
    psql -tAc "SELECT count(*) FROM jobs WHERE status = 'done'"
  done
done

The flags that matter: -n skips pgbench's own vacuum (the reseed already ran one), -c is concurrent clients, -T 20 runs each combination for 20 seconds, and -l writes per-transaction latency logs. The pgbench documentation describes the log format; the third column is latency in microseconds, so p95 is a sort-and-index away in any scripting language.

The final count(*) is the honesty check: for the two row-locking strategies it should equal pgbench's processed-transaction count. For the advisory strategy it can be lower — the difference is empty-handed claims that returned NULL, and that gap is itself a result worth recording.

How To Read the Results

The absolute numbers will differ by hardware, PostgreSQL version, and configuration. The shapes are what diagnose the behavior:

Two rules for a fair comparison:

• Reseed before every run. Comparing a fresh queue against one full of dead tuples measures vacuum debt, not claim strategy.
• Run each combination more than once and compare medians. Single 20-second runs on a busy machine can swing noticeably.

What This Harness Does Not Measure

A claim benchmark isolates one component, and it is worth being explicit about what is left out:

• Job execution time. In production, workers spend most of their time doing the job, not claiming it. If jobs take 500 ms, a claim-path difference of 2 ms versus 8 ms is irrelevant; if jobs take 5 ms, the claim path can dominate. Measure your job duration before optimizing the claim.
• Producer traffic. The harness drains a pre-filled queue. Concurrent inserts add index contention that slightly reduces claim throughput for every strategy.
• Crash recovery semantics. SKIP LOCKED row locks vanish on disconnect (jobs revert to claimable); a status = 'running' mark needs a stuck-job sweep. These operational paths, covered in Background Jobs in Production, matter more than raw claim speed in most incidents.
• Strict ordering requirements. If the system needs exact FIFO, all three parallel-claim strategies are compromises; that is a design decision upstream of any benchmark, related to the trade-offs in Optimistic vs Pessimistic Locking in SQL.

Choosing a Claim Strategy

The honest summary: on modern PostgreSQL, SKIP LOCKED is the default for a reason — it solves claim contention in the database engine itself, with one clause, at the cost of approximate ordering that queues almost always tolerate. Plain FOR UPDATE is a correctness success and a scalability trap. Advisory locks remain a specialist tool whose main queue-era advantage disappeared in 9.5, but whose session-scoped semantics still solve problems row locks cannot.

The harness above turns that summary from something read into something verified. Run it before a queue migration, after a PostgreSQL major upgrade, or whenever queue throughput becomes an incident topic — the shapes in the results table tell you which mechanism you are actually paying for.

For the wider system context around queues and the databases that back them, see the SQL And Data Correctness and Backend Reliability hubs.