On Wed, Oct 5, 2022 at 11:07:49AM +1300, David Rowley wrote:
> On Thu, 29 Sept 2022 at 04:45, Bruce Momjian <[email protected]> wrote:
> >
> > We have discussed the problems caused by the use of pg_stat_reset() and
> > pg_stat_reset_shared(), specifically the removal of information needed
> > by autovacuum. I don't see these risks documented anywhere. Should we
> > do that? Are there other risks?
>
> There was some discussion in [1] a few years back. A few people were
> for the warning. Nobody seemed to object to it. There's a patch in
> [2].
>
> David
>
> [1]
> https://www.postgresql.org/message-id/flat/CAKJS1f8DTbCHf9gedU0He6ARsd58E6qOhEHM1caomqj_r9MOiQ%40mail.gmail.com
> [2]
> https://www.postgresql.org/message-id/CAKJS1f80o98hcfSk8j%3DfdN09S7Sjz%2BvuzhEwbyQqvHJb_sZw0g%40mail.gmail.com
Ah, good point. I have slightly reworded the doc patch, attached.
However, the last line has me confused:
A database-wide <command>ANALYZE</command> is recommended after
the statistics have been reset.
As far as I can tell, analyze updates pg_statistics values, but not
pg_stat_all_tables.n_dead_tup and n_live_tup, which are used by
autovacuum to trigger vacuum operations. I am afraid we have to
recommand VACUUM ANALYZE after pg_stat_reset(), no?
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 342b20ebeb..a404738fc4 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5480,6 +5480,17 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</tgroup>
</table>
+ <warning>
+ <para>
+ Using <function>pg_stat_reset()</function> also resets counters that
+ autovacuum uses to determine when to trigger a vacuum or an analyze.
+ Resetting these counters can cause autovacuum to not perform necessary
+ work, which can cause problems such as table bloat or out-dated
+ table statistics. A database-wide <command>ANALYZE</command> is
+ recommended after the statistics have been reset.
+ </para>
+ </warning>
+
<para>
<function>pg_stat_get_activity</function>, the underlying function of
the <structname>pg_stat_activity</structname> view, returns a set of records
