MVCC, Vacuum, and Row Visibility

PostgreSQL uses multi-version concurrency control, usually called MVCC, so readers and writers can work at the same time without forcing every query through one global queue. The practical outcome is simple: a SELECT can see a consistent version of the database while another transaction is updating the same rows. The internal design is less simple, and understanding it is essential for this course because transactions, isolation levels, indexes, locks, and maintenance all meet at row visibility.

In this lesson, you will learn how PostgreSQL stores old and new row versions, how a snapshot decides which version is visible, why deleted rows do not disappear immediately, and how vacuum makes space reusable. You will also see why long transactions, idle sessions, and replication slots can stop cleanup and produce table bloat.

Purpose and Outcome

MVCC solves a concurrency problem. If one session updates a row while another session is reading it, PostgreSQL should not return half-written data, but it also should not block ordinary readers just because a writer is active. PostgreSQL handles this by creating a new tuple version for many updates and leaving the previous version available to older snapshots. A query does not ask, “what is the newest physical row on the page?” It asks, “which row version is visible to my transaction snapshot?”

After this chapter, you should be able to predict whether a row version is visible, explain why a table contains dead tuples after updates and deletes, inspect symptoms of vacuum lag, and run a small lab that demonstrates snapshot behavior.

MVCC Internals

PostgreSQL tables are heap relations. A logical row can have several physical tuple versions over time. Each tuple contains ordinary column values plus system metadata. The most important visibility fields are xmin, the transaction ID that inserted the tuple version, and xmax, the transaction ID that deleted or superseded it. A freshly inserted live tuple normally has an inserting xmin and no committed deleting xmax.

An UPDATE is closer to delete plus insert than to in-place overwrite. PostgreSQL marks the old tuple version as no longer current and inserts a new tuple version, often at a different physical location. The tuple’s ctid identifies its physical location. When an update chain exists, PostgreSQL can follow tuple metadata to find the version that matters for a snapshot. Indexes may also point to tuple versions, and heap-only tuple optimization can avoid extra index entries when indexed columns do not change and there is suitable page space.

A snapshot records enough transaction state to decide visibility. At a high level, a tuple version is visible when its inserting transaction committed before the snapshot and its deleting or replacing transaction had not committed before the snapshot. Details include in-progress transactions, aborted transactions, transaction ID wraparound handling, hint bits, and visibility map state, but the core rule remains: visibility is computed from transaction history, not just current physical storage.

Vacuum exists because MVCC intentionally leaves obsolete tuple versions behind. Once no active snapshot can need an old version, vacuum can mark that version as dead and make its space reusable. Normal vacuum usually does not shrink the operating-system file immediately; it cleans pages and records reusable space. VACUUM FULL rewrites the table and can return disk space, but it requires a much heavier lock. Autovacuum runs vacuum automatically based on table activity and age thresholds.

Syntax and Inspection Anatomy

The SQL surface for MVCC is small because visibility is implicit in every query. You normally observe it through transaction control, isolation level, system columns, and maintenance commands.

Example 1: Tuple Versions After Update

This first example shows that an update creates a new visible version of a row. The exact transaction IDs and physical tuple locations vary by database, so focus on the pattern: after the update, the selected row has the new column value and a new version identity.

DROP TABLE IF EXISTS mvcc_demo;
CREATE TABLE mvcc_demo (
  id integer PRIMARY KEY,
  status text NOT NULL
);

INSERT INTO mvcc_demo VALUES (1, 'draft');
SELECT id, status, xmin, xmax, ctid FROM mvcc_demo;

UPDATE mvcc_demo SET status = 'published' WHERE id = 1;
SELECT id, status, xmin, xmax, ctid FROM mvcc_demo;

The first SELECT returns draft. The second returns published. You do not see the old tuple through a normal query because your current statement snapshot sees the committed replacement. The old tuple may still exist physically until vacuum determines that no active snapshot can need it.

Example 2: Statement Snapshots in Read Committed

In the default READ COMMITTED isolation level, each statement gets a new snapshot. That means two selects in the same transaction can see different committed states if another transaction commits between them.

-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT status FROM mvcc_demo WHERE id = 1;
-- Keep this transaction open while Session B runs its update.
SELECT status FROM mvcc_demo WHERE id = 1;
COMMIT;

-- Session B, run between the two SELECT statements in Session A
UPDATE mvcc_demo SET status = 'archived' WHERE id = 1;

If Session A’s first select runs before Session B commits, it returns published. After Session B commits, Session A’s second select returns archived. This is not a dirty read; Session A only sees committed data. It is a consequence of statement-level snapshots.

Example 3: Stable Snapshots in Repeatable Read

REPEATABLE READ keeps one transaction snapshot, so repeated reads inside the transaction are stable even when another session commits a change.

-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT status FROM mvcc_demo WHERE id = 1;
-- Keep this transaction open while Session B runs its update.
SELECT status FROM mvcc_demo WHERE id = 1;
COMMIT;

-- Session B, run between the two SELECT statements in Session A
UPDATE mvcc_demo SET status = 'reviewed' WHERE id = 1;

If Session A first sees archived, its second select also sees archived, even after Session B commits reviewed. After Session A commits and starts a new transaction, it can see the newer value. This behavior is central to reporting queries, exports, and consistency-sensitive reads.

Design Choices and Trade-Offs

MVCC favors nonblocking reads and consistent snapshots, but it shifts cost into storage churn and maintenance. Update-heavy tables produce dead tuples. Indexes can accumulate entries that point to obsolete tuple versions until vacuum cleanup. Long-running transactions delay cleanup because PostgreSQL must preserve row versions that those snapshots might still read.

Choosing an isolation level is also a design choice. READ COMMITTED is efficient and works for many request-response applications, but each statement can see a newer committed world. REPEATABLE READ gives stable transaction-level reads, but it may require retry logic when write conflicts occur. SERIALIZABLE adds stronger anomaly prevention, but the application must handle serialization failures deliberately.

Vacuum configuration involves another trade-off. Aggressive vacuuming reduces bloat and transaction ID age risk, but it consumes I/O and CPU. Under-tuned vacuum settings can allow dead tuples to pile up, making sequential scans, index scans, and backups slower. The right setting depends on write volume, table size, latency goals, and how long transactions remain open.

Failure Modes and Troubleshooting

Symptom: a table grows quickly even though row count is stable. Cause: frequent updates or deletes create dead tuples faster than vacuum can clean them. Diagnose: inspect n_dead_tup, last vacuum times, and table size trends. Correct: reduce unnecessary updates, tune autovacuum for the table, add appropriate fillfactor for update-heavy rows, and run manual vacuum during a safe window if needed.

Symptom: vacuum runs but cannot remove many dead rows. Cause: an old transaction, prepared transaction, replication slot, or standby feedback is holding back the global cleanup horizon. Diagnose: check old sessions in pg_stat_activity, prepared transactions, and replication slot retention. Correct: end or fix idle transactions, resolve abandoned prepared transactions, and review replication slot policy.

Symptom: a reporting transaction does not see recently committed updates. Cause: it is using a transaction-level snapshot, commonly under REPEATABLE READ. Diagnose: check the transaction isolation level and transaction start time. Correct: commit and start a new transaction when fresh data is required, or keep the stable snapshot if repeatable reporting is the actual requirement.

Security, Performance, and Reliability

MVCC has security implications because old tuple versions may remain in database files until cleanup and reuse. Users cannot read invisible tuples through ordinary SQL without privileges and specialized inspection paths, but storage retention still matters for operational policy, backups, forensic handling, and sensitive data deletion expectations.

Performance depends heavily on keeping visibility checks cheap. Vacuum can set visibility map bits for pages whose tuples are visible to all current and future transactions, allowing index-only scans to avoid heap visits in favorable cases. If vacuum is starved, more pages require heap visibility checks, dead tuples consume cache, and query plans that looked acceptable on a clean table can slow down.

Reliability depends on transaction ID maintenance. PostgreSQL transaction IDs are finite, so vacuum also freezes old tuple metadata to prevent wraparound hazards. Autovacuum has protective behavior for wraparound risk, but relying on emergency cleanup is poor operations. Monitor transaction age before it becomes urgent.

Hands-On Lab

Prerequisites: access to a disposable PostgreSQL database, two psql sessions, and permission to create and drop a table in a scratch schema. Do not run this lab in a production schema.

1. In Session A, create the demo table by running the first example.
2. In Session A, begin a REPEATABLE READ transaction and select the row.
3. In Session B, update the row and commit immediately.
4. Back in Session A, select the row again and confirm that the value has not changed inside that transaction.
5. Commit Session A, run the select again, and confirm that the new committed value is now visible.
6. Generate dead tuples with several updates, then run the statistics query below.
7. Run VACUUM mvcc_demo;, wait briefly if statistics lag, and check the statistics again.

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'mvcc_demo';

VACUUM mvcc_demo;

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'mvcc_demo';

Verification: the repeatable-read transaction should show the same value before and after Session B commits, then show the newer value after Session A commits and starts a new statement outside that old snapshot. The statistics view should show the table and vacuum timestamp; tuple counts are estimates and may update asynchronously.

Cleanup: run DROP TABLE IF EXISTS mvcc_demo; in the scratch database. If you changed any session isolation settings, close those sessions or reset them before continuing other work.

Assessment Exercises

1. A transaction in READ COMMITTED runs two identical selects. Another session commits an update between them. Explain why the two results may differ without violating MVCC.
2. A table has stable logical row count but rapidly increasing disk size. Name two MVCC-related causes and the first catalog views you would inspect.
3. Why can terminating an old idle transaction allow a later vacuum to remove dead tuples?
4. Design a retry strategy for an application that uses stronger isolation and sometimes receives serialization failures.
5. Explain why VACUUM FULL is not a routine replacement for autovacuum on busy tables.

Summary

PostgreSQL MVCC keeps concurrent reads consistent by storing tuple versions and deciding visibility from transaction snapshots. Updates and deletes leave old versions behind until vacuum can safely reclaim them. This design gives excellent read-write concurrency, but it requires disciplined transaction lifetimes, appropriate isolation choices, autovacuum health, and monitoring of dead tuples, table growth, and transaction age.