PostgreSQL Queue Options Compared: SKIP LOCKED, LISTEN/NOTIFY, and External Queues

Many SaaS applications eventually need background work: sending email, provisioning tenants, indexing documents, charging invoices, or retrying calls to a slow third-party API. Because the application already depends on PostgreSQL, the first queue often starts as a table plus a worker process. That can be a good decision, but only if the queue pattern matches the workload and the team understands where PostgreSQL is strong.

The core question is not whether PostgreSQL can hold jobs. It can. The better question is which responsibilities belong in the database, which belong in the worker, and when a dedicated queue becomes simpler than continuing to tune a table that was never meant to behave like a high-throughput broker.

The problem with "just add a jobs table"

A jobs table is attractive because it is transactional. If creating an invoice and enqueueing an email happen in the same database transaction, either both commit or neither does. That property is hard to reproduce with a separate broker unless the application implements an outbox pattern. PostgreSQL also gives you familiar durability, indexes, permissions, backups, and operational visibility.

The trouble starts when workers compete for the same rows, retry logic grows, or old completed jobs make the hot part of the table expensive to scan. A queue table is usually one of the highest-churn tables in an application. Rows move from pending to running to done or failed; retries update timestamps; workers poll repeatedly; maintenance has to reclaim dead tuples. The design needs to account for locking and cleanup from the beginning.

The main options

PostgreSQL queue designs usually fall into four buckets. A worker can poll a table and claim rows with FOR UPDATE SKIP LOCKED. The application can use LISTEN and NOTIFY as a wake-up signal. Workers can coordinate with advisory locks for coarse-grained mutual exclusion. Or the application can move queueing to a broker such as Redis, SQS, RabbitMQ, or another managed queue while keeping PostgreSQL as the source of truth.

The comparison is less about features in isolation and more about failure behavior. If a worker crashes after claiming a job, can another worker find it? If a notification is missed, is the durable work still visible? If a deploy runs two schedulers at once, can only one execute the same maintenance task? The safest architecture answers those questions explicitly.

Table queues with SKIP LOCKED

FOR UPDATE SKIP LOCKED is the workhorse for PostgreSQL-backed queues. A worker selects a small batch of pending jobs, locks the rows, and skips rows already locked by other workers. That lets multiple workers claim jobs concurrently without blocking behind the first locked row. A typical claim query updates the selected rows to a running state and returns them in one transaction.

with next_jobs as (
  select id
  from jobs
  where status = 'pending'
    and run_at <= now()
  order by priority desc, run_at asc, id asc
  for update skip locked
  limit 25
)
update jobs
set status = 'running',
    locked_at = now(),
    attempts = attempts + 1
where id in (select id from next_jobs)
returning *;

That pattern keeps row ownership inside PostgreSQL. It also makes retries straightforward: a separate recovery pass can move rows stuck in running back to pending after a timeout if the worker died. The practical details matter. Keep the claim predicate backed by a partial index such as where status = 'pending'; process in small batches; keep worker transactions short; and archive or delete completed rows before they dominate table and index size.

The PostgreSQL documentation is clear that SKIP LOCKED provides an inconsistent view of the data because it intentionally skips locked rows. For a queue, that is usually acceptable because the goal is to find claimable work, not produce a perfectly ordered analytical result. It is not a good fit for business logic that requires strict global ordering under concurrency.

LISTEN/NOTIFY is a signal, not the queue

LISTEN and NOTIFY are useful when workers should wake quickly after new work is inserted. The reliable pattern is to store the job in a table and send a notification in the same transaction. When the transaction commits, listening workers receive a signal and then query the table for claimable jobs. If a worker is disconnected when the notification happens, the job still exists in the table and will be found by the next poll.

This distinction is important. A notification payload should not be treated as the only copy of the work. PostgreSQL documentation describes notifications as messages delivered to listening sessions; they are not a durable queue with replay, visibility timeouts, dead-letter handling, or consumer groups. In a managed PostgreSQL environment, connections can restart during maintenance, applications can redeploy, and network paths can be interrupted. The queue needs to survive those events even when the signal does not.

A good hybrid design uses NOTIFY to reduce latency and polling frequency, while the table remains the source of truth. Workers still run a periodic sweep, perhaps every few seconds, so missed signals do not strand work. The result is simple and robust for moderate job volume.

Where advisory locks fit

Advisory locks solve a different problem. They are not a queue, but they are useful when only one worker should perform a named task at a time. Examples include a daily billing run, a per-tenant import, or a maintenance job that must not overlap across deploys. PostgreSQL exposes session-level and transaction-level advisory locks, so the application can choose whether the lock lasts until transaction end or until the session releases it.

For background work, transaction-level advisory locks are often easier to reason about because they release automatically on commit or rollback. Session-level locks can be appropriate for long-running processes, but they require careful connection handling. If a pooler or application framework reuses connections in surprising ways, lock ownership can become unclear. Instrumentation should show which scheduler or worker currently owns an advisory lock and how long it has held it.

Use advisory locks to guard schedulers or enforce per-resource exclusivity. Do not use them as a substitute for durable job state. A job still needs a row, status, retry policy, and observability if the outcome matters.

When an external queue is the better choice

A dedicated queue becomes attractive when delivery semantics outgrow a database table. High fan-out, very high message volume, long delayed delivery, consumer groups, dead-letter queues, cross-service event streams, and independent autoscaling are all signs that a broker may simplify the system. PostgreSQL can still participate through an outbox table: the application writes the business change and an outbox event in the same transaction, then a relay publishes the event to the broker.

The tradeoff is operational. An external queue adds another service, credentials, monitoring surface, failure mode, and local development dependency. For a small product, that complexity may be less attractive than a well-indexed PostgreSQL queue. For a larger system, the broker may be exactly what prevents the primary database from becoming the coordination point for every asynchronous task.

Decision guide

Choose a PostgreSQL table queue when jobs are tightly coupled to database state, volume is moderate, and transactional enqueueing is more important than broker features. Add LISTEN/NOTIFY when lower latency matters, but keep polling as a safety net. Use advisory locks for singleton coordination around schedulers and resource-specific work. Move to an external queue when queue traffic, delivery features, or service boundaries begin to dominate the database workload.

For ArmorDB users, the same design rules apply. Managed PostgreSQL gives you backups, pooling, and operational management, but it does not remove the need to keep hot queue tables indexed and maintained. PgBouncer can help application connection pressure, while workers should still use short transactions and explicit claim queries. If a queue table becomes one of your busiest tables, review instance sizing, autovacuum behavior, and backup retention together rather than treating the queue as invisible application plumbing. The managed PostgreSQL instance sizing guide is a useful next step when background work starts to affect database headroom.

Practical implementation checklist

Start with a narrow jobs table: identifiers, status, priority, run time, attempt count, lock timestamp, payload, error text, and created or updated timestamps. Add a partial index for pending jobs in the same order workers claim them. Keep completed jobs out of the hot path by deleting, partitioning, or archiving them on a schedule. Make retries explicit by storing the next run time rather than sleeping inside a database transaction.

Worker behavior should be boring. Claim a small batch, commit quickly, perform the external work outside the claim transaction unless atomicity requires otherwise, then mark the job done or failed. If exactly-once side effects matter, design idempotency at the application boundary; a database queue can prevent two workers from claiming the same row at the same time, but it cannot make an email provider, payment processor, or webhook receiver exactly-once.

Finally, monitor the queue as a product dependency. Track pending count, oldest pending age, retry count, failed count, job duration, lock recovery count, table size, and index size. Those signals tell you whether the design is healthy long before customers notice delayed work.

Takeaway

PostgreSQL is a strong queue foundation when the work is durable, closely tied to database state, and moderate in volume. FOR UPDATE SKIP LOCKED handles concurrent claims, LISTEN/NOTIFY can wake workers, and advisory locks can protect singleton tasks. The durable state should remain in tables, not notifications or memory. When the queue becomes a high-throughput messaging platform, use an external broker and keep PostgreSQL as the transactional source of truth through an outbox.

Sources and further reading

• PostgreSQL documentation: SELECT and locking clauses
• PostgreSQL documentation: LISTEN
• PostgreSQL documentation: NOTIFY
• PostgreSQL documentation: Explicit locking and advisory locks
• PostgreSQL documentation: Monitoring database activity