vacuum_truncate

Definition PG 18 manual

Enables or disables vacuum to try to truncate off any empty pages at the end of the table. The default value is true. If true, VACUUM and autovacuum do the truncation and the disk space for the truncated pages is returned to the operating system. Note that the truncation requires an ACCESS EXCLUSIVE lock on the table. The TRUNCATE parameter of VACUUM, if specified, overrides the value of this parameter. The setting can also be overridden for individual tables by changing table storage parameters.

How it works

Enables vacuum to truncate empty pages at the end of the table. It can be changed at session scope, so different sessions may observe different behavior.

At the end of VACUUM, PostgreSQL may take an ACCESS EXCLUSIVE lock and remove wholly empty pages from the physical tail of a table. Disabling truncation avoids that lock episode but leaves the file allocated for later reuse.

Monitor and change vacuum_truncate together with vacuum_max_eager_freeze_failure_rate, vacuum_freeze_table_age, autovacuum_freeze_max_age. Validate on the relevant server role and real workload, then use its user context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Assuming truncation removes empty pages in the middle of a relation; only a contiguous empty tail can be removed.

Ignoring the ACCESS EXCLUSIVE lock attempt and blocking latency on a busy table.

Disabling truncation and expecting the operating system file size to shrink anyway.

Forgetting a table-level vacuum_truncate storage parameter can override the session/global value.

Relying on truncation during wraparound failsafe, when VACUUM can skip it to finish sooner.

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 vacuum_truncate 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.