Re: Improving the "Routine Vacuuming" docs - Mailing list pgsql-hackers

On Thu, Apr 14, 2022 at 5:03 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

> I would be on board with having the language of the entire section written with the assumption that autovacuum is
enabled,with a single statement upfront that this is the case.  Most of the content remains as-is but we remove a
non-trivialnumber of sentences and fragments of the form "The autovacuum daemon, if enabled, will..." and "For those
notusing autovacuum,..." 

+1

The second one goes on to say "a typical approach...", which seems to
imply there are plenty of installations that hum along happily with
autovacuum disabled. If there are, I haven't heard of any. (Aside: In
my experience, it's far more common for someone to disable autovacuum
on 118 tables via reloptions for who-knows-what-reason and without
regard for the consequences). Also:

- "Some database administrators will want to supplement or replace the
daemon's activities with manually-managed VACUUM commands". I'm not
sure why we go as far as to state that *replacing* is an option to
consider.

- " you will need to use VACUUM FULL, or alternatively CLUSTER or one
of the table-rewriting variants of ALTER TABLE."

If you follow the link to the ALTER TABLE documentation, there is no
easy-to-find info on what these table-rewriting variants might be.
Within that page there are two instances of the text "one of the forms
of ALTER TABLE that ...", but again no easy-to-find advice on what
those might be. Furthermore, I don't recall there being an ALTER TABLE
that rewrites the table with no other effects (*). So if you find
yourself *really* needing to VACUUM FULL or CLUSTER, which primary
effect of ALTER TABLE should they consider, in order to get the side
effect of rewriting the table? Why are we mentioning ALTER TABLE here
at all?

> If the basic content is deemed worthy of preservation, relocating all of those kinds of hints and whatnot to a single
"supplementingor disabling auto-vacuum" section. 

I think any mention of disabling should be in a context where it is
not assumed to be normal, i.e. exceptional situations. Putting it in a
section heading makes it too normal. Here's how I think about this: do
we have a section heading anywhere on disabling fsync? I know it's not
the same, but that's how I think about it.

(*) Should there be? As alluded to upthread, VACUUM FULL is a terrible
name for what it does as of 9.0. I continue to encounter admins who
have a weekly script that runs database-wide VACUUM FULL, followed by
REINDEX. Or, upon asking someone to run a manual vacuum on a table,
will echo what they think they heard: "okay, so run a full vacuum". I
would prefer these misunderstandings to get a big fat syntax error if
they are carried out.

--
John Naylor
EDB: http://www.enterprisedb.com