Databases

Building a Reliable PostgreSQL Job Queue: SKIP LOCKED, Partitioning, and Monitoring Without Redis

Ruslan Ismailov Published 14 min read
B

Introduction: When PostgreSQL Beats Redis or a Message Broker

Most teams instinctively reach for Redis or RabbitMQ when it comes to job queues. But in 2026, PostgreSQL is a mature, battle-tested platform with transactional guarantees that Redis simply doesn't offer out of the box. If PostgreSQL is already in your stack, adding a separate broker means: a new point of failure, extra operational overhead, increased infrastructure complexity, and the need to maintain consistency between two separate data stores.

A PostgreSQL queue makes sense in the following scenarios:

  • Jobs are tightly coupled with business data and require atomic operations with the main database.
  • Load is moderate — up to a few thousand jobs per second.
  • Exactly-once or at-least-once delivery semantics with transactional confirmation are required.
  • The team wants to simplify the stack and avoid the operational complexity of Redis Cluster.

In this article, we'll build a production-ready job queue without Redis: using SKIP LOCKED, partitioning, Go and PHP/Laravel workers, Prometheus/Grafana monitoring, and Kubernetes deployment.

The Job Queue Pattern in PostgreSQL: Schema, Statuses, and Indexes

The foundation of the pattern is a jobs table with explicit statuses and pessimistic locking. Here's the base schema:

CREATE TABLE jobs (
    id              BIGSERIAL PRIMARY KEY,
    queue           TEXT NOT NULL DEFAULT 'default',
    payload         JSONB NOT NULL,
    status          TEXT NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending','processing','done','failed')),
    attempts        INT NOT NULL DEFAULT 0,
    max_attempts    INT NOT NULL DEFAULT 3,
    scheduled_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    locked_until    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY LIST (queue);

CREATE TABLE jobs_default PARTITION OF jobs FOR VALUES IN ('default');
CREATE TABLE jobs_email   PARTITION OF jobs FOR VALUES IN ('email');
CREATE TABLE jobs_reports PARTITION OF jobs FOR VALUES IN ('reports');

-- Indexes for efficient polling
CREATE INDEX idx_jobs_default_pending
    ON jobs_default (scheduled_at)
    WHERE status = 'pending';

CREATE INDEX idx_jobs_default_processing
    ON jobs_default (locked_until)
    WHERE status = 'processing';

The scheduled_at field enables delayed job scheduling. locked_until is used to detect stalled workers: if a worker crashes, the job is automatically returned to the queue once the lock TTL expires. The JSONB payload field provides flexibility without requiring schema changes for each new job type.

SELECT ... FOR UPDATE SKIP LOCKED: The Core Mechanism

The main challenge with SQL-based queues is concurrent access to the same job by multiple workers. The classic approach of a separate SELECT followed by an UPDATE creates a race condition. SELECT ... FOR UPDATE SKIP LOCKED, introduced in PostgreSQL 9.5, solves this elegantly and atomically.

Here's how it works: when a worker executes SELECT ... FOR UPDATE, PostgreSQL locks the row. Other workers running the same query with SKIP LOCKED simply skip locked rows instead of waiting for the lock to be released. This turns PostgreSQL into an efficient distributed lock manager for the queue.

-- Atomic job acquisition by a worker
WITH next_job AS (
    SELECT id
    FROM jobs
    WHERE queue = 'default'
      AND status = 'pending'
      AND scheduled_at <= NOW()
    ORDER BY scheduled_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET
    status       = 'processing',
    attempts     = attempts + 1,
    locked_until = NOW() + INTERVAL '5 minutes',
    updated_at   = NOW()
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.*;

The entire query executes within a single transaction. If a worker fails to confirm job completion before locked_until, a separate reaper process returns the job to pending status:

-- Return stalled jobs (reaper)
UPDATE jobs
SET status = 'pending', updated_at = NOW()
WHERE status = 'processing'
  AND locked_until < NOW()
  AND attempts < max_attempts;

Implementing a Go Worker: Polling, Processing, and Acknowledgment

Go is an excellent fit for writing workers: low memory footprint, goroutines for concurrent processing, and a built-in context for graceful shutdown. We'll use pgx as the PostgreSQL driver.

package main

import (
    "context"
    "encoding/json"
    "log"
    "time"

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

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

func acquireJob(ctx context.Context, pool *pgxpool.Pool, queue string) (*Job, error) {
    row := pool.QueryRow(ctx, `
        WITH next_job AS (
            SELECT id FROM jobs
            WHERE queue = $1
              AND status = 'pending'
              AND scheduled_at <= NOW()
            ORDER BY scheduled_at
            LIMIT 1
            FOR UPDATE SKIP LOCKED
        )
        UPDATE jobs
        SET status = 'processing',
            attempts = attempts + 1,
            locked_until = NOW() + INTERVAL '5 minutes',
            updated_at = NOW()
        FROM next_job
        WHERE jobs.id = next_job.id
        RETURNING jobs.id, jobs.queue, jobs.payload
    `, queue)

    var job Job
    err := row.Scan(&job.ID, &job.Queue, &job.Payload)
    if err != nil {
        return nil, err
    }
    return &job, nil
}

func completeJob(ctx context.Context, pool *pgxpool.Pool, id int64) error {
    _, err := pool.Exec(ctx,
        `UPDATE jobs SET status = 'done', updated_at = NOW() WHERE id = $1`, id)
    return err
}

func failJob(ctx context.Context, pool *pgxpool.Pool, id int64) error {
    _, err := pool.Exec(ctx, `
        UPDATE jobs
        SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
            updated_at = NOW()
        WHERE id = $1
    `, id)
    return err
}

func runWorker(ctx context.Context, pool *pgxpool.Pool, queue string) {
    for {
        select {
        case <-ctx.Done():
            return
        default:
        }

        job, err := acquireJob(ctx, pool, queue)
        if err != nil {
            // No jobs available — wait before next poll
            time.Sleep(500 * time.Millisecond)
            continue
        }

        log.Printf("Processing job %d", job.ID)
        if err := processPayload(job.Payload); err != nil {
            log.Printf("Job %d failed: %v", job.ID, err)
            _ = failJob(ctx, pool, job.ID)
            continue
        }
        _ = completeJob(ctx, pool, job.ID)
        log.Printf("Job %d done", job.ID)
    }
}

func processPayload(payload json.RawMessage) error {
    // Business logic for job processing
    return nil
}

func main() {
    ctx, cancel := context.WithCancel(context.Background())
    defer cancel()

    pool, _ := pgxpool.New(ctx, "postgres://user:pass@localhost/db")
    defer pool.Close()

    // Start multiple workers in parallel
    for i := 0; i < 5; i++ {
        go runWorker(ctx, pool, "default")
    }

    // Graceful shutdown on signal...
    select {}
}

Note that when acquireJob returns an error due to no available jobs, the worker performs a short sleep instead of busy-waiting. In production, this value can be moved to configuration and combined with exponential backoff.

Implementing a PHP/Laravel Worker: Custom Driver

Laravel has built-in PostgreSQL support via the database driver, but it uses advisory locks rather than SKIP LOCKED. Let's write a custom driver that uses the correct locking mechanism.

<?php

namespace App\Queue;

use Illuminate\Queue\DatabaseQueue;
use Illuminate\Queue\Jobs\DatabaseJob;
use Illuminate\Support\Facades\DB;

class SkipLockedQueue extends DatabaseQueue
{
    public function pop($queue = null)
    {
        $queue = $this->getQueue($queue);

        return $this->getConnection()->transaction(function () use ($queue) {
            $job = $this->getNextAvailableJobWithSkipLocked($queue);

            if ($job !== null) {
                return new DatabaseJob(
                    $this->container,
                    $this,
                    $job,
                    $this->connectionName,
                    $queue
                );
            }
        });
    }

    protected function getNextAvailableJobWithSkipLocked($queue)
    {
        $job = DB::selectOne("
            WITH next_job AS (
                SELECT id FROM jobs
                WHERE queue = ?
                  AND status = 'pending'
                  AND scheduled_at <= NOW()
                ORDER BY scheduled_at
                LIMIT 1
                FOR UPDATE SKIP LOCKED
            )
            UPDATE jobs
            SET status = 'processing',
                attempts = attempts + 1,
                locked_until = NOW() + INTERVAL '5 minutes',
                updated_at = NOW()
            FROM next_job
            WHERE jobs.id = next_job.id
            RETURNING jobs.*
        ", [$queue]);

        return $job ? (object) $job : null;
    }
}

Register the driver in AppServiceProvider:

<?php

// In AppServiceProvider::boot()
Queue::extend('pgsql_skip_locked', function () {
    return new SkipLockedQueueConnector();
});

Add the connection in config/queue.php:

'pgsql_skip' => [
    'driver'    => 'pgsql_skip_locked',
    'table'     => 'jobs',
    'queue'     => 'default',
    'retry_after' => 300,
],

Now php artisan queue:work --queue=default --connection=pgsql_skip uses native PostgreSQL SKIP LOCKED instead of advisory locks.

Partitioning the Jobs Table: Scaling and Cleanup

Without cleanup, the jobs table grows indefinitely. Partitioning by queue (LIST partitioning, as in the schema above) solves two problems: isolating load between queues and enabling efficient cleanup via DROP PARTITION instead of DELETE.

To clean up completed jobs, we add time-based partitioning with an archive schema:

CREATE TABLE jobs_archive (
    LIKE jobs INCLUDING ALL
) PARTITION BY RANGE (created_at);

CREATE TABLE jobs_archive_2025_q1
    PARTITION OF jobs_archive
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE jobs_archive_2025_q2
    PARTITION OF jobs_archive
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- Move completed jobs to the archive (cron job)
INSERT INTO jobs_archive
SELECT * FROM jobs
WHERE status IN ('done', 'failed')
  AND updated_at < NOW() - INTERVAL '7 days';

DELETE FROM jobs
WHERE status IN ('done', 'failed')
  AND updated_at < NOW() - INTERVAL '7 days';

Dropping an old archive partition is instantaneous and creates no significant load:

-- Drop a quarter's partition without a table lock
DROP TABLE jobs_archive_2025_q1;

For high-load scenarios, you can split the main table by queue + id range, or use pg_partman for automatic partition management.

Monitoring: Metrics, Prometheus, and Grafana

Without monitoring, a queue is a black box. Key metrics for a PostgreSQL queue:

  • Queue depth — number of jobs in pending status per queue.
  • Processing time — average and p95 job processing duration.
  • Failed rate — proportion of jobs in failed status over the last N minutes.
  • Stuck jobs — jobs in processing with an expired locked_until.

SQL queries for metric collection (Go-based exporter or pg_stat_statements):

-- Queue depth by type
SELECT queue, status, COUNT(*) as count
FROM jobs
GROUP BY queue, status;

-- Average processing time (last 10 minutes)
SELECT queue,
       AVG(EXTRACT(EPOCH FROM (updated_at - created_at))) AS avg_processing_sec,
       PERCENTILE_CONT(0.95) WITHIN GROUP (
           ORDER BY EXTRACT(EPOCH FROM (updated_at - created_at))
       ) AS p95_processing_sec
FROM jobs
WHERE status = 'done'
  AND updated_at > NOW() - INTERVAL '10 minutes'
GROUP BY queue;

-- Stuck jobs
SELECT COUNT(*) AS stuck_count
FROM jobs
WHERE status = 'processing'
  AND locked_until < NOW();

We collect metrics via a custom Prometheus exporter and visualize them in Grafana. A sample dashboard config might include: a Queue Depth over Time chart, an alert when pending > 1000 for more than 5 minutes, and a heatmap of processing time distribution across queues.

For Prometheus integration, use postgres_exporter with custom queries via queries.yaml:

pg_job_queue_depth:
  query: |
    SELECT queue, status, COUNT(*) as count
    FROM jobs GROUP BY queue, status
  metrics:
    - queue:
        usage: LABEL
    - status:
        usage: LABEL
    - count:
        usage: GAUGE
        description: Number of jobs by queue and status

PostgreSQL vs Redis Queues: Pros and Cons

A PostgreSQL queue is not a silver bullet. Here's an honest comparison:

PostgreSQL advantages:

  • Transactionality: jobs and business data are updated atomically within a single transaction.
  • No additional infrastructure component — fewer points of failure.
  • Full SQL support for analytics, monitoring, and debugging.
  • Durability guarantees (WAL) out of the box, with no need to configure Redis AOF/RDB.
  • Partitioning and indexes for efficient management of large queues.

PostgreSQL limitations:

  • Lower throughput than Redis: beyond ~10k jobs/sec, PostgreSQL starts to fall behind.
  • Polling adds load to the database; aggressive polling is visible in CPU usage.
  • No built-in pub/sub or fan-out — a separate mechanism is needed (LISTEN/NOTIFY).
  • VACUUM pressure: frequent status UPDATEs generate dead tuples.

Bottom line: if you're dealing with thousands of jobs per second, Redis or Kafka are justified. For the majority of business applications, a PostgreSQL queue is a simpler, more reliable, and more cost-effective solution.

Deploying Workers in Kubernetes: Deployment vs Job

In Kubernetes, queue workers are deployed as a Deployment (not a Job), since they need to run continuously rather than execute a one-off task. Here's a sample manifest for a Go worker:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: queue-worker
  namespace: production
spec:
  replicas: 3
  selector:
    matchLabels:
      app: queue-worker
  template:
    metadata:
      labels:
        app: queue-worker
      annotations:
        prometheus.io/scrape: "true"
        prometheus.io/port: "9090"
    spec:
      containers:
      - name: worker
        image: myapp/queue-worker:1.2.0
        env:
        - name: DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: db-secret
              key: url
        - name: QUEUE_NAME
          value: default
        - name: WORKER_CONCURRENCY
          value: "5"
        resources:
          requests:
            cpu: 100m
            memory: 64Mi
          limits:
            cpu: 500m
            memory: 256Mi
        livenessProbe:
          httpGet:
            path: /healthz
            port: 9090
          initialDelaySeconds: 5
          periodSeconds: 10
      terminationGracePeriodSeconds: 60

Key aspects of Kubernetes deployment:

  • terminationGracePeriodSeconds: gives the worker time to finish the current job before the pod is stopped. The worker should intercept SIGTERM and stop polling after the current iteration completes.
  • HPA with custom metrics: configure horizontal autoscaling based on queue depth via the Prometheus Adapter — replicas are automatically added as pending jobs grow.
  • PodDisruptionBudget: ensures a minimum of 2 replicas during rolling updates.
  • Kubernetes Job: used for the reaper process, scheduled via a CronJob every 5 minutes to return stalled jobs to the queue.
apiVersion: batch/v1
kind: CronJob
metadata:
  name: queue-reaper
spec:
  schedule: "*/5 * * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: reaper
            image: myapp/queue-reaper:1.0.0
            env:
            - name: DATABASE_URL
              valueFrom:
                secretKeyRef:
                  name: db-secret
                  key: url
          restartPolicy: OnFailure

Conclusion

Building a reliable job queue on PostgreSQL is an entirely achievable goal without introducing additional technologies. The combination of SELECT ... FOR UPDATE SKIP LOCKED, a well-designed schema with partitioning, Go or Laravel workers, and Prometheus/Grafana monitoring delivers a production-ready solution for the vast majority of business use cases.

In 2026, a PostgreSQL queue is a deliberate architectural choice in favor of simplicity, reliability, and transactional integrity. Start with a single queue, measure the load, and only consider Redis or Kafka if you hit a performance ceiling. For 80% of projects, that ceiling will never come.

Technologies

Tags

Ruslan Ismailov

Senior Web / Backend Developer. Senior web/backend developer with 9 years of experience. Stack: PHP, Laravel, PostgreSQL, Redis, Docker, Kubernetes, REST, microservices, CI/CD. More about me →