Why pg_stat_insights
Main Statistics View (pg_stat_insights)
52 columns including userid, dbid, queryid, query text, plans, total/min/max/mean/stddev plan/exec times, rows, all buffer I/O metrics, WAL stats, JIT stats, parallel workers, and timestamps.
Top Queries by Time
pg_stat_insights_top_by_time view shows slowest queries by total_exec_time. Identifies performance bottlenecks and optimization opportunities.
Top Queries by Calls
pg_stat_insights_top_by_calls view shows most frequently executed queries. Finds high-frequency operations that need caching or optimization.
I/O Intensive Operations
pg_stat_insights_top_by_io view identifies highest I/O consumers based on shared_blks_read + temp_blks_read. Finds disk-heavy queries.
Cache Miss Analysis
pg_stat_insights_top_cache_misses view shows poor cache performers. Includes cache_hit_ratio calculation for buffer optimization.
Slow Query Detection
pg_stat_insights_slow_queries view filters queries with mean_exec_time over 100ms. Automatic slow query identification for tuning.
Error Tracking
pg_stat_insights_errors view shows queries with execution errors. Tracks failed queries for debugging and reliability improvement.
Plan Estimation Issues
pg_stat_insights_plan_errors view identifies plan estimation problems. Compares estimated vs actual rows for query planner accuracy.
Response Time Histograms
pg_stat_insights_histogram_summary aggregates queries into time buckets: <1ms, 1-10ms, 10-100ms, 100ms-1s, 1-10s, >10s for SLA tracking.
Time-Series Aggregation
pg_stat_insights_by_bucket view provides time-series data with bucket-based aggregation for trend analysis and capacity planning.
Replication Monitoring
pg_stat_insights_replication view tracks WAL sender/receiver stats, lag (write/flush/replay), sync_state, and replication health across all standbys.
11 Pre-Built Views for Complete Performance Visibility
52 Metrics
| Feature | pg_stat_statements | pg_stat_monitor | pg_stat_insights |
|---|---|---|---|
| Metric Columns | 44 | 58 | 52 |
| Pre-built Views | 2 | 5 | 11 |
| Configuration Options | 5 | 12 | 11 |
| Response Time Categories | ✗ No | ✓ Yes (10 buckets) | ✓ Yes (6 buckets) |
| Time-Series Tracking | ✗ No | ✓ Bucket-based | ✓ Bucket-based |
| TAP Test Coverage | ~ Standard | ~ Limited | ✓ 150 tests, 100% |
| Documentation | ~ Basic | ~ Medium | ✓ 30+ pages |
| Prometheus Integration | ~ Manual | ~ Manual | ✓ Pre-built queries |
| Replication Health | pg_stat_replication | Lag bytes/seconds, WAL position, state | Lag alerts, replication monitoring |
| Connection Stats | pg_stat_activity | Active, idle, idle-in-transaction counts | Connection leak detection |
| Lock Analysis | pg_locks | Lock types, blocking queries, wait times | Deadlock prevention insights |
| Vacuum Monitoring | pg_stat_progress_vacuum | Progress, tuples, phases, duration | Autovacuum optimization |
Key Features
52 Metrics
Execution time, plan time, cache hits, WAL generation, JIT stats, buffer I/O, parallel workers, and timing data, all in one extension.
11 Pre-Built Views
Access to top slow queries, cache misses, I/O intensive operations, errors, histogram summaries, and time-series aggregation.
Response Time Categories
Categorize queries by execution time: under 1ms, 1-10ms, 10-100ms, 100ms-1s, 1-10s, over 10s for SLA monitoring.
Cache Efficiency Analysis
Cache analysis with hit/miss ratios, shared_blks_hit, shared_blks_read, and buffer cache optimization insights.
WAL Generation Tracking
Monitor write-ahead log per query: wal_records, wal_fpi, wal_bytes, wal_buffers_full for write optimization.
JIT Compilation Stats
Track JIT functions, generation time, inlining, optimization, emission, and deform operations for query performance.
Parallel Query Monitoring
Track parallel_workers_to_launch vs parallel_workers_launched for parallel query efficiency analysis.
150 TAP Tests
Test suite with 16 test files, 150 test cases, 100% code coverage, and PostgreSQL 18 compatibility.
Prometheus and Grafana
Pre-built Prometheus queries, Grafana dashboards (8 panels), and 11 alert rules for production monitoring integration.
Monitor PostgreSQL Performance
Install pg_stat_insights and track 52 metrics across 11 pre-built views to identify slow queries, optimize cache performance, and monitor database health.