Monitoring query performance is a key part of managing and tuning a PostgreSQL database. While the built-in pg_stat_statements extension offers valuable insights, a more advanced observability tool—pg_stat_monitor—provides even deeper analysis and context.
Developed by Percona, pg_stat_monitor (PGSM) enhances PostgreSQL observability by combining query statistics, execution plans, and application-level insights in one unified view. It helps database administrators and developers better understand query behavior and improve performance with precision.
What is pg_stat_monitor?
pg_stat_monitor is a PostgreSQL query performance observability extension that integrates multiple monitoring tools into a single interface. It combines:
- pg_stat_activity for connection and application details
- pg_stat_statements for query statistics
- auto_explain for query execution plan details
Together, these components provide a comprehensive and unified picture of PostgreSQL performance.
Key Features of pg_stat_monitor
pg_stat_monitor provides several advanced capabilities beyond pg_stat_statements:
- Multi-level query grouping for granular analysis
- Visibility into which clients, users, and applications run queries
- Execution-time and failure tracking for queries
- Capture and analysis of query plans
- Time-based statistics using configurable time buckets
- Histogram-based latency visualization
- Fully compatible with pg_stat_statements (acts as a drop-in replacement)
- Open-source and actively maintained by Percona
How pg_stat_monitor Differs from pg_stat_statements
While pg_stat_statements provides aggregated query statistics, pg_stat_monitor expands observability by adding:
- Time-based query statistics grouped by buckets
- Query parameter capture for better accuracy
- Execution plan visibility integrated via auto_explain
- Histogram analysis for latency distribution
- Multidimensional grouping by user, database, and application
Installation Steps
Installing pg_stat_monitor is straightforward and similar to most PostgreSQL extensions.
git clone https://github.com/percona/pg_stat_monitor.git
cd pg_stat_monitor
make USE_PGXS=1 PG_CONFIG=$(which pg_config)
sudo make USE_PGXS=1 PG_CONFIG=$(which pg_config) install
Enable the extension in PostgreSQL:
CREATE EXTENSION pg_stat_monitor;
Select * from pg_available_extensions where name = ‘pg_stat_monitor’;
\dx+ pg_stat_monitor
Example output:
Objects in extension "pg_stat_monitor"
--------------------------------------------
function decode_error_level(integer)
function get_cmd_type(integer)
function get_histogram_timings()
function histogram(integer,bigint)
function pgsm_create_13_view()
function pgsm_create_14_view()
function pgsm_create_15_view()
function pgsm_create_17_view()
function pgsm_create_18_view()
function pgsm_create_view()
function pg_stat_monitor_internal(boolean)
function pg_stat_monitor_reset()
function pg_stat_monitor_version()
function range()
type pg_stat_monitor
type pg_stat_monitor[]
view pg_stat_monitor
(17 rows)
Understanding the Data with Example Queries
Once installed and enabled, the pg_stat_monitor view allows you to query detailed statistics for deep performance insights.
Basic Query Information
SELECT userid, datname, queryid, substr(query, 0, 50) AS query, calls
FROM pg_stat_monitor;
This query displays the most frequently executed statements along with the user and database context.
Query Source and Application Insight
SELECT application_name, client_ip, substr(query, 0, 100) AS query
FROM pg_stat_monitor;
This query identifies which applications or clients are executing particular statements—useful in environments with multiple client connections.
Plan Statistics and Execution Overview
SELECT queryid,rows, query, calls
FROM pg_stat_monitor;
This helps analyze how many plans were generated, how many rows were processed, and how often each query was executed.
Query Plan Details
SELECT substr(query_plan, 0, 80) AS query_plan
FROM pg_stat_monitor
WHERE query_plan IS NOT NULL
LIMIT 10;
This shows actual query execution plans for deeper insight into how queries are optimized.
Histogram Analysis
pg_stat_monitor includes histogram-based latency analysis:
What is a histogram?
In the pg_stat_monitor extension, a histogram is a statistical distribution that helps you understand how a particular metric (like query execution time, plan time, or I/O wait time) is spread across a range of values — not just the average.
SELECT resp_calls, query FROM pg_stat_monitor;
These queries visualize response time distributions and help detect slow-performing queries.
You get result like this
resp_calls | query
-----------------------------------------------+--------------------------------------------------
{2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0} | BEGIN ISOLATION LEVEL REPEATABLE READ READ WRITE
Key Functions in pg_stat_monitor
pg_stat_monitor includes multiple internal functions for managing and analyzing statistics:
- pg_stat_monitor_internal() — retrieves raw monitoring data
- pg_stat_monitor_reset() — resets all collected metrics
- pg_stat_monitor_version() — displays the current installed version
- histogram() — provides histogram data for query latency visualization
- range() — defines the range of histogram buckets
- decode_error_level() — translates internal error codes into readable messages
Important Configuration Parameters
To view all pg_stat_monitor settings:
SHOW pg_stat_monitor.pgsm_;
Key parameters include:
- pg_stat_monitor.pgsm_bucket_time: defines how long each statistics bucket lasts (in seconds).
- pg_stat_monitor.pgsm_enable_query_plan: enables capturing of execution plans for every query.
- pg_stat_monitor.pgsm_normalized_query: normalizes queries by parameterizing literals for aggregation.
- pg_stat_monitor.pgsm_histogram_buckets: determines the number of histogram buckets for latency analysis.
- pg_stat_monitor.pgsm_track_planning: tracks both planning and execution times for complete performance metrics.
Real-World Use Case
Consider a large e-commerce platform experiencing occasional slowdowns. Using pg_stat_monitor, you can:
- Identify which queries consume the most time and resources
- Determine which user, application, or client IP issues those queries
- Analyze query plans for missing indexes or inefficient joins
- Review histograms to identify when performance degradation occurs
By analyzing this data, performance bottlenecks can be isolated and addressed effectively.
Conclusion
pg_stat_monitor represents a major advancement in PostgreSQL performance observability. It provides a complete, time-based, and context-aware understanding of how queries behave and perform. With its integrated query statistics, plan analysis, and latency visualization, it empowers database administrators and developers to:
- Monitor query behavior over time.
- Identify bottlenecks with accuracy.
- Understand execution patterns and application impact.
- Optimize workloads for better overall performance.
If you already use pg_stat_statements, switching to pg_stat_monitor is a simple upgrade that provides far richer insight.