CodeParkMVCC (multi-version concurrency control) means PostgreSQL keeps several versions of each row.
- An
UPDATEdoes not overwrite the row in place. It writes a new version and marks the old one as expired by its transaction. - A
DELETEonly marks the row as expired.
Why readers don't block writers: each statement or transaction reads with a snapshot. The snapshot decides which row versions it can see. A reader simply sees the version that was current for its snapshot. So readers never wait for writers, and writers never wait for readers. Writers still block other writers that try to change the same row.
What VACUUM is for: the cost of MVCC is dead row versions (old versions that no transaction can see any more). VACUUM usually runs automatically as autovacuum. It:
- marks the space of dead rows as reusable
- updates the visibility map, which index-only scans use
- freezes old rows to prevent transaction ID wraparound (transaction IDs are a limited counter that eventually wraps back to the start)
Plain VACUUM does not shrink the file on disk. VACUUM FULL does shrink it, but it rewrites the whole table under an exclusive lock, which blocks all other access.
- An update creates a new row version instead of changing the row in place.
- A snapshot decides which row versions a query can see.
- Readers and writers do not block each other, but writers block other writers on the same row.
- VACUUM removes dead row versions, updates the visibility map and freezes old transaction IDs.