pg_stat_monitor

Overview

Version

Build

You can build the RPM / DEB packages for pg_stat_monitor using pig build:

pig build pkg pg_stat_monitor         # build RPM / DEB packages

Install

You can install pg_stat_monitor directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install pg_stat_monitor;          # Install for current active PG version

pig ext install -y pg_stat_monitor -v 18  # PG 18
pig ext install -y pg_stat_monitor -v 17  # PG 17
pig ext install -y pg_stat_monitor -v 16  # PG 16
pig ext install -y pg_stat_monitor -v 15  # PG 15
pig ext install -y pg_stat_monitor -v 14  # PG 14

dnf install -y pg_stat_monitor_18       # PG 18
dnf install -y pg_stat_monitor_17       # PG 17
dnf install -y pg_stat_monitor_16       # PG 16
dnf install -y pg_stat_monitor_15       # PG 15
dnf install -y pg_stat_monitor_14       # PG 14

apt install -y postgresql-18-pg-stat-monitor   # PG 18
apt install -y postgresql-17-pg-stat-monitor   # PG 17
apt install -y postgresql-16-pg-stat-monitor   # PG 16
apt install -y postgresql-15-pg-stat-monitor   # PG 15
apt install -y postgresql-14-pg-stat-monitor   # PG 14

Preload:

shared_preload_libraries = 'pg_stat_monitor';

Create Extension:

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):