How to Use HOT Updates in PostgreSQL to Boost Your UPDATE Queries

Nice information to boost your PostgreSQL updates.

HOT updates are a feature of PostgreSQL that can improve the performance and efficiency of UPDATE queries. HOT stands for Heap Only Tuple, which means that the new version of a row is stored in the same page as the old one, and no indexed columns are changed. This way, PostgreSQL can avoid updating the indexes and creating dead tuples that need to be vacuumed later.

HOT updates can only happen if there is enough free space in the page to store the new version of the row. This can be controlled by setting a fillfactor lower than 100 for the table or index. The fillfactor determines how much space is reserved for future updates. For example, a fillfactor of 80 means that only 80% of the page is filled initially, leaving 20% for HOT updates.

HOT updates can also only happen if no indexed columns are modified by the UPDATE query. If any indexed column is changed, PostgreSQL has to update all the indexes that reference that column, and create a new tuple in a different page. This causes write amplification and fragmentation, which can degrade the performance and storage efficiency of PostgreSQL.

This query is for finding the number of regular and HOT updates for a table named ‘table’ in the ‘public’ schema:

SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND relname = 'jobs';