Build a PostgreSQL Job Queue with SKIP LOCKED

In this post we will review how to use PostgreSQL as a background job queue. We will use FOR UPDATE SKIP LOCKED to let multiple workers claim jobs without waiting for the same row, and add leases so unfinished work can be recovered after a worker crashes.

Assume our application needs to generate reports. The HTTP request should save the report request and return quickly. Background workers will generate the reports independently.

If we already use PostgreSQL, a table can be enough to start. However a queue needs more than a SELECT followed by a DELETE. We need to decide who owns each job, when it can be retried, and what happens if the worker disappears.

The Job Claiming Problem

Let's assume two workers execute the same query:

SELECT job_id, payload
FROM background_job
WHERE status = 'queued'
ORDER BY job_id
LIMIT 1;

Both can receive the same job. Updating its status afterward does not fix the race unless the claim itself is coordinated.

We can lock the selected row using FOR UPDATE. However without SKIP LOCKED, another worker can wait for the first row even when other jobs are available.

SKIP LOCKED tells PostgreSQL to skip rows it cannot immediately lock. This makes it useful for distributing queue work among concurrent consumers. It is not appropriate for a normal query that needs a complete, consistent view of the matching data.

Create the Queue Table

The following examples use PostgreSQL SQL. The $1 and $2 values are parameters supplied by the application through its database driver.

CREATE TABLE background_job (
    job_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'queued'
        CHECK (status IN ('queued', 'running', 'done', 'failed')),
    available_at timestamptz NOT NULL DEFAULT now(),
    lease_until timestamptz,
    lease_version bigint NOT NULL DEFAULT 0,
    attempts integer NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz
);

CREATE INDEX background_job_queued_idx
ON background_job (available_at, job_id)
WHERE status = 'queued';

CREATE INDEX background_job_running_idx
ON background_job (lease_until, job_id)
WHERE status = 'running';

The payload contains the information required to perform the job. In a real application I prefer keeping it small, for example a report ID and its parameters, rather than a large document.

available_at enables delayed retries. lease_until defines how long a worker owns a running job. lease_version changes every time the job is claimed, so an old worker cannot complete a newer worker's claim.

Insert a Job

INSERT INTO background_job (payload)
VALUES ('{"report_id": "report-123"}'::jsonb)
RETURNING job_id;

If a business change and its background job must both be saved, insert them in the same database transaction. For example, create the report request and its queue row together.

This prevents a failure between saving the request and scheduling the work. The worker can see the job after the transaction commits.

Claim a Job Atomically

The worker executes the following statement:

WITH candidate AS (
    SELECT job_id
    FROM background_job
    WHERE
        (status = 'queued'
            AND available_at <= statement_timestamp())
        OR
        (status = 'running'
            AND lease_until <= statement_timestamp())
    ORDER BY available_at, job_id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE background_job AS job
SET
    status = 'running',
    lease_until = statement_timestamp() + interval '60 seconds',
    lease_version = job.lease_version + 1,
    attempts = job.attempts + 1
FROM candidate
WHERE job.job_id = candidate.job_id
RETURNING
    job.job_id,
    job.payload,
    job.lease_version,
    job.attempts;

The selected row is locked and updated within one statement. Other workers skip it while the claim transaction holds the lock.

Notice that the query also accepts running jobs whose leases expired. This is how another worker recovers work after a crash. Each new claim increments lease_version, including a recovered claim.

If no eligible job is available, the statement returns no rows. The worker waits before polling again. Add some jitter to the polling delay so workers do not all wake up together.

Run this statement in autocommit mode, or commit its short transaction immediately. The claim must be committed before the worker starts the report generation.

Do Not Keep the Transaction Open While Working

A tempting implementation is to select and lock a job, generate the report, then commit.

This holds a database connection and a row lock throughout the slow operation. Long transactions also interfere with database maintenance and make failure handling harder.

Instead, the flow is:

1. Claim the job and commit.
2. Perform the work outside the claim transaction.
3. Mark the job as completed using its claim version.

After step 1 the row lock is gone. The running status and lease now represent ownership. SKIP LOCKED coordinates the short claim; it does not protect the entire execution.

Complete Only the Current Claim

After generating the report, the worker executes:

UPDATE background_job
SET
    status = 'done',
    lease_until = NULL,
    finished_at = statement_timestamp()
WHERE
    job_id = $1
    AND lease_version = $2
    AND status = 'running'
    AND lease_until > statement_timestamp()
RETURNING job_id;

$1 is the job ID, and $2 is the lease version returned when this worker claimed it.

The application must check whether a row was returned. No row means that this completion was not accepted. The worker might have exceeded its lease, or another worker might already own a newer claim.

The lease check deliberately prevents completion after expiration, even if another worker has not reclaimed the job yet. Treat this as lost ownership, rather than silently reporting success.

Crash Recovery Does Not Mean Exactly Once

Assume the worker sends an email, then crashes before marking the job as done.

The lease expires. Another worker claims the job and sends the email again.

The queue cannot determine whether the first email was sent. This design provides at-least-once processing attempts, so the job handler must tolerate retries.

Use a stable idempotency key derived from the job or business operation. For an external API, use its idempotency support when available. For database changes, enforce a unique business key and commit the effect together with the guarded completion update when both live in the same database.

Do not use lease_version as the idempotency key. It changes on every attempt, while the business operation must retain the same identity.

The version check protects the queue row from stale workers. It does not stop a stale worker from calling an external service. That requires cooperation from the destination, idempotency, or a stronger fencing mechanism supported by that destination.

Choose a Lease Duration

The example uses a 60-second lease to keep the SQL readable. Production lease durations and polling intervals should be configurable.

If a job can take longer than its lease, renew it periodically:

UPDATE background_job
SET lease_until = statement_timestamp() + interval '60 seconds'
WHERE
    job_id = $1
    AND lease_version = $2
    AND status = 'running'
    AND lease_until > statement_timestamp()
RETURNING job_id;

Again, no returned row means ownership was lost. Stop further work where possible and do not acknowledge the old claim. Cancellation cannot undo a request that already reached an external system.

Renew well before expiration and allow for scheduling delays and temporary database latency. A very short lease causes unnecessary duplicate attempts. A very long lease delays recovery after a crash.

Retries and Failed Jobs

For a retryable error, the current owner can release the job with a delay:

UPDATE background_job
SET
    status = 'queued',
    available_at = statement_timestamp() + interval '30 seconds',
    lease_until = NULL
WHERE
    job_id = $1
    AND lease_version = $2
    AND status = 'running'
    AND lease_until > statement_timestamp()
RETURNING job_id;

Use bounded exponential backoff with jitter in production, and distinguish transient failures from invalid jobs that will never succeed. Avoid recording secrets or entire sensitive payloads in error messages.

The example claim query intentionally has no retry limit. Before using it in production, choose a maximum number of attempts and move exhausted jobs to failed. That policy must cover expired leases as well as errors caught by a live worker; otherwise a repeatedly crashing job can be reclaimed forever.

For example, a cleanup process can mark eligible queued or expired running jobs as failed once attempts reaches the configured limit, while the claim query excludes those jobs. Never fail a job merely because its attempt count reached the limit while its current lease is still valid.

Keep failed jobs available for investigation and explicit replay. A replay must preserve the business operation's idempotency rules.

What About Processing Order?

ORDER BY determines the preference for claiming eligible jobs. With SKIP LOCKED, a locked earlier job can be skipped, and concurrent workers can finish in any order.

This is suitable for independent report requests. It is not enough when events from the same conversation or customer must be processed sequentially.

For per-conversation ordering, store a conversation key and a sequence value defined by the producer. Only the earliest unfinished job for a key should be eligible for claiming. A NOT EXISTS condition can exclude candidates that have an earlier unfinished job with the same key.

Keep the eligibility rule on lease recovery too. An expired first job must be retried before a later job becomes eligible.

Also decide what a permanently failed first job means: block the conversation for investigation, or explicitly allow later jobs to continue. This is a business decision, not something SKIP LOCKED resolves.

Notice that an identity column alone does not guarantee business event order or transaction commit order. If ordering matters, define it at ingestion rather than assuming the generated job IDs express it.

Bound the Workers and the Backlog

Each worker should claim only the work it can start. Do not claim thousands of jobs and leave them waiting in memory while their leases expire.

Use a fixed worker count and consider the total across all application replicas. Ten pods with twenty workers can execute two hundred jobs at the same time.

A database queue also needs a backlog policy. Persistent storage is finite. Bound payload sizes, monitor queued bytes and job count, and define admission behavior when the backlog becomes too large.

Keep transactions short and monitor slow claim queries. The OR condition for ready and expired jobs is convenient for this example, but its execution plan should be checked against realistic queue size and traffic. Larger queues may benefit from a separate bounded lease-recovery process and a simpler queued-job claim.

Completed rows must also be removed or archived in bounded batches. Frequent status updates and deletes create dead tuples, so monitor autovacuum, table growth, and index growth.

Measure and Verify

Useful measurements include queue depth, age of the oldest ready job, execution duration, retry count, expired leases, failed jobs, and database time spent claiming work.

The age of the oldest ready job is particularly useful. A queue can have a modest number of jobs while one important job waits for a long time.

Before relying on the queue, verify the failure paths:

1. Two workers claiming concurrently receive different jobs.
2. A locked candidate does not prevent another worker from claiming an eligible row.
3. A worker crash leaves a job recoverable after lease expiration.
4. A stale worker cannot complete, renew, or release a newer claim.
5. Retrying after an external side effect does not repeat the business operation.
6. An exhausted job eventually becomes failed, including after repeated crashes.
7. If ordering is required, a later event cannot run while an earlier event remains unfinished.

When Is PostgreSQL Enough?

A PostgreSQL queue is useful when the application already depends on PostgreSQL, jobs fit its capacity, and creating a job in the same transaction as business data simplifies correctness.

It also means queue traffic competes with application queries for database resources. A dedicated broker can be a better choice for high message volume, fan-out, independent scaling, or messaging features that would otherwise require substantial custom code.

Final Note

FOR UPDATE SKIP LOCKED solves one specific problem: workers can claim different rows without waiting behind the same locked candidate.

A reliable background queue also needs short transactions, leases, ownership checks, idempotent handlers, bounded retries, and backlog monitoring. Once these rules are explicit, PostgreSQL can provide a practical starting point without adding another service to the deployment.