autovacuum_analyze_scale_factor

Definition PG 18 manual

Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.1 (10% of table size). 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

Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. 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_scale_factor together with autovacuum_analyze_threshold, 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_scale_factor 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.