MVCC & VACUUM — How Postgres Actually Manages Storage

This is this course's own central architectural chapter — not a syntax difference, but a genuinely different physical storage strategy underneath everything covered so far.

MVCC — Multi-Version Concurrency Control

MVCC exists so that readers never block writers and writers never block readers — each transaction sees a consistent snapshot of the database as of when it started, even while other transactions concurrently modify data. To be fair from the outset: this isn't unique to Postgres. MySQL's InnoDB engine has real, solid MVCC too — a common misconception worth correcting directly rather than implying otherwise.

What genuinely differs is how each engine physically implements it. Every Postgres row (tuple) carries hidden system columns xmin and xmax — xmin records the transaction ID that created this row version, xmax records the transaction ID that deleted or superseded it. An UPDATE in Postgres never modifies a row in place — it creates an entirely new tuple with a new xmin, and sets xmax on the old tuple to mark it superseded. That old version isn't removed immediately; it becomes a dead tuple.

InnoDB takes a different physical approach entirely: it keeps one current row in the main table data, plus a separate undo log storing the information needed to reconstruct older versions for transactions that still need to see them. Postgres duplicates the row itself on every update; InnoDB keeps one row plus reconstructable history.

Dead Tuples & Why VACUUM Exists

This is the direct, structural consequence of Postgres's own "new tuple per update" strategy: every UPDATE and DELETE leaves behind dead tuples — no longer visible to any current or future transaction, but still physically occupying disk space until cleaned up.

VACUUM's job is to scan a table, identify dead tuples no longer needed by any currently-running transaction, and mark that space as reusable for future inserts and updates. An important nuance: ordinary VACUUM does not shrink the file on disk — that's VACUUM FULL, a much heavier, table-locking operation. Ordinary VACUUM just marks space as internally reusable, so the table can absorb new data without allocating new disk space, without ever actually returning space to the operating system.

If VACUUM falls behind — running less often than the table's own update/delete rate demands — dead tuples accumulate faster than they're reclaimed, and the table's real on-disk size can grow well beyond what its live data actually requires. This is table bloat, a genuine, real operational concern with no real equivalent in InnoDB's own architecture, precisely because InnoDB never creates new physical tuples on update the same way.

autovacuum

Postgres runs an autovacuum background process by default, automatically triggering VACUUM (and ANALYZE, which refreshes query-planner statistics) once a table crosses a configurable dead-tuple threshold — the practical, day-to-day answer to dead tuples, running automatically rather than needing manual scheduling. Tuning knobs like autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold control exactly when it triggers per table; a high-churn table often needs more aggressive tuning than the defaults provide.

Contrasted With MySQL's InnoDB

Both strategies are real, working trade-offs, not a case of one engine "having MVCC" and the other not. Postgres's strategy makes table bloat a genuine, distinctive operational concern; InnoDB avoids that specific problem, but has its own version of it — a long-running transaction can prevent old undo-log entries from being purged, causing InnoDB's own undo tablespace to grow instead. Different physical strategies, each with its own honest cost.

Hands-On Exercises