When normally cheap queries suddenly take seconds, check whether they are waiting for a lock. A query can be active from the database’s point of view while making no progress because another transaction still holds a resource.

sql
SELECT pid,
       application_name,
       wait_event,
       now() - query_start AS query_age,
       pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

Look up the blocking session

Use the returned process IDs to inspect the blockers in pg_stat_activity. Check the application name, transaction age, and session state. A session that is idle in transaction can hold locks while its client is doing no database work. A migration can also wait behind an older transaction and leave other requests queued behind it.

On a shared system, coordinate with the owner before canceling work. A forced rollback may be expensive or interrupt a job that cannot simply rerun. Check whether the application has a safe retry path and whether the blocking transaction is healthy enough to let it finish.

After recovery, reproduce a small version of the wait in staging with two sessions. Look for a shorter transaction, an application that reliably commits or rolls back, or a migration workflow with an explicit lock timeout.

References