postgres Hot Heap Only Tuples - ghdrako/doc_snipets GitHub Wiki
Without HOT, every version of a row in an update chain has its own index entries, even if all indexed columns are the same. With HOT, a new tuple placed on the same page and with all indexed columns the same as its parent row version does not get new index entries. This means there is only one index entry for the entire update chain on the heap page. An index-entry-less tuple is marked with the HEAP_ONLY_TUPLE flag. The prior row version is marked HEAP_HOT_UPDATED, and (as always in an update chain) its t_ctid field links forward to the newer version.
A HOT update can occur when the data being updated is not an indexed column and there is free space on the data page for the new row version.
-- set the fill factor to a specific value at creation time
CREATE TABLE bluebox.inventory (
inventory_id serial4 NOT NULL,
film_id int4 NOT NULL,
store_id int4 NOT NULL,
last_update timestamptz DEFAULT now() NOT NULL
) WITH (fillfactor=95); -- Reduce the fill factor to allow more space for new and updated rows
ALTER TABLE bluebox.inventory SET (fillfactor=75);
Particularly for tables that you know to be update heavy, itโs worth keeping an eye on
a few columns in pg_stat_user_tables to monitor if HOT updates are occurring
SELECT schemaname, relname, n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables;
```When the number of HOT updates (n_tup_hot_upd) is growing at a similar rate with
the number of updated tuples (n_tup_upd), then there is adequate free space for
PostgreSQL to keep these modifications within the same data page. If HOT updates are
not occurring at a similar rate and you believe that they should be, lowering the fill factor
could increase the likelihood of HOT updates taking place.
If this were a live application
and no HOT updates were happening on the table (n_tup_hot_upd is 0) lowering the fill factor
to 75% or 80% would be a first step to see if HOT updates would begin to happen.