Multiversion Concurrency Control (MVCC) Keeps Old Row Versions, So Updates Leave Dead Rows
To let readers and writers work at the same time without blocking each other, PostgreSQL never overwrites a row in place: an UPDATE writes a brand-new row version and marks the old one as no longer current, and a DELETE only marks. Each version carries the transaction numbers that say which queries may see it, so an old statement keeps seeing the old data. The price is that the table accumulates dead row versions that still occupy pages, so a table updated millions of times can be mostly garbage that every scan still has to read past.
Questions this Concept answers
- Why can a long-running `SELECT` keep reading data that another transaction has already replaced?
Multiversion Concurrency Control in Databases: Row Versions Instead of Write Locks
Multiversion concurrency control (MVCC) is a database concurrency mechanism in which an update does not overwrite a row in place but writes a new version of that row tagged with a version identifier …