MVCC & Vacuum

The board behind this lesson, following B-Tree on Disk and part of the same Database Internals - the deep-dive set series. It draws the instant a database decides which version of a row you get to see.

Before you start. The last lesson showed you the index: a wide, shallow tree of pages that finds any row in three or four page reads. This one is about what happens to the row itself once two people try to read it and write it at the same moment.

The idea in 30 seconds

An UPDATE never edits a row in place - it writes a whole new version and leaves the old one sitting there. That rule is MVCC.
Two readers can look at the same row at the same instant and correctly see two different versions, because each reader is handed a snapshot: a record of which changes had finished at the moment it started, and it sees only those.
Old versions do not clear themselves. VACUUM is the job that comes back later and removes the ones no reader could ever be pointed at again.
Every version is stamped with a number when it is born, and Postgres has about two billion of them before the counter wraps, rolling back to the start like an odometer. If cleanup falls behind, the database stops taking writes rather than risk a row reading as newer than it really is.
Sentry, an error-monitoring company, lost most of a working day to this in July 2015: a constantly rewritten table, cleanup that could not keep up with the id counter, and a database that refused writes until the table was cleared.

Read the story · Skip to the numbers