API & Backend
PostgreSQL job queues with SKIP LOCKED and expiring leases
PostgreSQL’s FOR UPDATE SKIP LOCKED lets a job-queue worker claim an available row while other workers hold locks. A lease makes an abandoned job eligible again, and a generation check prevents its previous worker from marking the reclaimed job complete. Each mechanism addresses a different failure in the worker’s lifecycle.
The difficult case is a worker that pauses after claiming a job and resumes after its lease expires. Its replacement may already be processing the same job. This PostgreSQL 18.6 walkthrough reproduces that overlap at the database level: hold a lock, claim another row, expire a lease, reclaim the job, and reject the old generation’s completion update. External effects still need destination-side protection against duplicate or stale work.
The PostgreSQL example and test runner are available with the recorded results.
Run the PostgreSQL queue lab
Before you start:
- Install Python 3.11 or newer, Git, and Docker. Confirm the Docker engine is running with
docker info. - Run the commands from the repository root after cloning. The examples use pinned container versions and download them on the first run.
- Use a local development machine with room for disposable containers. The harness removes the resources it creates.
The repository contains the harness and SQL files. The harness starts an isolated PostgreSQL container without publishing its database port, waits for readiness, executes the assertions, and removes the container. It does not connect to an existing database. The supplied password is only a disposable local fixture.
git clone https://github.com/emminextechdocs/developer-examples.git
cd developer-examples
python3 deep-dives/walkthrough.py postgresKeep the resulting deep-dives/evidence/postgres.json. It records commands, outputs, and assertions from your execution. Names and timings can differ between runs; the important results are the claimed job IDs, their generations, and the number of rows changed by each completion attempt. This is a concurrency correctness experiment, not a throughput benchmark.
walkthrough.py runs the lab and prints selected commands and their responses alongside the assertions. Open the full-resolution capture.
Job allocation, ownership, and completion
Allocation answers which row a worker may claim now. Ownership answers whether that claim is still current. Completion answers whether the owning worker has durably finished the intended operation. A single boolean cannot represent all three: a running flag alone cannot explain whether a worker is alive, stuck, or replaced.
Our state machine has ready, running, and done. Every successful claim increments generation and assigns lease_until. A worker receives both the job ID and generation. Subsequent writes must present that generation. The SQL enforces these application rules.
Keep external work outside the claim transaction. PostgreSQL holds row locks until transaction end, so leaving a transaction open during an HTTP call would retain the database lock throughout that call. The official locking documentation describes that lifetime and the conflicts between competing row locks.
Step 1: Create the job schema and partial indexes
For the command-by-command version, open a Bash session and run these commands from the cloned repository root. This starts a fresh database, so it is independent of the automated run above.
bash
source deep-dives/postgres/session.sh
psql_lab < deep-dives/postgres/schema.sqlsession.sh creates a uniquely named PostgreSQL 18.6 container and waits for a successful TCP query. It defines psql_lab with ON_ERROR_STOP=1, so SQL errors stop the sequence, and registers cleanup when this Bash session exits. Keep the session open for the following steps. schema.sql contains:
The schema is intentionally small enough to inspect. generation starts at zero and changes only when a claim succeeds. The payload holds the business input, while the other columns describe execution. A real system might add attempt limits, scheduling, error classification, and tenant ownership, but those additions should have explicit transition rules.
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
state text NOT NULL DEFAULT 'ready' CHECK (state IN ('ready','running','done')),
generation bigint NOT NULL DEFAULT 0,
lease_until timestamptz,
payload jsonb NOT NULL
);
CREATE INDEX jobs_ready ON jobs (id) WHERE state='ready';
CREATE INDEX jobs_expired ON jobs (lease_until, id) WHERE state='running';
INSERT INTO jobs(payload) VALUES ('{"order_id":"ord-1"}'), ('{"order_id":"ord-2"}');Check the fields that govern recovery:
statelimits the row to ready, running, or done.generationdistinguishes successive claims for the same job.lease_untildetermines when a running job becomes eligible for another claim.payloadcontains the order input; it does not determine ownership.
The partial indexes cover new jobs and running jobs ordered by lease expiry. Check whether the planner uses them for this combined claim query. PostgreSQL can use a partial index only when the planner can establish that the query implies its predicate. See the partial-index rules.
Do not put a moving condition such as “expired right now” into the index definition as though it were a fixed membership rule. Keep time comparison in the query. For a larger queue, examine separate claim paths for ready and expired jobs, then measure the resulting plans against representative data rather than assuming two indexes remove every scan.
Step 2: Read the atomic claim query
The candidate query finds one eligible row, orders candidates by ID, and locks the selected row. The outer update changes ownership and returns the identifiers the worker needs. There is no application round trip between selecting a candidate and writing its new state.
The claim is saved in deep-dives/postgres/claim.sql. Read it now; the next step runs it while another session holds a lock.
WITH candidate AS (
SELECT id FROM jobs
WHERE state='ready' OR (state='running' AND lease_until<clock_timestamp())
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1
)
UPDATE jobs AS j
SET state='running', generation=j.generation+1,
lease_until=clock_timestamp()+interval '30 seconds'
FROM candidate
WHERE j.id=candidate.id
RETURNING j.id,j.generation;FOR UPDATE SKIP LOCKED skips row-level locks that cannot immediately be acquired. PostgreSQL explicitly warns that this produces an inconsistent view and identifies queue-like consumers as a suitable use. It does not mean “skip every kind of lock,” and it is not a general replacement for transaction isolation. The SELECT reference documents both limitations.
The candidate relation returns at most one row, and jobs.id is a primary key. Consequently, each target row has at most one matching candidate. That matters because an UPDATE ... FROM with multiple source matches can leave the selected source row unspecified. The UPDATE reference explains the distinction. Preserve the unique join if you extend this query.
Step 3: Test two competing database sessions
Run the lock holder in the background, then wait until it reaches pg_sleep. That wait makes the ordering observable instead of relying on a guessed startup delay.
psql_lab -c "SET application_name='manual-locker'; BEGIN; SELECT id FROM jobs WHERE id=1 FOR UPDATE; SELECT pg_sleep(5); ROLLBACK;" >/dev/null &
locker=$!
locked=false
for attempt in {1..50}; do
if [ "$(psql_lab -c "SELECT count(*) FROM pg_stat_activity WHERE application_name='manual-locker' AND wait_event='PgSleep';")" = 1 ]; then
locked=true
break
fi
sleep 0.1
done
"$locked" || { echo 'Lock holder did not reach its wait' >&2; exit 1; }
psql_lab < deep-dives/postgres/claim.sql
wait "$locker"
psql_lab < deep-dives/postgres/claim.sqlThe two claims print:
2|1
1|1The first number is the job ID; the second is its new generation. Execute this block once in the fresh session. Repeating a claim changes the database state, so rerunning only the last line is a different experiment.
The harness opens a separate session, locks job 1, and keeps its transaction open briefly. It waits until PostgreSQL reports that session sleeping before issuing the claim from another session. The second session starts its claim only after the first holds the lock.
The second session must return 2|1: job 2, generation 1. After the locking session rolls back, another claim returns job 1. Together, these assertions distinguish skipping a locked candidate from incorrectly treating the first candidate as permanently unavailable.
This test does not prove fairness. A job that is repeatedly locked when workers inspect it can be skipped repeatedly. Ordering by ID influences selection among eligible unlocked rows; it does not promise global FIFO execution across concurrent transactions. If strict ordering is a business requirement, define the ordering boundary before choosing this architecture.
Step 4: Check how the lease uses database time
The claim compares the lease with clock_timestamp() and calculates the next expiry from the same database clock. This avoids asking different application hosts to agree on the current time for these SQL decisions. It does not eliminate clock changes or make a lease a proof that a worker has stopped.
PostgreSQL distinguishes transaction-start time from wall-clock time. now() is tied to transaction start; clock_timestamp() can change during a statement. That distinction matters when transactions remain open, even though our claim is intentionally short. The date and time reference defines these functions.
Thirty seconds is a fixture policy, not a recommended universal lease duration. Choose a duration using observed processing time, renewal behavior, and acceptable recovery delay. A shorter lease makes abandoned work eligible sooner while increasing the chance that a slow but active worker overlaps its replacement. A longer lease makes that overlap less frequent at the cost of waiting longer after failure.
Step 5: Reject completion from a stale worker
In the same Bash session, expire job 1 and reclaim it:
psql_lab -c "UPDATE jobs SET lease_until=clock_timestamp()-interval '1 second' WHERE id=1;"
psql_lab < deep-dives/postgres/claim.sqlExpect 1|2. Now submit a completion for each generation:
for generation in 1 2; do
psql_lab -c "WITH completed AS (
UPDATE jobs SET state='done'
WHERE id=1 AND state='running' AND generation=$generation
AND lease_until>clock_timestamp()
RETURNING id
) SELECT count(*) FROM completed;"
doneRun this within the new 30-second lease. It prints 0, then 1: the old generation changes no rows, while the current one completes the job. If both results are zero after a long pause, the second lease may also have expired. Start a fresh session rather than changing the ownership predicate to force success.
When finished, run exit to remove this session’s container. For a repeatable run with the same checks and automatic cleanup, use bash deep-dives/postgres/manual.sh from the repository root.
After job 1 is claimed, the test moves its expiry into the past. This simulates lease expiry without a thirty-second wait; the worker process is not killed. The next claim must return job 1 with generation 2.
The first worker’s completion update includes generation 1. It changes zero rows. The second worker’s update includes generation 2, the running state, and an unexpired lease. It changes one row. Checking affected-row counts distinguishes an accepted completion from a successful statement that updated nothing.
Handle a zero-row completion explicitly. It can mean the lease expired, ownership changed, or the job is no longer running. Do not retry that update blindly with a newly fetched generation: doing so would let an obsolete worker impersonate the current owner. Return to the worker’s recovery policy and inspect the current record.
Protect external effects separately
A generation condition protects the database update that contains it. It does not automatically protect an email, payment request, object upload, or write to another database. An old worker can still reach an external service after its lease expires unless that service has a separate way to reject stale or duplicate effects.
For database-only work, consider performing the business mutation and generation-checked completion in one short transaction. Design its statements so that a rejected ownership check causes the transaction to roll back. The required transaction conditions depend on the business tables; adding a completion predicate after an already committed mutation would be too late.
For external work, use an application-level idempotency identifier that survives retries, or a destination that can enforce a fencing value. These are design requirements to evaluate, not capabilities demonstrated by this fixture. Our webhook idempotency walkthrough examines the related boundary between accepting an event and applying its effects.
Set retry limits and monitor job age
The minimal schema allows expired work to be reclaimed indefinitely. Before adopting it for production, decide which failures should retry, how many claims are allowed, and where exhausted jobs go. Keep a durable error record so operators can distinguish invalid input from unavailable dependencies.
Measure claim latency, ready-job age, expired-running count, attempts per job, and rejected completions. Collect these metrics for the queue state machine. They tell different stories: a rising ready-job age can coexist with successful claims, while rejected completions can expose leases shorter than actual processing time.
Do not infer queue health from worker process count alone. An alive worker may be blocked downstream, and a reclaimed job may still have an old worker executing. Tie traces and structured logs to both job ID and generation. Our OpenTelemetry tracing guide provides the instrumentation foundation; add the ownership fields without recording sensitive payloads.
Check query plans and vacuum activity
A queue repeatedly updates rows. PostgreSQL’s MVCC implementation keeps row versions that later need maintenance. The vacuuming guide explains why updates and deletes create work for vacuum. Plan autovacuum and job retention alongside worker capacity.
Use realistic queue depth and state distribution when examining the plan. A table with two rows tells you whether the query is valid, but little about an installation with many completed jobs and a small active set. Archive or remove old records according to your audit requirements, then evaluate the remaining workload.
EXPLAIN ANALYZE executes the statement it measures. For a claiming update, use a disposable database or a transaction you explicitly roll back. The EXPLAIN documentation describes that behavior. Never run a write benchmark against the real queue while assuming explain output is passive inspection.
Recover from rollback and uncertain commits
A claiming transaction can fail before its caller receives a result. Distinguish a known rollback from an uncertain response after a commit. In the first case the transaction did not establish the new ownership. In the second, the database may have established it even though the application never received the returned identifiers.
Design the worker’s recovery path around that uncertainty. It should inspect durable state or wait for the lease policy to make the job eligible again, rather than inventing an ownership token. Logging a request correlation value alongside the claim can help an operator connect a failed client call with database activity, but the log itself should not become the ownership authority.
Also test cancellation while the candidate is locked. Verify that the transaction ends and the lock is released before assuming another worker can proceed. The existing fixture’s separate locking transaction rolls back deliberately, which provides a small observable example of release at transaction end.
When adding business tables, keep lock acquisition order consistent across code paths. Adding account, inventory, or invoice locks can introduce deadlocks even when the queue claim is correct. PostgreSQL can abort a deadlock participant; your application still needs a bounded transaction retry policy that does not duplicate already committed external work. Keep the claim transaction short and retry only work whose previous result is understood.
Frequently asked questions
Does SKIP LOCKED guarantee FIFO job processing?
No. SKIP LOCKED bypasses rows another transaction has locked. An ORDER BY id therefore orders the available candidates, while a lower-ID locked job may run later. The test demonstrates this by holding job 1 and requiring another session to claim job 2. Use a different coordination design if strict execution order is part of the business contract.
Does a PostgreSQL lease guarantee exactly-once processing?
No. A lease permits recovery after expiry, so an old worker and its replacement can overlap. The generation predicate rejects stale updates to the job row. Protect an external operation with destination-side idempotency or fencing, and test its effect count. The queue record alone cannot establish whether an external service already applied the operation.
What does a zero-row completion update mean?
The update’s ownership or state conditions no longer match. Inspect the stored generation, state, and expiry to distinguish a replacement claim from an expired lease or completed job. Stop using the old claim. Fetching the new generation and retrying the old worker’s completion would defeat the ownership check the query is meant to enforce.
Can the business update and job completion share a transaction?
Yes, when both updates occur in the same PostgreSQL database. Check ownership and perform the business mutation in one short transaction, rolling back if ownership is rejected. Keep network calls outside that transaction where possible: row locks remain held until it ends. Test the exact statement order against concurrent reclamation before using it for real work.