Re: Add n_tup_newpage_upd to pg_stat table views

Hi,

On 2023-01-27 17:59:32 -0800, Peter Geoghegan wrote:
> > I think this might cause some trouble for existing monitoring setups after an
> > upgrade. Suddenly the number of updates will appear way lower than
> > before... And if we end up eventually distinguishing between different reasons
> > for out-of-page updates, or hot/non-hot out-of-page that'll happen again.
>
> Uh...no it won't? The new counter is totally independent of the existing
> HOT counter, and the transactional all-updates counter. It's just that
> there is an enum that encodes which of the two non-transactional "sub
> counters" to use (either for HOT updates or new-page-migration
> updates).
>
> > I wish we'd included HOT updates in the total number of updates, and just kept
> > HOT updates a separate counter that'd always be less than updates in total.
>
> Uh...we did in fact do it that way to begin with?

Sorry, I misread the diff, and then misremembered some old issue.

> > From that angle: Perhaps it'd be better to have counter for how many times a
> > page is found to be full during an update?
>
> Didn't Corey propose a patch to add just that? Do you mean something
> more specific, like a tracker for when an UPDATE leaves a page full,
> without needing to go to a new page itself?

Nope, I just had a brainfart.

> > 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.
>
> ISTM that it would make more sense to do that at the index level
> instead. It wouldn't be all that hard to teach ExecInsertIndexTuples()
> to remember whether each index received the indexUnchanged hint used
> by bottom-up deletion, which is approximately the same thing, but
> works at the index level.

I don't think that'd make it particularly easy to figure out how often
out-of-space causes non-HOT updates to go out of page, and how often it causes
potential HOT updates to go out of page. If you just have a single index,
it's not too hard, but after that seems decidedly nontrival.

But I might just be missing what you're suggesting.

Greetings,

Andres Freund