Re: How often do I need to reindex tables?

In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Bill Moran <wmoran(at)collaborativefusion(dot)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.

This goes back to the heart of the original question, which was: "how often
do I need to reindex." The answer was "rarely, if ever" and "if you're
really worried about this, you can monitor _size_ via these queries ..."

I guess I focused too much on size in my response. As you point out,
bloat isn't the only indicator that an index would benefit from being
rebuilt.

> 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.

Interesting. So a major factor in performance degradation is when the
index has to split pages. I read about FILLFACTOR in the docs, but it
didn't click as to what use it was until your statement.

We're still evaluating 8.2. We've hit a few issues with our application
and plpgsql, but I think those are minor. As far as deploying it for
our Bacula systems -- I just need to find the time to be sure that it
doesn't introduce any problems, but I suspect there's a low chance of
that with Bacula.

> > 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.

Yup.

--
Bill Moran
http://www.potentialtech.com