Identifying Blocking Queries in PostgreSQL: Keeping Your Database Running Smoothly

Have you ever encountered sluggish performance in your PostgreSQL database? One culprit could be blocking queries. These are queries that are waiting on resources held by other queries, creating a chain reaction that slows everything down. Identifying and resolving blocking queries is crucial for maintaining optimal database health.

This article dives into a powerful PostgreSQL query that helps pinpoint bottlenecks.

Sample Code

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: Requires PostgreSQL >= 9.6. Tested on PostgreSQL 9.6+, 10.x 11.x, 12.x, 13.0. Lists PostgreSQL queries blocked along with the pids of those holding the locks blocking them.

Breakdown and Key Points

Let's break down this query step-by-step:

1. Selecting Data:

• pid: This retrieves the process ID of the blocked query.
• usename: This identifies the user associated with the blocked query.
• pg_blocking_pids(pid) AS blocked_by_pids: This is the heart of the query. It utilizes the pg_blocking_pids function, introduced in PostgreSQL 9.6. This function returns an array of process IDs that are blocking the current query (identified by pid). We alias the result as blocked_by_pids for better readability.
• query AS blocked_query: This retrieves the actual SQL query that's causing the blockage.

2. Filtering Results:

• FROM pg_stat_activity: This clause specifies that we're retrieving data from the pg_stat_activity view, which provides information about current and recent database activities.
• WHERE cardinality(pg_blocking_pids(pid)) > 0: This is the key filter. The cardinality function determines the number of elements in an array. Here, we filter for processes (pid) where pg_blocking_pids returns more than zero elements (meaning there are blocking queries).

Insights and Explanations

By running this query, you get a clear picture of:

• Blocked Queries: You can identify the specific queries that are being held up due to resource contention.
• Blocking Processes: The blocked_by_pids array reveals the process IDs of the queries causing the blockage. This helps you understand which queries are responsible for slowing down others.
• Usernames: The usename column allows you to see which users are running the blocked and blocking queries. This can be helpful in identifying potential trends or resource-intensive users.
• Blocked Query Details: The blocked_query provides the actual SQL statement that's causing the bottleneck. By analyzing this query, you might identify inefficient structures or potential for optimization.

Additional Considerations

• Permissions: Running this query typically requires privileges to access the pg_stat_activity view. Consult your PostgreSQL documentation for details on granting such permissions.
• Performance Impact: The pg_blocking_pids function can introduce a slight overhead due to locking mechanisms. Use this query judiciously to avoid impacting overall performance.

By effectively using this query and analyzing the results, you can gain valuable insights into blocking queries in your PostgreSQL database. This knowledge empowers you to optimize queries, troubleshoot performance issues, and ultimately keep your database running smoothly.

References

PostgreSQL pg_stat_activity view: https://www.postgresql.org/docs/current/monitoring-stats.html

PostgreSQL pg_blocking_pids function: https://pgpedia.info/

Source

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_blocked_queries.sql