Building a Queue With PostgreSQL SKIP LOCKED

For background jobs stored in PostgreSQL, SELECT ... FOR UPDATE SKIP LOCKED lets multiple workers claim different pending rows without waiting for rows another worker has locked. A worker claims a job by reserving it in the database, typically by changing its status from pending to running. PostgreSQL documents this locking option for queue-like tables, while warning that skipped rows make the query’s view incomplete. PostgreSQL’s SELECT documentation

The practical pattern is to claim a small batch in a short transaction, commit, and then do the work. That keeps database locks out of potentially long-running job execution. You still need an application-level plan for jobs left running after a worker crashes.

Create a jobs table

A simple queue needs a job identifier, a payload for the worker, and a status that records where the job is in its lifecycle:

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')),
    created_at timestamptz NOT NULL DEFAULT now(),
    claimed_at timestamptz,
    attempts   integer NOT NULL DEFAULT 0
);

CREATE INDEX jobs_pending_created_at_idx
    ON jobs (created_at, id)
    WHERE status = 'pending';

The index includes only pending rows, so it can help PostgreSQL find eligible jobs without indexing every status. PostgreSQL calls this a partial index: an index built for rows that match a condition. Check the query plan and performance with your data to confirm that the index helps. PostgreSQL’s CREATE INDEX documentation

Claim jobs in one statement

A worker can select pending jobs, lock available rows, mark them as running, and return their payloads in a single statement:

WITH picked AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at, id
    LIMIT 10
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs AS j
SET status = 'running',
    claimed_at = now(),
    attempts = attempts + 1
FROM picked
WHERE j.id = picked.id
RETURNING j.id, j.payload, j.attempts;

FOR UPDATE locks the selected rows so another worker cannot claim those same rows at the same time. SKIP LOCKED tells PostgreSQL to move past rows another transaction has locked instead of waiting for them. ORDER BY sets the priority among available rows, and LIMIT caps the batch. Without ORDER BY, PostgreSQL does not promise a particular result order. PostgreSQL’s locking-clause documentation

Run this statement as a short transaction. With autocommit, the statement commits when it finishes; if the worker has opened an explicit transaction, it should commit as soon as the claim statement completes. The worker can then process the returned jobs and mark each successful job done in a separate transaction:

UPDATE jobs
SET status = 'done'
WHERE id = $1
  AND status = 'running';

The status condition prevents this update from changing a row that is no longer marked running. If your recovery policy allows another worker to reclaim a job, add a per-claim ownership token and require it in the completion update. That prevents an older worker from recording success after a newer worker has taken ownership.

Recover jobs after a worker crashes

A crash before the claim transaction commits rolls back the status change. The job remains pending, and another worker can claim it. PostgreSQL transactions apply their changes together, and row locks last only until the transaction ends. PostgreSQL’s transaction tutorial and explicit-locking documentation

A crash after the worker commits its claim has a different result: the row remains running. PostgreSQL does not know whether the job finished outside the database, so SKIP LOCKED does not automatically return it to the queue. Use an application-level recovery policy, such as treating claimed_at as the start of a lease and requeuing jobs whose leases have expired. Choose the lease duration and retry limit based on the work your workers perform; PostgreSQL does not prescribe them.

A lease can also cause duplicate work. If a slow worker continues after its lease expires, a second worker might reclaim and process the same job. Make job handlers idempotent, meaning they produce a safe result when the same job runs more than once. For work where an outdated worker must not save results, use an ownership token or another fencing check. A database row lock alone cannot make a transaction atomic with an external API call.

Keep claim transactions short

A row lock remains held until its transaction ends. If a worker claims jobs and then leaves the transaction open while calling an API or processing a file, other workers skip those locked rows for the entire time. A disconnected client can also leave locks in place until PostgreSQL ends the transaction. PostgreSQL warns that long-open transactions hold locks and can interfere with cleanup.

Have the worker claim and commit first, do the work outside the transaction, and then save the outcome in a new short transaction. PostgreSQL 18 also provides idle_in_transaction_session_timeout to end sessions that remain idle inside a transaction, and transaction_timeout to limit transaction duration. Set timeouts with the expected work and transaction lengths in mind; a timeout can interrupt legitimate database work, and it does not replace recovery for jobs already committed as running. PostgreSQL 18 client connection settings

Know what SKIP LOCKED guarantees

SKIP LOCKED avoids waiting for conflicting row locks, but it does not prevent every database wait. PostgreSQL still takes a table-level lock, which can conflict with other table operations. It also does not enforce strict first-in, first-out processing: a worker can skip an older locked job and claim a newer available one.

Use this pattern when workers should claim available jobs concurrently and a temporarily skipped job can wait for another polling pass. Keep retry and duplicate-execution behavior explicit, because row locking does not provide an end-to-end guarantee that each job runs exactly once.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com