Unifying VACUUM VERBOSE and log_autovacuum_min_duration output - Mailing list pgsql-hackers

I think that it's worth unifying VACUUM VERBOSE and
log_autovacuum_min_duration output, to remove the redundancy, and to
provide more useful VACUUM VERBOSE output.

Both variants already output approximately the same things. But, each
variant reports on certain details that the other variant lacks. I
find the extra information provided by log_autovacuum_min_duration far
more useful than the extra details provided by VACUUM VERBOSE. This is
probably because we've focussed on improving the former over the
latter, probably because autovacuum is much more interesting than
VACUUM on average, in practice, to users.

Unifying everything cannot be approached mechanically, so doing this
requires real buy-in. It's a bit tricky because VACUUM VERBOSE is
supposed to show real time information about what just finished, as a
kind of rudimentary progress indicator, while log_autovacuum_*
summarizes the whole entire VACUUM operation. This difference is most
notable when there are multiple index vacuuming passes ("index
scans"), or when we truncate the heap relation.

My preferred approach to this is simple: redefine VACUUM VERBOSE to
not show incremental output, which seems rather unhelpful anyway. This
does mean that VACUUM VERBOSE won't show certain information that
might occasionally be useful to hackers. For example, there is
detailed information about how rel truncation happened in the VERBOSE
output, and detailed information about how many index tuples were
deleted by each round of index vacuuming, for each individual index.
We can keep this extra information as DEBUG2 messages, as in the
current !VERBOSE case (or perhaps make some of them DEBUG1). I don't
think that we need to keep the getrusage() stuff at all, though.

I think that this would significantly improve VACUUM VERBOSE output,
especially for users, but also for hackers. Here are my reasons, in
detail:

* We have pg_stat_progress_vacuum these days.

* VACUUM VERBOSE doesn't provide much of the most useful
instrumentation that we have available in log_autovacuum_min_duration,
and yet produces output that is ludicrously, unmanageably verbose --
lots of pg_rusage_show() information for each and every step, which
just isn't useful.

* I really miss the extra stuff that log_autovacuum_min_duration
provides when I run VACUUM VERBOSE, though.

* In practice having multiple rounds of index vacuuming is quite rare
these days. And when it does happen it's interesting because it
happened at all -- I don't really care about the breakdown beyond
that. If I ever do care about the very fine details, I can easily set
client_min_messages to DEBUG2 on that one occasion.

* The fact that VACUUM VERBOSE will no longer report on
IndexBulkDeleteResult.num_index_tuples and
IndexBulkDeleteResult.tuples_removed seems like no great loss to me --
the fact that the number might be higher or lower for an index
typically means very little these days, with the improvements made to
index tuple deletion.

VERBOSE will still report on IndexBulkDeleteResult.pages_*, which is
what really matters. VERBOSE will also report on LP_DEAD-in-heap items
removed (or not removed) directly, which is a generic upper bound on
tuples_removed, that applies to all indexes.

* The detailed lazy_truncate_heap() instrumentation output by VACUUM
VERBOSE just isn't useful outside of debugging scenarios -- it just
isn't actionable to users (users only really care about how much
smaller the table became through truncation). The low level details
could easily be output as DEBUG1 (not DEBUG2) instead.

Thoughts?
--
Peter Geoghegan