Postgres Locking: Advisory vs. Row-Level Locks for Resilient Job Queues

The Concurrency Challenge in Distributed Job Queues

As systems scale, the humble job queue often becomes a critical point of contention. Implementing a queue that is not only performant but also resilient and correct under high concurrency is a non-trivial engineering task. While solutions like RabbitMQ or Redis are excellent, leveraging the transactional integrity and robustness of PostgreSQL for a job queue is a powerful and common pattern, eliminating the need for another piece of infrastructure.

The core problem is simple to state but hard to solve perfectly: how do multiple worker processes, running concurrently on different machines, pick a unique, pending job from a database table without picking the same one, and without blocking each other unnecessarily?

This is not an introductory post. We will assume you understand ACID properties, transaction isolation levels, and the basic concept of a database-backed job queue. Our focus is on the two most sophisticated locking primitives PostgreSQL offers for this specific problem:

The decision between these two is not a matter of preference; it's a critical architectural choice with profound implications for performance, scalability, and correctness. We will explore production-grade implementations of both, dissect their failure modes, and provide a data-driven framework for making the right choice.

The Battlefield: A Realistic Job Queue Schema

To ground our discussion, let's define a realistic schema for our jobs table. Note the inclusion of fields for tracking status, retries, and locking information.

CREATE TYPE job_status AS ENUM ('pending', 'running', 'completed', 'failed');

CREATE TABLE jobs (
    id BIGSERIAL PRIMARY KEY,
    queue_name TEXT NOT NULL DEFAULT 'default',
    payload JSONB NOT NULL,
    priority INTEGER NOT NULL DEFAULT 0, -- Lower is higher priority
    status job_status NOT NULL DEFAULT 'pending',
    max_retries INTEGER NOT NULL DEFAULT 3,
    current_retry INTEGER NOT NULL DEFAULT 0,
    run_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- For tracking who has the lock and when
    locked_by TEXT,
    locked_at TIMESTAMPTZ
);

-- The critical index for finding pending jobs efficiently
CREATE INDEX idx_jobs_fetch_pending ON jobs (priority, run_at) WHERE status = 'pending';

-- Optional: An index to quickly find jobs by status
CREATE INDEX idx_jobs_status ON jobs (status);

Our goal is to build a worker that can efficiently poll this table, grab a single pending job, and process it. The core of this challenge lies in the atomicity of the "find and lock" operation.

Pattern 1: The Fortress - Row-Level Locking with `UPDATE ... SKIP LOCKED`

The most direct, idiomatic, and often safest way to implement a job queue in modern PostgreSQL (9.5+) is using SKIP LOCKED. This clause, when used with FOR UPDATE or FOR SHARE, tells PostgreSQL to simply ignore any rows that are already locked by another transaction, rather than waiting for them to be released.

This non-blocking behavior is the magic ingredient for a high-throughput queue. Instead of a long line of workers waiting for the same popular job, each worker can swiftly grab the next available unlocked job.

A naive implementation might use SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1. However, a more robust and efficient pattern combines the locking, updating of the job's state, and retrieval of the job data into a single, atomic statement using UPDATE with a RETURNING clause. This avoids a second round trip to the database.

Production-Grade Implementation (Go)

Here is a complete, production-ready worker function in Go that demonstrates this pattern. We use the pgx driver for its excellent performance and feature set.

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"os"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

type Job struct {
	ID      int64
	Queue   string
	Payload json.RawMessage
}

// fetchAndLockJobWithSkipLocked finds, locks, and updates a job in a single atomic operation.
func fetchAndLockJobWithSkipLocked(ctx context.Context, dbpool *pgxpool.Pool, workerID string) (*Job, error) {
	query := `
        UPDATE jobs
        SET 
            status = 'running',
            locked_by = $1,
            locked_at = NOW()
        WHERE id = (
            SELECT id
            FROM jobs
            WHERE status = 'pending' AND run_at <= NOW()
            ORDER BY priority, run_at
            FOR UPDATE SKIP LOCKED
            LIMIT 1
        )
        RETURNING id, queue_name, payload;`

	job := &Job{}
	err := dbpool.QueryRow(ctx, query, workerID).Scan(&job.ID, &job.Queue, &job.Payload)
	if err != nil {
		// pgx.ErrNoRows is the expected error when no jobs are available. We don't treat it as a fatal error.
		if err.Error() == "no rows in result set" { // More robust check: errors.Is(err, pgx.ErrNoRows)
			return nil, nil 
		}
		return nil, fmt.Errorf("failed to fetch job: %w", err)
	}

	return job, nil
}

func main() {
	// In a real app, get this from config
	databaseUrl := "postgres://user:password@localhost:5432/jobs_db"
	dbpool, err := pgxpool.New(context.Background(), databaseUrl)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer dbpool.Close()

	workerID := fmt.Sprintf("worker-%d", os.Getpid())
	fmt.Printf("Starting worker: %s\n", workerID)

	for {
		job, err := fetchAndLockJobWithSkipLocked(context.Background(), dbpool, workerID)
		if err != nil {
			fmt.Fprintf(os.Stderr, "Error fetching job: %v\n", err)
			time.Sleep(5 * time.Second) // Backoff on error
			continue
		}

		if job == nil {
			fmt.Println("No available jobs. Waiting...")
			time.Sleep(2 * time.Second)
			continue
		}

		fmt.Printf("Worker %s processing job %d from queue '%s'\n", workerID, job.ID, job.Queue)
		// Simulate work
		time.Sleep(1 * time.Second)

		// In a real implementation, you would wrap this whole process in a transaction.
		// The lock is held for the duration of the transaction. If you commit, the job
		// state change is permanent. If you rollback, the lock is released and the
		// job status reverts, making it available again.
		// For simplicity, we mark it as completed here in a separate query.
		_, err = dbpool.Exec(context.Background(), "UPDATE jobs SET status = 'completed' WHERE id = $1", job.ID)
		if err != nil {
			fmt.Fprintf(os.Stderr, "Error completing job %d: %v\n", job.ID, err)
		}
		fmt.Printf("Worker %s finished job %d\n", workerID, job.ID)
	}
}

Analysis of the `SKIP LOCKED` Pattern

Pros:

* Atomicity and Correctness: This pattern is fundamentally safe. The database guarantees that the find, lock, and update operations are atomic. There is no possibility of a race condition where two workers could grab the same job.

* Simplicity: The logic is encapsulated in a single, albeit complex, SQL statement. The application code is straightforward and doesn't need to manage complex locking logic.

* Automatic Cleanup: The row lock is tied to the transaction. If a worker process crashes hard, the database connection will eventually time out, the transaction will be rolled back, and the lock on the row will be released automatically. The job's status will revert to pending, making it available for another worker. This is a massive win for fault tolerance.

Cons & Edge Cases:

* MVCC and Table Bloat: Every job acquisition involves a write (UPDATE). This creates dead tuples in the jobs table, which VACUUM must clean up. On an extremely high-throughput queue (thousands of jobs per second), this can contribute significantly to table bloat and vacuum pressure, potentially impacting overall database performance.

* Lock Contention on Hot Rows (Without SKIP LOCKED): While SKIP LOCKED prevents workers from blocking on each other, it's worth noting how it works. It modifies the visibility rules for the transaction, effectively hiding locked rows. This can have subtle performance implications on how the query planner navigates the index to find the next available row.

* Potential for Starvation: The ORDER BY priority, run_at clause is crucial. Without it, there's no guarantee which of the available jobs will be picked. With SKIP LOCKED, if the highest-priority jobs are constantly locked, a worker might repeatedly skip them and pick up lower-priority jobs. The ORDER BY ensures workers always attempt to grab the most important job first, but it doesn't entirely eliminate the possibility of some jobs being skipped for a long time if contention is pathologically high on the first few pages of the index.

Pattern 2: The Diplomat - Advisory Locks

Advisory locks are a completely different beast. They are a cooperative locking system. Think of them as a global set of mutexes managed by PostgreSQL, identified by numbers (bigint or two integers). They are not tied to any table or row; they are simply a signal that applications can agree to respect.

The key function for our use case is pg_try_advisory_xact_lock(key bigint). The try_ prefix makes it non-blocking—it will either acquire the lock and return true, or fail immediately and return false. The _xact_ suffix scopes the lock to the current transaction, meaning it will be automatically released on COMMIT or ROLLBACK, just like a row-level lock. This is critical for fault tolerance.

Implementing a job queue with advisory locks is a multi-step dance that requires extreme care to avoid race conditions.

• Begin a transaction.

• If the lock fails, or if the re-check shows the job is no longer pending, loop back and try another job.

The Critical Race Condition

Why is step 4, the "double-check," so important? Consider this sequence of events between Worker A and Worker B:

This looks safe. But what if the sequence is slightly different?

This is why you must re-verify the state of the world after you acquire the lock.

Production-Grade Implementation (Go)

package main

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"os"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

type Job struct { // Same struct as before
	ID      int64
	Queue   string
	Payload json.RawMessage
}

// fetchAndLockJobWithAdvisoryLock uses the double-checked locking pattern.
func fetchAndLockJobWithAdvisoryLock(ctx context.Context, dbpool *pgxpool.Pool, workerID string) (*Job, error) {
	// We need to loop because we might find a candidate job but fail to lock it.
	for i := 0; i < 5; i++ { // Limit retries to prevent infinite loops
		tx, err := dbpool.Begin(ctx)
		if err != nil {
			return nil, err
		}
		defer tx.Rollback(ctx) // Ensure rollback on error

		// 1. Find candidate jobs. We fetch a few to reduce contention.
		var candidateIDs []int64
		rows, err := tx.Query(ctx, `
            SELECT id FROM jobs 
            WHERE status = 'pending' AND run_at <= NOW() 
            ORDER BY priority, run_at 
            LIMIT 10;`)
		if err != nil {
			return nil, err
		}
		
		for rows.Next() {
			var id int64
			if err := rows.Scan(&id); err != nil {
				rows.Close()
				return nil, err
			}
			candidateIDs = append(candidateIDs, id)
		}
		rows.Close()

		if len(candidateIDs) == 0 {
			return nil, nil // No jobs available
		}

		// 2. Iterate through candidates and try to lock one.
		for _, jobID := range candidateIDs {
			var locked bool
			err = tx.QueryRow(ctx, "SELECT pg_try_advisory_xact_lock($1)", jobID).Scan(&locked)
			if err != nil {
				return nil, err
			}

			if locked {
				// 3. We got the lock! Now, re-verify the job state (the CRITICAL step).
				job := &Job{}
				var status string
				err := tx.QueryRow(ctx, `
                    SELECT id, queue_name, payload, status FROM jobs WHERE id = $1`, jobID).Scan(
						&job.ID, &job.Queue, &job.Payload, &status)

				if err != nil {
					if errors.Is(err, pgx.ErrNoRows) { // Job was deleted, try next candidate
						continue
					}
					return nil, err
				}

				if status != "pending" { // Another worker processed it between our steps
					continue // The lock will be released by the deferred rollback. Try next candidate.
				}

				// 4. Success! Update the job and commit.
				_, err = tx.Exec(ctx, `
                    UPDATE jobs SET status = 'running', locked_by = $1, locked_at = NOW() WHERE id = $2`, 
                    workerID, jobID)
				if err != nil {
					return nil, err
				}

				if err := tx.Commit(ctx); err != nil {
					return nil, err
				}
				return job, nil
			}
		}
		// If we went through all candidates and failed to lock, rollback and retry the whole process.
		tx.Rollback(ctx)
	}

	return nil, nil // No lockable jobs found after retries
}

// main function would be similar to the previous example, just calling fetchAndLockJobWithAdvisoryLock

Analysis of the Advisory Lock Pattern

Pros:

* No Table Write for Locking: The act of locking does not, by itself, modify the jobs table. This means no MVCC churn, no dead tuples, and no VACUUM pressure from the locking mechanism itself. This can be a significant performance win on extremely high-frequency queues.

* Flexibility: Advisory locks can represent concepts that don't map to a single row. For example, you could acquire a lock on a (tenant_id, task_type) tuple to ensure that a given tenant only runs one PDF generation job at a time, regardless of how many jobs are in the queue. This is impossible with row-level locks.

* Decoupling: The lock is logically separate from the data. This can simplify reasoning in some complex distributed algorithms.

Cons & Edge Cases:

* Complexity and Risk: The implementation is far more complex and fragile. The double-checked locking pattern is non-obvious and easy to get wrong, leading to subtle and catastrophic race conditions.

* Cooperative, Not Mandatory: A buggy piece of code or a developer manually running queries can ignore the advisory lock and modify the job row, breaking the system's correctness guarantees. SKIP LOCKED is enforced by the database itself.

* Increased Round Trips: The select-then-lock-then-re-select-then-update pattern involves more round trips to the database within a single transaction compared to the single-statement UPDATE ... RETURNING pattern, which can add latency.

* Lock Key Space Collisions: You are responsible for managing the bigint namespace for your locks. If two different systems accidentally use the same key for different purposes, they will conflict with each other in baffling ways.

Head-to-Head Performance Benchmark

To make an informed decision, we need data. Let's consider a synthetic benchmark with the following setup:

* Database: PostgreSQL 15 on a cloud instance (e.g., 4 vCPU, 16GB RAM).

* Workload: 100 concurrent workers polling for jobs. A producer script inserts 1,000 jobs/second into the jobs table.

* Metrics:

* Throughput: Total jobs processed per second.

* Job Acquisition Latency: The average time from when a worker starts looking for a job to when it has successfully locked one.

* DB CPU Utilization: The CPU load on the PostgreSQL server.

Hypothetical Benchmark Results

Now, let's crank up the pressure. Let's imagine a scenario with very short jobs, increasing the frequency of locking/unlocking operations to 5,000 jobs/sec with 200 workers.

Analysis of Results

These hypothetical results illustrate the core trade-off. The SKIP LOCKED pattern is a sprinter: it's incredibly fast and efficient out of the box due to its atomicity and simplicity. However, its performance is ultimately coupled to the write performance of the table itself.

The Advisory Lock pattern is a marathon runner: it has a higher baseline overhead due to its more complex, chatty protocol. But under extreme, sustained load where table write I/O and VACUUM pressure become the primary bottlenecks, its decoupled nature allows it to scale more gracefully.

Conclusion: A Decision Framework

So, which pattern should you choose? The answer, as always, is: it depends. But we can distill our findings into a clear set of production recommendations.

Start with UPDATE ... RETURNING ... SKIP LOCKED.

For 95% of use cases, this is the correct choice. Its combination of safety, correctness, fault tolerance, and excellent performance is unmatched. The simplicity of the implementation reduces the risk of human error. Do not optimize prematurely. You should only move away from this pattern if you have monitoring and profiling data that proves, conclusively, that table update/vacuum overhead is your primary performance bottleneck.

Use Advisory Locks When You Need One of These Superpowers:

If you do choose advisory locks, be paranoid. Write extensive tests for the race conditions, enforce the double-checked locking pattern through code reviews, and document clearly that the system relies on a cooperative locking scheme. The power and flexibility they offer are immense, but they come at the cost of complexity and a greater burden of responsibility on the developer to ensure correctness.