When and why should i trigger pg_stat_reset()?

Question

I am trying to understand how to monitor and tune postgresql performance. I started with exploring tables pg_stat_all_tables, pg_stat_statements in order to gather information about live tuples, dead tuples, last autovacuum time etc. There were some usefull information about n_live_tuples (near to real rows count in table) and n_dead_tup util i run pg_stat_reset query. After that i have some strange results - there are less n_live_tup than n_dead_tup. I can't find any articles/docs about why and when (some use cases) should i run pg_stat_reset query. Can somebody explain me that or provide some useful resources?

Answer 1

It is ok to run pg_stat_reset() occasionally, like once per month, to get a fairly up-to-date view on what is going on in your database.

But don't do that too often, as there is a down side to it: the system relevant autovacuum process relies on these statistics, so you will miss a couple of autovacuum (and autoanalyze) runs if you do that. That may or may not be a problem in your database, but at any rate I wouldn't do it too often. If you can, manually VACUUM and ANALYZE the database after calling pg_stat_reset().

There is no such problem with pg_stat_statements_reset(), so run that as often as you please.

The best thing for you would be to have a monitoring software that checks the values of the statistics regularly and provides you with a the development (differences to the previous run). Then you never have to reset the statistics and still have a good overview over what is going on.