Documentationpg_stat_insights Documentation

Configuration Reference

Baseline Configuration

Add pg_stat_insights to shared_preload_libraries and restart PostgreSQL. Tune retention and tracking filters to balance insight with overhead.

postgresql.conf

shared_preload_libraries = 'pg_stat_insights'
pg_stat_insights.max = 5000
pg_stat_insights.track = 'all'
pg_stat_insights.track_planning = true
pg_stat_insights.track_utility = true
pg_stat_insights.save = true

Revisit pg_stat_insights.max if your workload contains more than 5k unique fingerprints per maintenance window.

Parameter Catalog

pg_stat_insights.max

Controls how many distinct query fingerprints are tracked before older entries age out.

Default: 5000
Context: sighup

Increase for diverse workloads, but monitor memory footprint.

pg_stat_insights.track

Defines which statements are tracked (top-level only, or include nested statements).

Default: 'top'
Context: user

Use 'all' to capture functions and triggers; use 'top' to minimise overhead.

pg_stat_insights.track_utility

Includes utility commands (DDL, COPY, VACUUM) in the metrics.

Default: off
Context: sighup

Enable in staging during migrations to observe DDL impact.

pg_stat_insights.track_planning

Adds plan-time metrics such as total_plan_time and jit_functions.

Default: off
Context: sighup

Turn on temporarily during tuning sessions; planning stats add overhead.

pg_stat_insights.save

Persists statistics to disk across restarts and promote/failover events.

Default: on
Context: sighup

Leave enabled in production to keep baselines consistent.

Runtime Adjustments

Most parameters accept SIGHUP, enabling change via ALTER SYSTEM without a full restart. Use reloads during investigations and reset once complete.

Toggle parameters at runtime

-- Adjust track scope without restart
ALTER SYSTEM SET pg_stat_insights.track = 'all';
SELECT pg_reload_conf();

-- Enable planning metrics for 1 hour window
ALTER SYSTEM SET pg_stat_insights.track_planning = true;
SELECT pg_reload_conf();
-- ...collect diagnostics...
ALTER SYSTEM RESET pg_stat_insights.track_planning;
SELECT pg_reload_conf();

Verify Retention & Memory Usage

The view below summarises fingerprint counts and execution totals per database to ensure pg_stat_insights.max is sufficient.

Fingerprint inventory

SELECT datname,
       sum(total_exec_time) AS total_exec_ms,
       sum(calls) AS calls,
       count(*) AS fingerprints
FROM pg_stat_insights
 GROUP BY datname
 ORDER BY total_exec_time DESC;

Guidelines

If fingerprints approach the maximum, increase pg_stat_insights.max or shorten retention windows via cron job resets.
Monitor shared memory usage with SELECT pg_size_pretty(pg_stat_file('global/pg_stat/pg_stat_insights.stat').size);.
Reset statistics after major releases by calling SELECT pg_stat_insights_reset();.

Observability Integrations

Expose configuration metrics to dashboards or alert when planning metrics are accidentally left enabled in production.

Configuration audit

SELECT name,
       setting,
       source
  FROM pg_settings 
 WHERE name LIKE 'pg_stat_insights%'
 ORDER BY name;

Prometheus textfile exporter

#!/usr/bin/env bash
cat <<'EOF'
# HELP pg_stat_insights_planning_enabled 1 when planning stats are active
# TYPE pg_stat_insights_planning_enabled gauge
pg_stat_insights_planning_enabled $(psql -t -c "SELECT CASE WHEN current_setting('pg_stat_insights.track_planning')::boolean THEN 1 ELSE 0 END;")
EOF

PreviousGetting Started

NextMetrics Reference Guide