PostgreSQL FOR UPDATE SKIP LOCKED: Production Queue Pattern

Table of Contents:

• Why SKIP LOCKED Beats SELECT FOR UPDATE
• Core SQL Pattern
• Production Schema Design
• Python Worker Implementation
• Go Worker Implementation
• Indexing and Performance
• Stale Lock Recovery
• Testing and Local Development
• Combining with Outbox and Sagas
• Production Readiness Checklist
• Frequently Asked Questions

Why SKIP LOCKED Beats SELECT FOR UPDATE

Short answer: FOR UPDATE SKIP LOCKED lets multiple PostgreSQL workers claim distinct rows concurrently without blocking each other — the standard pattern for database-backed job queues and work-stealing schedulers.

Naive SELECT ... FOR UPDATE causes workers to queue behind each other: Worker B waits while Worker A holds a lock on the next available job. At scale, lock contention dominates latency. PostgreSQL 9.5+ added SKIP LOCKED, which skips rows already locked and returns the next available row immediately.

We use this pattern across backend API engineering projects where teams want durable queues without operating Redis or Kafka. Combined with proper indexes and stale-lock recovery, FOR UPDATE SKIP LOCKED handles thousands of jobs per minute on modest hardware.

Key Takeaways:

• Use FOR UPDATE SKIP LOCKED inside a transaction to atomically claim jobs
• Partial indexes on status = 'pending' keep claim queries fast
• Always implement visibility timeout for crashed workers
• Pair with outbox pattern for reliable event publishing

For broader context on queue architecture choices, see background job processing patterns.

Core SQL Pattern

The canonical claim query selects one (or N) pending jobs, locks them, and marks them processing — all in one transaction:

BEGIN;

WITH next_job AS (
    SELECT id
    FROM background_jobs
    WHERE queue = $1
      AND status = 'pending'
      AND run_at <= NOW()
    ORDER BY run_at ASC, id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE background_jobs j
SET status = 'processing',
    locked_at = NOW(),
    locked_by = $2,
    attempts = attempts + 1,
    updated_at = NOW()
FROM next_job
WHERE j.id = next_job.id
RETURNING j.*;

COMMIT;

Why this works:

1. FOR UPDATE SKIP LOCKED — concurrent workers never block on each other's locks
2. ORDER BY run_at, id — FIFO with deterministic tie-breaking
3. UPDATE ... RETURNING — single round-trip claim + state transition
4. Transaction boundary — claim is atomic; no double-processing if commit succeeds

Batch claiming for throughput:

-- Claim up to 10 jobs per poll
WITH next_jobs AS (
    SELECT id
    FROM background_jobs
    WHERE queue = $1 AND status = 'pending' AND run_at <= NOW()
    ORDER BY run_at ASC, id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 10
)
UPDATE background_jobs j
SET status = 'processing', locked_at = NOW(), locked_by = $2, attempts = attempts + 1
FROM next_jobs
WHERE j.id = next_jobs.id
RETURNING j.*;

According to PostgreSQL documentation, SKIP LOCKED is designed explicitly for queue-like workloads where missing a locked row is acceptable.

Production Schema Design

A queue table needs more than id and payload. Production schemas track attempts, scheduling, idempotency, and errors:

CREATE TYPE job_status AS ENUM ('pending', 'processing', 'completed', 'failed', 'dead');

CREATE TABLE background_jobs (
    id              BIGSERIAL PRIMARY KEY,
    queue           TEXT NOT NULL DEFAULT 'default',
    job_type        TEXT NOT NULL,
    payload         JSONB NOT NULL,
    idempotency_key TEXT,
    status          job_status NOT NULL DEFAULT 'pending',
    priority        SMALLINT NOT NULL DEFAULT 0,
    attempts        INT NOT NULL DEFAULT 0,
    max_attempts    INT NOT NULL DEFAULT 5,
    run_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    locked_at       TIMESTAMPTZ,
    locked_by       TEXT,
    last_error      TEXT,
    completed_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_queue_idempotency UNIQUE (queue, idempotency_key)
);

-- Partial index: only pending rows matter for claims
CREATE INDEX idx_jobs_pending_claim
    ON background_jobs (queue, priority DESC, run_at ASC, id ASC)
    WHERE status = 'pending';

-- Support stale lock sweeps
CREATE INDEX idx_jobs_stale_processing
    ON background_jobs (locked_at)
    WHERE status = 'processing';

Enqueue with idempotency inside business transactions:

INSERT INTO background_jobs (queue, job_type, payload, idempotency_key, run_at)
VALUES ('billing', 'charge_invoice', '{"invoiceId": 4421}', 'charge:4421', NOW())
ON CONFLICT (queue, idempotency_key) DO NOTHING;

This integrates cleanly with data pipeline integrations where ETL stages enqueue the next step atomically after writing staging rows.

Python Worker Implementation

Using asyncpg for high-concurrency workers:

import asyncio
import json
import os
import socket
from datetime import datetime, timezone

import asyncpg

WORKER_ID = f"{socket.gethostname()}:{os.getpid()}"
CLAIM_SQL = """
WITH next_job AS (
    SELECT id FROM background_jobs
    WHERE queue = $1 AND status = 'pending' AND run_at <= NOW()
    ORDER BY priority DESC, run_at ASC, id ASC
    FOR UPDATE SKIP LOCKED LIMIT 1
)
UPDATE background_jobs j
SET status = 'processing', locked_at = NOW(), locked_by = $2,
    attempts = attempts + 1, updated_at = NOW()
FROM next_job WHERE j.id = next_job.id
RETURNING j.*;
"""

async def complete_job(conn, job_id: int) -> None:
    await conn.execute(
        """
        UPDATE background_jobs
        SET status = 'completed', completed_at = NOW(), updated_at = NOW()
        WHERE id = $1
        """,
        job_id,
    )

async def fail_job(conn, job_id: int, error: str, max_attempts: int) -> None:
    row = await conn.fetchrow(
        "SELECT attempts, max_attempts FROM background_jobs WHERE id = $1", job_id
    )
    if row["attempts"] >= row["max_attempts"]:
        status = "dead"
    else:
        status = "pending"  # requeue with backoff via run_at
    run_at = datetime.now(timezone.utc) if status == "pending" else None
    await conn.execute(
        """
        UPDATE background_jobs
        SET status = $2, last_error = $3, locked_at = NULL, locked_by = NULL,
            run_at = COALESCE($4, run_at), updated_at = NOW()
        WHERE id = $1
        """,
        job_id, status, error[:2000], run_at,
    )

HANDLERS = {
    "send_receipt": send_receipt_handler,
    "sync_inventory": sync_inventory_handler,
}

async def worker_loop(pool: asyncpg.Pool, queue: str) -> None:
    while True:
        async with pool.acquire() as conn:
            async with conn.transaction():
                job = await conn.fetchrow(CLAIM_SQL, queue, WORKER_ID)
            if not job:
                await asyncio.sleep(0.5)
                continue
            try:
                handler = HANDLERS[job["job_type"]]
                await handler(json.loads(job["payload"]))
                await complete_job(conn, job["id"])
            except Exception as exc:
                await fail_job(conn, job["id"], str(exc), job["max_attempts"])

Deploy workers alongside FastAPI services built with our backend API engineering patterns — same Postgres pool, shared migrations, unified observability.

Go Worker Implementation

Go workers suit CPU-bound job types and static binaries in Kubernetes:

package main

import (
    "context"
    "encoding/json"
    "log"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

const claimSQL = `
WITH next_job AS (
    SELECT id FROM background_jobs
    WHERE queue = $1 AND status = 'pending' AND run_at <= NOW()
    ORDER BY priority DESC, run_at ASC, id ASC
    FOR UPDATE SKIP LOCKED LIMIT 1
)
UPDATE background_jobs j
SET status = 'processing', locked_at = NOW(), locked_by = $2,
    attempts = attempts + 1, updated_at = NOW()
FROM next_job WHERE j.id = next_job.id
RETURNING j.id, j.job_type, j.payload, j.max_attempts, j.attempts;
`

type Job struct {
    ID         int64
    JobType    string
    Payload    json.RawMessage
    MaxAttempts int
    Attempts   int
}

func runWorker(ctx context.Context, pool *pgxpool.Pool, queue, workerID string, handlers map[string]JobHandler) {
    for {
        select {
        case <-ctx.Done():
            return
        default:
        }
        tx, err := pool.Begin(ctx)
        if err != nil {
            time.Sleep(time.Second)
            continue
        }
        var j Job
        err = tx.QueryRow(ctx, claimSQL, queue, workerID).Scan(
            &j.ID, &j.JobType, &j.Payload, &j.MaxAttempts, &j.Attempts,
        )
        if err != nil {
            tx.Rollback(ctx)
            time.Sleep(500 * time.Millisecond)
            continue
        }
        if err := tx.Commit(ctx); err != nil {
            continue
        }
        h, ok := handlers[j.JobType]
        if !ok {
            markFailed(ctx, pool, j.ID, "unknown job type", j.Attempts, j.MaxAttempts)
            continue
        }
        if err := h(ctx, j.Payload); err != nil {
            markFailed(ctx, pool, j.ID, err.Error(), j.Attempts, j.MaxAttempts)
            continue
        }
        markCompleted(ctx, pool, j.ID)
    }
}

func main() {
    workerID := os.Getenv("HOSTNAME") + ":" + os.Getenv("POD_NAME")
    // pool setup omitted
    runWorker(context.Background(), pool, "default", workerID, defaultHandlers)
}

Graceful shutdown: on SIGTERM, stop claiming new jobs, finish in-flight work, or release locks so jobs return to pending.

Indexing and Performance

FOR UPDATE SKIP LOCKED performance depends entirely on index selectivity. Without a partial index, Postgres scans all historical completed rows.

Benchmark Snapshot (Internal, 2026)

We load-tested a queue table with 2M rows (1.8M completed, 200K pending) on db.r6g.large:

Rules:

• Partial index WHERE status = 'pending' is non-negotiable
• Archive or partition completed jobs older than 30 days
• Avoid SELECT * in hot paths — return only needed columns
• Use connection pooling (PgBouncer transaction mode works with short claim transactions)

Vacuum and autovacuum matter: heavy UPDATE churn bloats the table. Schedule VACUUM (ANALYZE) on queue tables during low traffic.

For high-volume fan-out, consider hybrid architecture: Postgres queue for transactional enqueue, relay to Kafka for event-driven consumers.

Stale Lock Recovery

Workers die mid-job — deploy crash, OOM kill, spot instance termination. Without recovery, jobs stay processing forever.

-- Run every minute via cron or background sweeper
UPDATE background_jobs
SET status = 'pending',
    locked_at = NULL,
    locked_by = NULL,
    run_at = NOW() + (POWER(2, attempts) * INTERVAL '1 second'),
    updated_at = NOW()
WHERE status = 'processing'
  AND locked_at < NOW() - INTERVAL '5 minutes'
  AND attempts < max_attempts;

-- Move exhausted jobs to dead status
UPDATE background_jobs
SET status = 'dead', updated_at = NOW()
WHERE status = 'processing'
  AND locked_at < NOW() - INTERVAL '5 minutes'
  AND attempts >= max_attempts;

Tune visibility timeout to p99 job duration + buffer. A 5-minute default works for sub-minute jobs; long-running exports need 30–60 minutes or heartbeat extensions:

async def extend_lock(conn, job_id: int, worker_id: str) -> bool:
    result = await conn.execute(
        """
        UPDATE background_jobs
        SET locked_at = NOW(), updated_at = NOW()
        WHERE id = $1 AND locked_by = $2 AND status = 'processing'
        """,
        job_id, worker_id,
    )
    return result == "UPDATE 1"

Long-running jobs should call extend_lock every 60 seconds from a background task.

Monitor stale recovery with a gauge metric — spikes indicate worker instability or timeouts set too aggressively. Pair with database connection pooling so recovery sweeps do not exhaust connections during incident response.

Testing and Local Development

Queue logic fails silently without concurrency tests. Minimum test suite:

import asyncio
import pytest

@pytest.mark.asyncio
async def test_concurrent_claims_never_overlap(db_pool, seeded_jobs):
    """Ten workers claiming 10 jobs should each get exactly one unique job."""
    claimed = []

    async def worker():
        async with db_pool.acquire() as conn:
            async with conn.transaction():
                row = await conn.fetchrow(CLAIM_SQL, "test", "worker-test")
                if row:
                    claimed.append(row["id"])

    await asyncio.gather(*[worker() for _ in range(10)])
    assert len(claimed) == len(set(claimed)) == 10

@pytest.mark.asyncio
async def test_idempotent_enqueue(db_pool):
    async with db_pool.acquire() as conn:
        await enqueue_job(conn, "billing", "charge", {"id": 1}, "charge:1")
        await enqueue_job(conn, "billing", "charge", {"id": 1}, "charge:1")
        count = await conn.fetchval(
            "SELECT COUNT(*) FROM background_jobs WHERE idempotency_key = 'charge:1'"
        )
    assert count == 1

Use testcontainers or Docker Compose for integration tests against real Postgres — SQLite does not implement SKIP LOCKED semantics.

Load-test claims with pgbench custom scripts or Locust driving enqueue endpoints before Black Friday traffic. Baseline claim p95 on staging with production-like row counts — not empty tables.

Combining with Outbox and Sagas

FOR UPDATE SKIP LOCKED solves job claiming. It does not solve cross-service messaging. Two patterns complete the picture:

Outbox Pattern

Write domain data and outbox rows in one transaction. A relay worker (using the same SKIP LOCKED claim) publishes to SNS, Kafka, or webhooks:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
INSERT INTO outbox (event_type, payload) VALUES ('FundsDebited', '{"accountId": 1, "amount": 100}');
COMMIT;

See outbox pattern for guaranteed message delivery.

Saga Orchestration

Multi-step distributed workflows enqueue saga steps as jobs. Each step claims via SKIP LOCKED, executes, and enqueues the next step or a compensating action:

INSERT INTO background_jobs (queue, job_type, payload, idempotency_key)
VALUES ('saga', 'compensate_payment', '{"sagaId": "s-99", "step": "refund"}', 'saga:s-99:compensate:refund');

See saga pattern for distributed transactions.

CQRS projections often rebuild via SKIP LOCKED workers polling an event table — same mechanics, different payload semantics. Read CQRS in practice.

Event-driven systems described in our event-driven architecture guide typically use SKIP LOCKED for both outbox relay and consumer job queues in the same cluster.

Production Readiness Checklist

When claim latency exceeds 50ms at steady state, investigate bloat and index health before adding hardware. API layers enqueueing jobs should use the same pool configuration as workers to avoid connection starvation during traffic spikes.

Primary references: official documentation, official documentation, official documentation.

Operating PostgreSQL FOR UPDATE SKIP LOCKED as a System

The implementation is only one part of PostgreSQL FOR UPDATE SKIP LOCKED. A production design also needs an explicit contract for inputs, outputs, ownership, and failure behavior. Write that contract before selecting a library. It should identify which component validates input, where state lives, what may be retried, and which result is authoritative when two components disagree. This prevents a convenient prototype boundary from silently becoming the long-term architecture.

Start with a representative baseline. Capture request shape, traffic distribution, dependency latency, error classes, and the quality signal users actually care about. Averages hide the cases that cause incidents, so keep percentiles and segment measurements by workload type. Record the configuration and dataset version beside every result. Without that context, a faster or more accurate run cannot be reproduced and should not be used to approve a rollout.

Define the failure model

List failures by where they originate: invalid input, capacity exhaustion, dependency timeout, partial state change, malformed output, and semantically wrong output. Each class needs a different response. Validation errors should fail immediately. Transient dependency failures may be retried with a budget and jitter. An operation that may have committed must use an idempotency key or reconciliation step before retrying. A syntactically valid but incorrect result belongs in evaluation and review, not a blind retry loop.

Set a deadline for the complete operation and derive smaller budgets for each dependency. Local timeouts that add up to more than the caller's deadline merely create abandoned work. Propagate cancellation where the protocol supports it. Bound every queue, retry loop, context buffer, and concurrency pool; an unbounded safety mechanism becomes a second outage during overload.

Design a degraded mode before it is needed. Depending on the workload, that can mean returning a cached answer, selecting a simpler path, placing work in a durable queue, or asking for human review. The degraded response must be visible in telemetry and, where it changes meaning, visible to the caller. Silent fallback makes quality regressions almost impossible to diagnose.

Measure the decision, not just the component

Use three layers of signals. System metrics cover latency, throughput, saturation, and errors. Correctness metrics measure whether the result satisfies its contract. Business or user metrics show whether the system solved the intended problem. Improving only one layer can move the others backward, so release criteria should name acceptable movement for all three.

Attach a reason code to every route, rejection, fallback, and retry. Include version identifiers for configuration, code, model, schema, and data when relevant. Logs should let an engineer reconstruct a decision without storing secrets or raw personal data. Traces should cross process boundaries, while metrics should remain low-cardinality enough to operate reliably.

Alert on symptoms that require action, not every internal anomaly. A useful alert names the affected service objective, links to a runbook, and distinguishes a customer-visible incident from exhausted headroom. Dashboards serve a different purpose: they support diagnosis and capacity planning. Treating a dashboard as an alerting strategy leaves failures undiscovered until someone happens to look.

Roll out with reversible steps

Ship PostgreSQL FOR UPDATE SKIP LOCKED behind a versioned interface and a kill switch. Begin with offline replay using production-shaped, privacy-safe samples. Then use shadow execution when duplicate work has acceptable cost and side effects can be suppressed. A small canary should exercise the real dependency graph before traffic expands. Compare the canary with the baseline by cohort rather than mixing both populations into one aggregate.

Promotion gates should be written before the rollout. Include a minimum sample size or observation window, maximum regression in tail latency and error rate, and a correctness threshold. Roll back automatically when a hard safety boundary is crossed; use manual review for ambiguous quality movement. Preserve enough evidence from both paths to explain why the gate passed or failed.

Configuration deserves the same discipline as code. Review changes, validate them before activation, keep an immutable history, and make rollback a single operation. If a deployment changes code and configuration together, record both versions. Otherwise an incident responder may roll back the binary while leaving the triggering configuration active.

Capacity and cost controls

Model capacity in units the bottleneck understands: concurrent connections, tokens, queue jobs, database transactions, GPU memory, or bytes in flight. Convert the expected traffic distribution into those units and include burst behavior. Then load-test the first constrained dependency, not merely the public endpoint. A system that accepts more work than it can finish within its deadline is overloaded even if CPU utilization looks comfortable.

Cost is also a reliability limit. Add per-request attribution, tenant or workflow budgets, and a global circuit breaker for unexpectedly expensive paths. Review unit economics at the same granularity as performance; a cheap median can conceal a small class of requests responsible for most spend. Optimize only after measuring, because reducing context, replicas, validation, or redundancy can trade visible cost for less visible risk.

Production readiness review

Before launch, ask an engineer who did not build the feature to follow the runbook through one simulated failure. Verify backups or checkpoints by restoring them, not by checking that a job reported success. Exercise credential rotation, dependency unavailability, bad configuration, and rollback. Assign an owner for each alarm and a date for reviewing thresholds after real traffic arrives.

The final architecture document should be short enough to remain current. Keep the decision, rejected alternatives, invariants, dependency contracts, dashboards, and rollback procedure. Link detailed experiments rather than pasting them into the document. Teams that need help turning this review into an operable service can use our PostgreSQL FOR UPDATE SKIP LOCKED engineering support.

Frequently Asked Questions

What does FOR UPDATE SKIP LOCKED do in PostgreSQL?

It locks selected rows for update but skips rows already locked by other transactions instead of waiting. Multiple workers can claim different rows concurrently — ideal for job queues.

Is FOR UPDATE SKIP LOCKED safe for job processing?

Yes, when claim and status update happen in one transaction. Each job row is locked to exactly one worker at a time. Combine with idempotency keys for at-least-once delivery safety.

How is SKIP LOCKED different from NOWAIT?

NOWAIT fails immediately if any selected row is locked. SKIP LOCKED skips locked rows and returns the next available ones. For queues, SKIP LOCKED is almost always correct.

Can I use SKIP LOCKED without PostgreSQL?

MySQL 8+ supports FOR UPDATE SKIP LOCKED. SQL Server uses different patterns (READPAST hint). PostgreSQL remains the most common choice for ACID queue patterns.

What throughput can a Postgres queue handle?

With partial indexes and modest hardware, hundreds to low thousands of claims per second per queue is achievable. Beyond that, partition queues by domain or add a broker tier.

Do I need Redis if I have SKIP LOCKED?

Not necessarily. Postgres queues cover transactional enqueue, moderate throughput, and operational simplicity. Add Redis when you need sub-millisecond poll latency or very high fan-out.

How do I prioritize jobs?

Add a priority column and ORDER BY priority DESC, run_at ASC in the claim query. Cap high-priority starvation with aging — boost run_at or priority for jobs waiting too long.

Does SKIP LOCKED work with pgBouncer?

Yes in transaction pooling mode — keep claim + mark-processing in a single transaction. Session pooling also works. Avoid holding locks across long job execution (claim in one transaction, process outside).

Conclusion

PostgreSQL FOR UPDATE SKIP LOCKED is the production-standard pattern for database-backed queues:

• Atomic, concurrent job claiming without worker blocking
• Partial indexes and archival keep claims fast at scale
• Stale lock recovery and heartbeats handle worker crashes
• Composes with outbox, saga, and CQRS patterns in the same Postgres cluster

Skip the bespoke locking library. Use the database's native semantics, instrument queue depth and claim latency, and treat idempotency as a requirement — not an optimization.

At HinterBuild:

• Backend API Engineering
• Data Pipelines & Integrations
• Cloud Infrastructure & DevOps

Schedule a consultation for PostgreSQL queue architecture review.