iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To diagnose a stuck PostgreSQL queue job, check the queue row’s heartbeat, lease, worker, and attempt history alongside PostgreSQL’s session and lock views. A running status alone cannot tell you whether a worker has disappeared, is waiting on a lock, or is legitimately doing slow work. Before retrying a job, make sure its former worker cannot still commit effects; retries may otherwise duplicate work.
What “stuck” means depends on your queue
PostgreSQL can show database processes and locks, but it does not own the application’s job lifecycle. Define a stuck job using the queue’s expected job duration and heartbeat or lease interval—not a universal age threshold. A long-running job may be healthy; an expired lease or missing heartbeat is stronger evidence that ownership needs investigation.
For useful monitoring, store and expose the job’s status, creation and start times, heartbeat or lease expiry, worker identity, attempt count, and last error. Alert on expired leases or stale heartbeats together with queue age and retry counts. Treat PostgreSQL’s process view as a live diagnostic snapshot, not the authoritative job ledger: PostgreSQL 18 documents pg_stat_activity as one row per server process, with fields such as state, query, and wait events. Visibility into other sessions may depend on database privileges.
Diagnose the job before changing it
1. Capture the queue record and history
Record the job ID, status, created, started, heartbeat and lease timestamps, worker ID, attempt count, and last error. Check its history and whether the same worker owns several old jobs or one queue class is accumulating. This helps distinguish a single failure from a worker or queue-wide problem.
#1 Best Overall
2. Inspect worker database sessions
Filter pg_stat_activity by the worker database, role, and application_name, if your workers set one. Compare relevant sessions’ state, query_start, wait_event_type, and wait_event with the job row and worker logs. A missing database session does not prove that an external action failed to complete before the connection disappeared.
Restrict access to query text and session details to authorized operators. Also remember that a live session does not, by itself, prove it still owns the application job.
3. Check for lock waits and blockers
Use pg_locks to look for ungranted locks, then join its PID to pg_stat_activity.pid to inspect the sessions involved. Identify the blocker and its transaction age before intervening. PostgreSQL’s pg_locks documentation describes outstanding locks and how to associate them with sessions.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
A lock wait explains why SQL may not be progressing; it does not establish whether the worker is healthy or whether the application’s timeout has elapsed. Resolve the blocking transaction or application fault when possible. The PostgreSQL wiki has example lock-monitoring queries, but treat them as starting points with limitations, and verify queries against the documentation for your deployed PostgreSQL version.
Use a short, atomic claim transaction
For multiple consumers, a common pattern is to select eligible rows with FOR UPDATE SKIP LOCKED, update their ownership and lease fields in the same transaction, and commit promptly. PostgreSQL documents this option for queue-like tables. It reduces contention by skipping rows that cannot be locked immediately, but skipped rows make the result an inconsistent view—not a general-purpose consistent read.
BEGIN;
WITH picked AS (
SELECT id
FROM jobs
WHERE status = 'ready'
AND available_at <= now()
ORDER BY priority DESC, available_at, id
FOR UPDATE SKIP LOCKED
LIMIT 20
)
UPDATE jobs AS j
SET status = 'running',
worker_id = $1,
started_at = now(),
heartbeat_at = now(),
attempt_count = attempt_count + 1
FROM picked
WHERE j.id = picked.id
RETURNING j.*;
COMMIT;This is an illustrative pattern, not a complete production queue. The example assumes a jobs schema with the named columns and a worker identifier supplied as $1. Use a unique ordering and suitable indexes, and adapt the SQL to your schema and PostgreSQL version. Do slow external work after the claim transaction commits: holding row locks through the work can make other consumers wait or skip those rows. The lock arbitrates the claim; durable status and lease fields describe ownership afterward.
A lease by itself may not stop an old worker from writing success after a newer worker claims the job. If workers can outlive a lease, use a fencing token or another application-level mechanism that prevents stale owners from completing. PostgreSQL advisory locks can coordinate application-defined resources, but their key meanings and consistent use belong to the application; they are not a durable job status or lease.
Choose the least disruptive intervention
| Situation | Safer next step | Why |
|---|---|---|
| A blocker or application fault explains the delay | Resolve the blocker or fault, then check whether the job resumes. | A lock wait alone does not prove the job is abandoned. |
| An identified backend query is the problem | Consider canceling that query only after assessing impact and confirming authorization. | Cancellation targets the current query; it does not reset queue state or rule out completed side effects. |
| The session must be stopped and cancellation is insufficient | Terminate the session only when necessary and authorized; then inspect the job row and effects. | Stopping a database session is not the same as safely retrying application work. |
| The lease expired or heartbeat is stale | Verify the old owner is gone or fenced before transactionally requeueing under the retry contract. | An old worker may still be able to finish or produce effects. |
PostgreSQL’s backend signaling functions include pg_cancel_backend(pid), which requests cancellation of the current query in the specified backend. Role-based restrictions apply. Cancellation does not decide whether a job should be retried, repair application state, or establish that an external side effect did not happen.
Quick Recap
Recover under the application’s retry contract
- Confirm ownership can no longer succeed. Check the worker and session evidence, lease expiry, and any fencing mechanism. Do not assume that a missing session means no external action occurred.
- Apply the documented retry policy transactionally. Requeue only after the policy’s lease timeout and ownership checks pass. Increment the attempt count and record why the job was reset.
- Make repeated effects safe where feasible. Use idempotency keys or check whether an effect already occurred before repeating it. Retries can repeat work, so blind recovery is unsafe when partial completion is possible.
- Stop retrying jobs that have exhausted policy. Move repeatedly failing jobs to an application-defined terminal or dead-letter state rather than cycling them indefinitely.
- Verify and audit the outcome. Confirm a worker claims the job, its heartbeat advances, queue age falls, and no duplicate side effect occurred. Keep a record of manual interventions.
Common recovery mistakes
- Resetting every old
runningrow: a slow but healthy worker may still finish, causing duplicate work. - Killing a backend just because its query is old: inspect its wait state, lock chain, and transaction first; long work may be legitimate.
- Holding a claim transaction open during job execution: row locks last for the transaction, not for the application’s durable job lifecycle.
- Treating
SKIP LOCKEDas a consistent queue snapshot: PostgreSQL explicitly cautions that skipped rows yield an inconsistent view. - Treating a database or advisory lock as the job ledger: durable job ownership, heartbeat, and retry state must be represented and managed by the application.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
