Heap Only Tuples

When doing a UPDATE, Postgres’s MVCC to keeps track of version of rows. This updated version of row also needs to update index. Remove old version of rows and its index is expensive.

To reduce this overhead, Postgres has optimization called heap-only tuples HOT. An update is HOT when:

• update does not modify columns referenced by indexes - incl. expression and partial indexes.
• Enough free storage on page contains old row for the updated row.

HOT:

• No need to build new index for updated rows.
• Old version of updated rows can be removed during SELECTs, instead of needing periodic VACUUM -> Make VACUUM less work to do, faster to complete.

The system view pg_stat_all_tables can monitor occurrence of HOT and non-HOT updates.