TL;DR: SKIP LOCKED is a PostgreSQL SELECT ... FOR UPDATE option that makes it easy to build efficient worker queues: rows locked by other transactions are skipped rather than blocked. That lets multiple workers concurrently fetch work without stepping on each other’s toes, avoiding hot-row contention and simplifying retry logic.

The problem: multiple workers picking up tasks

Imagine a table jobs that stores work to be processed. If many workers attempt to claim the same ready job at once, you get contention:

• naive SELECT then UPDATE approaches can lead to races where multiple workers try to process the same job (lost updates or duplicated work)
• using SELECT ... FOR UPDATE without SKIP LOCKED causes workers to block on locked rows and reduces throughput.

We want a simple, safe pattern where multiple workers can concurrently claim available jobs without blocking each other and without duplicating work.

SKIP LOCKED: what it does

SKIP LOCKED is used with SELECT ... FOR UPDATE (or FOR NO KEY UPDATE) and tells PostgreSQL to skip rows that are already locked by other transactions instead of waiting for the lock to be released.

Behavioral points:

• Rows that are locked by other transactions are omitted from the result set.
• Because you do FOR UPDATE, rows returned are locked for your transaction and safe to update/consume.
• It is a non-blocking way to claim rows — perfect for worker queues.

Important caveat: SKIP LOCKED provides no ordering guarantees relative to skipped rows — you’re accepting “best-effort” fairness. That’s fine for many queue scenarios but not for strict ordering requirements.

Task queue pattern using SKIP LOCKED (SQL)

Schema (simple example):

CREATE TABLE jobs (
    id BIGSERIAL PRIMARY KEY,
    payload jsonb NOT NULL,
    state text NOT NULL DEFAULT 'queued',
    locked_at timestamptz NULL,
    attempts int NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now()

);
CREATE INDEX ON jobs (state, created_at);

Worker claim one job

BEGIN;
UPDATE jobs
SET state = 'processing', locked_at = now(), attempts = attempts + 1
WHERE id = (
  SELECT id FROM jobs
  WHERE state = 'queued'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;
COMMIT;

Explanation:

• The subquery selects one queued row and acquires a lock on it (FOR UPDATE SKIP LOCKED).
• The UPDATE ... WHERE id = (subquery) then atomically marks that locked row as processing and returns it.
• Other workers running the same statement will skip locked rows and claim different ones, avoiding contention.

Handling failures and retries

• If a worker crashes while processing, you need a re-queue mechanism. Options:
• Use a locked_at timestamp and a background reaper that moves stale processing jobs back to queued after a timeout.

• Use attempts to backoff or dead-letter after N retries.

• Example pseudo-SQL:

UPDATE jobs
SET state = 'queued',
    locked_at = NULL
WHERE state = 'processing'
  AND locked_at < NOW() - INTERVAL '5 minutes';

When SKIP LOCKED is the right tool

• Best for work queues where ordering is not strictly required and high throughput with many workers is desired.
• Not suitable if strict ordering and fairness are required — SKIP LOCKED sacrifices strict ordering for throughput. If you need strict ordering, look at single-consumer partitions or other coordination mechanisms.

Summary

SKIP LOCKED gives you a simple, efficient, and robust primitive to implement worker queues in PostgreSQL. It avoids blocking contention and lets multiple workers claim tasks concurrently. Combined with a small reaper and careful retry logic, it becomes a production-ready foundation for many background processing needs.