live versions
1
Lesson 05 · The rows you can't see
MVCC: UPDATE never updates
Here is the fact that reorganizes your mental model of a database: in PostgreSQL (and, via undo logs, MySQL/Oracle too), UPDATE never modifies a row. It writes a new version of the row and marks the old one as superseded: both versions coexist on disk, each stamped with the transaction IDs that created and expired it. Every transaction is then handed a snapshot: a rule for deciding which versions it's allowed to see: and reads simply filter. This is Multi-Version Concurrency Control, and it's the answer to the oldest concurrency question: how can readers and writers not block each other? By never fighting over one copy: there are as many copies as there are recent truths.
Two transactions, one row, zero locks (for reads)
Watch the version chain grow
Row: account #7, bal=300. Each version carries xmin (creator tx) / xmax (expirer tx). Open a reader (T1), then let a writer (T2) update and commit: and see what T1 can and cannot see, and why nobody waited for anybody.
Replay what just happened, because it's the whole trick: T2 wrote a complete new row version without touching the one T1 was reading: no reader lock taken, no writer waiting. T1's snapshot ("transactions committed before I began") made version selection a pure, local computation: check each version's xmin/xmax against the snapshot, no coordination required. Writers still lock against writers on the same row (two concurrent updates must serialize: one waits), but the reader/writer war that defined lock-based databases simply doesn't exist. This is why a long analytics query doesn't freeze your OLTP writes: and the price is stated in the next lab's title.
The price: dead versions, and the vacuum that collects them
Update a hot row five times, then look at the disk
Same account row, five quick updates (all committed, no readers left behind). Then count what's actually stored: and clean it up.
dead versions
0
page space used
1 row
page 42: [v1: bal=300, live]
Multiply this by every UPDATE and DELETE on every table, forever: MVCC turns modification into accumulation, and something must take out the trash. In PostgreSQL that's VACUUM (and autovacuum): it removes versions no possible snapshot could still need: which is where the operational drama lives. A single transaction held open for six hours (an idle-in-transaction connection, a forgotten psql session, a stuck batch job) pins the "oldest visible" horizon: nothing newer than it can be cleaned, table- and cluster-wide. Tables bloat, queries slow (scans wade through dead rows), and in the extreme the 32-bit transaction ID counter approaches wraparound: the emergency that forces aggressive autovacuum and pages the on-call. The single most valuable Postgres operational habit this course can give you: monitor and kill long-lived idle transactions. (MySQL's flavor differs: old versions live in undo logs, reconstructed on demand, with "history list length" as the equivalent alarm: same disease, different closet.)
The mechanics of a transaction, assembled: you can now read a COMMIT's full biography across three lessons. BEGIN assigns a transaction ID and (at first read) a snapshot. Writes create new row versions stamped xmin=you (Lesson 5) and emit WAL records (Lesson 4). COMMIT fsyncs the log and flips one bit: your txid's status in the commit log (pg_xact): which instantly makes every version you wrote visible to future snapshots, with no need to revisit any page. ROLLBACK flips the other bit, and your versions become invisible garbage for VACUUM. Atomicity isn't achieved by carefully undoing work: it's achieved by one bit deciding, retroactively, whether the work ever counted.
Check yourself
1. A 4-hour analytics SELECT runs against the orders table while thousands of UPDATEs commit. Under MVCC, what does the SELECT see, and who waited?
Snapshot semantics: the query answers "the truth as of when I started," internally consistent even across a 4-hour scan (totals balance, foreign keys agree). The costs are the honest ones: results are 4 hours stale by completion, and the snapshot pins VACUUM's horizon the whole time: the bloat lab's warning. The old-world alternative (shared read locks blocking all writers for 4 hours) is why MVCC won.
2. Monitoring shows a table at 40GB whose live data is 6GB, autovacuum "running constantly but reclaiming little." What's the classic root cause to check first?
The 6-hour-transaction disease at production scale. VACUUM's rule is conservative and correct: if any live snapshot might see a version, it stays. One connection BEGINning and then idling (an app server bug, a leaked ORM session) holds the horizon for the entire database. The remedies are policies, not heroics: idle_in_transaction_session_timeout, connection-pool hygiene, and an alert on transaction age. (Disabling autovacuum converts slow bloat into eventual wraparound shutdown: the one truly forbidden move.)
3. Why is ROLLBACK nearly instant in PostgreSQL, even for a transaction that modified 10 million rows?
The one-bit-decides principle from the callout, in action. Visibility checks consult the creator's commit status, so retroactively declaring "tx 8891: aborted" invalidates all its versions at once, lazily, with zero page writes at rollback time. (MySQL/Oracle chose the other design: undo logs: which makes their rollbacks proportional to work done, and their version reads costlier. Every MVCC implementation picks where to put this pain.)