Re: non-HOT update not looking at FSM for large tuple update

On Wed, Feb 24, 2021 at 10:44 AM Floris Van Nee <florisvannee@optiver.com>
wrote:
>
> The problem here is two-folded: for any non-HOT update of a tuple that’s
larger than the size of the fillfactor, the fsm will not be used, but
instead a new page will be chosen.

I confirmed this not only non-HOT updates, but regular inserts, which are
the same thing in this context.

> This seems to rely on the false assumption that every existing page has
at last one tuple on it.

Yep.

> Secondly, and this is a bit trickier.. Even if we would ask the FSM to
come up with a free page with a free size of “MaxHeapTupleSize”, it
wouldn’t find anything… This is, because the FSM tracks free space
excluding any unused line pointers.

> There are 7 line pointers on this page, consuming 28 bytes. Plus the 24
byte header, that means that lower=52. However, all line pointers are
unused, so the page really is empty. The FSM does not see the page as empty
though, as it only looks at “upper-lower”.
>
>
>
> When asking the FSM for slightly less space (MaxHeapTupleSize – 50 for
example), it does find the free pages. I’ve confirmed that with such a hack
the table is not growing indefinitely anymore. However, this number 50 is
rather arbitrary obviously, as it depends on the number of unused line
items on a page, so that’s not a proper way to fix things.
>
>
>
> In any case, the behavior feels like a bug to me, but I don’t know what
the best way would be to fix it. Thoughts?

One idea is to take your -50 idea and make it more general and safe, by
scaling the fudge factor based on fillfactor, such that if fillfactor is
less than 100, the requested freespace is a bit smaller than the max. It's
still a bit of a hack, though. I've attached a draft of this idea.

--
John Naylor
EDB: http://www.enterprisedb.com