PostgreSQL SKIP LOCKED Queue Workers: Test Fairness, Starvation, and Claim Semantics

A PostgreSQL worker pool can prevent two workers from claiming the same row at the same instant and still be operationally wrong. A worker can hold a row lock while doing slow work. An old job can be bypassed repeatedly. A committed claim can survive after its worker disappears. A retry can execute business work twice. An apparently FIFO query can stop behaving like strict FIFO as soon as locked rows are skipped.

This playbook tests those correctness questions without reopening the broader “PostgreSQL versus Redis” architecture debate. It is deliberately a deeper correctness-and-fairness follow-up to Refonte Learning’s existing queue coverage, not another argument for choosing a database as a message broker.

The laboratory boundary is intentionally narrow: one disposable PostgreSQL instance, synthetic rows, fixed worker IDs, deterministic ordering fields, local delay only, and no customer data, production databases, real queues, webhooks, email, payments, or other external side effects. No experiment below has been executed for this article, so every “actual observation” field starts as not observed.

Four evidence labels apply throughout. Documented behavior means PostgreSQL explicitly documents the semantic. Engineering inference means a conclusion derived from those semantics and the proposed design. Proposed experiment means something the acceptance team must run. Actual observation means captured evidence from that run; this article does not fabricate it.

The PostgreSQL baseline is the current PostgreSQL 18 documentation: PostgreSQL 18 SELECT documentation (current documentation, undated page; accessed September 22, 2026), Transaction Isolation (current documentation, undated page; accessed September 22, 2026), and Explicit Locking (current documentation, undated page; accessed September 22, 2026). As accessed, PostgreSQL lists 18 as the current supported documentation branch.

Why SKIP LOCKED correctness is different from simple duplicate prevention

FOR UPDATE SKIP LOCKED solves a narrower problem than many queue implementations implicitly assign to it. PostgreSQL documents that SKIP LOCKED causes rows that cannot be locked immediately to be skipped. PostgreSQL also explicitly warns that this produces an inconsistent view of the data and describes the behavior as useful for queue-like tables with multiple consumers rather than for general-purpose consistent reads.

That means “two concurrent workers did not return the same locked row” is only one correctness property. It does not establish FIFO service, starvation freedom, eventual completion, balanced distribution among workers, exactly-once execution, or safe recovery after a committed claim. Those properties either need additional application state or must be defined as testable operational invariants.

A locking SELECT also interacts with transaction lifetime. FOR UPDATE prevents another transaction from modifying or acquiring conflicting row locks on the selected row until the transaction ends, while ordinary non-locking reads are not blocked by row locks. PostgreSQL advises against holding transactions open for long periods.

The broader operational mindset fits Refonte Learning’s Database Administration in 2026: Performance, Security, and Scaling: queue acceptance is database operations work because transaction duration, indexing, monitoring, recovery, and failure evidence matter alongside SQL syntax.

Engineering inference. SKIP LOCKED should be treated as a contention-handling primitive, not as a fairness contract. The acceptance design therefore separates row-lock correctness, claim-state correctness, job progress, retry behavior, and worker distribution.

Actual observation. None. The table defines questions to prove in the disposable laboratory, not results.

Define worker, job, claim, retry, and fairness invariants

The vocabulary must be frozen before testing. A job is one synthetic row representing one logical unit of work. A worker is a deterministic client identity such as worker-a. A claim is a committed state transition assigning one eligible job to one worker and one claim token. Execution occurs after that transaction commits.

A retry is a later claim attempt for the same logical job after the previous attempt fails, expires, or is explicitly released. An incremented attempt number identifies that new attempt. A duplicate execution means the logical effect is attempted more than once, even when each attempt held a valid claim at a different time.

For this playbook, fairness is operationally defined, not attributed to PostgreSQL. Job-order fairness means that unlocked eligible jobs should follow the test’s total ordering. Starvation freedom means a finite eligible cohort eventually leaves ready after temporary blockers disappear. Worker fairness is weaker: the test records worker distribution but does not demand equal counts.

Engineering inference. The id tie-breaker matters because ORDER BY should be total for deterministic tests. PostgreSQL notes that LIMIT without a sufficiently deterministic ORDER BY can return different subsets; a complete ordering removes an avoidable source of ambiguity from this lab.

The fairness statement should therefore read: given this eligibility predicate, immutable ordering fields, this total ordering, and the absence of a conflicting row lock on the highest-ranked candidate, the test expects that candidate to be selected. It must not read: PostgreSQL guarantees fair queues.

Build a deterministic synthetic queue schema

The schema needs enough state to distinguish locking, ownership, execution attempts, lease recovery, and terminal completion. It does not need production payload complexity. Keep payloads human-readable and job IDs explicit so evidence from concurrent sessions can be compared without reconstructing generated identifiers.

The partial index mirrors the candidate predicate and ordering. It is a hypothesis to inspect, not a promise that PostgreSQL will always choose that index. Planner choices depend on data and statistics, so acceptance evidence records the selected plan rather than asserting one in advance. PostgreSQL’s EXPLAIN documentation describes plans as planner-selected strategies and explicitly notes that selected strategies can vary.

CREATE TABLE queue_jobs (
    id             bigint PRIMARY KEY,
    priority       integer NOT NULL DEFAULT 0,
    payload        text NOT NULL,
    state          text NOT NULL
                   CHECK (state IN ('ready', 'claimed', 'done', 'failed')),
    available_at   timestamptz NOT NULL,
    created_at     timestamptz NOT NULL,

    claimed_by     text,
    claimed_at     timestamptz,
    claim_token    text,
    lease_until    timestamptz,

    attempts       integer NOT NULL DEFAULT 0,
    completed_at   timestamptz,
    last_error     text
);

CREATE INDEX queue_jobs_ready_order_idx
    ON queue_jobs
       (priority DESC, available_at ASC, created_at ASC, id ASC)
    WHERE state = 'ready';

CREATE TABLE job_events (
    event_id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    job_id          bigint NOT NULL REFERENCES queue_jobs(id),
    worker_id       text,
    attempt         integer,
    claim_token     text,
    from_state      text,
    to_state        text NOT NULL,
    event_at        timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    note            text
);

Seed a closed cohort with explicit IDs. Give jobs the same priority where FIFO-like behavior is being tested, vary priority only in tests that intentionally exercise priority ordering, and make one old job the designated contention probe.

INSERT INTO queue_jobs
    (id, priority, payload, state, available_at, created_at)
SELECT
    n,
    0,
    'synthetic-job-' || n,
    'ready',
    TIMESTAMPTZ '2026-09-22 09:00:00+03',
    TIMESTAMPTZ '2026-09-22 08:00:00+03' + (n * INTERVAL '1 second')
FROM generate_series(1, 24) AS n;

The lab is conceptually adjacent to backend database work; Refonte Learning’s Choosing Your Programming Path describes backend development in terms that include databases, APIs, and server-side business logic.

Proposed experiment. Recreate this schema before each scenario or restore the same fixture.

Actual observation. Not executed.

Validate the transaction boundary of a claim

A claim should make a short database decision: identify an eligible row, lock it, change durable ownership metadata, return the exact updated row, and commit. PostgreSQL documents that UPDATE … RETURNING returns values computed from rows actually updated. The companion Returning Data from Modified Rows (current PostgreSQL 18 documentation, undated page; accessed September 22, 2026) explains that RETURNING avoids a separate retrieval query for changed rows.

Claim atomically, work outside the transaction

Use an application-generated claim token unique to the attempt. The exact token format is an application design choice; deterministic test tokens such as worker-a-job-7-attempt-1 are sufficient here.

BEGIN;

WITH candidate AS (
    SELECT id
    FROM queue_jobs
    WHERE state = 'ready'
      AND available_at <= CURRENT_TIMESTAMP
    ORDER BY priority DESC, available_at ASC, created_at ASC, id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE queue_jobs AS j
SET state       = 'claimed',
    claimed_by  = :worker_id,
    claimed_at  = CURRENT_TIMESTAMP,
    claim_token = :claim_token,
    lease_until = CURRENT_TIMESTAMP + :visibility_interval,
    attempts    = j.attempts + 1
FROM candidate AS c
WHERE j.id = c.id
RETURNING
    j.id,
    j.payload,
    j.claimed_by,
    j.claimed_at,
    j.claim_token,
    j.lease_until,
    j.attempts;

COMMIT;

Only after COMMIT should the worker begin its synthetic work. PostgreSQL releases row locks at transaction end; keeping execution outside the claim transaction therefore prevents the job’s application runtime from automatically becoming the row-lock lifetime.

The test should prove the opposite case as well. Pause a session between the UPDATE … RETURNING and COMMIT; another claimant should not obtain that same row. Then commit, verify the persisted claimed state, and show that another claimant ignores the row because state = 'ready' no longer matches.

Documented behavior. Row locks are held until transaction end, and RETURNING describes rows actually updated.

Engineering inference. A worker that performs slow application work before committing the claim unnecessarily extends database lock lifetime.

Actual observation. Not executed.

Compare blocking FOR UPDATE with SKIP LOCKED behavior

The baseline experiment needs two sessions and one deliberately locked old row. Session A begins a transaction and locks job 1 without committing. Session B first runs a locking query without SKIP LOCKED, then repeats the scenario with SKIP LOCKED.

PostgreSQL documents the difference directly: an ordinary locking clause waits when the selected row cannot immediately be locked; NOWAIT raises an error instead, while SKIP LOCKED skips rows that cannot immediately be locked. The table-level lock required by the statement is not itself skipped by SKIP LOCKED; PostgreSQL specifies that the skipping behavior applies to row-level locks.

-- Session A
BEGIN;

SELECT id
FROM queue_jobs
WHERE id = 1
FOR UPDATE;

-- Leave Session A open only for the controlled experiment.

-- Session B: blocking control
SELECT id
FROM queue_jobs
WHERE state = 'ready'
ORDER BY priority DESC, available_at, created_at, id
FOR UPDATE
LIMIT 1;

Reset the fixture, lock job 1 again, then run:

-- Session B: skip-locked variant
SELECT id
FROM queue_jobs
WHERE state = 'ready'
ORDER BY priority DESC, available_at, created_at, id
FOR UPDATE SKIP LOCKED
LIMIT 1;

What SKIP LOCKED does and does not guarantee

The expected semantic is not “the queue now starts at job 2.” It is: the candidate that cannot immediately be row-locked is skipped, allowing another eligible row to be considered. Which row is next is governed by the query’s ordering and eligibility, not by an undocumented fairness scheduler.

Proposed experiment. Record the session start, target job ID, returned job ID, whether Session B waited, and the transaction end that made job 1 lockable again.

Actual observation. Not executed; do not replace the evidence cell with assumed results.

Test ordering under concurrent workers

Start with one worker because concurrency should not be blamed for an ordering bug that is already present in a single-client query. Repeated claims against the closed cohort should follow the total order when no rows are locked and ordering fields remain unchanged.

Then introduce worker-a, worker-b, and worker-c behind a client-side barrier. Give each worker exactly one claim opportunity per round. That synchronization is important: an unrestricted tight loop mostly measures client scheduling and application speed, making it difficult to separate worker scheduling from PostgreSQL claim semantics.

PostgreSQL warns about another ordering edge case: at READ COMMITTED, a locking query with ORDER BY can appear to return rows out of order if it blocks and ordering columns are concurrently changed. The documented workaround of pushing the locking clause into a subquery changes locking breadth and can have performance consequences.

The cleaner queue test avoids that confounder: once a job enters the ready cohort, keep priority, available_at, created_at, and id immutable. State and ownership metadata may change, but ranking data does not.

For every claim, capture the queue snapshot’s expected rank immediately before the synchronized round where practical, plus the returned job ID. Do not interpret different numbers of total jobs per worker as a PostgreSQL ordering violation; a faster client can simply issue more claims when the harness is not synchronized.

Engineering inference. Equal job counts per worker are a poor acceptance criterion for SELECT FOR UPDATE queue fairness because the database does not promise round-robin worker scheduling.

Actual observation. Not executed.

Test starvation of older or repeatedly skipped jobs

Starvation is the most important property that “we never saw a duplicate” fails to address. A row can be correctly excluded from every claim while locked. The relevant operational question is what happens after the condition causing it to be skipped disappears.

Create the strongest deterministic probe: job 1 is the oldest job and highest-ranked candidate under the total order. Hold its row lock in Session A. Let the other workers claim repeated batches with SKIP LOCKED. The expected test evidence should show job 1 remaining ready but inaccessible while its lock exists, while newer rows make progress.

PostgreSQL’s documentation is enough to explain the skipping, but it does not promise starvation freedom. In a separate PostgreSQL UPDATE batching example, the documentation even notes that SKIP LOCKED can help prevent concurrent commands from selecting the same rows while a final pass is still needed to ensure rows are not overlooked. That is strong guidance against equating “skip” with “eventually serviced.” PostgreSQL 18 UPDATE documentation (current documentation, undated page; accessed September 22, 2026).

Detecting an old job that repeatedly loses access

Capture a skip-probe snapshot after every batch:

SELECT
    id,
    state,
    priority,
    available_at,
    created_at,
    attempts,
    claimed_by,
    claimed_at
FROM queue_jobs
WHERE state = 'ready'
ORDER BY priority DESC, available_at, created_at, id;

After several batches, release Session A’s lock. Do not insert a more-preferred job between release and the next synchronized claim. Under the lab’s defined fairness invariant, job 1 should then be the first eligible unlocked candidate.

A second starvation test should keep queue depth high. Maintain a backlog larger than the synchronized claim batch, but mark the original cohort separately from newly inserted jobs. Acceptance should focus on whether each original, continuously eligible job eventually progresses, rather than whether total queue depth reaches zero.

Engineering inference. Queue depth can stay high while an old cohort still makes acceptable progress. Conversely, aggregate completions can look healthy while one old job remains stranded. Therefore, progress evidence needs per-job age and identity, not only aggregate counters.

Actual observation. Not executed; a small synthetic pass would not prove production starvation freedom even if successful.

Test long-running work without holding unnecessary locks

A deliberately slow job is useful only if it tests the right transaction boundary. Claim job 2, commit, then simulate slow work with a local application sleep. Do not hold the claim transaction open while sleeping.

PostgreSQL states that row-level locks are released at transaction end and recommends avoiding long-lived transactions. A row-level FOR UPDATE lock prevents competing writers and lockers on that row while the transaction remains open.

The important distinction is between job ownership and database lock ownership. After the claim commits, the application’s durable state='claimed', claimed_by, token, and lease represent ownership. A row lock does not need to survive for the entire business task if the claim predicate excludes committed claimed jobs.

This separation is also relevant to ordinary backend design, where database transactions should match the atomic database decision rather than the duration of arbitrary application work. Refonte Learning’s API Developer coverage connects API/backend work with database integration and server-side execution boundaries.

Run both A and B only in the disposable database so the difference is visible. While A sleeps, other workers should continue claiming other ready jobs. While B holds its transaction, the locked row becomes another skip target and the open transaction is intentionally demonstrating the anti-pattern.

Engineering inference. A transaction that spans arbitrary application work expands lock lifetime and failure scope without being required by SKIP LOCKED.

Actual observation. Not executed; record transaction IDs or session identifiers, claim timestamps, commit timestamps, and worker work-start/work-end timestamps when the lab is run.

Test abandoned claims and visibility recovery

There are two materially different “worker died” moments.

If a worker disappears before its claim transaction commits, PostgreSQL transaction rollback semantics remove its uncommitted state and transaction-held locks cease when the transaction ends. If the application connection truly terminates, another worker can later compete for the row again. Row locks themselves are not durable lease records.

If a worker disappears after committing state='claimed', the database has exactly what the application asked it to persist: a claimed row. SKIP LOCKED will not magically return that row to ready, because the problem is no longer an uncommitted row lock. Recovery now belongs to the queue’s application-level visibility policy.

Visibility timeout versus transaction lifetime

Treat lease_until as an explicit application invariant. A recovery process can make an expired synthetic claim eligible again:

UPDATE queue_jobs
SET state       = 'ready',
    claimed_by  = NULL,
    claimed_at  = NULL,
    claim_token = NULL,
    lease_until = NULL,
    available_at = CURRENT_TIMESTAMP,
    last_error   = 'lease expired; synthetic recovery'
WHERE state = 'claimed'
  AND lease_until < CURRENT_TIMESTAMP
RETURNING id, attempts;

That recovery statement is not a PostgreSQL SKIP LOCKED feature. It is queue policy layered on ordinary transactional updates.

The proposed abandoned-claim experiment should claim one job with worker-a, commit, and then intentionally stop that worker before completion. Wait only as required by the configured synthetic visibility interval, run recovery, and let worker-b claim the row with a new token.

Then restart the old completion path using worker-a’s token. That completion must fail its ownership predicate.

Engineering inference. A visibility timeout is a durable ownership-recovery rule; a PostgreSQL transaction lifetime is a database concurrency boundary. Conflating them leads either to unnecessarily long transactions or permanently abandoned claimed rows.

Actual observation. Not executed.

Test retries, duplicate execution, and idempotency boundaries

A queue can have perfectly serialized claims and still execute logical work twice. Consider worker-a: it commits a claim, performs the synthetic business operation, and fails before recording completion. The lease later expires. worker-b reclaims the job because the database has no proof that the earlier business operation finished.

Nothing about SKIP LOCKED makes that ambiguity disappear. The database can serialize access to queue rows; it cannot infer whether an operation performed outside the claim transaction happened before a process died.

Duplicate execution as an application invariant

Model that problem inside the lab without external effects. Add a synthetic effects table whose primary key represents one logical effect:

CREATE TABLE synthetic_effects (
    job_id          bigint PRIMARY KEY REFERENCES queue_jobs(id),
    first_worker    text NOT NULL,
    first_attempt   integer NOT NULL,
    recorded_at     timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

On the first execution, insert the effect. Simulate a worker crash before marking the queue job done. After visibility recovery, let a different worker execute the same job and attempt the same insert. The uniqueness rule should expose the duplicate attempt rather than silently manufacturing a second synthetic effect.

Completion must also be fenced:

UPDATE queue_jobs
SET state        = 'done',
    completed_at = CURRENT_TIMESTAMP,
    lease_until  = NULL
WHERE id = :job_id
  AND state = 'claimed'
  AND claimed_by = :worker_id
  AND claim_token = :claim_token
RETURNING id, attempts, completed_at;

Because PostgreSQL UPDATE … RETURNING reports the rows actually updated, an empty result gives the application a clean way to detect that its ownership predicate no longer matches.

Engineering inference. Exactly-once business execution is not established by FOR UPDATE SKIP LOCKED; it must be enforced or made harmless at the application/effect boundary.

Actual observation. Not executed.

Observe queue/index behavior without inventing production benchmarks

Acceptance should inspect query behavior, but a disposable synthetic database cannot justify production capacity claims. Do not publish requests per second, production latency, CPU sizing, worker-count recommendations, or claims about how many rows this design can process based on this lab.

Use EXPLAIN first because it plans without executing the statement. PostgreSQL documents that EXPLAIN shows how tables will be scanned and which plan the planner chose. EXPLAIN ANALYZE, by contrast, actually executes the supplied statement; PostgreSQL warns that data-changing side effects therefore occur unless they are contained and rolled back. PostgreSQL 18 EXPLAIN documentation (current documentation, undated page; accessed September 22, 2026).

EXPLAIN (VERBOSE, COSTS)
SELECT id
FROM queue_jobs
WHERE state = 'ready'
  AND available_at <= CURRENT_TIMESTAMP
ORDER BY priority DESC, available_at, created_at, id
LIMIT 1;

If the team wants actual buffers and row counts, use EXPLAIN (ANALYZE, BUFFERS) only in the disposable fixture and remember that it executes the query. For a mutating claim statement, wrap exploratory execution in BEGIN/ROLLBACK exactly as the PostgreSQL documentation recommends for side-effecting statements under EXPLAIN ANALYZE.

Refonte Learning’s Database Administrator in 2026: Skills, Salary & Projects provides the broader cloud-database and operational context for plan reading, monitoring, backup/recovery, security, and performance work.

Also inspect whether ordering requires an explicit sort and whether the partial index is considered. A sequential scan on a tiny fixture is not automatically a defect: the planner is free to choose the plan it estimates cheapest. PostgreSQL explicitly describes planner cost as an estimate rather than measured execution time.

Actual observation. Not executed; paste the real plans into the evidence package rather than writing expected plan names as results.

Define fairness and progress acceptance thresholds

The acceptance criteria should be strict where correctness is deterministic and deliberately modest where PostgreSQL provides no fairness guarantee.

For duplicate live claims, the threshold can be absolute: zero accepted instances of two current ownership tokens for the same job attempt. For stale completions, zero old-token completions are acceptable. For a finite cohort after blockers and recovery conditions have been cleared, every job must eventually reach the defined terminal disposition.

For ordering, the threshold is conditional. When the highest-ranked candidate is eligible and unlocked, it should win the next controlled claim in the deterministic harness. While it is locked, newer rows may pass it because that is precisely what SKIP LOCKED requests.

Progress evidence across multiple workers

Do not demand a 33/33/33 split among three workers. Instead, use barrier-controlled rounds in which each worker is permitted one claim while at least that many eligible rows exist. The purpose is to prove distinct claims and pool progress, not an undocumented round-robin scheduler.

For the high-depth test, tag the original cohort logically and keep adding synthetic ready rows so total depth does not drain. The cohort passes if its members continue transitioning rather than becoming permanently hidden behind newer work.

The READ COMMITTED default also matters when interpreting snapshots. PostgreSQL states that successive commands in one transaction can see different committed data when concurrent transactions commit between those commands. Updating/locking commands may also re-evaluate rows after waiting on concurrent changes. PostgreSQL 18 Transaction Isolation (current documentation, undated page; accessed September 22, 2026).

That is another reason to record timestamps and state transitions rather than trying to reconstruct concurrency from a single final table dump.

Engineering inference. Passing these tests demonstrates consistency with the playbook’s operational fairness definition under its synthetic conditions. It does not prove universal or production-scale PostgreSQL queue fairness.

Actual observation. Not executed.

Build an evidence package and failure classification

A useful acceptance run should be reproducible by someone who did not run it. Screenshots alone are insufficient because they often omit transaction boundaries, exact SQL, or event chronology. Preserve the schema, seed SQL, worker commands, captured rows, and experiment-specific logs together.

At minimum, each successful claim record needs the job ID, worker ID, claim timestamp, token, attempt, previous state, resulting state, and transaction completion point. Each job completion needs its completion timestamp. Recovery needs the expired lease and the worker that subsequently reclaimed the job.

The final state dump is necessary but not sufficient. Two different histories can produce the same final done row. The event log is what distinguishes “claimed once and completed” from “claimed, expired, reclaimed, stale worker returned, second worker completed.”

Classify failures by violated invariant rather than by vague labels such as “race condition.”

The evidence directory can be as simple as:

evidence/
  schema.sql
  seed.sql
  claim.sql
  complete.sql
  recover.sql
  experiment-blocking.txt
  experiment-ordering.csv
  experiment-starvation.csv
  experiment-abandoned-claim.csv
  experiment-duplicate-effect.csv
  query-plans.txt
  final-jobs.csv
  job-events.csv
  acceptance.md

A per-experiment row should state documented basis, engineering hypothesis, procedure, expected acceptance condition, and actual observation. Until somebody runs the lab, the last field remains N,cO39T EXECUTED.

This style of evidence aligns with database-administration practice because it makes recovery and performance conclusions auditable rather than anecdotal. Refonte Learning’s database content also emphasizes monitoring, operational health, performance, security, and recovery as distinct parts of database work.

Actual observation. No evidence artifacts were generated for this article because executing tests would violate the requested publication boundary.

Final acceptance decision and Refonte Learning CTA

Use this acceptance gate to sign off a SKIP LOCKED worker design only after every required correctness artifact is green.

Build the broader operational skill set through Refonte Learning’s Database Administrator Essentials program.