Re: automatic REINDEX-ing

In response to Joao Ferreira gmail <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com>:

>
> 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(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023