Re: Open issues for HOT patch

"Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com> writes:
> On 9/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Perhaps we could
>> replace that heuristic with something that is page-local; seems like
>> dividing the total used space by the number of item pointers would give
>> at least a rough approximation of the page's average tuple size.
>>
> We might get it completely wrong unless we know the number of
> normal line pointers (redirected, dead and unused line pointers
> do not take any real storage).

Sure, but it's only a heuristic anyway. Probably a more serious
objection is that it fails in the wrong direction: if you start to get
line pointer bloat then the estimated average tuple size goes down,
making it less likely to prune instead of more. But maybe do something
that looks at free space and number of pointers independently, rather
than operating in terms of average tuple size?

> Another option would be to prune whenever the free space goes
> below table fillfactor and hope that users would set fillfactor so that
> atleast one updated tuple can fit in the block. I know its not best to
> rely on the users though. But it can be good hint.

If default fillfactor weren't 100% then this might be good ;-). But
we could use max(1-fillfactor, BLCKSZ/8) or some such.

> Yet another option would be to set a hint on the page whenever we
> fail to do HOT update because of not-enough-free-space in the
> block. Next time we shall prune and so the subsequent updates
> would be HOT update.

This would be a good idea independent of anything else, I think.
There's plenty of room for page hint bits, and the write will be
"free" since we must set the old tuple XMAX anyway.

regards, tom lane