PostgreSQL interview question · Most asked
How does MVCC work in PostgreSQL?
Summary
Every row version (tuple) carries xmin (the transaction that created it) and xmax (the transaction that deleted or updated it). An UPDATE writes a new tuple and marks the old one dead; each transaction's snapshot decides which versions it can see. Readers never block writers and writers never block readers.
Longer Answer
Unlike InnoDB, PostgreSQL keeps old versions in the table itself rather than in an undo log. That makes rollbacks instant, but dead tuples accumulate and must be cleaned up by VACUUM. Updates also write new index entries unless the update is HOT (heap-only tuple: no indexed column changed and there is room on the same page).
Visibility checks use the snapshot (which transactions were in progress) and commit status from the commit log; hint bits cache that status on tuples after the first check.
Key Points
- Tuples carry xmin/xmax.
- UPDATE = new tuple + dead old one.
- Old versions live in the table; VACUUM removes them.
- HOT updates avoid index writes.
Explanation
What interviewers look for
Understanding that PostgreSQL's MVCC design is why VACUUM exists, and its consequences (bloat, write amplification).
Follow-up questions
- Why can a long-running transaction cause table bloat?
More most-asked PostgreSQL questions
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?
- What index types does PostgreSQL support, and when do you use each?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What does VACUUM do, and how does autovacuum work?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- How do you build a job queue on PostgreSQL with SELECT ... FOR UPDATE SKIP LOCKED?