What Actually Happens When You UPDATE a Row in PostgreSQL?

The intuitive mental model of a database update—an in-place overwrite of a value, such as changing 25 to 30—is functionally incorrect in PostgreSQL. Because PostgreSQL implements Multi-Version Concurrency Control (MVCC), an UPDATE operation does not modify the original data. Instead, it creates an entirely new tuple version, preserving the original record to ensure transactional consistency for concurrent operations.

To understand this architecture, one must view a PostgreSQL table as a collection of fixed-size 8 KB pages. Each page contains a series of tuples (rows) accessed via line pointers that map to physical byte offsets. When an UPDATE is issued, the system follows this workflow:

• Tuple Versioning: The existing tuple remains on the page, and a new version is inserted, containing the updated values.
• Transaction Visibility: Each tuple header contains xmin (the ID of the transaction that created the tuple) and xmax (the ID of the transaction that deleted or replaced it). The database engine uses these fields, combined with the transaction’s snapshot, to determine which version is visible to a specific query.
• Dead Tuples: Once a transaction concludes, the original tuple may become obsolete—a "dead tuple"—if no other active transaction requires it.
• Maintenance: These dead tuples occupy storage space until the VACUUM process identifies them as reclaimable, at which point the space is marked as available for future inserts.

A notable optimization within this model is the Heap-Only Tuple (HOT) update. If an UPDATE modifies columns that are not indexed and sufficient free space exists within the same page, PostgreSQL can link the new tuple version to the old one without updating index entries. This prevents index bloat and reduces I/O overhead. Effectively, PostgreSQL's storage engine treats state changes as an append-only lifecycle, prioritizing data integrity and isolation over the simplistic (and technically inaccurate) model of direct memory overwriting.

PostgreSQL stores table rows in fixed-size pages, typically 8 KB. Each page can contain multiple rows, referred to internally as tuples. A page has a structured layout rather than a simple sequence of rows: a page header, an array of line pointers, a free space region, and the tuple area where tuple data is placed.

• Page header – stores metadata about the page.
• Line pointers – an array of items; each entry stores the byte offset and length of a tuple within the page.
• Free space – unused space managed for new tuples or updated tuple versions.
• Tuple area – the actual tuple data.

Every tuple has a system column called ctid. The ctid is a pair (block number, item identifier) that identifies the page and the line pointer, not a raw byte address.

SELECT id, ctid FROM users;

 id | ctid
----+-------
  1 | (0,1)
  2 | (0,2)
  3 | (1,1)

In (0,1), 0 is the block/page number and 1 is the item identifier referencing line pointer #1. That line pointer contains the actual offset and length of the tuple inside the page. Therefore, to reach a tuple, PostgreSQL reads the heap page, follows the line pointer, and accesses the tuple at the stored offset. Indexes use the same mechanism: an index entry stores a TID (identical in form to a ctid), and the heap lookup goes page → line pointer → tuple.

For example, when an update occurs, PostgreSQL may create a new tuple version on the same page; the old version remains until it becomes dead and is reclaimed by VACUUM. The ctid of a row can change when an updated version is placed in a different location.

A B-tree index in PostgreSQL provides an ordered mapping from key values to tuple identifiers (TIDs). It does not store the full row data; the index entry contains only the indexed key and a pointer to the heap tuple. A TID (also exposed as the system column ctid) is a pair: a block number identifying which 8 KB heap page holds the tuple, and an item identifier number selecting a line pointer within that page.

The line pointer is a small entry in the page header area that stores the byte offset and length of the tuple inside the page. Therefore, the complete lookup path for an indexed query is:

• Index key value, e.g., id = 1.
• B-tree traversal returns a TID, e.g., (0,1).
• PostgreSQL reads the heap page 0.
• Using item identifier 1, it reads the line pointer.
• The line pointer's offset/length locates the actual tuple.

This indirection is important for UPDATE behavior. When an UPDATE modifies a row, PostgreSQL under MVCC does not overwrite the existing tuple; it creates a new tuple version elsewhere on a heap page. If the updated column is not indexed, and if the new tuple fits on the same page, PostgreSQL can use a heap-only tuple (HOT) update: the new tuple's line pointer is chained from the old tuple, and no new index entry is needed. The existing index TID still points to the old tuple, which then redirects to the new version.

However, if the UPDATE changes a column that is part of an index, PostgreSQL must also insert or update the index entry to point to the new TID. The index does not contain any of the row's non-key columns or data, so index maintenance is limited to the key and pointer. This separation of storage is what allows PostgreSQL to keep indexes small and to reason about tuple visibility independently of index entries.

In PostgreSQL, an UPDATE does not overwrite the existing row in place. Table data is stored in fixed-size pages (typically 8 KB), and each row, or tuple, is located via a line pointer referenced by its ctid (block number and item identifier). When an UPDATE executes, PostgreSQL finds the target tuple and creates a brand-new tuple version in the heap, leaving the old version physically in place. An index, if one exists, ends up pointing at the appropriate heap tuple via its TID.

The reason for this behavior is Multi-Version Concurrency Control (MVCC). Each tuple carries header metadata, including xmin and xmax:

• xmin — the transaction ID that created the tuple version.
• xmax — the transaction ID that deleted or replaced the tuple version.

Consider a tuple created by transaction 5. When transaction 8 runs UPDATE users SET age = 30 WHERE id = 1, PostgreSQL marks the original tuple with xmax = 8 and inserts a new tuple version with xmin = 8. Both versions now coexist:

Old version: id=1, age=25, xmin=5, xmax=8
New version: id=1, age=30, xmin=8

The old version cannot be removed immediately because a concurrent transaction with an older snapshot may still need to see age = 25. At query time, PostgreSQL checks each candidate version against the transaction snapshot. The visibility rules are more involved than comparing xmin and xmax alone; they also consider whether the creating and deleting transactions are committed, still in progress, or aborted.

This design has direct operational consequences:

• Updated rows leave behind obsolete tuple versions, which become dead tuples once no active snapshot can see them.
• VACUUM, typically via autovacuum, reclaims the space occupied by dead tuples.
• If an update does not modify indexed columns and the new version fits on the same page, PostgreSQL can perform a Heap-Only Tuple (HOT) update, avoiding new index entries and reducing write amplification.

PostgreSQL does not overwrite row values in place. An UPDATE creates a new tuple version in the heap and leaves the old version on the page. ctid (block, line pointer) identifies the physical location of each version, but a SELECT does not simply return the newest ctid.

Each tuple header carries xmin (the creating transaction) and xmax (the deleting or replacing transaction). The visibility check consults the transaction snapshot — the set of committed and in-progress transactions as of the query start — plus each transaction's commit status, to decide which version is visible. As a simplified rule:

• A version is visible if xmin is committed and xmax does not indicate a committed deleter visible to the snapshot.
• A version is invisible if xmin is uncommitted, or if xmax is committed before the snapshot was taken.

For example, UPDATE users SET age = 30 WHERE id = 1; writes a new version with xmin = 8, and the old version (age = 25) receives xmax = 8. A transaction whose snapshot precedes transaction 8 still sees age = 25; a later snapshot sees age = 30.

The old version becomes obsolete once no active snapshot references it, but it remains in the page as a dead tuple. PostgreSQL cannot remove it immediately because an older transaction might still need it. Its lifecycle:

• INSERT creates a tuple.
• UPDATE creates a new version; the old version becomes obsolete.
• Once no snapshot needs it, the obsolete version becomes a dead tuple.
• VACUUM reclaims its space for reuse.

VACUUM scans heap pages, removes dead tuples, and makes their space reusable. Autovacuum is essential because it triggers this process automatically when dead-tuple thresholds are exceeded; without it, bloat accumulates and query performance degrades. The HOT (Heap-Only Tuple) optimization reduces this pressure by allowing an update that changes no indexed columns and fits on the same page to create a new version without a new index entry, decreasing the amount of index maintenance an UPDATE requires.

Heap-Only Tuple (HOT) updates represent a sophisticated optimization strategy within PostgreSQL designed to mitigate the performance overhead associated with standard UPDATE operations. In a standard update, PostgreSQL creates a new tuple version and—if indexes are involved—must generate new index entries pointing to that new tuple, even if the indexed columns remain unchanged. This process increases write amplification and index bloat.

A HOT update avoids this overhead by performing the update entirely within the heap, keeping the index pointer focused on the original location. PostgreSQL can perform a HOT update if the following criteria are satisfied:

• The UPDATE statement does not modify any columns currently included in an index.
• There is sufficient free space available within the same page (block) to accommodate the new tuple version.

When these conditions are met, the system creates the new tuple version on the same page as the original. Because the index key remains valid for the new tuple version, the existing index entry does not need to be modified. This mechanism significantly reduces the maintenance burden on B-tree indexes, as the index structure remains untouched by the update.

To maintain the integrity of Multi-Version Concurrency Control (MVCC), the following lifecycle occurs during a HOT update:

1. Locate: The database engine finds the old tuple via the existing index pointer.
2. Create: A new tuple version is written to the same page, preserving index visibility.
3. Visibility: PostgreSQL utilizes xmin and xmax tuple headers to manage visibility based on transaction snapshots; concurrent transactions continue to access the appropriate version.
4. Obsolete: The old version is marked as dead once it is no longer required by any active transaction snapshot.
5. Reclamation: The VACUUM process eventually reclaims the space occupied by the dead tuple, making it available for subsequent insertions.

By bypassing unnecessary index updates, HOT optimization improves throughput and reduces the rate at which vacuuming processes must trigger to clear dead tuples.