Getting Started with pg_stat_insights

Prerequisites

Enable 52 query performance metrics and 11 diagnostic views. Follow the three-step installation, then explore tuning guides below.

pg_stat_insights ships as a single extension and requires no external collectors. Load it at startup, create the extension, then query the telemetry views immediately.

• PostgreSQL 16, 17, or 18 with superuser access
• Ability to modify shared_preload_libraries and restart PostgreSQL
• Build toolchain (make, gcc/clang) for optional source installs
• Optional: Package repository or Homebrew for binary installs

Installation in 3 Steps

Execute these commands from the PostgreSQL superuser. Restart is required only after enabling shared_preload_libraries.

Step 1 · Update postgresql.conf

Enable the extension at startup and restart PostgreSQL.

-- Enable pg_stat_insights in PostgreSQL configuration
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_insights';

-- Restart PostgreSQL after changing shared_preload_libraries
-- sudo systemctl restart postgresql
-- brew services restart postgresql@18

Step 2 · Create the Extension

Connect to the target database and register pg_stat_insights.

-- Connect to your database
\c your_database_name

-- Create the extension
CREATE EXTENSION pg_stat_insights;

Step 3 · Run First Diagnostics

Inspect top slow queries, cache ratios, and disk usage immediately.

-- Slowest queries by total runtime
SELECT query, calls, total_exec_time, mean_exec_time
FROM   pg_stat_insights_top_by_time
LIMIT  10;

-- Queries with the weakest cache hit ratios
SELECT query, shared_blks_hit, shared_blks_read,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_ratio
FROM   pg_stat_insights_top_cache_misses
WHERE  shared_blks_hit + shared_blks_read > 0
LIMIT  10;

Complete Installation Reference

Build from source, install via packages, or automate with CI. Use the commands below as a template.

Build from source

# Clone repository
git clone https://github.com/pgelephant/pg_stat_insights.git
cd pg_stat_insights

# Build extension shared library
make clean && make

# Install binaries (may require sudo)
sudo make install

# Override pg_config if using custom PostgreSQL
make install PG_CONFIG=/path/to/pg_config

Package installs

# Ubuntu / Debian
sudo apt-get install postgresql-18-pg-stat-insights

# RHEL / Rocky / AlmaLinux
yum install pg-stat-insights_18

# Homebrew (macOS)
brew install pg_stat_insights

Configuration Checklist

Tune runtime overhead, memory consumption, and view attribution with the optional GUC parameters below.

-- Optional GUC tuning
SET pg_stat_insights.track_timing = on;
SET pg_stat_insights.max_entries = 10000;
SET pg_stat_insights.track_histogram = on;
SET pg_stat_insights.track_user = on;
SET pg_stat_insights.track_application = on;

Next Steps

• Views Reference - Understand all 11 curated analytics views.
• Metrics Guide - Detailed definitions for all 52 collected metrics.
• Usage Playbooks - Troubleshoot latency, cache misses, locking, and WAL.
• Monitoring Integration - Prometheus exporters, Grafana dashboards, and alerting tips.