PostgreSQL uses tuple freezing to prevent transaction ID (XID) wraparound. VACUUM marks sufficiently old row versions as frozen, identifying them as definitively past and outside the current XID space. section 6.6.1
Without timely freezing, an old tuple can become indistinguishable from a future transaction after roughly 2.15 billion XIDs. PostgreSQL then launches noninterruptible anti-wraparound autovacuum, potentially slowing the system severely. section 6.6.1
Operational practices reduce the chance that automatic freezing falls behind:
- Batch commits when atomicity permits; a batch size of 1000 can reduce the application’s XID burn rate to about one-thousandth of its previous level. section 6.6.2
- Tune autovacuum to work more aggressively when heavy OLTP activity burns through XIDs faster than routine maintenance can freeze tuples. section 6.6.2
- Investigate idle-in-transaction sessions because they can hold back the XID horizon and defer freezing of old rows. section 6.6.1
- Review whether high-volume data must remain in PostgreSQL; discard unused ingestion data or resample it before storage when appropriate. section 6.6.2
A high-throughput workload
An OLTP application can consume billions of XIDs quickly by committing every operation separately. Before automatic freezing falls behind, reorganize incoming work into batches—such as 1000 operations per commit—and make autovacuum more aggressive where necessary. section 6.6.2
Figure 6.3 Depiction of PostgreSQL’s transaction IDs as a circular space
Use the circular XID-space diagram to understand why sufficiently old tuples can resemble future transactions and why freezing removes that ambiguity. section 6.6.1
