Documentationpg_stat_insights Documentation
Monitoring Integration
Prometheus Exporter
Configure postgres_exporter or the PostgreSQL built-in exporter to scrape pg_stat_insights data using custom queries.
queries.yaml
pg_stat_insights:
query: |
SELECT queryid,
LEFT(query, 100) AS query_snippet,
calls,
mean_exec_time,
total_exec_time,
shared_blks_read,
shared_blks_hit
FROM pg_stat_insights
WHERE calls > 100;
metrics:
- queryid:
usage: 'LABEL'
description: 'Fingerprint'
- query_snippet:
usage: 'LABEL'
description: 'Query preview'
- calls:
usage: 'COUNTER'
description: 'Executions'
- mean_exec_time:
usage: 'GAUGE'
description: 'Average execution time (ms)'
- shared_blks_read:
usage: 'COUNTER'
description: 'Blocks read'
- shared_blks_hit:
usage: 'COUNTER'
description: 'Blocks served from cache'Grafana Dashboards
Visualise execution time, cache hit ratio, and WAL generation to spot regressions quickly. Import a json dashboard or build panels using the sample PromQL queries below.
PromQL: Top queries by runtime
topk(10, increase(pg_stat_insights_total_exec_time[5m]))PromQL: Cache hit ratio
1 - (sum(increase(pg_stat_insights_shared_blks_read[5m])) /
sum(increase(pg_stat_insights_shared_blks_hit[5m]) + increase(pg_stat_insights_shared_blks_read[5m])))Alerting Rules
Create alerts that trigger when runtime increases beyond a tolerated baseline or when lag accumulates due to IO pressure.
Alertmanager rule
groups:
- name: pg_stat_insights
rules:
- alert: SlowQueriesSpike
expr: topk(1, increase(pg_stat_insights_total_exec_time[10m])) > 1.5e05
for: 5m
labels:
severity: warning
annotations:
summary: "pg_stat_insights slow query spike"
description: "Runtime increased past 150s in the last 10 minutes."Automated Resets
Reset counters during maintenance and archive snapshots for historical comparisons.
Capture + reset
COPY (
SELECT now() AS captured_at, *
FROM pg_stat_insights
) TO '/var/lib/postgresql/metrics/pg_stat_insights_snapshot.csv' WITH CSV;
SELECT pg_stat_insights_reset();