Every UPDATE Leaves a Ghost: MVCC, Bloat, and VACUUM in PostgreSQL

This post provides a deep dive into PostgreSQL's MVCC implementation, explaining how tuples, transaction IDs (XIDs), and snapshots work together to provide consistent and isolated data views. It details the `xmin` and `xmax` system columns for tracking tuple versions, the mechanics of snapshots using `pg_current_snapshot()`, and how visibility rules are applied. The post also introduces subtransactions and their impact on XIDs, highlighting the underlying mechanisms that lead to bloat and the eventual need for VACUUM.