Building Fault-Tolerant Background Job Queues Natively in Postgres

Every time a new project needs to run background tasks like sending emails, processing uploads, or syncing third-party data, the default response is to add new infrastructure. We spin up a Redis instance, install a library like BullMQ, Sidekiq, or Celery, and connect our application.

But adding another piece of infrastructure to your stack is a trade-off. It introduces a new point of failure, requires separate monitoring, increases deployment complexity, and presents the dual-write problem.

The dual-write problem occurs when you try to update your primary database and write to an external queue in the same operation. If your database transaction commits but the write to Redis fails, your background job is lost. If you write to Redis first and the database transaction rolls back, your worker processes a job for data that does not exist. Solving this in distributed systems requires complex patterns like the transactional outbox pattern or two-phase commits.

You can avoid this complexity entirely by keeping your queue in the database you already have. Postgres is highly capable of acting as a reliable, transactional queue. By using native SQL features, you can build a fault-tolerant background job queue that guarantees atomic operations and runs on your existing database setup.

The Engine: SELECT FOR UPDATE SKIP LOCKED

Historically, using a relational database as a queue was considered an anti-pattern. If multiple worker processes tried to fetch jobs simultaneously, they would lock the table or conflict on row locks. This caused database contention, high CPU usage, and slow performance. Workers spent more time waiting for locks to release than processing jobs.

Postgres 9.5 introduced SKIP LOCKED. This clause changed how we handle concurrent row access.

When a worker queries the jobs table to claim a task, it locks the selected row using FOR UPDATE. If another worker runs the same query concurrently, Postgres detects the lock, skips over those locked rows, and returns the next available row.

Workers no longer block each other. They find work instantly, allowing you to run dozens of concurrent workers against a single Postgres table without performance degradation.

Designing the Schema

To build a production-grade queue, you need a table design that tracks the lifecycle of each job, handles retries, and records errors. Here is a schema that covers these requirements:

CREATE TYPE job_status AS ENUM ('queued', 'processing', 'failed', 'completed');
 
CREATE TABLE background_jobs (
    id BIGSERIAL PRIMARY KEY,
    queue_name VARCHAR(255) NOT NULL DEFAULT 'default',
    payload JSONB NOT NULL,
    status job_status NOT NULL DEFAULT 'queued',
    locked_at TIMESTAMP WITH TIME ZONE,
    run_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    attempts INT NOT NULL DEFAULT 0,
    max_attempts INT NOT NULL DEFAULT 3,
    last_error TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

The payload column uses the JSONB data type, allowing you to pass arbitrary structured data to your workers. The run_at column determines when a job should execute, which is useful for scheduling delayed jobs or implementing retry backoffs.

To keep query speeds fast as the table grows, you need an index. A partial index is highly effective here because workers only care about jobs that are ready to run:

CREATE INDEX idx_jobs_queued_run_at 
ON background_jobs (queue_name, run_at) 
WHERE status = 'queued';

This index is small because it excludes completed and failed jobs. As workers process jobs, the index size remains stable, preventing performance degradation over time.

Fetching Jobs Safely

The query to fetch and claim a job must be atomic. It needs to find the next available job, lock it so other workers cannot claim it, and mark it as processing:

UPDATE background_jobs
SET status = 'processing',
    locked_at = NOW(),
    attempts = attempts + 1,
    updated_at = NOW()
WHERE id = (
    SELECT id
    FROM background_jobs
    WHERE status = 'queued'
      AND run_at <= NOW()
      AND queue_name = 'default'
    ORDER BY run_at ASC, id ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

The subquery finds the oldest queued job that is scheduled to run. The FOR UPDATE SKIP LOCKED clause locks that row. The outer UPDATE immediately changes its status to processing and records the lock time. The database returns the id and payload to the worker.

If no jobs are ready, the query returns an empty result set. The worker can then sleep for a short period before trying again.

The Worker Lifecycle

A common mistake when building database-backed queues is keeping the database transaction open while the worker executes the job.

If a job involves calling a slow external API that takes 10 seconds to respond, keeping the database transaction open for those 10 seconds holds a connection active in your pool. If you have 20 workers doing this, you will quickly exhaust your database connection pool and block other parts of your application.

Your worker code should follow a state machine pattern where database transactions are short-lived:

1. Claim the job: Run the fetch query, which commits immediately and releases the row lock, leaving the status as processing.
2. Execute the work: Run the job logic in your application memory, outside of any database transaction.
3. Record the outcome: Open a new transaction to mark the job as either completed or failed.

If the job succeeds, you can update its status:

UPDATE background_jobs
SET status = 'completed',
    updated_at = NOW()
WHERE id = :job_id;

Alternatively, you can delete the row entirely if you do not need an execution history.

Handling Failures and Retries

Jobs fail for many reasons, such as network timeouts or transient API errors. A resilient queue must support retries with exponential backoff.

When a job execution fails, the worker updates the job status back to queued and schedules it to run again in the future:

UPDATE background_jobs
SET status = CASE 
                WHEN attempts >= max_attempts THEN 'failed'::job_status
                ELSE 'queued'::job_status
             END,
    run_at = CASE 
                WHEN attempts >= max_attempts THEN NOW()
                ELSE NOW() + (INTERVAL '10 seconds' * POWER(2, attempts))
             END,
    last_error = :error_message,
    updated_at = NOW()
WHERE id = :job_id;

This query calculates the next execution time dynamically. The first retry runs after 20 seconds, the second after 40 seconds, and so on.

If the job exceeds its maximum attempts, the status changes to failed. This acts as a built-in Dead Letter Queue. You can monitor your database for rows with a failed status and set up alerts for your engineering team to investigate.

Recovering from Crashes (Orphaned Jobs)

If a worker process crashes, runs out of memory, or loses power while executing a job, the job status remains stuck in processing indefinitely. Because the transaction that claimed the job was already committed, Postgres cannot automatically roll it back.

To handle these orphaned jobs, you need a sweeper process. This is a simple script that runs on a cron schedule (for example, every minute) and looks for jobs that have been locked for too long:

UPDATE background_jobs
SET status = 'queued',
    run_at = NOW() + INTERVAL '1 minute',
    last_error = 'Worker timeout detected',
    updated_at = NOW()
WHERE status = 'processing'
  AND locked_at < NOW() - INTERVAL '10 minutes';

Any job that has been processing for more than 10 minutes is reset to queued and rescheduled to run a minute later. Adjust this timeout value based on the maximum expected execution time of your longest-running job.

Low Latency with LISTEN and NOTIFY

If your workers poll the database every second to check for new jobs, they generate unnecessary database traffic. If you lower the polling interval to 100 milliseconds to achieve lower latency, the constant queries can drive up database CPU usage.

Postgres provides a pub/sub mechanism called LISTEN and NOTIFY that solves this. You can set up a database trigger that sends a notification whenever a new job is inserted:

CREATE OR REPLACE FUNCTION notify_job_added()
RETURNS TRIGGER AS `
BEGIN
    PERFORM pg_notify('job_created', NEW.queue_name);
    RETURN NEW;
END;
` LANGUAGE plpgsql;
 
CREATE TRIGGER trigger_job_added
AFTER INSERT ON background_jobs
FOR EACH ROW
EXECUTE FUNCTION notify_job_added();

Your worker processes can connect to Postgres and run the LISTEN job_created; command. Instead of polling, the workers block and wait for a notification.

When a job is inserted, Postgres sends a lightweight signal to the workers. The workers wake up, run the fetch query, process all available jobs until the queue is empty, and then return to a waiting state. This provides low latency without the overhead of continuous polling.

Managing Table Bloat

Postgres uses Multi-Version Concurrency Control (MVCC) to handle concurrent data access. When you update a row, Postgres does not modify the data in place. Instead, it writes a new version of the row and marks the old one as dead.

In a high-throughput queue where jobs are constantly inserted, updated, and deleted, you will generate a large volume of dead rows (tuples). If these dead rows are not cleaned up, your queries will slow down.

To keep your queue running smoothly:

1. Tune Autovacuum: Ensure the Postgres autovacuum daemon is configured to clean up the background_jobs table frequently. You can set aggressive autovacuum settings specifically for this table:

   ALTER TABLE background_jobs SET (
       autovacuum_vacuum_scale_factor = 0.05,
       autovacuum_vacuum_threshold = 100
   );

   This forces autovacuum to run whenever 5% of the rows plus 100 rows have changed, keeping table bloat under control.

2. Prune Completed Jobs: If you do not delete completed jobs immediately, you need a pruning script to delete old records:

   DELETE FROM background_jobs
   WHERE status = 'completed'
     AND updated_at < NOW() - INTERVAL '3 days';

   Running this daily keeps the table size small and manageable.

Scaling Limits and Trade-offs

A Postgres queue is a great choice for many applications, but it has limits.

If your application processes tens of thousands of jobs per second, the disk write overhead of Postgres (writing to the Write-Ahead Log and updating indexes) will eventually saturate your disk I/O. Relational databases are designed for data durability and complex queries, not high-frequency transient data storage.

If your throughput is under 1,000 jobs per second, Postgres will handle the load easily while keeping your infrastructure stack simple. You get transactional safety, zero extra infrastructure costs, and standard SQL tooling. When your scale outgrows these limits, you can transition to a dedicated message broker like RabbitMQ or AWS SQS with a clear understanding of why you are making the move.