Re: Opportunistically pruning page before update

On Wed, Jun 21, 2023 at 8:51 AM James Coleman <jtc331(at)gmail(dot)com> wrote:
> While at PGCon I was chatting with Andres (and I think Peter G. and a
> few others who I can't remember at the moment, apologies) and Andres
> noted that while we opportunistically prune a page when inserting a
> tuple (before deciding we need a new page) we don't do the same for
> updates.
>
> Attached is a patch series to do the following:
>
> 0001: Make it possible to call heap_page_prune_opt already holding an
> exclusive lock on the buffer.
> 0002: Opportunistically prune pages on update when the current tuple's
> page has no free space. If this frees up enough space, then we
> continue to put the new tuple on that page; if not, then we take the
> existing code path and get a new page.

I've reviewed these patches and have questions.

Under what conditions would this be exercised for UPDATE? Could you
provide an example?

With your patch applied, when I create a table, the first time I update
it heap_page_prune_opt() will return before actually doing any pruning
because the page prune_xid hadn't been set (it is set after pruning as
well as later in heap_update() after RelationGetBufferForTuple() is
called).

I actually added an additional parameter to heap_page_prune() and
heap_page_prune_opt() to identify if heap_page_prune() was called from
RelationGetBufferForTuple() and logged a message when this was true.
Running the test suite, I didn't see any UPDATEs executing
heap_page_prune() from RelationGetBufferForTuple(). I did, however, see
other statement types doing so (see RelationGetBufferForTuple()'s other
callers). Was that intended?

> I started to work on benchmarking this, but haven't had time to devote
> properly to that, so I'm wondering if there's anyone who might be
> interested in collaborating on that part.

I'm interested in this feature and in helping with it/helping with
benchmarking it, but I don't yet understand the design in its current
form.

- Melanie