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.
