Re: How often do I need to reindex tables?

Bill Moran <wmoran@collaborativefusion.com> writes:
> Just an FYI ... I remembered what prompted the cron job.

> We were seeing significant performance degradation.  I never did actual
> measurements, but it was on the order of "Bill, why is restoring taking
> such a long time?" from other systems people.  At the time, I poked around
> and tried some stuff here and there and found that reindex restored
> performance.  I didn't look at actual size at that time.

A reindex might improve performance for reasons other than bloat --- to
wit, that a freshly-built index is in perfect physical order, which
tends to get degraded over time by page splits.  How important that is
depends on your usage patterns.  If this is what the story is for your
situation, then what might fix it (in 8.2) is to create the index with
FILLFACTOR 50 or so, so that it's already at the steady state density
and won't need many page splits.

> Anyway, I'll report back in a few weeks as to what the numbers look like.

Yeah, please for the moment just watch what happens with the default
behavior.

            regards, tom lane