Re: Should the docs have a warning about pg_stat_reset()?

Em ter, 26 de mar de 2019 às 09:54, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> escreveu:
>
> As I mentioned in [1], I've had a few cases recently about auto-vacuum
> not working. On the other thread, it was all about auto-vacuum being
> configured to run too slowly. The other culprit for auto-vacuum not
> working is when people periodically use pg_stat_reset().
>
> The problem with pg_stat_reset() is that it zeros n_dead_tup and
> n_mod_since_analyze. If say a user resets the stats on a monthly
> basis then this can mean that tables that normally receive an
> auto-vacuum any less frequently than once per month could never
> receive an auto-vacuum... at least not until an anti-wraparound vacuum
> gets hold of it.
>
It seems a bug^H^H^H new feature. The problem is if you keep resetting
statistic before reaching an ANALYZE threshold. In this case,
autoVACUUM was never triggered because we don't have stats. The
consequence is a huge bloat.

> The best I can think to do to try and avoid this is to put a giant
> WARNING in the docs about either not using it or to at least run
> ANALYZE after using it.
>
+1. I am afraid it is not sufficient.

> Does anyone else think this is a problem worth trying to solve?
>
I don't remember why we didn't consider table without stats to be
ANALYZEd. Isn't it the case to fix autovacuum? Analyze
autovacuum_count + vacuum_count = 0?

If at least autovacuum was also time-based, it should mitigate the
lack of statistic.

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento