Database field guide · 09
PostgreSQL MVCC Backstage
Tuple versions, visibility, vacuum, and recovery
Look behind PostgreSQL snapshots to understand heap tuples, index behavior, vacuum, uniqueness, and crash recovery.
Franck Pachot8 chaptersPostgreSQL · MVCC · vacuum
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
Updates create tuple versions
PostgreSQL normally updates by writing a new heap tuple version and marking the old version with transaction metadata. Readers choose the visible version for their snapshot, allowing reads and writes to overlap without returning uncommitted state.
- xmin records the creating transaction.
- xmax participates in deletion, update, and lock state.
- Visibility also depends on commit status and the reader snapshot.
02
The index points into the heap
A regular index entry identifies a heap tuple location. Because visibility information lives primarily with heap tuples and transaction state, an index match may still require a heap visit before the row can be returned.
- An index entry is not by itself proof of a visible row.
- Updates can leave obsolete index entries until cleanup.
- CTID is a physical locator, not durable row identity.
03
The visibility map enables index-only scans
The visibility map records pages whose tuples are all visible to all transactions. An index-only scan can trust that summary and avoid a heap check for those pages. Recent writes clear the useful state until vacuum establishes it again.
- Coverage and all-visible state are both required.
- Heap fetches reveal how much the optimization actually helped.
- Write-heavy tables naturally have fewer all-visible pages.
04
HOT keeps some updates out of indexes
A heap-only tuple update is possible when indexed columns do not change and the page has room for the new version. PostgreSQL links versions on the same heap page, avoiding new entries in every index and reducing write amplification.
- Fillfactor can reserve room for update-heavy tables.
- An extra index can disqualify HOT when its column changes.
- Use table statistics to observe HOT effectiveness.
05
Vacuum makes reuse safe
Vacuum determines which dead versions are no longer visible to any relevant snapshot, marks space reusable, maintains visibility information, and freezes old transaction identifiers. It works with concurrent traffic rather than compacting every table into a new file.
- Long transactions delay cleanup eligibility.
- Autovacuum thresholds must match table size and churn.
- Anti-wraparound vacuum protects correctness, not optional tidiness.
06
Uniqueness consults visibility
A unique index may encounter an entry belonging to an in-progress, deleted, or superseded tuple. PostgreSQL coordinates with that transaction and examines heap visibility before deciding whether a new key truly conflicts.
- Unique checking is a concurrency protocol.
- Waiting on an uncommitted key is expected behavior.
- ON CONFLICT builds on these visibility semantics.
07
WAL recovers physical consistency
Write-ahead logging records changes before corresponding data pages are considered durable. Full-page images protect recovery from torn pages after checkpoints, while crash recovery replays WAL and treats transactions without commit records as aborted.
- A backend crash and an operating-system crash have different blast radii.
- fsync and storage guarantees are correctness settings.
- Replication reuses the ordered WAL stream for change delivery.
08
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
Read tuple metadata cautiously
xmin identifies the inserting transaction and xmax is overloaded for deletion, update, and row-lock state. They are 32-bit transaction identifiers interpreted with an epoch and commit-status data; they are not permanent business values. ctid identifies a block and item offset, and normally changes on update. Exposing them is useful for a controlled experiment, not an application contract.
SELECT ctid, xmin, xmax, id, status
FROM orders
WHERE id = 42;
UPDATE orders SET status = 'paid' WHERE id = 42;
SELECT ctid, xmin, xmax, id, status FROM orders WHERE id = 42;
Measure HOT rather than assuming it
A HOT update requires that no indexed column changes and that the heap page has room for the new tuple. The old index entry leads to the root of an on-page tuple chain. Lower fillfactor reserves update space but increases table size and scan work. Compare n_tup_hot_upd with n_tup_upd after a representative interval.
SELECT relname, n_tup_upd, n_tup_hot_upd, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';
ALTER TABLE orders SET (fillfactor = 80);
-- A rewrite is needed for existing pages to adopt the space layout.
Understand the two visibility-map bits
Each heap page has all-visible and all-frozen state. All-visible allows index-only scans to skip heap visibility checks; all-frozen means tuples no longer need future freezing. Any modification clears all-visible for the page. Vacuum can set it only after proving every tuple is globally visible, which is why write-heavy tables still show Heap Fetches in an Index Only Scan.
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map_summary('orders');
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders WHERE customer_id = 42;
Tune autovacuum from change volume
Vacuum trigger thresholds combine a fixed threshold and a fraction of estimated table rows. A 20 percent scale factor is too slow for many large, high-churn tables. Per-table settings let vacuum start after a bounded number of dead tuples. Also monitor transaction age: anti-wraparound vacuum is a correctness deadline and can ignore normal cost delays.
ALTER TABLE orders SET (
autovacuum_vacuum_threshold = 5000,
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_analyze_scale_factor = 0.005
);
SELECT relname, n_dead_tup, last_autovacuum,
age(relfrozenxid) AS xid_age
FROM pg_stat_user_tables JOIN pg_class USING (relname);
09
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
