Why Postgres SKIP LOCKED Eliminates the Message Broker Tax

The default reaction to needing background job processing is almost always to spin up Redis, RabbitMQ, or AWS SQS. For high-volume streaming platforms that process hundreds of thousands of events every second, a dedicated distributed queue is indispensable; however, for the vast majority of web applications handling emails, PDF generation, webhooks, and billing syncs, adopting a dedicated broker introduces unnecessary operational weight and architectural hazards.

By leveraging PostgreSQL's native FOR UPDATE SKIP LOCKED clause, you can run transactional, high-throughput task queues directly on your existing database.

The Dual-Write Trap of External Brokers

When your application state lives in PostgreSQL and your job queue lives in Redis or SQS, every asynchronous operation creates a classic dual-write distributed transaction. Consider a user checkout flow: your API creates an invoice in PostgreSQL and pushes an invoice_created notification job to Redis.

If the database transaction commits but the network call to Redis fails, the background job vanishes. If the job is enqueued first and the database transaction later rolls back due to a constraint violation, your background worker processes a ghost order. Even when both succeed, workers pulling from a fast Redis queue can attempt to read the newly created database record before the database transaction has committed, resulting in "record not found" race conditions.

Solving this correctly outside the database requires complex patterns like the Transactional Outbox with Change Data Capture (CDC). If you keep the queue table inside PostgreSQL, enqueuing a job becomes an ordinary INSERT within the same transaction that modifies your business data. Either both succeed or neither does.

The Concurrency Bottleneck in Traditional Queues

Building a naive job table with a status column (pending, running, completed) exposes fundamental concurrency bottlenecks.

If two workers query the table simultaneously using a simple query:

SELECT id, payload FROM jobs 
WHERE status = 'pending' 
ORDER BY id ASC LIMIT 1;

Both workers read the exact same row before either can update its status to running, executing duplicate work on identical data. Adding a traditional pessimistic lock with FOR UPDATE solves the duplicate work bug, but it destroys worker parallelism. When Worker A executes SELECT ... FOR UPDATE, PostgreSQL locks the matching row. When Worker B runs the identical query, it halts and waits until Worker A commits its entire transaction. Your fleet of background workers collapses into a single-file line.

How SKIP LOCKED Unblocks Concurrent Consumers

PostgreSQL introduced SKIP LOCKED to resolve this exact friction. When a query requests rows with FOR UPDATE SKIP LOCKED, PostgreSQL inspects candidates in order. If a row is already locked by another transaction, PostgreSQL does not block or wait; it skips that row immediately and moves down the index to lock and return the next available item.

This behavior gives you lock-free concurrency semantics at the application level while retaining transactional guarantees at the storage engine level.

The Atomic Claim Pattern with Common Table Expressions

In production, workers should claim a job, mark it as in-flight, and retrieve its payload in a single round-trip using an update query powered by a Common Table Expression (CTE):

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

This single query guarantees that exactly one worker grabs the job, locks it, transitions its status, and returns the payload instantly. The claiming transaction commits immediately, ensuring database connection locks are not held open during long-running background tasks. If the worker process crashes mid-execution, a periodic reaper query can detect stale locked_at timestamps and reset the status to pending for automatic retries.

Operational Hygiene and Performance Limits

Running job queues in a relational database requires two specific maintenance considerations:

1. Partial Indexes: A composite partial index ensures the lookup only scans pending work:

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

Because finished jobs are excluded from this index, queue polling remains lightning-fast regardless of how many millions of historical records exist in the table.

1. Table Bloat and Purging: High-frequency updates generate dead row versions in PostgreSQL's multi-version concurrency control (MVCC) engine. Ensure autovacuum is aggressively tuned on the jobs table, or archive completed jobs to a separate table and truncate old partitions.

For workloads below 5,000 to 10,000 jobs per second, PostgreSQL with SKIP LOCKED offers atomic reliability, trivial observability through plain SQL, and eliminates the infrastructure tax of dedicated message brokers.

Sources

1. postgresql.org
2. postgresql.org