Parameter · Vacuuming / Default Behavior
vacuum_truncate
Enables vacuum to truncate empty pages at the end of the table.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
If the last pages of a table are empty after cleanup, VACUUM can give that space back to the operating system by truncating the file. To do that it briefly needs an exclusive lock on the table, which blocks even SELECTs.
Turning it off keeps the space inside the table for future rows and avoids the lock.
For DBAs and operators
New as a server setting in PostgreSQL 18 (the per-table option vacuum_truncate and VACUUM (TRUNCATE false) existed before; both still override it). The lock is taken conditionally: VACUUM tries for a few seconds and gives up instead of waiting forever, and it releases the lock as soon as another session queues for it.
Why support cares
- On a standby, the truncation is replayed with an
AccessExclusiveLock, which conflicts with every query there: long queries on replicas get ERROR: canceling statement due to conflict with recovery (DETAIL: User was holding a relation lock for too long.), or replay waits up tomax_standby_streaming_delay. Turning truncation off for hot tables is a common fix. - Busy queue tables repeatedly grow and shrink; truncation there is pure overhead.
VERBOSE output: INFO: table "orders": truncated 89 to 23 pages. Autovacuum logs it as pages: 66 removed, 23 remain.
How to change it
Any user can change it for their session with SET.