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?

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.

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
  • 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
Get instant access — 7-Day Pass, $12

One-time payment. Does not auto-renew.

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 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

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