Troubleshooting

Fast Triage Checklist

Use these guided diagnostics to fix preload errors, missing metrics, excessive overhead, and reset issues. Each section provides ready-to-run SQL or configuration commands.

• Verify shared_preload_libraries includes pg_stat_insights and PostgreSQL was restarted
• Run SELECT * FROM pg_extension WHERE extname = 'pg_stat_insights'; to confirm the installed version
• Check log_min_messages = debug1 temporarily if extension loading fails
• Collect EXPLAIN (ANALYZE, BUFFERS) plans before tuning query-level metrics

Run remediation in staging first. Revert temporary settings (e.g. debug logging) after successful validation.

Extension Load & Setup

Solve common installation and configuration errors that prevent pg_stat_insights from loading.

Check shared_preload_libraries

SHOW shared_preload_libraries;

Install or update extension

CREATE EXTENSION IF NOT EXISTS pg_stat_insights;
ALTER EXTENSION pg_stat_insights UPDATE;

Capture load errors

journalctl -u postgresql -n 100 | grep pg_stat_insights

Missing or Incomplete Metrics

Ensure collections are running and view-specific tables are populated.

Check primary view

SELECT COUNT(*) AS fingerprints
  FROM pg_stat_insights;

Increase retention & max fingerprints

ALTER SYSTEM SET pg_stat_insights.max = 10000;
ALTER SYSTEM SET pg_stat_insights.save = true;
SELECT pg_reload_conf();

Reset safely

SELECT pg_stat_insights_reset();

High Overhead or Bloat

Reduce collection overhead when pg_stat_insights impacts latency or memory.

Disable planning metrics temporarily

ALTER SYSTEM SET pg_stat_insights.track_planning = false;
SELECT pg_reload_conf();

Filter utility commands

ALTER SYSTEM SET pg_stat_insights.track_utility = false;
SELECT pg_reload_conf();

Archive snapshots & vacuum stats schema

VACUUM ANALYZE pg_stat_insights;
VACUUM ANALYZE pg_stat_insights_plan;

Export & Alerting

Wire pg_stat_insights metrics into Prometheus/Grafana and alert on regressions.

Prometheus scrape excerpt

pg_stat_insights:
  query: |
    SELECT queryid,
           calls,
           total_exec_time,
           mean_exec_time
      FROM pg_stat_insights
     WHERE calls > 100;
  metrics:
    - queryid:
        usage: "LABEL"
    - calls:
        usage: "COUNTER"
    - total_exec_time:
        usage: "COUNTER"
    - mean_exec_time:
        usage: "GAUGE"

Example alert rule

- alert: SlowQueriesSpike
  expr: topk(1, increase(pg_stat_insights_total_exec_time[10m])) > 1.5e05
  for: 5m
  labels:
    severity: warning