On Wed, Dec 14, 2005 at 01:59:09PM -0500, Dan wrote: > I was under the impression that a full vacuum was more extensive and > compacted the tables more than a regular vacuum. > > I'll check out autovacuum, thanks.
Well, yes, it does. And as you discovered it also takes an exclusive lock on the tables. And the tables are just going to grow again as soon as you start using them. But even vacuum full by itself won't prevent indexes from bloating if you're not vacuuming frequently enough. The real key is to just make sure you're vacuuming frequently enough and then not worry about it. If you're using version 8.1 simply turning on the built-in autovacuum should be fine. If you're on an earlier version, you'll want pg_autovacuum from contrib, but you'll want to tune it to be more aggressive; I normally do -V 0.2 or -V 0.4. That will allow up to 20% or 40% dead space, respectively. On a side note, it would be good if SA itself issued periodic vacuums after X number of updates or deletes to tables, but I suspect adding that might be more trouble than it's worth. > On 12/13/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > Why are you running vacuum full? There shouldn't be any need to if > > you're vacuuming regularly enough. Your best bet is to use autovacuum. > > > > What version of PostgreSQL are you using? > > > > On Tue, Dec 13, 2005 at 04:50:43PM -0500, Dan wrote: > > > I am running Bayes, AWL, and userprefs on postgres and have VACUUM > > > ANALYZE run nightly via cron. I have been running a full vacuum > > > manually once every other week, but I would like to use a cron script. > > > The problem is that the database has to be locked while it is run. > > > > > > The best idea that I was able to come up with is a script that > > > restarts SA with a .cf file that disables Bayes and AWL while > > > vacuuming and then restarts SA with the normal config. > > > > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > > Pervasive Software http://pervasive.com work: 512-231-6117 > > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
