What is transaction ID wraparound in PostgreSQL and how is it prevented?
Transaction IDs are 32-bit numbers, so after about two billion, old ones would look like the future and rows would vanish. VACUUM freezes old row versions to prevent this, and PostgreSQL forces aggressive vacuums and finally stops accepting writes if it falls too far behind.
Explain it simply
Think of a clock with only 12 numbers. After 12 comes 1 again, so you cannot tell whether "3" means three hours ago or nine hours from now. PostgreSQL transaction IDs behave like that clock, with a circle of about 4.2 billion where half counts as "past" and half as "future".
Without help, a row written long ago would eventually seem to be created in the future and become invisible. To prevent it, VACUUM marks very old row versions as frozen, meaning "older than every transaction, always visible".
Example
-- How close is each database to the danger zone?
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
-- Relevant settings
SHOW autovacuum_freeze_max_age; -- default 200 millionWhen a table's age passes autovacuum_freeze_max_age, autovacuum starts an anti-wraparound vacuum even if autovacuum is disabled. If age gets within about 3 million of the limit, PostgreSQL refuses new write transactions until a vacuum finishes.
Long-running transactions, unused replication slots and prepared transactions can hold back freezing.
What to say in the interview
"XIDs are 32-bit and circular, so VACUUM must freeze old rows. I monitor age(datfrozenxid), keep autovacuum healthy, and look for long transactions or stale replication slots that block it. Ignoring it can end in a forced read-only state."
Common mistakes
- Disabling autovacuum to save I/O.