Subject: Re: Add n_tup_newpage_upd to pg_stat table views

From: Corey Huinker <[email-masked]>
Date: Mon, 30 Jan 2023 13:40:15 -0500

On Fri, Jan 27, 2023 at 6:55 PM Andres Freund <[email-masked]> wrote:

> Hi,
>
> On 2023-01-27 18:23:39 -0500, Corey Huinker wrote:
> > This patch adds the n_tup_newpage_upd to all the table stat views.
> >
> > Just as we currently track HOT updates, it should be beneficial to track
> > updates where the new tuple cannot fit on the existing page and must go
> to
> > a different one.
>
> I like that idea.
>
>
> I wonder if it's quite detailed enough - we can be forced to do out-of-page
> updates because we actually are out of space, or because we reach the max
> number of line pointers we allow in a page. HOT pruning can't remove dead
> line
> pointers, so that doesn't necessarily help.
>

I must be missing something, I only see the check for running out of space,
not the check for exhausting line pointers. I agree dividing them would be
interesting.

> Similarly, it's a bit sad that we can't distinguish between the number of
> potential-HOT out-of-page updates and the other out-of-page updates. But
> that'd mean even more counters.
>

I wondered that too, but the combinations of "would have been HOT but not
no space" and "key update suggested not-HOT but it was id=id so today's
your lucky HOT" combinations started to get away from me.

I wondered if there was interest in knowing if the tuple had to get
TOASTed, and further wondered if we would be interested in the number of
updates that had to wait on a lock. Again, more counters.

---

From: Andres Freund <[email-masked]>
Date: Mon, 30 Jan 2023 10:45:28 -0800

Hi,

> not the check for exhausting line pointers. I agree dividing them would be

See PageGetHeapFreeSpace(), particularly the header comment and the
MaxHeapTuplesPerPage check.

> your lucky HOT" combinations started to get away from me.

Not sure I follow the second part. Are you just worried about explaining when
a HOT update is possible?

> updates that had to wait on a lock. Again, more counters.

Those seem a lot less actionable / related to the topic at hand to me.

Greetings,

Andres Freund