HOT Updates in Postgres

In the previous article we watched every UPDATE leave dead tuple behind. The same copy-on-write behaviour shows up from the operational angle in DELETEs are difficult. That's the tradeoff of MVCC and on the heap alone it's tolerable. The problem is the indexes. Every UPDATE in PostgreSQL potentially writes to every index on the table, even when the indexed columns didn't change. Five indexes, one updated column? Five extra index writes, five new entries to vacuum, five times the WAL traffic. At thousands of updates per second this becomes the dominant cost of running a write-heavy table. Heap-Only Tuple (HOT) updates are PostgreSQL's escape hatch from this problem. They are, in my opinion, the single cleverest optimization in the storage engine. Let's trace exactly how they work. Cost of a normal UPDATE Without HOT, index maintenance scales poorly. Here's a table with multiple indexes: pageinspect ships with the contrib modules and is available on most installations. It exposes raw…