How to check which queries are active or blocked in PostgreSQL?

How to check which queries are active or blocked in PostgreSQL?


You can use the pg_stat_activity view in PostgreSQL to check which queries are currently active or blocked. This view provides information about each running query, including the query text, start time, state, and many other details. To check the active queries, you can run the following query:

This query will return all rows from the pg_stat_activity view where the state column is not ‘idle’, indicating that the query is currently running. To check the blocked queries, you can run the following query:

This query will return all rows from the pg_stat_activity view where the state column is ‘blocked’, indicating that the query is currently blocked by another query. You can also use pg_locks and pg_stat_activity to show the blocking and blocked processes.

This query will show the process that is holding the lock, the process that is waiting for the lock, the query that is being executed by the waiting process, and the query that is being executed by the process holding the lock.

About Shiv Iyer 72 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.

Be the first to comment

Leave a Reply