Jim,
* Jim Nasby (Jim.Nasby@BlueTreble.com) wrote:
> On 12/23/14 12:52 PM, Stephen Frost wrote:
> >Autovacuum can certainly run vacuum/analyze on a few tables every 12
> >hours, so I'm not really following where you see autovacuum being unable
> >to cope. I agree that there*are* such cases, but getting more
> >information about those cases and exactly what solution*does* work
> >would really help us improve autovacuum to address those use-cases.
>
> (going through some old email...)
>
> The two cases I've dealt with recently are:
>
> - Tables with a fair update/delete rate that should always stay small
>
> The problem with these tables is if anything happens to upset vacuuming you can end up with a significantly larger
thanexpected table that's now essentially impossible to shrink. This could be caused by a single long-running
transactionthat happens to be in play when autovac kicks off, or for other reasons. Even once you manage to get all the
tuplesoff the end of the heap it can still be extremely difficult to grab the lock you need to truncate it. Running a
vacuumevery minute from cron seems to help control this. Sadly, your indexes still get bloated, so occasionally you
wantto re-cluster too.
The difference between the autovacuum-run vacuum and the cron-run vacuum
is that the one running out of cron will just keep holding the lock
until it's actually able to truncate the end of the relation, no? I
recall discussion previously that we need a way to either support that
in autovacuum for (a configurable set of) regular relations or come up
with a solution that doesn't require that lock.
> - Preemptively vacuuming during off-hours
>
> Many sites have either nightly or weekend periods of reduced load. Such sites can gain a great benefit from
schedulingpreemptive vacuums to reduce the odds of disruptive vacuuming activity during heavy activity periods. This is
especiallytrue when it comes to a scan_all vacuum of a large table; having autovac do one of those at a peak period can
reallyhose things.
Having preferrable times for autovacuum to run vacuums would certainly
be nice to support this use-case.
All that said, I'm not against a role attribute which allows the user to
vacuum/analyze anything. I do think that's a bit different from the
existing effort to reduce the activities which require superuser as with
the vacuum/analyze case you *could* have a single role that's a member
of every role that owns the relations which you want to vacuum/analyze.
I grant that it's a bit awkward though.
Thanks,
Stephen