MVCC

Database / 16 Sep 2026 / 5 min read

PostgreSQL VACUUM Tail Truncation Requires an Exclusive Lock

Plain PostgreSQL VACUUM normally leaves reclaimed heap space inside the relation for later reuse. A distinct tail-truncation phase can instead shorten the relation file when a contiguous run of empty pages exists at its physical end. That phase requires an ACCESS EXCLUSIVE lock. The lock boundary makes tail truncation materially different from ordinary vacuum cleanup. Routine heap and index maintenance is designed to coexist with normal reads and writes, while shortening the physical relation requires a brief period in which concurrent table access cannot proceed.

PostgreSQL Database

Database / 16 Sep 2026 / 5 min read

PostgreSQL HOT Updates Avoid New Index Entries

A PostgreSQL UPDATE can create a new physical row version without creating corresponding new entries in ordinary tuple-addressing indexes. This heap-only tuple optimization, commonly called HOT, applies when the replacement tuple remains on the same heap page and the update does not change values that disqualify HOT for the table’s indexes. That distinction matters because PostgreSQL implements MVCC updates by retaining row versions rather than overwriting a tuple in place. Without HOT, an update can add work to both the heap and every index even when the indexed key values remain stable.

PostgreSQL Database

Software Engineering / 16 Sep 2026 / 7 min read

Long PostgreSQL Snapshots Delay Dead Tuple Reclamation

A PostgreSQL transaction can remain idle while still preserving a visibility horizon that constrains cleanup elsewhere. Rows updated or deleted after that transaction acquired its snapshot may become obsolete for newer transactions, yet some older versions can remain potentially visible to the retained snapshot. VACUUM cannot reclaim a row version merely because the newest application state no longer references it. This is a direct consequence of multiversion concurrency control. Visibility and physical reclamation are separate decisions: one transaction changes which row version is current, while the database must retain versions that can still be observed by relevant snapshots.

Software Engineering PostgreSQL

Database / 15 Sep 2026 / 5 min read

PostgreSQL Visibility Map Tracks Heap Page State

PostgreSQL keeps tuple visibility metadata in heap rows, but checking every heap tuple is unnecessary when an entire page is already known to satisfy stronger conditions. A visibility map stores that page-level state in a compact relation fork. Each heap page has two corresponding bits. The all-visible bit records that every tuple on the page is visible to every current and future transaction. The all-frozen bit records that every tuple on the page is frozen. Those facts let PostgreSQL avoid work in index-only scans and vacuum operations without moving MVCC visibility data into indexes.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL Visibility Map Controls Index-Only Heap Fetches

An Index Only Scan can still report heap fetches. The index may contain every value required by the query, yet PostgreSQL must also establish that each matching tuple is visible to the current MVCC snapshot. Index entries do not carry enough tuple-visibility state to make that decision independently. The visibility map supplies a page-level shortcut. When the heap page referenced by an index tuple is marked all-visible, the executor can accept the tuple’s visibility without reading that heap page. When the bit is clear, the executor visits the heap tuple and performs the normal visibility check.

PostgreSQL Indexes

Database / 15 Sep 2026 / 5 min read

PostgreSQL Visibility Map Controls Index-Only Heap Access

An index can contain every value a PostgreSQL query needs and still require heap access. The missing piece is MVCC visibility: index entries do not carry enough information to prove that a tuple is visible to the current snapshot. PostgreSQL resolves that gap with the visibility map. An index-only scan checks this compact structure before deciding whether a matching index tuple can be returned without visiting the heap. The result depends on page state, not merely on index coverage.

PostgreSQL Database

Database / 15 Sep 2026 / 6 min read

PostgreSQL Tuple Freezing Keeps Transaction IDs Comparable

PostgreSQL transaction IDs are 32-bit values, so the normal XID space eventually wraps. MVCC visibility still has to distinguish old row versions from transactions that have not happened yet. PostgreSQL resolves that finite-number problem by freezing sufficiently old tuple versions during vacuum processing. Freezing is not primarily a space-reclamation feature. It is a correctness mechanism that lets long-lived rows remain valid as the transaction counter continues around its finite range.

PostgreSQL Database

Database / 15 Sep 2026 / 4 min read

PostgreSQL Multixact Vacuum Bounds Member Age

PostgreSQL can place more than one transaction behind a tuple’s xmax. This occurs when concurrent transactions hold compatible row-level locks on the same tuple. A single transaction ID cannot represent that set, so PostgreSQL records a multixact identifier that refers to members stored outside the heap tuple. That indirection creates its own age boundary. Multixact identifiers are finite, and their member records occupy SLRU-backed storage. Old tuple metadata therefore cannot retain multixact references indefinitely. VACUUM participates in keeping both identifier age and member storage bounded.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL HOT Updates Reuse Existing Index Entries

A PostgreSQL UPDATE creates a new tuple version rather than overwriting the old tuple in place. That MVCC behavior normally has a second cost: indexes need entries that lead scans to the new version. Heap-only tuple updates, usually called HOT updates, avoid that index work under a specific set of conditions. HOT is not a separate update command or an optimizer choice exposed in SQL. It is a storage optimization selected while PostgreSQL updates a heap tuple. Its effect is concentrated in the relationship between heap pages and indexes: an existing index entry can remain useful across multiple row versions.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL HOT Updates Keep Index Entries Stable

A PostgreSQL UPDATE normally creates a new physical row version. That MVCC behavior can also require fresh index entries, even when an application changes only a small non-key field. Heap-only tuple updates, commonly called HOT updates, avoid that index work under specific conditions by keeping successive row versions on one heap page and retaining the existing index reference. HOT is therefore a property of a particular update, not a permanent table mode. Whether an update qualifies depends on the columns it changes and the free space available on the heap page that contains the current row version.

PostgreSQL Database

Database / 15 Sep 2026 / 6 min read

PostgreSQL Frozen Pages Bound Transaction ID Maintenance

A PostgreSQL heap page can reach a state where anti-wraparound vacuum no longer needs to inspect its tuple transaction IDs. The visibility map records this state with the all-frozen bit, allowing later aggressive vacuum work to skip the page until a data change invalidates that fact. This is separate from reclaiming dead tuples. A table with little update or delete activity can still require vacuum work because transaction IDs have a finite comparison range. Freezing converts sufficiently old tuple transaction metadata into a form that remains valid across transaction ID wraparound.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Visibility Maps Gate Heap-Free Index Scans

An index can contain every value a query needs and still require heap access. PostgreSQL must also establish that each candidate tuple is visible to the current MVCC snapshot. Index entries do not carry enough transaction visibility state to answer that check on their own. The visibility map provides a page-level shortcut. When a heap page is marked all-visible, an index-only scan can trust that every tuple on that page is visible and return indexed values without visiting the heap tuple.

Database PostgreSQL

Database / 14 Sep 2026 / 5 min read

PostgreSQL Tuple Freezing Bounds XID Age

PostgreSQL transaction IDs are finite. A normal transaction ID occupies 32 bits, so the numeric counter eventually wraps and reuses values. MVCC visibility cannot treat those values as an ever-growing integer sequence. PostgreSQL instead compares normal transaction IDs in a circular space, where an ID can only remain safely classifiable as old for a bounded span. Tuple freezing removes that age dependency for row versions whose creating transactions are far enough in the past. VACUUM records the tuple as frozen, allowing PostgreSQL to treat its insertion as visible to every normal transaction without relying on the original transaction ID’s position in the circular XID space.

PostgreSQL Database

Database / 14 Sep 2026 / 6 min read

PostgreSQL HOT Updates Reuse Index Entries

An UPDATE in PostgreSQL creates a new row version. That MVCC behavior can imply fresh index entries even when an application changes only a non-indexed attribute. Heap-only tuple updates, usually called HOT updates, provide a narrower path: under specific conditions, PostgreSQL can link the new row version on the same heap page and keep existing index entries in place. The optimization reduces index maintenance for eligible updates. Its boundary is physical as well as logical. Unchanged indexed values are not enough; the heap page must also have room for the new tuple version.

PostgreSQL Database

Database / 14 Sep 2026 / 6 min read

PostgreSQL HOT Updates Reduce Index Churn

A PostgreSQL UPDATE creates a new row version rather than overwriting the old tuple in place. That MVCC behavior supports concurrent readers, but a routine update can also create work in every index attached to the table. Heap-only tuples, usually called HOT updates, let PostgreSQL avoid much of that index work when the new row version meets a narrow set of conditions. HOT is not a different SQL operation. It is a storage-level optimization selected by PostgreSQL during an ordinary UPDATE.

PostgreSQL Database