Re: Proposal: two new role attributes and/or capabilities?

Jim,

* Jim Nasby (Jim(dot)Nasby(at)BlueTreble(dot)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 than expected table that's now essentially impossible to shrink. This could be caused by a single long-running transaction that happens to be in play when autovac kicks off, or for other reasons. Even once you manage to get all the tuples off the end of the heap it can still be extremely difficult to grab the lock you need to truncate it. Running a vacuum every minute from cron seems to help control this. Sadly, your indexes still get bloated, so occasionally you want to 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 scheduling preemptive vacuums to reduce the odds of disruptive vacuuming activity during heavy activity periods. This is especially true when it comes to a scan_all vacuum of a large table; having autovac do one of those at a peak period can really hose 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