Re: documentation on HOT

On Fri, Jul 22, 2022 at 8:09 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> Thanks! This is great. Probably the most concise and clear explanation I
> have seen for HOT, which is exactly what we need for the docs :)

I'm delighted that this is finally happening!

> I think we should expand on this and explain how adjusting "fillfactor"
> will affect this. I think that may change the final sentence too.

Definitely -- this is by far the most important reason to tune heap
fill factor, which can make a big difference. There should be a link
from the fill factor docs to the new HOT section, at a minimum.

Another thing: would be good to point out how to monitor the number of
HOT updates vs non-HOT updates using views like pg_stat_user_tables.

One minor correction: Opportunistic pruning isn't limited to heap-only
tuples -- "HOT pruning" is actually a bit of a misnomer, that somehow
caught on in the Postgres community. Opportunistic pruning can and
will happen even with non-HOT updates -- the only difference on the
heap page level is that 4 byte LP_DEAD line pointers will accumulate
over time, which only VACUUM is able to clean up (we need LP_DEAD
items to stick around until then as tombstones, so that index scans
don't ever get confused). But pruning will still be able to free most
of the space on the heap page, in almost the same way.

Of course the indexes on the table are another matter -- each and
every index will accumulate versions when there are many non-HOT
updates, which can be a huge problem. My point is that *from the point
of view of an affected heap page*, pruning (whether opportunistic or
performed by VACUUM) can go ahead without HOT tuples/updates, and
isn't really that different in terms of the amount of space freed each
time.

Bottom-up index deletion is effective is no small part because pruning
was always pretty effective even with non-HOT updates, except within
indexes -- it pretty much compensates for that remaining problem. The
heuristics that it uses in the nbtree code work quite well because of
the natural tendency of those 4 byte LP_DEAD item pointers to
concentrate on individual heap pages. We can fit so many more 4 byte
LP_DEAD items than full sized live heap tuples that it becomes pretty
obvious (from the point of view of the nbtree code that starts out by
looking at an index leaf page) which pointed-to heap blocks are likely
to enable it to free space via index tuple deletes -- it often visits
the heap pages with the most TIDs.

Before bottom-up index deletion was added, I would notice that pgbench
variant workloads with non-HOT updates would result in a
pgbench_accounts table that is maybe 1%-2% larger after a few hours,
while the indexes on the same table would grow by 100% - 200%.
Opportunistic pruning would work pretty well for the heap, but
wouldn't do anything at all to control bloat in indexes. So
opportunistic pruning was always very effective with non-HOT updates
in one important way, but totally ineffective in another way.

There is a section about bottom-up index deletion in the docs (in the
B-Tree internals chapter) that already references HOT. It should link
to this new chapter now, I think.

-- 
Peter Geoghegan