Skip to content

C04 — Long-Running Queries & Lock Contention

Sessions that have been doing something for too long, and the contention that causes. A query running for hours, a transaction left idle-in-transaction (which blocks vacuum from cleaning up dead rows it might still need to see), and chains of sessions blocking each other on the same locks — all things that degrade or stall an application well before they show up anywhere else.

11 checks, PGHF04-001 through PGHF04-011. Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.

PGHF04-001 — Longest-running query age

A long-running query holds its locks and its snapshot open for as long as it runs — the longer it runs, the more it blocks autovacuum progress and the more resources it monopolizes; catching it early gives an operator time to cancel it before it does real damage.

How to fix

  1. Identify it and its plan:
SELECT pid, now() - query_start AS age, state, query
  FROM pg_stat_activity
 WHERE state != 'idle'
 ORDER BY age DESC;
  1. If it's genuinely stuck or no longer needed, cancel it (graceful) or terminate it (forceful):
SELECT pg_cancel_backend(<pid>);   -- ROLLBACK, query stops
SELECT pg_terminate_backend(<pid>); -- connection dropped outright
  1. To prevent recurrence, set statement_timeout (PGHF04-005) as a backstop.

PGHF04-002 — Idle-in-transaction age

An idle-in-transaction session holds locks and an open snapshot while doing nothing — it silently prevents autovacuum from reclaiming dead tuples for as long as it sits there, same underlying risk as PGHF01-004.

How to fix

See PGHF01-004's remediation — terminate the offending session and set idle_in_transaction_session_timeout so this can't recur unbounded.

PGHF04-003 — Lock blocker chains

A blocker chain means one session is stalling a whole queue of others waiting on the same lock — the blocked sessions are often invisible in a simple activity count, but they're real, growing latency the application is absorbing right now.

How to fix

  1. Find the head of the chain — the session that's blocking but not itself blocked:
SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query, blocking_activity.query AS blocking_query
  FROM pg_locks blocked_locks
  JOIN pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
   AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
   AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
   AND blocking_locks.pid != blocked_locks.pid
  JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
  JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
 WHERE NOT blocked_locks.granted;
  1. Decide whether the blocker is legitimate (a long DDL/maintenance operation) or a bug (a forgotten open transaction) — terminate it only in the latter case.
  2. Set lock_timeout (PGHF04-010) so a future chain resolves itself instead of piling up indefinitely.

PGHF04-004 — Deadlock count

Every deadlock means PostgreSQL had to forcibly abort a transaction to break a cycle — a nonzero count points at inconsistent lock-acquisition order somewhere in the application, not a database-level problem that tuning alone fixes.

How to fix

This is an application fix, not a server-tuning one:

  1. Set log_lock_waits = on and check the log for the deadlock detail message — it names the exact two queries and the objects each was waiting on.
  2. Change the application so every transaction touching the same set of tables acquires locks on them in the same order.
  3. Where lock order genuinely can't be made consistent, consider SELECT ... FOR UPDATE up front to acquire row locks explicitly, in a fixed order, rather than letting them be acquired incidentally mid-transaction.

PGHF04-005 — statement_timeout

With no statement_timeout, a single runaway query can hold its locks and resources indefinitely — there's nothing left to protect the server from a badly-written or hung query once it starts.

How to fix

ALTER SYSTEM SET statement_timeout = '30s';
SELECT pg_reload_conf();

A cluster-wide default is a safety net; if some workloads (batch jobs, reporting) legitimately need longer, override it per-role instead of raising the global default:

ALTER ROLE batch_user SET statement_timeout = '1h';

PGHF04-006 — idle_in_transaction_session_timeout

Without this timeout, a client that opens a transaction and never commits or rolls back — a forgotten connection, an app-level bug — can sit idle-in-transaction forever, with all the PGHF04-002/PGHF01-004 consequences that implies.

How to fix

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

PGHF04-007 — pg_stat_statements extension

Without pg_stat_statements, query-level performance analysis is severely limited — several other checks in this framework (PGHF04-008/PGHF04-011) and most real-world query-tuning workflows depend on it being installed and preloaded.

How to fix

It must be preloaded, not just CREATE EXTENSION'd — add it to shared_preload_libraries and restart:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

pg_ctl restart

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pghf.seed_data();  -- registers PGHF04-008/PGHF04-011, gated on this extension being present

PGHF04-008 — Top queries by total_exec_time

The queries consuming the most cumulative execution time are, by definition, where optimization effort pays off the most — this ranked list is where a DBA should start looking, rather than guessing at which query is worth tuning.

How to fix

For each top offender: run EXPLAIN (ANALYZE, BUFFERS) against it, check for a missing index (PGHF06-005 for FK columns specifically), a sequential scan that should be an index scan (see PGHF03-010's random_page_cost note), or stale statistics (PGHF03-014/ANALYZE). Since this is cumulative time (calls × mean time), also check whether it's one slow query or a fast query called an extreme number of times — the fix differs (query tuning vs. reducing call volume, e.g. batching or caching in the app).

PGHF04-009 — log_min_duration_statement

With slow-query logging disabled, identifying a performance regression after the fact means having no record of which queries were actually slow at the time — this is cheap, low-overhead insurance worth having on.

How to fix

ALTER SYSTEM SET log_min_duration_statement = '1s';
SELECT pg_reload_conf();

Pick a threshold above normal query latency for this workload so the log doesn't fill with routine noise — start conservative (e.g. 1s) and lower it only if it stays quiet.

PGHF04-010 — lock_timeout

Without lock_timeout, a session waiting on a contended lock waits indefinitely — under real contention this turns into a growing pile-up of stuck connections rather than a bounded, recoverable wait.

How to fix

ALTER SYSTEM SET lock_timeout = '5s';
SELECT pg_reload_conf();

Keep it shorter than statement_timeout (PGHF04-005) — it should fire while a session is still just waiting for a lock, not after it's already been granted and started real work.

PGHF04-011 — Worst mean exec time (statistically-significant queries)

A high count of statistically-significant queries with a slow mean execution time is a direct signal of a performance regression — not a one-off slow run, but a query pattern that's consistently expensive every time it executes.

How to fix

Same investigation as PGHF04-008, but this ranking is already filtered to consistently-slow patterns rather than merely high-cumulative-time ones — EXPLAIN (ANALYZE, BUFFERS) each one and look for a plan that's bad on every run, not just occasionally, which usually means a missing/unused index or genuinely poor statistics rather than transient contention.

Continue to C05 — Vacuum & Autovacuum Health.