HOT Updates in PostgreSQL: Why an Indexed Column Change Can Still Stay on the Same Page
A user experimenting with heap-only tuple (HOT) updates in PostgreSQL ran into a case that looks like it should not qualify: the column being modified was part of an index, yet the row version did not move to a new block.
The test setup
The table carries a primary key on age, a separate btree index on name, autovacuum disabled, and a fillfactor of 70 percent. It was loaded with 235 rows generated by generate_series, each getting a name of the form My name-N.
Before any modification, the row for age 1 sits at ctid (0,1) with the name My name-1.
The unexpected result
The user then updates an indexed column:
update my_ages set "name" = 'My awesome name 1' where "name" = 'My name-1';Querying the row back by age = 1, or by the new name value, returns ctid (0,110) — still within the same block. Querying by either the primary key or the name index produces the same result.
That leaves the question the user raises: is this a misunderstanding of how HOT works, or is something in the setup wrong?
