hardConceptScenarioSeniorStaffLead
In PostgreSQL, what does an UPDATE do to a row under MVCC, and why does a long-running transaction make that expensive?
6 min readUpdated 2026-08-15
Answer
Short answer
An UPDATE writes a new row version and leaves the old one visible to older snapshots; vacuum reclaims it only once no snapshot can see it.A long transaction pins that horizon, so dead tuples accumulate as bloat and freezing stalls, which is what leads to transaction-id wraparound.
Keep mvcc explicit in the answer because that is the concept the interviewer is actually trying to test. A good mvcc explanation names the trade-off, the failure mode, and the evidence you would use before choosing. Use mvcc once more at the decision point so the answer reads as judgement rather than a detached example.
Was this answer helpful?
Likely follow-ups
- How would you find the transaction that is holding the horizon back right now?
- What is a HOT update, and which of these costs does it avoid?
- Why does VACUUM FULL reclaim disk space when plain VACUUM does not, and what does it cost you?
- An index has bloated but the table has not. How does that happen and how do you fix it online?
Related questions
- Monitoring says the age of the oldest transaction ID on this PostgreSQL database keeps climbing. What is it measuring, and what happens if it is ignored?hardAlso on postgresql and vacuum5 min
- Walk me through the transaction isolation levels and which anomalies each one permits.hardAlso on mvcc and postgresql6 min
- A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?hardAlso on postgresql5 min
- You are getting a handful of deadlock errors an hour under load. How do you find the cause, and how do you stop them?hardAlso on postgresql7 min