PostgreSQL Tutorial: Check Blocked Queries

Summary: in this tutorial, you will learn how to check blocked queries in PostgreSQL.

Table of Contents

Introduction

PostgreSQL databases power many mission critical applications, and applications expect consistent query performance. If even a single query takes longer than expected, it can lead to unhappy users, or delayed background processes. We can use EXPLAIN to debug a slow query, but there is another problem which slows down the query: blocked queries. You may also know this as “blocked sessions” from other database systems. This is when one query holds a lock on a table and the other is waiting for those locks to be released.

The key tables and functions which is useful to check blocked queries are:

• pg_stat_activity: A table with one entry per server process, showing details of the running query for each.
• pg_locks: Information on current locks held within the database by open transactions, with one row per lockable object.
• pg_blocking_pids(): A function that can find the process IDs (PIDs) of sessions that are blocking the PostgreSQL server process of a supplied PID.

Finding blocked processes and blocking queries

The pg_stat_activity view allows you to see all running queries and how long they are taking on your PostgreSQL database.

The pg_blocking_pids() function is a useful shortcut to find the database connections / sessions that are blocking another session. The pg_blocking_pids() function returns an PostgreSQL array of PIDs that are blocking the specified server process PID that you provide to the query. Typically, a server process blocks another if it holds a lock that is needed by the second process.

A useful SQL statement to show the blocked processes on your database along with the actual query that is blocking them is:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
ORDER BY activity.query_start;

Viewing locks with table names and queries

Looking at pg_locks shows you what locks are granted and what processes are waiting for locks to be acquired. Figuring out what processes are holding or waiting for locks is easier if you cross-reference against the information in pg_stat_activity.

Joining the pg_lock view to pg_stat_activity for the query and pg_class for the table names can be useful to gather more context as to what is locking on your database at any point in time:

SELECT
    relname AS relation_name,
    query,
    pg_locks.*
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid

See more

PostgreSQL Monitoring