Re: auto vacuuming

On Mar 31, 2006, at 9:29 PM, Tom Lane wrote:

> "Matthew T. O'Connor" <matthew@zeut.net> writes:
>> I think the closest approximation of disabling autovacuum on a per
>> database basis is to connect to the database in question and perform:
>> update pg_autovacuum set enabled = 'false';
>
> Not really gonna help unless you insert a row into pg_autovacuum for
> each table in the database.

True.

> As I just commented in another reply, I don't actually believe in the
> value of disabling autovac entirely --- it should at least be able to
> fire when you are risking XID wraparound.  What could make sense is to
> push the thresholds up to very large values, such that autovac won't
> fire until you've forgotten manual vacuums for a very long time.
> And that you can already do on a per-database basis, using ALTER
> DATABASE SET.  (Or at least, it *should* work to do that; if the
> autovac
> process fails to absorb per-db values for its GUC variables, then we
> ought to fix it.  I'm too lazy to test it right now...)

The problem with that is unless I missed a change in 8.1, autovac
knows absolutely nothing about when manual vacuums have been run. To
do that I'm pretty sure we'd need a catalog table that captured the
statistic counts on each table when vacuum ran (and ideally an XID
and a timestamp, too). If we end up with some kind of dirty page
bitmap that might remove the need for that.

Even if tweaking the thresholds did work you still can't do it at a
per-database level. It doesn't seem unreasonable to support different
autovac settings at the database level.
--
Jim C. Nasby, Database Architect                decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"




--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461