Parameter · Vacuuming / Freezing
vacuum_freeze_table_age
Age at which VACUUM should scan whole table to freeze tuples.
- Default:
150000000 - Type: integer
- Range: 0 … 2000000000
- Applies: Session (any user)
In plain words
Normally VACUUM skips pages already marked all-visible, which is fast but means old rows on those pages are not frozen. Once a table is older than this age (150 million), the next vacuum is aggressive: it reads every page that is not all-frozen, so the table's age can be reset.
For DBAs and operators
Compared with age(relfrozenxid) when any vacuum (manual or autovacuum) starts. The effective value is capped at 95% of autovacuum_freeze_max_age, so that a regular, cancellable autovacuum gets a chance to do the aggressive scan before a forced anti-wraparound one is needed.
The log tells you which kind ran: LOG: automatic aggressive vacuum of table "app.public.orders": index scans: 1. Aggressive vacuums also must wait for cleanup locks on every page they need to freeze, so a pinned page (cursor) can stall them.
VACUUM (FREEZE)or setting this to 0 forces an aggressive scan.- A good maintenance pattern: run manual
VACUUM (FREEZE, VERBOSE)on the oldest big tables in a quiet window before they reachautovacuum_freeze_max_age.
How to change it
Any user can change it for their session with SET.
See it in context
XID wraparound and freezing VACUUM Free space map and visibility map
Related parameters
vacuum_freeze_min_age autovacuum_freeze_max_age vacuum_multixact_freeze_table_age