Master in Data Analyst | New Batch Starting From 10th Oct 2025 at 6 PM IST | Register for Free Demo

How to Check Blocking Sessions in PostgreSQL

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
, featuring a smiling instructor beside the PostgreSQL elephant logo
  • User AvatarPradip
  • 09 Oct, 2025
  • 0 Comments
  • 3 Mins Read

How to Check Blocking Sessions in PostgreSQL

How to Check Blocking Sessions in PostgreSQL

When working with PostgreSQL databases, you might encounter situations where one session is blocking another, causing performance issues or query delays.
Understanding which session is blocking and why is crucial for quick troubleshooting.
In this guide, we’ll explore different ways to identify blocking sessions, view lock details, and terminate blocking processes safely.

1. Simple Query to Check Blocking Sessions

You can identify blocking and blocked sessions by using PostgreSQL’s system views pg_locks and pg_stat_activity.

SQL Query

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,
blocking_activity.query AS blocking_query,
blocked_activity.application_name AS blocked_app,
blocking_activity.application_name AS blocking_app,
blocked_activity.query_start AS blocked_since,
blocking_activity.query_start AS blocking_since
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
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.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
JOIN pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Explanation

  • pg_locks → Displays locks held by all active sessions.

  • pg_stat_activity → Provides details about current queries and session states.

  • WHERE NOT blocked_locks.GRANTED → Filters only those locks waiting for release (i.e., blocked sessions).

This query gives you detailed insights into who is blocking whom, what query they’re executing, and since when the block has been active.

2. Quick Summary View (Simplified)

If you just need a quick summary to identify blocking and blocked users, use the following lightweight query:

SELECT
bl.pid AS blocked_pid,
a.usename AS blocked_user,
kl.pid AS blocking_pid,
ka.usename AS blocking_user
FROM pg_locks bl
JOIN pg_stat_activity a
ON bl.pid = a.pid
JOIN pg_locks kl
ON bl.locktype = kl.locktype
AND bl.database IS NOT DISTINCT FROM kl.database
AND bl.relation IS NOT DISTINCT FROM kl.relation
AND bl.page IS NOT DISTINCT FROM kl.page
AND bl.tuple IS NOT DISTINCT FROM kl.tuple
AND bl.classid IS NOT DISTINCT FROM kl.classid
AND bl.objid IS NOT DISTINCT FROM kl.objid
AND bl.objsubid IS NOT DISTINCT FROM kl.objsubid
JOIN pg_stat_activity ka
ON kl.pid = ka.pid
WHERE NOT bl.GRANTED;

This provides a minimal view — useful for quick checks during database performance monitoring.

3. Kill Blocking Session

Once you identify the blocking session PID, you can terminate it to release the lock using the following command:

SELECT pg_terminate_backend(<blocking_pid>);

Example:

SELECT pg_terminate_backend(12345);

4. View All Current Locks and Queries

To see all active locks in your PostgreSQL database:

SELECT * FROM pg_locks;

To view currently running queries along with their start time:

SELECT pid, usename, state, query, query_start
FROM pg_stat_activity
ORDER BY query_start;

This helps in understanding which queries are long-running and might be contributing to blocking behavior.

Summary

Task Query / Command
Check detailed blocking sessions Full join query with pg_locks and pg_stat_activity
Quick summary view Simple blocking vs blocked session query
Terminate blocking session SELECT pg_terminate_backend(<pid>);
View all locks SELECT * FROM pg_locks;
View running queries SELECT pid, usename, query, query_start FROM pg_stat_activity;

Conclusion

Blocking sessions are common in multi-user databases but can cause performance bottlenecks if not managed properly.
By using the above queries, DBAs can quickly identify blocking sessions, analyze their impact, and take corrective actions.

Regularly monitoring pg_stat_activity and pg_locks helps maintain a smooth and high-performing PostgreSQL environment.

Oracle ASM Queries