Explain transaction ID wraparound the way the committer sees it, how does Postgres keep a row from four billion transactions ago still visible?
Transaction IDs are 32-bit numbers, and PostgreSQL compares them in a circle: for any transaction, about two billion IDs are in its past and about two billion are in its future. An unfrozen row from two billion transactions ago would flip from past to future and disappear from view. Freezing prevents that. Vacuum marks old row versions as frozen, so they stay visible whatever the counter reads. Two points decide this answer. Age is relative, so a very old row is a problem and an old database is not. And freezing is not the visibility map's all-visible flag, although vacuum sets both. In production, PostgreSQL stops assigning new transaction IDs before any row is misread. This page gives a 90-second answer and the corrections worth volunteering.
What the interviewer is scoring
Score points for modular visibility, the commit-log role, and the oldest-unfrozen age as what you actually monitor, plus knowing that the system refuses writes to protect itself rather than silently misreading rows.
In short: They want the mechanism: 32-bit ids compared modulo 2^31, the commit log recording commit status, and freezing as the thing that lets old ids be recycled safely.
The spoken answer, backed by captured output
- The full 90-second answer, written first person, the way you would actually say it
- Why: the mechanism, the decision points, and where the claim stops
- Verification steps with output captured verbatim from a controlled PostgreSQL lab run.
- Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point
How to reason through it
- State the constraint: transaction ids are 32-bit and visibility is judged modulo 2^31, half past and half future.
- Bring in the commit log: it records whether each transaction committed.
- Explain freezing: marking a row version unconditionally visible so its inserting id can be recycled.
- Distinguish freezing from the visibility map's all-visible page flag; vacuum maintains both and they answer different questions.
- Name what you monitor: age of the oldest unfrozen id, per table as well as per database.
- Cover the safety net: escalating anti-wraparound vacuum, then a protective refusal of new write transactions.
What I would verify
- Read the age of the oldest unfrozen id per table as well as per database. The database figure is the age of its oldest table, but it does not say which table that is.
- Identify what is preventing freezing: a long-running transaction, a replication slot that is not advancing, a prepared transaction, or a standby with hot_standby_feedback on.
- Look for failsafe behaviour in the logs, which means the system is already prioritising freezing over other work.
- Confirm whether autovacuum is being repeatedly cancelled on the offending table.
Follow-ups they push on
- So can wraparound actually corrupt data?
- Autovacuum is running but the age is not falling. Why?
- Why per table and not just per database?
The probes are open. Pro carries the spoken answer, the reasoning behind it, and the verification steps, including a worked response to each of these.
Concepts tested
Learn it, run it, then say it
Three steps, in order. Nothing here is a detour.
1 · Learn the mechanism
Understand it before you try to say it.
2 · Practise it for real
Run it once so the answer describes something you have seen.
3 · Rehearse the next question
Keep going while the mechanism is fresh.
Questions that go with this one
- staff · ProGive me a multixact scenario and take it deep, when does one get created, and how does it bite you in production?The second 32-bit clock, which teams routinely forget to watch.
- mid · FreeHow does VACUUM relate to MVCC, and what happens if it falls behind?Freezing is vacuum's other job, and the same holders stall both.