PostgreSQL assigns each transaction a transaction ID (XID) and uses it with tuple metadata to enforce visibility under multi-version concurrency control (MVCC). Written tuples carry an xmin for the inserting XID and, when deleted, an xmax; because an update is an insert plus a delete, updates consume transaction-ID space. section 6.6.1 section 6.4.1
The operational behavior follows from treating XIDs as a bounded, circular visibility space:
- Inspect the current transaction ID with SELECT txid_current(); and obtain a fresh visibility snapshot with pg_current_snapshot(). section 6.6.1 section 6.4.1
- XIDs are unsigned 32-bit integers, providing approximately 4.3 billion values; visibility effectively divides that space into roughly 2.15 billion past and future values. section 6.6.1
- Heavy write workloads can exhaust XID distance faster than autovacuum freezes old tuples, creating pressure toward XID wraparound. section 6.6.2
- Prevent wraparound by freezing sufficiently old tuples; reduce burn rate through batching, improve autovacuum effectiveness, and avoid retaining unnecessary high-volume data. section 6.6.1 section 6.6.2
The tempting high-throughput mistake
An application commits every incoming operation separately because PostgreSQL handles fast INSERT and UPDATE workloads well. A batch size of 1,000 can reduce the resulting transaction burn rate to about one-thousandth. Conversely, a session left idle after BEGIN can retain an old visibility horizon and keep later DDL waiting until the transaction ends. section 6.6.2 section 6.5.1
Figure 6.3 Depiction of PostgreSQL’s transaction IDs as a circular space
Use the circular-space diagram to visualize why an old tuple can become ambiguous after roughly 2.15 billion XIDs and why freezing is necessary. section 6.6.1
