Vacuum, bloat, and transaction ID age

Understand

Mental model

Updates and deletes create row versions that remain until no transaction can need them. Standard vacuum marks that space reusable inside the table, updates visibility information, and freezes sufficiently old transaction IDs. It normally does not return the file to the operating system. Vacuum health is therefore about keeping cleanup and freezing ahead of change, not forcing files to shrink after every delete.

Calibrate

What healthy usually looks like

Dead-row estimates stabilise relative to change rate and table size.
Autovacuum runs frequently enough that table XID age remains well below emergency thresholds.
Long transactions, abandoned replication slots, and prepared transactions do not pin old horizons indefinitely.

Observe

Ask the database a bounded question

Prioritise tables where dead rows or old transaction IDs suggest maintenance is falling behind.

SELECT
  table_stats.schemaname,
  table_stats.relname,
  table_stats.n_live_tup,
  table_stats.n_dead_tup,
  table_stats.last_autovacuum,
  table_stats.autovacuum_count,
  age(table_class.relfrozenxid) AS transaction_id_age
FROM pg_stat_user_tables AS table_stats
JOIN pg_class AS table_class
  ON table_class.oid = table_stats.relid
ORDER BY transaction_id_age DESC, table_stats.n_dead_tup DESC
LIMIT 30;

low observation cost

Interpret

Read the output

Compare vacuum work with the table's change rate. A busy table may need lower scale factors or more vacuum capacity.
Check old `backend_xmin`, prepared transactions, and inactive replication slots when cleanup cannot advance.
Use standard vacuum for routine work; `VACUUM FULL` rewrites and takes an `ACCESS EXCLUSIVE` lock.

Avoid

Common traps

Using `VACUUM FULL` as routine maintenance because a relation file did not shrink.
Raising autovacuum thresholds globally without measuring the tables that generate the workload.
Watching dead tuples while ignoring transaction ID age and old snapshot holders.

Verify

Check your reasoning

Continue

Related runbooks

Verify independently

Sources

PostgreSQL 18: Routine Vacuuming