waiting_queries

Waiting queries

Dimensions:None

Available on:

Datadog (1)

Interface Metrics (1)

Datadog

postgresql.waiting_queries

Enabled with `collect_activity_metrics`. The number of waiting queries in this database. This metric (by default) is tagged with db, app, user.

Dimensions:None

Sources

postgresql.waiting_queriesgithub.com

Technical Annotations (18)

Configuration Parameters (1)

lock_timeout

prevents queries from waiting indefinitely for locks

CLI Commands (9)

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;diagnostic

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid WHERE pl.granted = false;diagnostic

SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query AS waiting_query, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type = 'Lock';

diagnostic

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid IN (SELECT unnest(pg_blocking_pids(waiting_pid)));remediation

SELECT pg_cancel_backend(pid);remediation

WITH RECURSIVE lock_tree AS ( SELECT pid, pg_blocking_pids(pid) AS blockers, query, 0 AS depth FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0 UNION ALL SELECT a.pid, pg_blocking_pids(a.pid), a.query, lt.depth + 1 FROM pg_stat_activity a JOIN lock_tree lt ON a.pid = ANY(lt.blockers) WHERE lt.depth < 5 ) SELECT * FROM lock_tree ORDER BY depth;

diagnostic

SELECT * FROM tasks WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;remediation

SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;remediation

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_query FROM pg_locks blocked_locks JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype AND blocked_locks.relation = blocking_locks.relation AND blocked_locks.pid <> blocking_locks.pid JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid WHERE NOT blocked_locks.granted;

diagnostic

Technical References (8)

pg_lockscomponentpg_blocking_pids()componentpg_stat_activitycomponentpg_blocking_pidscomponentwait_event_typecomponentlock_tree CTEconceptFOR UPDATE SKIP LOCKEDconceptFOR UPDATE NOWAITconcept

Related Insights (6)

Lock contention visible in pg_locks indicates waiting processeswarning

▸

Lock conflicts and deadlocks degrade query performancewarning

▸

Queries blocked by lock contention wait indefinitely without timeoutwarning

▸

Lock blocking chains create cascading query delayswarning

▸

Queue-like patterns cause lock contention on pending taskswarning

▸

Blocking locks cause query delays and timeoutscritical

▸