pg_stat_monitor is a Query Performance Monitoring tool for PostgreSQL.
pg_stat_monitor collects performance statistics and provides query performance insights in a single view and graphically in histogram.
These insights allow database users to understand query origins, execution, planning statistics and details, query information, and metadata. This significantly improves observability, enabling users to debug and tune query performance.
This is the documentation for the latest release,
pg_stat_monitor 2.0.2 (Release notes).
pg_stat_monitor is developed on the basis of
pg_stat_statements as its more advanced replacement. While
pg_stat_statements provides ever-increasing metrics,
pg_stat_monitor aggregates the collected data, saving user efforts for doing it themselves.
pg_stat_monitor stores statistics in configurable time-based units – buckets. Such bucket-based data collection allows focusing on statistics generated for shorter time periods and makes query timing information such as max/min/mean time more accurate.
pg_stat_monitor tracks the following operations:
- stored procedures and other non-utility statements
- Time Interval Grouping: Instead of supplying one set of ever-increasing counts,
pg_stat_monitorcomputes stats for a configured number of time intervals - time buckets. This allows for much better data accuracy, especially in the case of high resolution or unreliable networks.
- Multi-Dimensional Grouping: While
pg_stat_statementsgroups counters by userid, dbid, queryid,
pg_stat_monitoruses a more detailed group for higher precision. This allows a user to drill down into the performance of queries.
- Capture Actual Parameters in the Queries:
pg_stat_monitorallows you to choose if you want to see queries with placeholders for parameters or actual parameter data. This simplifies debugging and analysis processes by enabling users to execute the same query.
- Query Plan: Each SQL is now accompanied by its actual plan that was constructed for its execution. That’s a huge advantage if you want to understand why a particular query is slower than expected.
- Tables Access Statistics for a Statement: This allows us to easily identify all queries that accessed a given table. This set is at par with the information provided by the
- Histogram: Visual representation is very helpful as it can help identify issues. With the help of the histogram function, one can now view a timing/calling data histogram in response to an SQL query. And yes, it even works in psql.
pg_stat_monitor is compatible with:
- PostgreSQL provided by PostgreSQL Global Development Group (PGDG)
- Percona Distribution for PostgreSQL.
pg_stat_monitor should work on the latest version of both Percona Distribution for PostgreSQL and PostgreSQL, but is only tested with these versions:
|Percona Distribution for PostgreSQL||11, 12, 13, 14, 15 and 16||Percona|
|PostgreSQL||11, 12, 13, 14, 15 and 16||PostgreSQL Global Development Group (PGDG)|
The RPM packages are also available in the official PostgreSQL (PGDG) YUM repositories.
Created: September 19, 2023