pg_stat_monitor

pg_stat_monitor : The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_statements. pg_stat_monitor provides aggregated statistics, client information, plan details including plan, and histogram information.

Overview

Packages

Source

pig build pkg pg_stat_monitor;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_stat_monitor;		# install via package name, for the active PG version

pig install pg_stat_monitor -v 18;   # install for PG 18
pig install pg_stat_monitor -v 17;   # install for PG 17
pig install pg_stat_monitor -v 16;   # install for PG 16
pig install pg_stat_monitor -v 15;   # install for PG 15
pig install pg_stat_monitor -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_stat_monitor';

Create this extension with:

CREATE EXTENSION pg_stat_monitor;

Usage

pg_stat_monitor: query performance monitoring tool for PostgreSQL

pg_stat_monitor is an advanced replacement for pg_stat_statements that aggregates statistics into configurable time-based buckets, provides query origin information, actual parameter capture, and query plan details.

Querying Statistics

-- Basic query monitoring
SELECT application_name, userid::regrole AS user_name,
       datname AS database_name, substr(query, 0, 50) AS query,
       calls, client_ip
FROM pg_stat_monitor;

-- Bucket-based time analysis
SELECT bucket, bucket_start_time, query, calls,
       mean_exec_time, total_exec_time
FROM pg_stat_monitor
ORDER BY total_exec_time DESC;

-- Show query plans
SELECT query, query_plan FROM pg_stat_monitor
WHERE query_plan IS NOT NULL;

Key Features

• Time-based buckets: Statistics are grouped into configurable intervals for more accurate analysis
• Client IP tracking: Each query records the originating client IP address
• Actual parameters: Optionally capture real query parameter values instead of placeholders
• Query plans: Each query is accompanied by its actual execution plan
• Top query tracking: Identify the most resource-intensive queries per bucket
• Histogram support: Visual timing distribution via histogram function

Functions

-- Reset all statistics
SELECT pg_stat_monitor_reset();

-- View internal info
SELECT * FROM pg_stat_monitor_info;

Configuration

Key parameters (set in postgresql.conf):