Re: scheduling autovacuum at lean hours only.

On Wed, 11 Feb 2009, Scott Carey wrote:

> On a large partitioned database, ordinary vacuum is a very very difficult option.
>
> Most of the time on such a system, most tables are dormant with respect to writes and never need to be vacuumed. A 'daily vacuum' is likely to take a full day to run on larger systems. Since ordinary vacuum can't be run on subsets of tables without explicitly naming them one at a time (can't just vacuum a schema, tablespace, or use a regex to match table names), good luck using it effectively if you have a few thousand tables in partitions. You'll have to have application code or a script with knowledge of all the partition names and which are in need of an analyze/vacuum.
>
> Autovacuum is good enough in recent versions to be tuned to have very low impact though. If you have to, rather than stop and start it, just turn the delay or cost settings up and down during different parts of the day. More than likely however, it will be able to keep up with a single set of settings.
> In particular, rather than making the delay longer, make the appropriate cost larger -- page miss or page dirty affect how much I/O it will do, and page hit will mostly affect how much CPU it uses.
>
> Perhaps a feature request is to have a version of the manual vacuum command that doesn't bother running on tables that autovacuum wouldn't touch due to insufficient data churn. This way, at lean hours one can manually vacuum to help an autovacuum that was tuned for very low impact 'catch up'.
> Also, if there was some way to make vacuum not stink so badly on tables that were just loaded with pg_load, where it causes huge write activity for tables that clearly have no bloat (I believe this is a hint bits thing?).

Oh, I agree with everything you say. I'm just pointing out that if you
really do want control over when things get vacuumed (e.g. you have a
mostly-read database 20 hours a day and then 4 hours of heavy churn at
night) then you can still do that if you want.