Why Postgres SKIP LOCKED Beats Dedicated Message Queues

The default instinct when building asynchronous task processing is to deploy a dedicated message broker like Redis, RabbitMQ, or Amazon SQS. For the vast majority of applications, this introduces unnecessary operational overhead and severe transactional failure modes that PostgreSQL's native FOR UPDATE SKIP LOCKED solves with a single SQL clause.

Adding an external queue splits your application state. When a web request creates an order and needs to send a confirmation email, handling this across a database and an external queue creates the classic dual-write problem: if the database transaction commits but the queue push fails, the email never sends; if the queue pushes first and the database transaction rolls back, the worker processes a ghost order. Solving this usually requires implementing the Transactional Outbox pattern, which ironically involves writing jobs to a Postgres table first anyway.

The Concurrency Bottleneck of Naive Database Queues

Historically, developers avoided using relational databases as queues because concurrent polling caused catastrophic locking contention or duplicate processing. In a naive implementation, multiple workers poll for unprocessed tasks:

-- Worker 1 and Worker 2 run simultaneously
SELECT id, payload FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1;
-- Both workers receive id = 42
UPDATE jobs SET status = 'processing' WHERE id = 42;

Without locking, both workers read the exact same row before either updates its status, resulting in duplicate job execution.

Adding a standard row lock via SELECT ... FOR UPDATE eliminates the race condition but creates a serialized bottleneck. When Worker 1 acquires an exclusive row lock on the oldest pending record, Worker 2 must halt execution and wait until Worker 1's transaction commits before it can even inspect the row. A pool of twenty parallel workers collapses into a single-file convoy, destroying processing throughput.

How FOR UPDATE SKIP LOCKED Changes the Game

Introduced in PostgreSQL 9.5, FOR UPDATE SKIP LOCKED fundamentally alters row-locking mechanics. Instead of blocking when encountering a row currently locked by another open transaction, the database engine simply skips past it and selects the next unlocked row matching the query criteria.

This behavior allows dozens of concurrent workers to query the same table simultaneously without blocking each other, deadlocking, or claiming duplicate jobs. Each worker instantly locks and receives a distinct record.

Here is the canonical, battle-tested pattern to atomically fetch and claim a batch of jobs in a single statement:

WITH next_job AS (
  SELECT id 
  FROM jobs 
  WHERE status = 'pending' 
    AND run_at <= NOW()
  ORDER BY priority DESC, created_at ASC
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET 
  status = 'processing',
  started_at = NOW(),
  attempts = attempts + 1
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.*;

Because this lock is bound to the database transaction, if a worker process crashes mid-execution, PostgreSQL automatically aborts the transaction and releases the lock. The job immediately returns to an available state without requiring complex distributed heartbeat monitors.

Production Performance and Indexing Strategy

A database-backed queue table undergoes high write, update, and delete churn. Without proper indexing and maintenance, table bloat and sequential scans can degrade throughput rapidly.

To ensure constant-time fetches, use a partial index that covers only active tasks:

CREATE INDEX idx_jobs_pending 
ON jobs (priority DESC, created_at ASC) 
WHERE status = 'pending';

By indexing only rows where status = 'pending', the index remains extremely small and fits entirely in RAM, regardless of how many millions of completed jobs sit in the historical table.

For cleanup, rather than keeping all processed jobs in the active table, prune completed tasks with a periodic batch delete or an aggressive autovacuum setting tailored specifically for the queue table:

ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.05);

When to Transition to a Dedicated Broker

A PostgreSQL queue backed by SKIP LOCKED comfortably handles hundreds to low thousands of processed jobs per second on modest database instances. Frameworks like River (Go), Oban (Elixir), and GoodJob (Ruby) run at scale on this exact primitive.

However, you should evaluate dedicated brokers under specific constraints:

1. Ultra-high throughput: If your system ingests tens of thousands of tasks per second, the Write-Ahead Log (WAL) overhead and MVCC page turnover will saturate disk I/O.
2. Fan-out pub/sub: If a single message must be broadcast independently to dozens of distinct consumer groups with minimal latency, specialized brokers like Apache Kafka or RabbitMQ are better suited.
3. Extremely long-running locks: Holding database transactions open for tasks that take hours can hold open older transaction IDs, delaying autovacuum cleanup across the cluster.

For typical transactional web workflows—welcome emails, PDF rendering, webhook dispatching, and asynchronous payment verifications—a Postgres table with SKIP LOCKED offers complete transactional integrity, simpler backups, zero extra services to monitor, and exceptional developer ergonomics.

Sources

1. postgresql.org
2. neon.tech
3. prisma.io