Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On 3/24/22 01:59, Andres Freund wrote:
> Hi,
>
> On 2022-03-23 17:55:16 -0700, Andres Freund wrote:
>> Maybe I just don't understand what these reset functions are intended for?
>> Their introduction [3] didn't explain much either. To me the behaviour of
>> resetting pg_stat_database.stats_reset but nothing else in pg_stat_database
>> makes them kind of dangerous.
>
> Forgot to add: At the very least we should document that weird behaviour,
> because it's certainly not obvious. But imo we should either remove the
> behaviour or drop the functions.
>

I agree it should have been documented, but I still stand behind the
current behavior. I'm not willing to die on this hill, but I think the
reasoning was/is sound.

Firstly, calculating transactions/second, reads/second just from by
looking at pg_stat_database data (counter and stat_reset) is nonsense.
It might work for short time periods, but for anything longer it's bound
to give you bogus results - you don't even know if the system was
running at all, and so on.

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).

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company