Benefits of a lower table fillfactor besides HOT updates?

Question

Setting a fillfactor lower than the default of 100% allows HOT (Heap Only Tuple) updates on the table. However HOT updates may not be possible if the updated column is part of an index. If updates on a table always affect an indexed column so HOT updates are never taking place and this is confirmed by looking at the n_tup_hot_upd column for the table in pg_stat_all_tables, then is there any benefit to setting a fillfactor less than 100% in this scenario?

Answer 1

The manual doesn't mention HOT updates as the reason for the fillfactor at all.

fillfactor (integer)

The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables.

No matter which method is used (HOT or otherwise) to update the row postgres needs to:

• Write the new row
• Keep the old row so that other transactions can still read it, but ensure the old row is marked as old.

So regardless of whether HOT is being used; postgres must always write / change two rows as the result of an UPDATE. This will always be more efficient if done on one page rather than two.