Getting Started with pg_stat_insights

Requirements

Monitor PostgreSQL query performance in under 5 minutes. pg_stat_insights is a standalone extension with 52 metrics across 11 pre-built diagnostic views.

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

Installation in 3 Steps

Step 1 · Enable Extension in postgresql.conf

Add pg_stat_insights to shared_preload_libraries and restart PostgreSQL.

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

-- Restart PostgreSQL server (required for shared_preload_libraries changes)
-- On systemd systems:
-- sudo systemctl restart postgresql

-- On macOS/Homebrew:
-- brew services restart postgresql@18

Step 2 · Create the Extension

Create the extension in your target database. No additional dependencies required.

-- Connect to your database
\c your_database_name

-- Create the pg_stat_insights extension
CREATE EXTENSION pg_stat_insights;

Step 3 · View Your Performance Data

Query any of the 11 curated diagnostic views for immediate performance insights.

-- View your slowest queries
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_written
FROM pg_stat_insights_top_by_time
LIMIT 10;

-- Check cache efficiency
SELECT
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    round(100.0 * shared_blks_hit / (shared_blks_hit + shared_blks_read), 2) AS cache_hit_ratio
FROM pg_stat_insights_top_cache_misses
WHERE shared_blks_hit + shared_blks_read > 0
LIMIT 10;

Complete Installation Guide

Prefer packages or Homebrew? Use the reference commands below to build or install pg_stat_insights.

Build from source

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

# Build the extension
make clean && make

# Install system-wide
sudo make install

# Or install to custom location
make install PG_CONFIG=/path/to/pg_config

Package installation (Ubuntu / Debian)

# Add PostgreSQL repository (if not already added)
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update package list
sudo apt-get update

# Install pg_stat_insights
sudo apt-get install postgresql-18-pg-stat-insights

Homebrew installation (macOS)

# Install pg_stat_insights via Homebrew
brew install pg_stat_insights

# Or if using custom PostgreSQL installation
cd pg_stat_insights
make && make install PG_CONFIG=/usr/local/pgsql/bin/pg_config

Configuration Options

Fine-tune monitoring coverage with 5 GUC parameters concentrated on memory usage, histogram tracking, and attribution.

-- Enable query timing tracking
SET pg_stat_insights.track_timing = on;

-- Set maximum number of queries to track
SET pg_stat_insights.max_entries = 10000;

-- Enable histogram collection for response time analysis
SET pg_stat_insights.track_histogram = on;

-- Track queries by database user
SET pg_stat_insights.track_user = on;

-- Track queries by application name
SET pg_stat_insights.track_application = on;

Quick Performance Analysis

Run targeted analytics to uncover bottlenecks, cache issues, and response time distributions.

Top Slow Queries

SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    rows,
    round((shared_blks_hit * 100.0 / (shared_blks_hit + shared_blks_read))::numeric, 2) AS cache_hit_pct
FROM pg_stat_insights_top_by_time
WHERE mean_exec_time > 100  -- Queries slower than 100ms
ORDER BY total_exec_time DESC
LIMIT 20;

Cache Performance Issues

SELECT
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    shared_blks_hit + shared_blks_read AS total_blocks,
    round((shared_blks_hit * 100.0 / (shared_blks_hit + shared_blks_read))::numeric, 2) AS cache_hit_ratio
FROM pg_stat_insights_top_cache_misses
WHERE shared_blks_hit + shared_blks_read > 1000  -- Significant I/O
ORDER BY shared_blks_read DESC
LIMIT 15;

Response Time Distribution

SELECT
    response_time_category,
    count,
    round((count * 100.0 / sum(count) OVER ())::numeric, 2) AS percentage,
    round(avg_exec_time::numeric, 2) AS avg_time_ms,
    round(min_exec_time::numeric, 2) AS min_time_ms,
    round(max_exec_time::numeric, 2) AS max_time_ms
FROM pg_stat_insights_histogram_summary
ORDER BY response_time_category;

Next Steps

Continue learning about pg_stat_insights with these resources:

• Views Reference - Complete documentation for all 11 pre-built views.
• Metrics Guide - Detailed explanation of all 52 metric columns.
• Configuration - All 5 GUC parameters with tuning recommendations.
• Prometheus & Grafana - Integration guide with pre-built dashboards.