Skip to content

pg_stat_insights

Advanced PostgreSQL query performance monitoring, SQL optimization, and database analytics extension

Track 52 Metrics Across 11 Views - Monitor PostgreSQL Query Performance in Real-Time

PostgreSQL License: MIT Tests Metrics

Overview

pg_stat_insights is an advanced PostgreSQL extension for database performance monitoring, query optimization, and SQL analytics. Track and analyze 52 comprehensive metrics across 24 pre-built views to identify slow queries, optimize cache performance, monitor replication health (physical & logical), detect bottlenecks, and debug replication issues in real-time.

Perfect for:

  • Database Administrators monitoring PostgreSQL performance
  • DevOps teams tracking query performance and resource usage
  • Developers optimizing SQL queries and database operations
  • SREs implementing database monitoring and alerting

Key Features

  • 52 metric columns - Execution time, cache hits, WAL generation, JIT stats, buffer I/O
  • 24 pre-built views - Instant access to top slow queries, cache misses, I/O intensive operations, comprehensive replication monitoring (physical & logical) with bottleneck detection, subscription tracking, publication management, and health diagnostics
  • 11 parameters - Fine-tune tracking, histograms, and statistics collection
  • Drop-in replacement for pg_stat_statements with enhanced metrics
  • PostgreSQL 16-18 - Full compatibility with PostgreSQL 16, 17, and 18
  • 22 regression tests - Comprehensive test coverage for all features
  • Response time tracking - Categorize queries by execution time (<1ms to >10s)
  • Cache analysis - Identify buffer cache inefficiencies and optimization opportunities
  • WAL monitoring - Track write-ahead log generation per query
  • Advanced features - JSON/JSONB, arrays, partitioning, triggers, window functions
  • Time-series data - Historical performance trending and bucket analysis
  • Prometheus/Grafana ready - Pre-built dashboards and alert rules included
  • CI/CD ready - GitHub Actions workflows for multi-version testing

Quick Start

Installation

-- Step 1: Enable extension in PostgreSQL configuration
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_insights';
-- Restart PostgreSQL server required

-- Step 2: Create the extension in your database
CREATE EXTENSION pg_stat_insights;

-- Step 3: View your slowest queries instantly
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_insights_top_by_time 
LIMIT 10;

Quick Examples

Find slow queries:

SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time
FROM pg_stat_insights_slow_queries
ORDER BY mean_exec_time DESC;

Check cache efficiency:

SELECT 
    query,
    cache_hit_ratio,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_insights_top_cache_misses
WHERE cache_hit_ratio < 0.9;

Monitor WAL generation:

SELECT 
    query,
    wal_records,
    wal_bytes,
    calls
FROM pg_stat_insights
WHERE wal_bytes > 1000000
ORDER BY wal_bytes DESC;

Documentation

Why Choose pg_stat_insights?

Solve Common PostgreSQL Performance Problems:

  • Find slow queries - Identify queries consuming excessive execution time and resources
  • Optimize cache usage - Detect queries with poor buffer cache hit ratios
  • Track WAL generation - Monitor write-ahead log generation per query pattern
  • Monitor JIT compilation - Analyze Just-In-Time compilation impact on performance
  • Analyze I/O patterns - Identify queries causing excessive disk I/O
  • Detect planning issues - Track planning time vs execution time ratios
  • Monitor parallel queries - Analyze parallel worker efficiency
  • Track query trends - Historical performance analysis with time-series buckets

What's New

Version 1.1 (Unreleased)

  • Enhanced test suite: 22 comprehensive regression tests (was 13)
  • PostgreSQL 16-18 support: Full compatibility across versions
  • New test coverage: Prepared statements, complex joins, JSON/JSONB, arrays, partitioning, triggers, window functions, transactions
  • GitHub Actions: Build matrix and documentation deployment workflows
  • Deterministic tests: ORDER BY clauses and fixed timestamps
  • CI/CD ready: Automated testing across all supported versions

See CHANGELOG for complete history.

Community

License

pg_stat_insights is released under the MIT License.

Credits

Built with ❤️ by the pgElephant team.

Based on PostgreSQL's pg_stat_statements with significant enhancements.