staffData platformPro answerTested in a controlled lab, with the output captured

Explain transaction ID wraparound the way the committer sees it, how does Postgres keep a row from four billion transactions ago still visible?

Two details decide this answer, and both are commonly got slightly wrong. The first is that transaction ID comparison is modular, so age is relative rather than absolute, which is why a very old row is a problem and a very old database is not necessarily one. The second is what freezing actually changes about a row version, because it is frequently confused with the page-level all-visible marking that vacuum also maintains. Beyond the mechanism, a staff panel wants to hear what the failure really looks like in production, and the honest answer is that PostgreSQL defends itself long before anything is misread. This page covers a precise ninety-second version 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.

ProFrom a real interview loop

The spoken answer, backed by captured output

The question, the intro, what the interviewer is scoring, the reasoning outline, what you would verify and the follow-up probes are all open above. Pro unlocks the spoken answer to “Explain transaction ID wraparound the way the committer sees it, how does Postgres keep a row from four billion transactions ago still visible?”, the reasoning behind it, and the verification that backs it.
  • The full 90-second answer, written first person, the way you would actually say it
  • How I reason through it: the mechanism, the decision points, and where the claim stops
  • Verification steps with output captured verbatim from a controlled PostgreSQL 18 lab run.
  • Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point

Card required. Cancel before day 7 and you are not charged.

Compare plans

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, not just per database; the database figure hides its worst relation.
  • Identify what is preventing freezing: a long transaction, an unconsumed replication slot, or a prepared transaction.
  • 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

XID wraparoundclog / commit logFreezingdatfrozenxid

Learn it, run it, then say it

Three steps, in order. Nothing here is a detour.

Questions that go with this one