Re: Remove autovacuum GUC?

On Wed, Oct 19, 2016 at 9:24 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> After all these years, we are still regularly running into people who say,
> "performance was bad so we disabled autovacuum". I am not talking about once
> in a while, it is often. I would like us to consider removing the autovacuum
> option. Here are a few reasons:
>
> 1. It does not hurt anyone
> 2. It removes a foot gun
> 3. Autovacuum is *not* optional, we shouldn't let it be
> 4. People could still disable it at the table level for those tables that do
> fall into the small window of, no maintenance is o.k.
> 5. People would still have the ability to decrease the max_workers to 1
> (although I could argue about that too).

Setting autovacuum=off is at least useful for testing purposes and
I've used it that way. On the other hand, I haven't seen a customer
disable this unintentionally in years. Generally, the customers I've
worked with have found subtler ways of hosing themselves with
autovacuum. One of my personal favorites is autovacuum_naptime='1 d'
-- for the record, that did indeed work out very poorly.

I think that this the kind of problem that can only properly be solved
by education. If somebody thinks that they want to turn off
autovacuum, and you keep them from turning it off, they just get
frustrated. Sometimes, they then find a back-door way of getting what
they want, like setting up a script to kill it whenever it starts, or
changing the other thresholds so that it barely ever runs. But
whether they resort to such measures or not, there is no real chance
that they will be happy with PostgreSQL. And why should they be? It
doesn't let them configure the settings that they want to configure.
When some other program doesn't let me do what I want, I decide it's
stupid. Pretty much the same thing here. The only way you actually
get out from under this problem is by teaching people the right way to
think about the settings they're busy misconfiguring.

I'd actually rather go the other way with this and add a new
autovacuum setting, autovacuum=really_off, that doesn't let autovacuum
run even for wraparound. For example, let's say I've just recovered a
badly damaged cluster using pg_resetxlog. I want to start it up and
try to recover my data. I do *not* want VACUUM to decide to start
removing things that I'm trying to recover. But there's no way to
guarantee that today. So, you can't start up the cluster, look
around, and then shut it down with the intent to change the next
transaction ID if it's not right. Your data will be disappearing
underneath you.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company