How to monitor live sessions in PostgreSQL?

In PostgreSQL, you can monitor live sessions using a few different methods:

  1. pg_stat_activity view: This view provides information about current activity in the database, including information about active sessions. You can query the pg_stat_activity view to see a list of currently running queries and the details of each session, such as the user running the query, the client address, and the query start time.
  2. pg_stat_replication view: If you are using PostgreSQL replication, you can use this view to monitor the status of replicas, including the current state, lag, and replication progress.
  3. pg_top utility: pg_top is a utility that provides real-time monitoring information about PostgreSQL processes, similar to the Unix top utility. You can use pg_top to see a list of active queries, including their current state, query start time, and the resources they are consuming.
  4. Third-party tools: There are also several third-party tools available for monitoring PostgreSQL, including open-source tools like pgAdmin and commercial tools like pgMonitor and PG Sentinel. These tools offer more advanced monitoring and performance tuning capabilities and can be a good choice if you need more robust monitoring capabilities.

You can use any of these methods to monitor live sessions in PostgreSQL, depending on your needs and the resources available to you.

SQL script to monitor Disk I/O generated by PostgreSQL threads and sort by read intensive threads

You can use the following SQL script to monitor disk I/O generated by PostgreSQL threads and sort by read-intensive threads:

This script first creates a subquery named activity that retrieves information about active sessions from the pg_stat_activity view. The subquery calculates the total I/O time and I/O bytes, as well as the read-specific I/O time and bytes, for each session.

The main query then selects only the columns of interest and orders the results by the read_time column in descending order, so that the sessions with the most read-intensive I/O are listed first.

Note that the values in the read_time and read_bytes columns represent the amount of time and bytes spent on disk reads since the last statistics reset, and do not reflect real-time usage. To get real-time usage, you would need to query the view multiple times and calculate the differences between consecutive results.

SQL script to monitor Disk I/O generated by PostgreSQL threads and sort by write intensive threads

You can use the following SQL script to monitor disk I/O generated by PostgreSQL threads and sort by write-intensive threads:

This script is similar to the previous script, but it orders the results by the write_time column in descending order, so that the sessions with the most write-intensive I/O are listed first.

Note that the values in the write_time and write_bytes columns represent the amount of time and bytes spent on disk writes since the last statistics reset, and do not reflect real-time usage. To get real-time usage, you would need to query the view multiple times and calculate the differences between consecutive results.

SQL script to monitor expensive queries from PostgreSQL threads and sort by higher Response Time (Latency) 

You can use the following SQL script to monitor expensive queries from PostgreSQL threads and sort by higher response time:

This script first creates a subquery named activity that retrieves information about active sessions from the pg_stat_activity view. The subquery filters out sessions that are idle and calculates the response time for each active session, which is the difference between the current time and the time the query started.

The main query then selects only the columns of interest and orders the results by the response_time column in descending order, so that the sessions with the longest response times are listed first. The LIMIT 10 clause limits the number of results to the top 10 longest response times.

Note that the response_time column represents the amount of time a query has been running since it started and does not reflect the actual response time for the query as seen by clients. The actual response time for a query depends on many factors, including the complexity of the query, the state of the database, and the resources available on the system.

About Shiv Iyer 443 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.