In response to Joao Ferreira gmail <joao.miguel.c.ferreira@xxxxxxxxx>: > > On Tue, 2008-08-12 at 11:53 -0400, Tom Lane wrote: > > TW, "more aggressive routine vacuuming" does NOT mean "use vacuum > > full". > > Vacuum full tends to make index bloat worse, not better. > > > > regards, tom lane > > > Ok. so what does it mean ? > > I'm a bit lost here. I'm currently executing VACUUM FULL _and_ REINDEX > (tbls & idxs) every week. > > Should I keep the REINDEX and drop VACUUM FULL ? Don't "vacuum full" as part of regular maintenance. Do plain vacuum. If that's unable to keep up with the database bloat, then do it more often. Whether you use autovacuum or cron isn't as important as whether you're vacuuming often enough. Personally, I like to put explicit VACUUM commands in my applications after operations that are known to bloat tables. This isn't always possible as it sometimes introduces a performance issue, but I use it where it doesn't cause problem as it solves the bloat problem at the point of creation. REINDEX is normally not needed, although there _are_ some corner cases that seem to require it. One particular corner case is VACUUM FULL, which tends to bloat indexes. If you're using vacuum on a schedule appropriate to your database activity, you'll probably not need reindex. If you do find that your use is one of those corner cases where reindex is necessary, then go ahead and do it. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@xxxxxxxxxxxxxxxxxxxxxxx Phone: 412-422-3463x4023
