HOT Updates in Postgres (12 minute read)
PostgreSQL's HOT updates avoid index maintenance when updating non-indexed columns by chaining new tuple versions on the same page, dramatically cutting write overhead on tables with multiple indexes.
What: HOT (Heap-Only Tuple) updates is a PostgreSQL optimization that skips index maintenance when updating non-indexed columns by chaining tuple versions on the same page, which indexes can follow without needing new entries.
Why it matters: Standard PostgreSQL updates write to every index on a table even when indexed columns haven't changed, so updating one column on a table with five indexes means five extra index writes, WAL traffic, and vacuum overhead—HOT eliminates this for non-indexed column updates.
Takeaway: Check your HOT success rate with pg_stat_user_tables (n_tup_hot_upd / n_tup_upd ratio), configure fillfactor to 80-90 on update-heavy tables to reserve space for HOT chains, and audit your ORMs to ensure they only update changed columns rather than rewriting entire rows.
Deep dive
- PostgreSQL's default UPDATE behavior writes to every index on a table even when indexed columns don't change, creating massive overhead (five indexes × one updated column = five extra index writes, five times WAL traffic)
- HOT updates bypass index maintenance by placing the new tuple on the same page as the old one and creating a chain that index scans can follow
- A HOT update requires two conditions: the new tuple must fit on the same page, and none of the updated columns can be indexed—if either fails, it becomes a cold update
- The old tuple is marked HOT_UPDATED with t_ctid pointing to the new tuple, while the new tuple is marked HEAP_ONLY meaning no direct index entries point to it
- Multiple HOT updates create chains within a single page (lp1 → lp5 → lp6 → lp7), and indexes still point only to the original ctid, never growing despite repeated updates
- Long HOT chains have cost since every index scan must walk the entire chain, so 50 HOT updates between vacuums means 50 hops per index lookup
- Page pruning happens opportunistically during normal queries when dead tuples are invisible to all transactions (pd_prune_xid < RecentGlobalXmin) and the page is roughly 10% full or more
- During pruning, dead intermediate tuples become LP_UNUSED, the original line pointer becomes LP_REDIRECT (just 4 bytes, no tuple data), and the page is defragmented
- The LP_REDIRECT persists until VACUUM rewrites index entries to point directly at the current tuple location, only then can it finally become LP_UNUSED
- Lowering fillfactor from the default 100 to 80-90 reserves space on pages specifically for HOT updates, trading storage efficiency for update performance
- You can monitor HOT effectiveness via pg_stat_user_tables looking at the ratio of n_tup_hot_upd to n_tup_upd
- Common HOT killers: pages too full (adjust fillfactor or vacuum more frequently), ORMs updating all columns when only some changed (enable dirty tracking), too many indexes, and updating indexed columns
- Long-running transactions pin RecentGlobalXmin and prevent page pruning just like they block VACUUM, making them dangerous for PostgreSQL write performance
- Page pruning is limited—it cannot touch index entries, set visibility map bits, update the free space map, or reach across pages; those operations require VACUUM
Decoder
- HOT (Heap-Only Tuple): A PostgreSQL optimization where UPDATE creates new tuple versions on the same page without touching indexes
- ctid: A tuple's physical address in PostgreSQL storage, consisting of (page_number, line_pointer)
- t_xmax: Transaction ID that deleted or updated a tuple, marking it as potentially dead
- Line pointer (lp): A 4-byte entry in the page header that points to tuple data within the page
- LP_REDIRECT: A line pointer state where it redirects to another line pointer rather than pointing to tuple data, occupying just 4 bytes
- fillfactor: Storage parameter controlling how full pages are packed during INSERT (default 100%), lower values reserve space for updates
- RecentGlobalXmin: The oldest snapshot xmin across all active transactions, representing the horizon below which tuples are definitely invisible to everyone
- pd_prune_xid: Page header field tracking the oldest unpruned transaction ID on the page, used to trigger opportunistic pruning
- Cold update: A normal UPDATE that creates new index entries because either indexed columns changed or the new tuple doesn't fit on the same page
- MVCC (Multi-Version Concurrency Control): PostgreSQL's approach where updates create new tuple versions rather than modifying in place
- WAL (Write-Ahead Log): PostgreSQL's transaction log used for crash recovery and replication
Original article
HOT Updates in PostgreSQL is a clever storage optimization that allows UPDATEs on unindexed columns to avoid touching indexes entirely when the new tuple fits on the same page as the old one. Instead of creating new index entries, PostgreSQL marks the old tuple as HOT_UPDATED and places a HEAP_ONLY tuple on the same page, forming a chain that scans can follow, which reduces WAL traffic, index maintenance, and vacuuming overhead.