HOT Updates and Heap-Only Tuple Chains

Because PostgreSQL uses MVCC, an UPDATE does not overwrite a row in place. Instead it writes a brand-new tuple (the new row version) and marks the old one dead. The old version stays until VACUUM reclaims it.

Naively, every new tuple needs a new pointer in every index on the table, even indexes whose columns did not change. With 8 indexes, one logical update means 8 index inserts plus index bloat. This is index write amplification.

• More WAL written (each index change is logged)
• More index bloat (dead pointers accumulate)
• More CPU and I/O per update

This lesson is about a mechanism that lets PostgreSQL skip that index work: HOT updates.

HOT stands for Heap-Only Tuple. A HOT update creates the new tuple version on the same heap page as the old one and updates no indexes at all.

Two conditions must both hold for an update to qualify as HOT:

• No indexed column changed. If you touch any column used by any index, HOT is impossible.
• There is room on the same page for the new tuple version.

When both hold, the old tuple's line pointer is redirected to point at the new tuple, forming a HOT chain. Indexes keep pointing at the original line pointer and never need to be touched.