PostgreSQL VACUUM and Transaction ID Wraparound

PostgreSQL uses multiversion concurrency control, so an update creates a new row version while the old tuple remains until no transaction can need it. VACUUM marks reusable space and freezes sufficiently old transaction IDs. If freezing falls too far behind, PostgreSQL protects data by refusing normal writes before IDs wrap around.

The practical model

Treat postgresql vacuum and transaction id wraparound as a system with four review points: distinguish cleanup from freezing, monitor age, not just dead rows, tune hot tables locally, respond without creating a second incident. Give each point an owner, a measurable acceptance condition, and a rollback trigger. Thresholds should come from observed workloads, contracts, or risk tolerance rather than an unexplained default.

Distinguish cleanup from freezing

Routine vacuum removes visibility obligations for dead tuples and updates the visibility map. Freezing changes old tuple metadata so age comparisons remain safe as the 32-bit transaction ID counter advances. VACUUM FULL rewrites and locks a table; it is not the normal remedy for missed autovacuum.

Monitor age, not just dead rows

Track age(datfrozenxid) by database and age(relfrozenxid) by table, then compare them with autovacuumfreezemaxage and your alert thresholds. Also watch lastautovacuum, autovacuum_count, dead tuples, long transactions, replication slots, and vacuum progress. Old snapshots can prevent cleanup even when workers run.

Tune hot tables locally

Autovacuum triggers combine a fixed threshold with a scale factor based on table size. On a huge, frequently updated table, the default scale factor can allow millions of dead tuples. Set per-table scale factors, cost limits, and worker capacity from observed write rate, vacuum duration, and available I/O.

Respond without creating a second incident

Find blockers first, especially idle-in-transaction sessions and abandoned logical slots. Vacuum the oldest at-risk tables with appropriate resources and monitor progress. Avoid indiscriminate VACUUM FULL during an emergency because its exclusive lock and disk rewrite may make availability worse.

Common pitfalls

• Disabling autovacuum to reduce I/O.

• Watching only database size.

• Leaving idle transactions open for hours.

• Assuming a managed service removes the need for age alerts.

A focused test plan

1. Distinguish cleanup from freezing: verify that routine vacuum removes visibility obligations for dead tuples and updates the visibility map. Then introduce the failure case “disabling autovacuum to reduce i/o” and confirm that the system fails visibly, stays within its safety boundary, and has a documented recovery action.

1. Monitor age, not just dead rows: verify that track age(datfrozenxid) by database and age(relfrozenxid) by table, then compare them with autovacuumfreezemax_age and your alert thresholds. Then introduce the failure case “watching only database size” and confirm that the system fails visibly, stays within its safety boundary, and has a documented recovery action.

1. Tune hot tables locally: verify that autovacuum triggers combine a fixed threshold with a scale factor based on table size. Then introduce the failure case “leaving idle transactions open for hours” and confirm that the system fails visibly, stays within its safety boundary, and has a documented recovery action.

1. Respond without creating a second incident: verify that find blockers first, especially idle-in-transaction sessions and abandoned logical slots. Then introduce the failure case “assuming a managed service removes the need for age alerts” and confirm that the system fails visibly, stays within its safety boundary, and has a documented recovery action.

Primary references

• PostgreSQL routine vacuuming documentation

Conclusion

Wraparound prevention is routine capacity management. Alert on transaction age early, remove blockers, tune the busiest tables, and reserve enough worker and I/O capacity for vacuum to keep pace.