Question regarding pg_stat_reset() in postgresql
I have a question regarding function pg_stat_reset();
I am trying to collect database table stats on regular basis and for that purpose i am using stats from various postgres tables like pg_stat_all_tables, pg_stat_database
. But data in this table is accumulated over period of time. I am planning to reset it on daily basis. I just want to know if its safe to run this function on daily, hourly basis on production database. And also if safe are there any settings PG offer by which i can reset stats of regular interval, apart from setting cronjob and running pg_stat_reset()
https://www.postgresql.org/docs/12/monitoring-stats.html
Solution 1:
Don't do it, it will interfere with proper functioning of autovacuum. Rather than resetting counters to zero, just take snapshots of the counters and subtract one snapshot from a later one.