pg_stat_insights Overview
What is pg_stat_insights?
pg_stat_insights augments PostgreSQL with deep query analytics, adding lightweight views that expose plan cost variance, buffer churn, wait events, and WAL amplification. It ships as an extension and integrates with standard monitoring pipelines using SQL alone.
52
Metric columns covering execution, planning, IO, WAL, JIT, and parallelism.
11
Views tailored to schedules, wait events, cache performance, and aggregates.
5
Core configuration parameters to tune sampling frequency and retention.
Key Capabilities
Workload visibility
Track per-query latency, row throughput, and block cache efficiency with automatic baselines for regression detection.
Planner insight
Compare estimated versus actual cost, row counts, and startup times to tune statistics and identify planners that need more sampling.
JIT & parallel analytics
Measure LLVM compilation overhead and parallel worker contribution to decide when JIT or parallelism should be disabled or encouraged.
WAL amplification tracking
Understand write-heavy workloads by monitoring wal_bytes, wal_fpi_bytes, and total WAL generation per query.
View the Metrics
Start with the main pg_stat_insights view for top queries, then drill into specialized views such as pg_stat_insights_plan, pg_stat_insights_jit, and pg_stat_insights_wal.
Top 10 queries by execution time
SELECT dbname,
userid,
queryid,
calls,
total_exec_time,
rows,
shared_blks_hit,
shared_blks_dirty,
wal_bytes
FROM pg_stat_insights
ORDER BY total_exec_time DESC
LIMIT 10;Next Steps
Continue with installation and configuration guides to enable the extension and integrate it into dashboards.