Re: Second attempt, roll your own autovacuum

Glen Parker wrote:
> Tom Lane wrote:
>> What is it that you find inadequate
>> about regular autovacuum? It is configurable through the pg_autovacuum
>> catalog --- which I'd be the first to agree is a sucky user interface,
>> but we're not going to set the user interface in concrete until we are
>> pretty confident it's feature-complete. So: what do you see missing?
>
> Traditional vacuum does every table in the DB, which is absolutely The
> Wrong Thing for us. Vacuum can be fired against individual tables, but
> then how do I know which tables need it? Autovacuum is smart about
> which tables it hits, but exceedingly stupid about *when* it hits them.
>
> What I want is a way to do all needed vacuuming, in as short a time span
> as possible, when I decide it should be done. For us, that's between ~2
> AM and ~3 AM each morning. If a vacuum runs past 3 AM, so be it, but
> it's better to hit it hard and try to be done by 3 AM than it is to
> lolly gag around about it unil 5 AM.
>
> The obvious answer for me is to vacuum all the tables that autovacuum
> would hit, but only on demand. Something like "VACUUM CONDITIONAL WHERE
> autovacuum_says_so()" :-)

I believe the correct answer to this problems is to write a cron script
that enables autovacuum at 2AM and disables it at 3AM. I think there is
some talk of this in the archives somewhere.

If you need to hit specific tables more often than that, then you can
have another cron script that vacuums a table ever hour or something or
something along those lines.