Re: Open issues for HOT patch

Tom Lane wrote:
> * We also need to think harder about when to invoke the page pruning
> code. As the patch stands, if you set a breakpoint at
> heap_page_prune_opt it'll seem to be hit constantly (eg, once for every
> system catalog probe), which seems uselessly often. And yet it also
> seems not often enough, because one thing I found out real fast is that
> the "prune if free space < 1.2 average tuple size" heuristic fails badly
> when you look at queries that execute multiple updates within the same
> heap page. We only prune when we first pin a particular target page,
> and so the additional updates don't afford another chance to see if it's
> time to prune.
>
> I'd like to see if we can arrange to only do pruning when reading a page
> that is known to be an update target (ie, never during plain SELECTs);
> I suspect this would be relatively easy with some executor and perhaps
> planner changes. But that only fixes the first half of the gripe above;
> I'm not at all sure what to do about the multiple-updates-per-page
> issue.

There is one wacky idea I haven't dared to propose yet:

We could lift the limitation that you can't defragment a page that's
pinned, if we play some smoke and mirrors in the buffer manager. When
you prune a page, make a *copy* of the page you're pruning, and keep
both versions in the buffer cache. Old pointers keep pointing to the old
version. Any new calls to ReadBuffer will return the new copy, and the
old copy can be dropped when its pin count drops to zero.

Tracking multiple copies of a page requires some changes to the buffer
manager. LockBuffer would need to return the latest version of the page,
because anything that checks visibility or does updates would need to
use the latest copy, and callers of LockBuffer would need to be changed
accordingly.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com