Re: Autovacuum in the backend

On Thu, 16 Jun 2005, Matthew T. O'Connor wrote:

> Gavin Sherry wrote:
>
> >I guess my main concern is that we'll have a solution to the problem of
> >dead tuples which is only half way there. It is only an incremental
> >improvement upon the contrib module and solves only one real problem:
> >users do not read up on VACUUM or autovacuum. This is at the expense of
> >making it appear to be suitable for the general user base when it isn't,
> >in my opinion. That isn't the fault of autovacuum but is a function of the
> >cost of ordinary vacuum.
> >
> >
>
> Would you mind expounding on why you think autovacuum isn't suitable for
> the general public?  I know it's not a silver bullet, but I think in
> general, it will be helpful for most people.

As I said, this is largely the fault of VACUUM. The main thing I'd like to
see is a complete solution to the problem. I'm not picking on autovacuum.
However, I will elaborate a little on why I think autovacuum should not
be a feature of the backend:

1) The main argument so far is that autovacuum will ensure that users who
do not read the maintenance section of the manual will not notice a
deterioration of performance. This means that we anticipate autovacuum
being on by default. This suggests that the default autovacuum
configuration will not need tuning. I do not think that will be the case.

2) By no fault of its own, autovacuum's level of granularity is the table
level. For people dealing with non-trivial amounts of data (and we're not
talking gigabytes or terabytes here), this is a serious drawback. Vacuum
at peak times can cause very intense IO bursts -- even with the
enhancements in 8.0. I don't think the solution to the problem is to give
users the impression that it is solved and then vacuum their tables during
peak periods. I cannot stress this enough.

3) autovacuum on by default means row level stats are on by default. This
will have a non-trivial performance impact on users, IMHO. For right or
wrong, our users take the postgresql.conf defaults pretty seriously and
this level of stats collection could and will remain enabled in some
non-trivial percentage of users who turn autovacuum off (consider many
users' reluctance to change shared_buffers in previous releases). To quote
from the README:

"The overhead of the stats system has been shown to be significant under
certain workloads.  For instance, a tight loop of queries performing
"select 1" was found to run nearly 30% slower when row-level stats were
enabled."

I'm not one for "select 1" benchmarks but this is a problem that hasn't
even been mentioned, as far as I recall.

4) Related to this, I guess, is that a user's FSM settings might be
completely inappropriate. The 'Just read the manual' or 'Just read the
logs' argument doesn't cut it, because the main argument for autovacuum in
the backend is that people do not and will not.

5) It doesn't actually shrink tables -- ie, there's no VACUUM FULL. If
we're telling users about VACUUM less often than we are now, there's bound
to be bloating issues (see 4).

I guess the main point is, if something major like this ships in the
backend it says to users that the problem has gone away. pg_autovacuum is
a good contrib style solution: it addresses a problem users have and
attempts to solve it the way other users might try and solve it. When you
consider it in the backend, it looks like a workaround. I think users are
better served by solving the real problem.

Gavin