Re: Is Autovacuum running?

On Tue, 21 Feb 2023 at 08:42, Brad White <b55white(at)gmail(dot)com> wrote:
>
> I'm concerned that Autovacuum may not be running based on the results of this query.
>
> SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
> gives 211 rows like this...
> relname | last_vacuum | last_autovacuum
> BusinessIncidentCategories | null | null
> Valid Use | null | null
> Serial Pool Part Types | null | null
> BusinessIncidentLog | null | null
> Rate Categories | null | null

It's probably worth having a look at:

select datname,stats_reset,now() from pg_stat_database where datname =
current_database();

while you're connected to the database with those tables. If you're
resetting the stats, then that resets the counters used by autovacuum
and also the columns you mention above. That can lead to autovacuum
not doing any vacuuming or analyzing.

There's a warning in the documentation about this [1]:

"Using pg_stat_reset() 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 ANALYZE is recommended after the statistics have been
reset."

David

[1] https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW