Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On Tue, Mar 29, 2022 at 1:37 PM Andres Freund <andres(at)anarazel(dot)de> wrote:

> > Secondly, to do anything really meaningful you need to calculate deltas,
> > and be able to detect if some of the stats were reset for the particular
> > interval. And the stat_reset timestamp was designed to be a simple way
> > to detect that (instead of having to inspect all individual timestamps).
>
> I wonder if we should just split that per-database timestamp into two. One
> about the pg_stat_database contents, one about per-database stats? That
> doesn't have the same memory-usage-increase concerns as adding
> per-table/function reset stats.
>
>
That seems like only half a solution. The reasoning for doing such a split
for pg_stat_database is identical to the reason that new fields should be
added to pg_stat_all_tables and pg_stat_user_functions (and possibly
others).

pg_stat_all_tables already has 16 bigint fields, 4 timestamptz fields, 2
names and an oid. Seems like one more timestamptz field is a marginal
increase whose presence lets us keep the already implemented per-table
reset mechanism. We should at least measure the impact that adding the
field has before deciding its presence is too costly.

But then, I'm going on design theory here, I don't presently have a horse
in this race. And the fact no one has called us on this deficiency (not
that I've really been looking) does suggest the status quo is at least
realistic to maintain. But on that basis I would just leave
pg_stat_database alone with its single field. And then explain that the
single field covers everything, including the database statistics. And so
while it is possible to reset a subset of the statistics the field really
loses its usefulness when that is done because the reset timestamp only
applies to a subset. It only regains its meaning if/when one performs a
full stats reset.

David J.