Re: a verbose option for autovacuum

Hey Tom

> Seems like that would very soon feel like log spam. What would be the
> use case for having this on? If you want one-off results you could
> run VACUUM manually.

In my case I have a fairly large, fairly frequently updated table with a
large number of indexes where autovacuum's runtime can fluctuate between 12
and 24 hours. If I want to investigate why autovacuum today is running many
hours longer than it did last week, the only information I have to go off
is from pg_stat_progress_vacuum, which reports only progress based on the
number of blocks completed across _all_ indexes.

VACUUM VERBOSE's output is nice because it reports runtime per index, which
would allow me to see if a specific index has bloated more than usual.

I also have autovacuum throttled much more aggressively than manual
vacuums, so information from a one-off manual VACUUM isn't comparable.

As for log spam, I'm not sure it's a problem as long as the verbose option
is disabled by default.

Tommy

On Fri, Jan 22, 2021 at 2:33 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Tommy Li <tommy(at)coffeemeetsbagel(dot)com> writes:
> > I was surprised to see that there's no way to get `VACUUM VERBOSE`-like
> > output from autovacuum. Is there any interest in enabling this?
>
> Seems like that would very soon feel like log spam. What would be the
> use case for having this on? If you want one-off results you could
> run VACUUM manually.
>
> > Additionally, is there any interest in exposing more vacuum options to be
> > run by autovac? Right now it runs FREEZE and ANALYZE, which leaves the
> > VERBOSE, SKIP_LOCKED, INDEX_CLEANUP, and TRUNCATE unconfigurable.
>
> To the extent that any of these make sense in autovacuum, I'd say they
> ought to be managed automatically. I don't see a strong argument for
> users configuring this. (See also nearby thread about allowing index
> AMs to control some of this.)
>
> regards, tom lane
>