To diagnose a stuck PostgreSQL queue job, check the application’s job record and worker heartbeat alongside PostgreSQL’s session and lock state. A row marked running does not prove that a worker is alive—or that it has stopped. Before retrying, make sure the old worker cannot still complete the job’s side effects.
What counts as a stuck job?
Define “stuck” using the queue’s contract, not a single old timestamp or a long-running query. Choose thresholds that reflect expected job duration and the worker’s heartbeat or lease cadence. A legitimate long task can exceed a generic timeout, while a failed worker may leave a job untouched much sooner.
As an Amazon Associate I earn from qualifying purchases.
PostgreSQL records database processes, not the full business lifecycle of a job. Keep queue-level ownership and progress fields such as started_at, heartbeat_at or lease_expires_at, worker_id, attempt_count, and last_error. Alert on expired leases or stale heartbeats in context with queue age and retry count.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchDiagnose the job before changing it
Inspect the queue row and history
Record the job ID, status, creation and start times, latest heartbeat or lease expiry, worker identity, attempt count, and last error. Look for patterns: one worker holding several old jobs may point to a worker problem, while a growing backlog limited to one queue class may indicate a workload-specific fault. Compare the row with worker logs and any intervention history.
#1 Best Overall
Inspect PostgreSQL sessions
In PostgreSQL 18, pg_stat_activity provides a live row for each server process, including state, query and wait-event details. Filter by the worker database, role, and application_name, then review state, query_start, wait_event_type, and wait_event. Visibility into other sessions depends on privileges, so use an authorized operator account and avoid exposing query text or session details unnecessarily. Check documentation for the PostgreSQL major version and permissions in use.
This view is a database-side snapshot, not the authoritative job ledger. A worker may have lost its database connection after completing an external action, and a worker can be doing work that is not visible as an active database query.
Check for lock waits and blockers
pg_locks shows outstanding locks. Join its PID to pg_stat_activity.pid to identify sessions with ungranted locks and inspect the sessions holding conflicting locks. Determine which transaction is blocking progress and how old it is before intervening. A lock wait explains why SQL may not be advancing; it does not tell you whether the worker is healthy or whether the application’s job timeout has elapsed.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →The PostgreSQL wiki lock-monitoring examples can be a starting point, but their queries have stated limitations. Prefer current official documentation and check any diagnostic query against your deployed server version.
Choose the least disruptive intervention
First resolve the application fault or blocking transaction if possible. Do not cancel or terminate a backend merely because its query looks old: it may be processing a legitimate long task or holding a lock for a reason. Match the intervention to the evidence and the risk that the worker can still produce effects.
- When a specific query is the problem:
pg_cancel_backend(pid)requests cancellation of the current query in that backend. It does not reset the queue row, choose a retry policy, or establish whether an external action already happened. Signaling functions have role-based restrictions. - When cancellation is insufficient: Terminating a session is more disruptive. Use it only when necessary, authorized, and after identifying the impact. Then inspect the job state and external effects before retrying.
Advisory locks can coordinate application-defined resources, but PostgreSQL does not enforce what an advisory-lock key means. The application must use the keys consistently; an advisory lock is not a durable job status or lease.
Recover under an explicit retry policy
- Establish ownership: Confirm that the previous worker is gone, or that its lease has expired and a fencing mechanism prevents it from later writing success. A missing session in
pg_stat_activityis not proof that an external action did not complete just before the connection disappeared. - Assess partial effects: Check whether the handler may have completed an external action before failing. Make retries safe with idempotency keys or effect checks where feasible; duplicate processing is a design risk, not something PostgreSQL’s queue-row locks prevent.
- Transition state transactionally: Requeue according to the application’s policy, increment the attempt count, and record why the job was reset. Move repeatedly failing jobs to a defined terminal or dead-letter state rather than retrying without limit.
- Verify recovery: Confirm that a worker claims the job, its heartbeat advances, queue age is falling, and no duplicate side effect occurred. Keep an audit trail for manual interventions.
Blindly resetting every old running row can create duplicate work. The retry threshold, terminal state, and handling of partial completion belong to the application’s queue contract; PostgreSQL does not supply those policies.
Claim jobs atomically, then do the work
PostgreSQL documents FOR UPDATE SKIP LOCKED as a way for multiple consumers to avoid contention when claiming rows in a queue-like table. For example, a worker can select eligible jobs, update their ownership and status in the same short transaction, and commit before doing slow work:
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;The example assumes an illustrative jobs schema; it is not a complete production queue implementation. Use a unique ordering, suitable indexes, and a lease or fencing design appropriate to the application. Perform expensive work after the claim transaction commits. Row locks end with the transaction; durable queue fields must represent ownership after commit. If a worker can outlive its lease, a fencing token or equivalent must stop the old owner from writing success after a newer worker takes over.
There is an important trade-off: PostgreSQL warns that skipping locked rows gives an inconsistent view of the data. That makes SKIP LOCKED useful for queue consumers seeking available work, not for general-purpose reads that require a consistent view. Keeping the claim transaction short also avoids holding row locks while the worker performs slow external operations.
Quick Recap
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.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
