Re: Remove autovacuum GUC?

On 21 Oct. 2016 12:57 am, "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
>
> Hello,
>
> What about a simpler solution to all of this. Let's just remove it from
postgresql.conf. Out of sight. If someone needs to test they can but a
uneducated user won't immediately know what to do about that "autovacuum
process" and when they look it up the documentation is exceedingly blunt
about why to *not* turn it off.

Then they'll just do what I've seen at multiple sites: create a cron job
that kills it as soon as it starts. Then their DB performance issues go
away ... for a while. By the time they're forced to confront it their DB is
immensely listed and barely staggering along, or has reached wraparound
shutdown. So we get the fun job of trying to fix it using special freeze
tools etc because they broke the normal ones...

We still have fsync=off available. If you want a user foot gun to crusade
against, start there. Even that's useful and legitimate though I wish it
were called enable_crash_safety = off. It's legit to use it in testing, in
data ingestion where you'll fsync afterward, in cloud deployments where you
rely on replication and the whole instance gets nuked if it crashes anyway.

There are similarly legit reasons to turn autovac off but the consequences
are less bad.

Personally what I think is needed here is to make monitoring and bloat
visibility not completely suck. So we can warn users if tables haven't been
vac'd in ages and have recent churn. And so they can easily SELECT a view
to get bloat estimates with an estimate of how much drift there could've
been since last vacuum.

Users turn off vacuum because they cannot see that it is doing anything
except wasting I/O and cpu. So:

* A TL;DR in the docs saying what vac does and why not to turn it off. In
particular warning that turning autovac off will make a slow SB get slower
even though it seems to help at first.

* A comment in the conf file with the same TL;DR. Comments are free, let's
use a few lines.

* Warn on startup when autovac is off?

Personally I wouldn't mind encouraging most users to prefer table or db
level autovac controls. Though we really need to make them more visible. If
that improved I wouldn't really mind removing the global autovac option
from the conf file though I'd prefer to just give it a decent comment.