MVCC Visibility & Implementation

MVCC core idea

Multi-Version Concurrency Control — readers see a snapshot of the database as of transaction start; writers create new row versions without overwriting in place. No read locks on the hot path.

Postgres, InnoDB, and Oracle use variants; SQL Server uses mixed locking + row versioning.

Postgres tuple headers

Each heap row carries system columns:

Column	Role
xmin	Inserting transaction ID
xmax	Deleting/updating transaction ID (or 0)
ctid	Physical location (changes when row moves)

UPDATE = insert new version + mark old xmax; old row becomes dead tuple until vacuumed.

Snapshot visibility rules (simplified)

A row version is visible to transaction T if:

1. xmin committed before T's snapshot, and
2. xmax is null or aborted or committed after T's snapshot

pg_snapshot / txid_current() help debug visibility bugs in production.

HOT updates

Heap-Only Tuple optimization: if updated columns aren't indexed and new version fits same page, Postgres avoids updating every index — critical for update-heavy tables.

Bloat and VACUUM

Dead tuples occupy space and force scans to skip garbage. VACUUM reclaims space for reuse (doesn't always shrink file). VACUUM FULL rewrites table — locks exclusively.

Symptom	Likely cause
Table 10× logical size	Long-running tx blocking vacuum horizon
Index bloat	Heavy updates on indexed columns
Transaction ID wraparound	Autovacuum falling behind — urgent

Monitor n_dead_tup, age(datfrozenxid), autovacuum lag.

InnoDB contrast (brief)

InnoDB stores undo logs in rollback segments; old versions reconstructed from undo chain. Purge thread reclaims undo. Same trade-off: MVCC convenience vs storage reclamation work.

vs locking-based serializable

MVCC snapshots can permit write skew under Repeatable Read. Serializable adds predicate locks or SSI tracking to detect dangerous interleavings.

Senior-level signal

"Postgres is slow after a migration" → check table bloat and autovacuum before adding indexes. Long idle in transaction sessions are a storage incident waiting to happen.

Where this goes next

RDBMS Replication & Failover — when one node's MVCC snapshot isn't enough because you have copies on other machines.