autovacuum_analyze_threshold

Definition PG 18 manual

Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

How it works

Minimum number of tuple inserts, updates, or deletes prior to analyze. A configuration reload applies a new value; existing work already in flight is not retroactively changed.

The automatic-ANALYZE trigger is autovacuum_analyze_threshold plus autovacuum_analyze_scale_factor times pg_class.reltuples; inserts, updates, and deletes feed the cumulative counter. Per-table storage parameters can override the global values, and the statistics are eventually consistent estimates.

Monitor and change autovacuum_analyze_threshold together with autovacuum_analyze_scale_factor, default_statistics_target, track_counts. Validate on the relevant server role and real workload, then use its sighup context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Changing the global value while a table storage parameter overrides it.

Treating reltuples and cumulative change statistics as exact real-time counts.

Blaming a threshold without checking long transactions, replication slots, and worker saturation.

Buying short-term quiet by deferring maintenance until wraparound failsafe activates.

Workload guidance

OLAP: Run explicit ANALYZE/VACUUM after bulk loads instead of waiting only for proportional triggers; plan freezing and visibility-map advancement separately for append-only partitions.

OLTP: Tune autovacuum_analyze_threshold from table size, change rate, and maintenance SLA, using per-table thresholds for large/hot relations. Observe trigger intervals, dead tuples, and ANALYZE/VACUUM duration.

SMALL: Start with upstream. Fixed thresholds dominate on small tables; after changes, confirm worker and I/O headroom.