PostgreSQL: Advisory Locks vs. SKIP LOCKED for Job Queues

The Senior Engineer's Dilemma: Building a Resilient PG-Backed Job Queue

For many distributed systems, introducing an external message broker like RabbitMQ or Kafka adds significant operational overhead. A common and powerful pattern is to leverage the transactional integrity of PostgreSQL to implement a reliable job queue directly within your primary data store. However, the seemingly simple task of "pulling a job off the queue" is fraught with concurrency perils that can lead to duplicate processing, deadlocks, and catastrophic performance degradation under load.

This is not a beginner's guide. We assume you understand why a naive SELECT followed by an UPDATE is a recipe for disaster. Instead, we will dissect the two dominant, production-ready patterns for achieving concurrency-safe job dequeuing in PostgreSQL:

This article provides a rigorous, implementation-focused comparison of these two strategies. We'll analyze their internal mechanics, performance characteristics under load, failure modes, and the subtle trade-offs that senior engineers must weigh when designing high-throughput, mission-critical systems.

The Foundational Schema: A Production-Grade `jobs` Table

Before we dive into locking, let's establish a realistic table structure. A simplistic schema will hide the real-world complexities we need to address.

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

CREATE TABLE jobs (
    id BIGSERIAL PRIMARY KEY,
    queue_name TEXT NOT NULL DEFAULT 'default',
    payload JSONB NOT NULL,
    
    -- State and Scheduling
    status job_status NOT NULL DEFAULT 'pending',
    run_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Retry Logic
    max_attempts INT NOT NULL DEFAULT 5,
    attempts INT NOT NULL DEFAULT 0,
    
    -- Auditing and Error Info
    last_error TEXT,
    processed_at TIMESTAMPTZ,
    finished_at TIMESTAMPTZ,
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- The most critical index for polling workers
CREATE INDEX idx_jobs_on_polling ON jobs (queue_name, status, run_at) WHERE status = 'pending';

-- Optional: Index for cleanup or monitoring
CREATE INDEX idx_jobs_on_status ON jobs (status);

Key Design Choices:

* job_status ENUM: Provides type safety over plain text.

* run_at: Allows for scheduled/delayed jobs.

* attempts/max_attempts: Essential for building resilient, auto-retrying jobs.

* The Polling Index (idx_jobs_on_polling): This is the most important performance component. It's a partial index covering only 'pending' jobs, keeping it small and highly efficient. The order of columns (queue_name, status, run_at) is critical for allowing workers to efficiently find the next available job in a specific queue.

Pattern 1: `SELECT FOR UPDATE SKIP LOCKED` - The Atomic Powerhouse

This approach leverages a feature introduced in PostgreSQL 9.5 that is tailor-made for queueing workloads. It instructs the database to find rows matching the WHERE clause, attempt to acquire an exclusive FOR UPDATE lock on them, and if a row is already locked by another transaction, simply skip it and move to the next one.

Mechanism Deep Dive

When a worker transaction executes SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1, the following happens atomically within the database engine:

• For the first candidate row it finds, it checks for existing row-level locks.

• If the row is already locked, it is ignored entirely, and the engine immediately moves to the next candidate row in the index scan.

This entire process is a single, atomic database operation, which is its greatest strength.

Production Implementation (Go)

Here is a complete, production-ready Go function for a worker that uses this pattern. Error handling and transactional integrity are paramount.

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

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

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

// processJobsWithSkipLocked is a worker function that fetches and processes one job.
func processJobsWithSkipLocked(ctx context.Context, db *pgxpool.Pool, queueName string) (processed bool, err error) {
	tx, err := db.Begin(ctx)
	if err != nil {
		return false, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback(ctx) // Rollback is a no-op if tx has been committed.

	// Atomically find, lock, and retrieve the next pending job.
	query := `
		SELECT id, queue_name, payload, attempts
		FROM jobs
		WHERE queue_name = $1 AND status = 'pending' AND run_at <= NOW()
		ORDER BY run_at ASC
		FOR UPDATE SKIP LOCKED
		LIMIT 1`

	var job Job
	row := tx.QueryRow(ctx, query, queueName)
	err = row.Scan(&job.ID, &job.Queue, &job.Payload, &job.Attempts)

	if err == sql.ErrNoRows {
		// This is not an error, it just means the queue is empty.
		return false, nil
	}
	if err != nil {
		return false, fmt.Errorf("failed to scan job: %w", err)
	}

	// Mark the job as 'processing' immediately.
	// This provides visibility into which jobs are currently running.
	_, err = tx.Exec(ctx, 
		"UPDATE jobs SET status = 'processing', attempts = attempts + 1, processed_at = NOW() WHERE id = $1", 
		job.ID)
	if err != nil {
		return false, fmt.Errorf("failed to mark job as processing: %w", err)
	}

	// The job is now locked and marked as processing. We can commit the transaction
	// early to release the lock, allowing other workers to poll the table.
	// This is an optimization for long-running jobs.
	if err := tx.Commit(ctx); err != nil {
		return false, fmt.Errorf("failed to commit processing state: %w", err)
	}

	// --- Perform the actual job logic here ---
	// For example, call an external API, process a file, etc.
	// If this part fails, we need to handle the failure.
	jobErr := processPayload(job.Payload)

	// Start a new transaction to update the final state.
	tx2, err := db.Begin(ctx)
	if err != nil {
		return true, fmt.Errorf("failed to begin final state transaction: %w", err)
	}
	defer tx2.Rollback(ctx)

	if jobErr != nil {
		// Job failed. Mark it as 'failed' or requeue if attempts remain.
		_, updateErr := tx2.Exec(ctx,
			"UPDATE jobs SET status = 'failed', last_error = $1, finished_at = NOW() WHERE id = $2",
			jobErr.Error(), job.ID)
		if updateErr != nil {
			return true, fmt.Errorf("job failed, but couldn't update status: %w", updateErr)
		}
	} else {
		// Job succeeded.
		_, updateErr := tx2.Exec(ctx,
			"UPDATE jobs SET status = 'completed', finished_at = NOW() WHERE id = $2",
			job.ID)
		if updateErr != nil {
			// The job succeeded, but we failed to record it. This is a critical state.
			// Requires monitoring and manual intervention.
			return true, fmt.Errorf("job succeeded, but couldn't update status: %w", updateErr)
		}
	}

	if err := tx2.Commit(ctx); err != nil {
		return true, fmt.Errorf("failed to commit final state: %w", err)
	}

	return true, nil
}

func processPayload(payload json.RawMessage) error {
	// Simulate work
	time.Sleep(100 * time.Millisecond)
	// In a real scenario, this could return an error.
	return nil
}

Advanced Consideration: The Two-Transaction Update

Notice the use of two separate transactions. The first one is very short: it finds, locks, and updates the job's status to processing. By committing this transaction quickly, we release the row-level lock on the jobs table. This is a critical optimization for jobs that take a long time to execute (e.g., seconds or minutes). If we held the row lock for the entire duration of the job, we would increase contention on the table, even with SKIP LOCKED, and hold the transaction open for an unacceptably long time.

Pros of `SKIP LOCKED`

* Simplicity and Atomicity: The core logic is a single, atomic SQL query. It's difficult to get wrong, and the database guarantees that two workers will never pick up the same job.

* Performance: It's extremely fast. The database engine handles the locking and skipping at a very low level, avoiding application-level round-trips.

* Robustness: It's tightly integrated with PostgreSQL's transaction semantics. If a worker process dies mid-transaction, the lock is automatically released upon transaction rollback, and the job becomes available again.

Cons and Edge Cases

"Hot Row" Contention: If your ORDER BY clause is static (e.g., ORDER BY priority DESC, run_at ASC), all workers will attempt to lock the same few rows* at the top of the index. While SKIP LOCKED prevents them from blocking, they are still competing for the same index pages and creating CPU overhead. This can become a bottleneck under very high worker counts.

* Locking Overhead: While efficient, row-level locks are not free. They generate WAL (Write-Ahead Log) traffic and consume memory. In systems with tens of thousands of transactions per second, this can be a measurable overhead.

* Limited Flexibility: The lock is tied directly to the row. You cannot use this mechanism to implement more complex locking strategies, such as "only one job for tenant_id 123 can run at a time, across all workers."

Pattern 2: Advisory Locks - The Cooperative Concurrency Primitive

Advisory locks are a fundamentally different mechanism. They are managed by PostgreSQL, but they are not tied to any table or row. They are a cooperative locking system where your application code decides what a lock means. The common pattern for job queues is to use the job's primary key (id) as the lock key.

The database does not enforce these locks automatically. If one process holds an advisory lock on the integer 12345, another process is still free to UPDATE or DELETE the job with id = 12345. Your application logic must be disciplined and always check for the lock before acting.

Mechanism Deep Dive

The flow for a worker using advisory locks is more complex and involves multiple steps:

Production Implementation (Go)

This implementation is more intricate and highlights the need for careful state management.

// processJobsWithAdvisoryLock is a worker function using the advisory lock pattern.
func processJobsWithAdvisoryLock(ctx context.Context, db *pgxpool.Pool, queueName string) (processed bool, err error) {
	// Step 1: Find a candidate job ID without locking.
	var jobID int64
	pollQuery := `
		SELECT id FROM jobs
		WHERE queue_name = $1 AND status = 'pending' AND run_at <= NOW()
		ORDER BY run_at ASC
		LIMIT 1`
	err = db.QueryRow(ctx, pollQuery, queueName).Scan(&jobID)
	if err == sql.ErrNoRows {
		return false, nil // Queue is empty
	}
	if err != nil {
		return false, fmt.Errorf("failed to poll for job id: %w", err)
	}

	tx, err := db.Begin(ctx)
	if err != nil {
		return false, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback(ctx)

	// Step 2: Attempt to acquire a transaction-scoped advisory lock.
	var locked bool
	if err := tx.QueryRow(ctx, "SELECT pg_try_advisory_xact_lock($1)", jobID).Scan(&locked); err != nil {
		return false, fmt.Errorf("failed to acquire advisory lock: %w", err)
	}

	// Step 3: Handle lock failure.
	if !locked {
		// Another worker got the lock first. This is normal, not an error.
		return false, nil 
	}

	// Step 4: The CRITICAL re-check.
	// We have the lock, but we must verify the job is still in the state we expect.
	var job Job
	refetchQuery := `SELECT id, queue_name, payload, attempts FROM jobs WHERE id = $1 AND status = 'pending'`
	err = tx.QueryRow(ctx, refetchQuery, jobID).Scan(&job.ID, &job.Queue, &job.Payload, &job.Attempts)
	if err == sql.ErrNoRows {
		// The job was processed by another worker between our poll and lock acquisition.
		// This is also normal. Commit transaction to release lock and move on.
		tx.Commit(ctx)
		return false, nil
	}
	if err != nil {
		return false, fmt.Errorf("failed to re-fetch locked job: %w", err)
	}

	// At this point, we have a confirmed lock on a pending job.
	// --- Process the job logic here (can be long-running) ---
	jobErr := processPayload(job.Payload)

	if jobErr != nil {
		_, updateErr := tx.Exec(ctx,
			"UPDATE jobs SET status = 'failed', last_error = $1, finished_at = NOW() WHERE id = $2",
			jobErr.Error(), job.ID)
		if updateErr != nil {
			return true, fmt.Errorf("job failed, but couldn't update status: %w", updateErr)
		}
	} else {
		_, updateErr := tx.Exec(ctx,
			"UPDATE jobs SET status = 'completed', finished_at = NOW() WHERE id = $2",
			job.ID)
		if updateErr != nil {
			return true, fmt.Errorf("job succeeded, but couldn't update status: %w", updateErr)
		}
	}

	if err := tx.Commit(ctx); err != nil {
		return true, fmt.Errorf("failed to commit final state: %w", err)
	}

	return true, nil
}

Pros of Advisory Locks

* Flexibility: This is their killer feature. Because you lock an arbitrary number, you can create sophisticated locking schemes. For example, you could use a hash of a tenant_id to ensure only one job per tenant runs concurrently. pg_try_advisory_xact_lock(hashtext(tenant_id)).

* Lower Table Contention: The initial poll is a non-locking read. The lock itself doesn't place a traditional row or page lock, potentially reducing contention on the jobs table itself, especially if the table is subject to other kinds of writes.

Cons and Edge Cases

* Complexity & Race Conditions: The logic is significantly more complex than SKIP LOCKED. The poll -> lock -> re-fetch sequence is easy to get wrong. Forgetting the re-fetch step will introduce subtle and hard-to-debug race conditions where jobs are missed or processed based on stale state.

* Increased Round-Trips: The process requires at least two, and often three, round-trips to the database for every job processed, which adds latency compared to the single-query SKIP LOCKED approach.

* Stuck Session Locks: It is critical to use transaction-scoped locks (pg_try_advisory_xact_lock). If you use session-scoped locks (pg_try_advisory_lock) and your worker crashes without explicitly releasing the lock, that lock will be held until the database connection is fully terminated, which can be a long time in pooled environments. This will effectively block any job with that ID from ever being processed.

Performance Benchmark & Comparative Analysis

Theory is useful, but empirical data is essential. We ran a benchmark using a Go program with a configurable number of concurrent workers against a PostgreSQL 14 instance. The jobs table was pre-populated with 1 million pending jobs.

Methodology:

* Instance: db.m6g.large on AWS RDS (2 vCPU, 8 GiB RAM)

* Workload: Each job simulates 20ms of processing time.

* Metrics: Jobs processed per second (Throughput).

* Test: We measured the throughput for both patterns across a range of concurrent worker counts.

Analysis of Results

The results are clear: for a standard, high-throughput job queue, SELECT FOR UPDATE SKIP LOCKED is the decisive performance winner.

The reason is fundamental: SKIP LOCKED is a single, highly-optimized, native database operation. The entire find-and-lock process happens inside the database engine without the latency of network round-trips between the application and the server. The Advisory Lock pattern, with its poll -> lock -> re-fetch dance, inherently pays a latency penalty for each job it processes. As concurrency increases, this penalty becomes more pronounced.

The Advisory Lock pattern's throughput is not poor, but it cannot compete with the raw efficiency of SKIP LOCKED for this specific use case.

Final Verdict: Which Pattern Should You Use?

Choosing the right pattern is a matter of applying the right tool for the job. Based on this analysis, the guidance for senior engineers is clear:

Choose SELECT FOR UPDATE SKIP LOCKED if:

* Your primary goal is to build a high-throughput, general-purpose job queue.

* Your locking requirement is simple: "ensure only one worker processes this specific job row at a time."

* You value simplicity, robustness, and maximum performance.

* You can tolerate the two-transaction pattern for long-running jobs to minimize lock duration.

In short, SKIP LOCKED should be your default choice for 95% of database-backed job queue implementations.

Choose Advisory Locks if:

* You have complex, application-defined concurrency requirements that go beyond simple row-level locking. For example:

* "Ensure only one billing-related job for customer_id 500 runs at a time, regardless of which job row it is."

* "Implement a global rate limit of 10 concurrent image processing jobs across the entire system."

* "Create a mutex to protect a non-relational resource, using PostgreSQL as the coordination point."

* You are willing to accept a performance trade-off in exchange for this advanced flexibility.

* You are confident in your team's ability to correctly and rigorously implement the more complex poll -> lock -> re-fetch logic to avoid race conditions.

By understanding the deep mechanics and performance characteristics of both patterns, you can make an informed architectural decision that ensures your system is not only correct and robust but also scalable to meet production demands.