Using FOR UPDATE SKIP LOCKED For Queue Workflows

One of the most common and powerful patterns in modern application development is the job queue. Whether you’re sending emails, processing images, or running complex calculations, offloading tasks to background workers is essential for building responsive and scalable systems. Many developers reach for dedicated queueing software like RabbitMQ or Redis, but for many use cases, your primary PostgreSQL database already has all the tools you need to build a robust, transactional, and incredibly performant job_queue_postgres.

The challenge, however, lies in coordination. How do you allow multiple concurrent_consumers (workers) to safely grab jobs from a table without them all trying to work on the same task, or worse, getting stuck in a traffic jam of database locks? The naive approach often leads to race conditions, while traditional locking (FOR UPDATE) can serialize your workers, defeating the purpose of parallelism.

This is where a powerful but often overlooked PostgreSQL feature comes into play: FOR UPDATE SKIP LOCKED. This single clause provides an elegant and highly efficient mechanism to create a deadlock_free_queue, allowing your workers to operate in parallel with minimal contention.

TL;DR

• FOR UPDATE SKIP LOCKED lets a query skip rows already locked by other transactions instead of waiting, which turns a PostgreSQL table into a high-throughput job queue.
• It fixes the contention that breaks naive queues: plain logic causes duplicate processing and plain FOR UPDATE forces workers into a single-file convoy, while SKIP LOCKED keeps them running in parallel.
• Because the lock is tied to the transaction, a worker crash automatically rolls back and returns the job to pending, making it safer and simpler than manually managed advisory locks.
• Performance depends on a composite index like (status, created_at), an ORDER BY for processing order, partitioning for huge tables, and monitoring queue depth, throughput, and lock contention.

The Classic Job Queue Problem: A Recipe For Contention

Let’s imagine a simple jobs table with a status column (pending, processing, completed). The most basic worker logic looks something like this:

1. Select a Job: Find a job where the status is ‘pending’.
2. Update the Status: Change the job’s status to ‘processing’ to mark it as taken.
3. Process the Job: Perform the actual work.

The problem occurs between steps 1 and 2. If two workers run the SELECT query at nearly the same time, they will both see the same ‘pending’ job. They will then both try to UPDATE it, leading to either duplicate processing (a disaster for non-idempotent tasks) or one worker’s update failing.

A more robust approach uses pessimistic locking with FOR UPDATE. The worker’s query would look for a pending job and lock the row immediately. When a second worker tries to select that same row, the database forces it to wait until the first worker’s transaction is complete. This prevents duplicate work but introduces a new bottleneck. Your workers, which are supposed to run in parallel, now form a “convoy,” waiting in a single-file line for the first worker to finish. This leads to high blocking_time and poor queue_performance.

The Elegant Solution: FOR UPDATE SKIP LOCKED

Introduced in PostgreSQL 9.5, SKIP LOCKED is a modifier for FOR UPDATE that completely changes its behavior. When a query with SKIP LOCKED tries to acquire a row_level_lock on a row that is already locked by another transaction, it doesn’t wait. Instead, it simply skips that row as if it didn’t exist and moves on to the next one that meets the WHERE clause criteria.

This is the perfect primitive for a postgres_worker_queue. Each worker can now execute a query to find and lock the first available pending job.

A highly efficient dequeue_script can be built into a single, atomic query. This query finds a pending job, locks it (skipping any that are already locked by other workers), updates its status to ‘processing’, and returns the job’s data to the worker application all in one go. Because this happens in a single statement, it’s incredibly fast and eliminates any chance of a race condition between selecting and updating.

Building A Robust Worker

The full workflow for a worker using this transactional_queue model would be:

1. Begin Transaction: The worker starts a new database transaction.
2. Dequeue a Job: The worker executes the atomic SKIP LOCKED query.
3. Check for Work: If the query returns a job, the worker proceeds. If it returns nothing (meaning all pending jobs are currently locked by other workers), the worker can sleep for a short period before trying again.
4. Process the Job: The worker performs the business logic using the data from the dequeued job.
5. Finalize the Job:
   • On success, the worker updates the job’s status to completed.
   • On failure, it updates the status to failed and potentially records the error message. This is a good place to implement retry_logic_sql, perhaps by incrementing a retry_count and setting the status back to pending if the count is below a threshold.
6. Commit Transaction: The worker commits the transaction, releasing the lock and making the final status change permanent.

If the worker process crashes for any reason, the database automatically rolls back the transaction. The lock is released, and the job’s status reverts to ‘pending’, making it available for another worker to pick up.

SKIP LOCKED vs Advisory Locks

Before SKIP LOCKED was available, a common pattern for building a postgres_queue involved using advisory locks. An advisory_lock is a cooperative lock that developers manage manually in their application code. A worker would try to acquire a lock using a function call. If successful, it would process the job; if not, it would move on.

So, how does advisory_lock_vs_skip_locked stack up?

• Complexity: Advisory locks are more complex. The application is responsible for managing the lock lifecycle, including ensuring locks are always released. A bug could lead to a “stuck” lock that requires manual intervention. SKIP LOCKED ties the lock directly to the row and the transaction, so it’s managed automatically by the database.
• Safety: SKIP LOCKED is safer. Because the lock is tied to the transaction, a worker crash guarantees the lock is released. With session-level advisory locks, a crashed worker could hold a lock until the database connection times out, which could be a very long time.
• Performance: For this specific use case, SKIP LOCKED is generally more performant as it’s a native, engine-level feature designed for this purpose.

For building a task_queue_db, FOR UPDATE SKIP LOCKED is the idiomatic, simpler, and more robust solution in modern PostgreSQL.

Performance Considerations & Best Practices

To ensure your optimistic_queue runs at peak performance, follow these best practices:

• Create the Right Index: This is the most critical factor for queue_performance. Your jobs table must have a composite index on the columns used for finding work. For example, an index on (status, created_at) would allow PostgreSQL to very quickly find the oldest pending jobs.
• Order Your Queue: Use an ORDER BY clause in your query to define the order in which jobs are processed (e.g., ORDER BY created_at for a FIFO queue, or ORDER BY priority, created_at for a priority queue).
• Partition Large Tables: If your jobs table grows to hundreds of millions of rows, consider using PostgreSQL’s native table partitioning. You could partition by date or status, which can significantly improve query performance and make maintenance easier.
• Monitor Your Queue: You can’t optimize what you can’t see. Continuously monitor key queue metrics:
  • Queue Depth: The number of jobs with status = 'pending'. A constantly growing number indicates your workers can’t keep up.
  • Worker Throughput: The rate of message_processing (jobs moved to completed or failed per second).
  • Lock Contention: Even with SKIP LOCKED, monitoring overall database lock contention is a good health check.

By combining the power of FOR UPDATE SKIP LOCKED with smart indexing and proactive monitoring, you can build a highly concurrent, reliable, and scalable job queue directly within the database you already trust, without adding another piece of infrastructure to your stack.

Netdata’s auto-discovery and detailed PostgreSQL dashboards make it easy to monitor these critical queue metrics in real-time, helping you fine-tune your worker pool and ensure your background job processing runs smoothly. Try Netdata for free today.

FOR UPDATE SKIP LOCKED FAQs

What Does FOR UPDATE SKIP LOCKED Do In PostgreSQL?

FOR UPDATE SKIP LOCKED is a modifier for the FOR UPDATE row-locking clause. When a query tries to lock a row that another transaction has already locked, the SKIP LOCKED part tells PostgreSQL not to wait. Instead, it skips that row as if it didn’t exist and moves on to the next row matching the WHERE clause. That behavior makes it ideal for job queues: multiple workers can each grab the first available pending job without blocking each other or processing the same row twice.

What Problem Does SKIP LOCKED Solve In A Job Queue?

It solves the contention problem that plain queue logic creates. A naive worker selects a pending job, then updates its status, but if two workers run the select at nearly the same moment they both grab the same job, causing duplicate processing or a failed update. Plain FOR UPDATE fixes the duplication but forces workers into a single-file “convoy,” each waiting for the one ahead. SKIP LOCKED lets every worker skip locked rows and pick up a different job, restoring true parallelism with minimal contention.

When Was SKIP LOCKED Introduced In PostgreSQL?

SKIP LOCKED arrived in PostgreSQL 9.5 as a modifier for FOR UPDATE. Before it existed, developers building a Postgres-backed queue typically reached for advisory locks, which the application had to manage manually. SKIP LOCKED made that pattern far simpler by tying the lock to the row and the transaction, so the database handles the lock lifecycle natively rather than leaving it to application code.

Does FOR UPDATE SKIP LOCKED Prevent Deadlocks In A Worker Queue?

Yes, it avoids the lock contention that stalls concurrent workers. Because a query with SKIP LOCKED never waits on a row another transaction holds, workers don’t pile up behind each other in a blocking convoy. Each one moves straight to an available job. The lock is also bound to the transaction, so if a worker crashes mid-job, the database rolls the transaction back, releases the lock, and the job’s status reverts to pending for another worker to claim. That combination keeps a multi-worker queue moving without stuck locks.

How Is SKIP LOCKED Different From Advisory Locks?

Both can build a Postgres queue, but they differ in three ways. Advisory locks are more complex: the application manages the full lock lifecycle and a bug can leave a “stuck” lock needing manual cleanup. SKIP LOCKED is safer because the lock is tied to the transaction, so a worker crash guarantees the lock is released, whereas a session-level advisory lock can linger until the connection times out. And for this use case SKIP LOCKED is generally more performant, since it’s a native engine feature built for exactly this purpose. For most modern Postgres queues it’s the simpler, more robust choice.

What Happens To A Job If A Worker Crashes Mid-Processing?

The job is automatically returned to the queue. Each worker wraps its work in a transaction: it begins the transaction, dequeues and locks a job with the SKIP LOCKED query, processes it, then commits to finalize the status. If the worker process crashes before committing, PostgreSQL rolls the transaction back. The row lock is released and the job’s status reverts to pending, making it available for another worker to pick up. You get crash recovery without writing any extra cleanup logic.

How Do I Make A SKIP LOCKED Job Queue Performant?

Indexing is the most important factor. Add a composite index on the columns your dequeue query filters and orders by, such as (status, created_at), so PostgreSQL can find the oldest pending jobs instantly. Use an ORDER BY clause to control processing order, like created_at for FIFO or priority, created_at for a priority queue. If the jobs table grows into hundreds of millions of rows, consider native table partitioning by date or status. Together these keep the queue fast as it scales.

What Metrics Should I Monitor For A Postgres Job Queue?

Watch three things. Queue depth is the count of jobs with status = ‘pending’; a number that keeps climbing means your workers can’t keep up. Worker throughput is the rate of jobs moving to completed or failed per second, which tells you how fast the pool is draining work. And lock contention is worth tracking as a database health check even with SKIP LOCKED in place. Tools like Netdata auto-discover PostgreSQL and chart these metrics in real time, so you can size your worker pool against live data.