Whenever an application needs background task processing—such as sending transactional emails, rendering PDFs, or ingesting asynchronous webhooks—the default reflex among backend engineers is to provision an external message broker like Redis, RabbitMQ, or Amazon SQS. While dedicated brokers excel at massive scale, introducing them adds operational overhead: an extra stateful service to monitor, back up, and secure, alongside the notorious risk of dual-write discrepancies between your database and the queue.
If your stack already runs on PostgreSQL, you frequently do not need an external message broker at all. Thanks to FOR UPDATE SKIP LOCKED, PostgreSQL natively turns any standard relational table into a robust, crash-resilient, and non-blocking job queue.
The Trap of Naive Database Queues
Developers historically avoided database-backed queues due to concurrency bottlenecks. A naive polling implementation looks deceptively simple:
-- Worker 1 & Worker 2 query simultaneously
SELECT id, payload FROM background_jobs
WHERE status = 'pending'
ORDER BY scheduled_at ASC
LIMIT 1;
If two workers run this query at the exact same millisecond, both receive the identical task ID, leading to duplicate execution and race conditions.
To prevent duplicate processing, engineers often added standard row locking with FOR UPDATE:
SELECT id, payload FROM background_jobs
WHERE status = 'pending'
ORDER BY scheduled_at ASC
LIMIT 1
FOR UPDATE;
While FOR UPDATE prevents double execution, it introduces severe serialization. When Worker 1 locks the oldest pending row, Worker 2 does not jump to the next available task; it freezes, waiting for Worker 1 to finish its entire transaction before proceeding. Adding more worker instances does not increase throughput—it merely lines them up in a single-file convoy.
How SKIP LOCKED Enables Real Parallelism
PostgreSQL solved this contention pattern with the SKIP LOCKED modifier. When a worker selects rows with FOR UPDATE SKIP LOCKED, PostgreSQL evaluates rows in order, automatically ignores any row currently locked by another active transaction, and immediately returns the next unlocked candidate.
BEGIN;
SELECT id, payload
FROM background_jobs
WHERE status = 'pending'
ORDER BY scheduled_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Worker processes payload here...
UPDATE background_jobs
SET status = 'completed', updated_at = NOW()
WHERE id = :claimed_id;
COMMIT;
With SKIP LOCKED, ten workers polling the database simultaneously claim ten distinct jobs in parallel with zero lock contention and zero waiting. If a worker process crashes midway through execution, its database connection drops, PostgreSQL aborts the transaction, and the row lock is instantly released back to the pool without orphaned state.
The Production Pattern: Atomic CTE Claim
In high-throughput environments, keeping database transactions open while executing long-running business logic in your application layer consumes precious database connections and holds row locks unnecessarily long. A cleaner production design uses a single atomic UPDATE with a Common Table Expression (CTE) to claim tasks instantly in a short transaction:
WITH next_job AS (
SELECT id
FROM background_jobs
WHERE status = 'pending'
AND scheduled_at <= NOW()
ORDER BY scheduled_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE background_jobs j
SET status = 'processing',
locked_at = NOW(),
attempts = attempts + 1
FROM next_job
WHERE j.id = next_job.id
RETURNING j.id, j.payload, j.attempts;
This statement completes in milliseconds. The application grabs the payload, finishes the database round-trip immediately, performs the background computation, and issues a separate UPDATE setting status = 'completed' upon completion. By adding a locked_at timestamp, a background reaper process can easily detect timed-out jobs where a worker silently died without updating status, resetting them back to pending.
Event-Driven Wakeups with LISTEN and NOTIFY
Polling a database table every few hundred milliseconds introduces idle query churn. To combine low latency with low resource usage, pair your queue table with PostgreSQL's built-in Pub/Sub mechanism via LISTEN and NOTIFY.
When a new job is inserted into background_jobs, a trigger or the inserting transaction broadcasts a notification:
NOTIFY job_channel, 'new_job';
Idle workers issue LISTEN job_channel; and sleep on the connection. As soon as a notification arrives, the worker wakes up instantly, runs the CTE claim query, processes available work, and goes back to sleep once the queue is drained. This eliminates polling overhead while maintaining millisecond-level reaction times.
Indexing Strategy for Maximum Speed
Without an appropriate index, PostgreSQL performs sequential table scans to find pending jobs, degrading performance as the history table grows. A partial index targeting only active workloads keeps queue lookups blazing fast:
CREATE INDEX idx_jobs_pending
ON background_jobs (scheduled_at ASC)
WHERE status = 'pending';
Because this partial index excludes millions of completed and archived rows, its memory footprint remains minuscule, allowing index scans to fit comfortably in PostgreSQL's shared_buffers cache.
Operational Limits: When to Move Beyond Postgres
A PostgreSQL-backed queue provides unbeatable transactional guarantees: you can insert domain entities and their associated background jobs inside a single database transaction, eliminating dual-write synchronization bugs entirely. For workloads processing thousands of jobs per minute, it is often the most reliable and maintenance-free architecture you can deploy.
However, PostgreSQL is not designed for continuous high-churn ephemeral writes at ten thousand items per second. If massive write churn is sustained without aggressive table pruning or table partitioning, PostgreSQL's MVCC mechanism creates table bloat, forcing VACUUM workers to burn CPU cleaning dead row tuples. When your throughput genuinely surpasses hundreds of tasks per second, transition to specialized streaming systems like Kafka or SQS. But until you reach that scale, leveraging FOR UPDATE SKIP LOCKED inside your existing database gives you enterprise-grade queuing with zero extra infrastructure.
Loading comments…