What is a HOT update in PostgreSQL and how does fillfactor help?
A HOT (heap-only tuple) update happens when no indexed column changes and the new row version fits on the same page, so no new index entries are needed. Lowering fillfactor leaves free space on each page to make that more likely.
Explain it simply
Normally an UPDATE creates a new row version, and every index on the table needs a new entry pointing to it. That is a lot of work if only a non-indexed column such as last_seen changed.
A HOT update shortcuts this: if the new version lands on the same table page and no indexed column changed, the old version simply points to the new one inside the page, and the indexes are left alone. It is like writing a correction in the margin instead of reprinting the whole index of the book.
Example
-- Leave 30% of every page free for future updates
ALTER TABLE accounts SET (fillfactor = 70);
VACUUM FULL accounts; -- applies to existing pages too
SELECT relname, n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE relname = 'accounts';A high n_tup_hot_upd relative to n_tup_upd is healthy. Indexing a column that changes constantly, like last_seen, destroys HOT updates.
The default fillfactor for tables is 100, packing pages full, which suits read-mostly data but leaves no room for same-page updates.
What to say in the interview
"A HOT update skips index maintenance when no indexed column changes and the new version fits on the same page. For update-heavy tables I lower fillfactor to leave room, and I avoid indexing frequently changing columns."
Common mistakes
- Indexing a timestamp that is updated on every request.