MVCC & Distributed Tx — Series 3

Preview — 3 of 10 questions

Beyond just reclaiming dead tuple space, what does VACUUM do to actually prevent transaction ID wraparound from happening?

AIt periodically resets the transaction id counter back to zero once it gets close to the maximum

BIt converts the table's transaction id columns from 32-bit to 64-bit automatically once wraparound risk is detected

CIt has no relationship to wraparound at all; wraparound is only prevented by autovacuum_freeze_max_age acting independently

DVACUUM "freezes" old row versions by marking their xmin with a special frozen value that's always considered "in the past" by every future transaction's visibility check, regardless of how far the transaction id counter has advanced since. It tracks the oldest not-yet-frozen transaction id per table as relfrozenxid — as long as freezing keeps up with autovacuum_freeze_max_age, the counter never actually needs to wrap around against still-relevant unfrozen rows

A HOT (Heap-Only Tuple) update avoids creating a new index entry when none of a rows indexed columns changed. Does MVCCs usual xmin/xmax versioning still apply to a HOT update?

ANo — HOT updates modify the row in place with no new version and no xmin/xmax bookkeeping at all, which is exactly what makes them fast

BHOT updates only apply to INSERT statements, not UPDATE

CHOT updates disable MVCC entirely for that specific row going forward

DYes — a HOT update still creates a genuinely new row version on the same page, with its own xmin, and sets xmax on the old version, exactly like any other update; the "HOT" optimization is specifically about not having to also insert a new entry into every index (since indexes point to the old version's location, and a same-page chain lets lookups follow through to the live version) — not about skipping MVCC versioning itself

A PREPARE TRANSACTION 'txn_42'; was issued as part of two-phase commit, but the coordinator process crashed before ever sending COMMIT PREPARED or ROLLBACK PREPARED. What's the operational consequence of leaving it in this state?

APostgreSQL automatically commits any prepared transaction that's been idle for more than a few minutes

BNothing — a prepared transaction with no coordinator has zero ongoing effect on the database until manually addressed

CThe prepared transaction continues holding all of its locks and keeping its snapshot's resources pinned indefinitely — including preventing VACUUM from cleaning up dead tuples that transaction could still theoretically need to see, and contributing to transaction id wraparound risk exactly like a very long-running open transaction would — until an administrator manually issues COMMIT PREPARED or ROLLBACK PREPARED (or, since PostgreSQL 12+, an operator explicitly forces resolution)

DPrepared transactions are automatically rolled back the moment the coordinator's connection is detected as closed

Answer all 10 questions (7 more), see explanations for every answer, and track your score.