Re: documentation on HOT

On Fri, Jul 22, 2022 at 10:05:04AM -0700, Peter Geoghegan wrote:
> On Fri, Jul 22, 2022 at 8:09 AM Jonathan S. Katz <jkatz(at)postgresql(dot)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.

Done in the attached patch, and updated URL:

https://momjian.us/tmp/pgsql/storage-hot.html

> 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.

I added a sentence about that, good idea.

> 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.

I see your point. I changed the benefits from pruning to no new page
item identifiers.

> 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.

Already done in the attached patch, I think.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson