HOT Updates and fillfactor: When an Update Can Skip Touching Every Index
Normally an UPDATE writes a new row version and must add a matching entry to every index on the table, even indexes on columns you did not change. A heap-only tuple, or HOT, update avoids that: if no indexed column changed and there is free space on the same page, the new version goes on that page and the indexes are left alone. You make HOT updates possible by not indexing columns that churn and by lowering fillfactor so pages are left with room, which is one of the few tuning knobs that makes writes cheaper without removing an index.
Questions this Concept answers
Why does a HOT update make an `UPDATE` statement cheaper?
J
jeremy
Video
Lowering PostgreSQL Fillfactor to Enable HOT Updates and Cut MVCC Write Amplification
Under multi-version concurrency control (MVCC), a PostgreSQL UPDATE never modifies a row in place: it marks the existing row version dead, inserts a new version, and repoints every index on the table…