pg_blocking_pids — Find Blocking Queries in PostgreSQL

calendar Mar 25, 2024 / · 11 min read · pg_blocking_pids pg_stat_activity pg_locks postgresql locks database performance database administration sql queries deadlocks postgres dba  ·
Share on: linkedin copy

Identifying Blocking PostgreSQL Queries: A Guide for Database Administrators

This article explores a PostgreSQL query designed to identify currently blocked database queries and the processes (identified by PIDs) responsible for blocking them. This information is crucial for database administrators troubleshooting performance bottlenecks and ensuring smooth database operation.

Sample Code from Command Line

1SELECT
2  pid,
3  usename,
4  pg_blocking_pids(pid) AS blocked_by_pids,
5  query AS blocked_query
6FROM
7  pg_stat_activity
8WHERE
9  cardinality(pg_blocking_pids(pid)) > 0;

Notes: Lists PostgreSQL queries blocked along with the pids of those holding the locks blocking them. Requires PostgreSQL >= 9.6. Tested on PostgreSQL 9.6+, 10.x 11.x, 12.x, 13.0. The function's signature and behavior have not changed since introduction, and the same query runs unmodified on every currently supported PostgreSQL release.

Filtered variant, once you already have a blocked PID from the query above and want to inspect just the sessions holding it up:

 1SELECT
 2  pid,
 3  usename,
 4  state,
 5  wait_event_type,
 6  wait_event,
 7  now() - query_start AS running_for,
 8  query AS blocking_query
 9FROM
10  pg_stat_activity
11WHERE
12  pid = ANY (pg_blocking_pids(12345));

Replace 12345 with a pid value returned in the first query's blocked_by_pids array. This reverses the lookup: instead of asking "who is blocking me," it asks "what is this specific blocker actually doing right now" — running a live query, sitting idle in an open transaction, or waiting on something else entirely.

Purpose and Overview

This article explores a PostgreSQL query designed to identify currently blocked database queries and the processes (identified by PIDs) responsible for blocking them. This information is crucial for database administrators troubleshooting performance bottlenecks and ensuring smooth database operation.

Understanding the Code

The provided PostgreSQL code utilizes the following functions and features:

  • pg_stat_activity: This built-in function offers a real-time view of currently active PostgreSQL backend processes.
  • pid: This column within pg_stat_activity represents the process identifier (PID) of each database session.
  • usename: This column reveals the username associated with the database session.
  • pg_blocking_pids(pid): This function accepts a PID as input and returns a comma-separated list of PIDs that are currently blocking the specified process.
  • cardinality(): This function determines the number of elements within a set. In this case, it counts the PIDs returned by pg_blocking_pids(pid).
  • WHERE: This clause filters the results based on the specified condition.

Code Breakdown

  1. Data Retrieval: The query retrieves data from the pg_stat_activity system view.
  2. Selection: It selects specific columns:
    • pid: The process identifier of the blocked session.
    • usename: The username of the blocked session's owner.
    • pg_blocking_pids(pid) AS blocked_by_pids: This expression utilizes the pg_blocking_pids function to identify the PIDs of processes blocking the current session. The result is aliased as blocked_by_pids for better readability.
    • query AS blocked_query: This retrieves the actual query currently being blocked. The result is aliased as blocked_query for clarity.
  3. Filtering: The WHERE clause ensures only processes with active blockers are included. It achieves this by checking if the cardinality (number of elements) of the blocked_by_pids list is greater than zero.

Insights and Explanations

This query provides valuable insights into potential database performance issues:

  • Blocked queries indicate processes waiting for resources held by other processes. This can lead to slowdowns and bottlenecks.
  • Identifying the blocking PIDs allows pinpointing the root cause of the blocking issue.
  • Examining the blocked_query can reveal the specific operations causing the blockage.

Optimizing Database Performance

By analyzing the results of this query, database administrators can:

  • Terminate long-running queries if necessary (with caution to avoid data loss).
  • Optimize inefficient queries to reduce resource consumption.
  • Identify potential database schema deadlocks and address them.

Conclusion

This PostgreSQL query equips database administrators with a powerful tool to diagnose blocking queries and optimize database performance. By understanding the code's functionality and interpreting the results effectively, database administrators can ensure a smooth and responsive database environment for their applications.

Why pg_blocking_pids Replaced the Manual pg_locks Join

Before PostgreSQL 9.6, finding a blocking session meant a manual self-join against pg_locks — matching every waiting lock request against every granted lock on the same object, then checking by hand whether the two lock modes actually conflicted. PostgreSQL's own release notes for that version describe the old approach as "unreasonably tedious to do... with any modicum of correctness," and note that parallel queries made it worse still, since a lock can be held or awaited by a child worker process rather than the session's own backend. pg_blocking_pids() was added specifically to replace that self-join with a single, reliable function call.

The function distinguishes two kinds of blocking. A hard block is a session holding a lock that directly conflicts with the target session's request. A soft block is a session that hasn't acquired the conflicting lock either — it is simply ahead of the target session in the same wait queue, so the target cannot jump ahead of it even though neither session currently holds the lock. Both count as blocking in the array pg_blocking_pids() returns, which is why the sample query above catches queueing delays, not only direct lock conflicts.

Two edge cases are worth knowing before trusting the output in production. When a query runs under parallel workers, pg_blocking_pids() always reports the client-visible leader PID — the same one pg_backend_pid() returns — even when the actual lock is held or awaited by a child worker process, so the same PID can appear more than once in the array. And when a prepared transaction (one left open by PREPARE TRANSACTION for two-phase commit) holds the conflicting lock, the function reports it with a process ID of zero, since a prepared transaction has no live backend attached to it. PostgreSQL's documentation also flags a real cost behind the convenience: because the function needs exclusive access to the lock manager's shared state to build its answer, calling it frequently — inside a tight polling loop, for example — can measurably affect database performance.

Key pg_locks and pg_stat_activity Columns for Blocking Analysis

The sample query above answers "who is blocked and by whom." Two catalog views supply the columns that answer the follow-up questions — "why," and "is it still happening."

mode and locktype — What Kind of Lock Is Actually Held

pg_locks.mode names the specific lock mode a session holds or is waiting for, and locktype names the kind of object it applies to — table, tuple, transaction ID, and others. PostgreSQL's documentation on explicit locking is direct about how these get chosen: SELECT acquires only an ACCESS SHARE lock, while UPDATE, DELETE, INSERT, and MERGE acquire ROW EXCLUSIVE on the target table — in general terms, the mode acquired by any command that modifies data. Some commands go further: TRUNCATE cannot run safely alongside other operations on the same table, so PostgreSQL gives it an ACCESS EXCLUSIVE lock, the most restrictive mode there is. Reading mode alongside the blocked query's text usually explains the conflict immediately — a long-running report holding ACCESS SHARE is not what a TRUNCATE blocks; it's the other way around, and the report has to finish first.

granted — Held vs. Still Waiting

pg_locks.granted is true for a row representing a lock a session already holds, and false for a row representing a lock request still sitting in the queue. A session that has not been granted its lock is asleep until the conflicting lock ahead of it clears — a single process can be waiting to acquire at most one lock at a time, which is what makes a blocking chain traceable one link at a time rather than a tangle of simultaneous waits.

wait_event_type and wait_event — What the Session Is Waiting On

Before PostgreSQL 9.6, pg_stat_activity only exposed a boolean waiting column that was true while a backend waited on a heavyweight lock, and nothing otherwise. The same release that added pg_blocking_pids() replaced that boolean with wait_event_type and wait_event, and extended visibility to waits on lightweight locks and buffer pins as well — not just heavyweight table and row locks. A blocked session with wait_event_type = 'Lock' is waiting on exactly the kind of contention this post's query surfaces; other values point to I/O, client communication, or extension activity instead.

state — Is the Blocking Session Even Doing Anything

pg_stat_activity.state reports whether a session is active (running a query right now), idle, or one of two transaction-specific states: idle in transaction and idle in transaction (aborted). A blocking session sitting in idle in transaction is not running any query at all — it opened a transaction, touched a row, and then never committed or rolled back, and it will hold its locks for as long as that connection stays open. This is one of the most common real-world causes of unexpected blocking, and the filtered query earlier in this post is built to surface exactly that state alongside the blocker's pid.

Practical Applications

Reading the blocking-session output is only half the job; what happens next depends on the situation.

Deciding Whether to Cancel or Terminate a Blocking Session

The "terminate long-running queries if necessary" step above has two concrete tools behind it. pg_cancel_backend() sends SIGINT, which cancels the backend's current query but leaves the session itself connected — the safer first move when a blocker is mid-query. pg_terminate_backend() sends SIGTERM, which ends the whole session; PostgreSQL's documentation notes that a role can only terminate a backend it has privileges over, and that supplying a timeout argument makes the function wait for confirmation the backend actually stopped before returning true. Both take the same pid column this post's query already returns.

pg_blocking_pids() returns only the PIDs directly blocking a given session — if session C is blocked by B, which is itself blocked by A, a query against C returns only B. Running the same query again with B's PID, or joining pg_stat_activity to itself through repeated pg_blocking_pids() calls, walks the chain back to A — the session actually responsible for the whole pileup, and the only one worth investigating first.

Catching Idle-in-Transaction Blockers Before They Escalate

Because an idle-in-transaction session holds its locks indefinitely, the highest-value monitoring check is not the blocked queries themselves but the blockers' state. Alerting whenever a row in the blocking output shows state = 'idle in transaction' for more than a few minutes catches a forgotten transaction before it turns into a growing queue of blocked sessions behind it.

Investigating Parallel-Query Blocking

When the same PID shows up more than once in a blocked_by_pids array, that is the signature of parallel-worker blocking described above, not a bug in the query. Cross-referencing leader_pid in pg_stat_activity — which is null for a session that is itself a parallel group leader, and set to the leader's PID for its workers — confirms whether the reported blocker is genuinely one session or a parallel query spread across several backend processes.

Version Compatibility

pg_blocking_pids() was added in PostgreSQL 9.6, specifically to retire the tedious and, with parallel queries in the picture, increasingly unreliable manual pg_locks self-join. The same 9.6 release replaced pg_stat_activity's old boolean waiting column with the wait_event_type and wait_event columns used in the filtered query above, and widened wait visibility from heavyweight locks alone to lightweight locks and buffer pins too. Both changes shipped together because they solve the same underlying problem: giving administrators an accurate, function-based view of session waits instead of one built by hand.

The function's signature and behavior are unchanged in the current PostgreSQL documentation, so the sample query in this post runs without modification on every release from 9.6 through the latest supported version. The main compatibility question for older fleets is not the function itself but the columns it is typically paired with — wait_event_type and wait_event are 9.6-and-later columns, so a cluster still running 9.5 or earlier needs the deprecated waiting boolean instead, and cannot use pg_blocking_pids() at all.

Best Practices

  • Prefer pg_cancel_backend() before pg_terminate_backend() — a cancel stops the query and keeps the connection, giving the application a chance to retry; termination drops the session outright.
  • Call pg_blocking_pids() in a scheduled check, not a tight polling loop — PostgreSQL's own documentation flags the lock-manager overhead of frequent calls.
  • Treat duplicate PIDs in the result as a parallel-query signal, not an error — cross-check leader_pid before assuming the data is wrong.
  • Alert on idle in transaction blockers separately from busy ones — they are silent, easy to miss, and hold locks the longest.
  • Confirm role privileges before scripting automatic termination — pg_terminate_backend() only succeeds against a backend the calling role has privileges over.
  • Pair blocking checks with the lock mode, not just the PID — the mode column usually explains why a conflict happened, which changes whether the fix is a code change, an index, or just patience.

References

Posts in this series