Re: How often do I need to reindex tables?

On Feb 28, 2007, at 5:35 PM, Bill Moran wrote:

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

I have two huge tables (one tracks messages sent, one tracks URL
click-throughs from said messages) from which I purge old data every
few weeks. The primary key indexes on these get bloated after a few
months and performance goes way down like you observe. A reindex
fixes up the performance issues pretty well on those tables, and
often shaves off a few gigs of disk space too.

We have to manually run the reindex because it has to be timed such
that the service is not impacted (ie, run on major holiday weekends)
and we have to take down part of the service and point other parts to
backup servers, etc. Not an easy chore...

This is on Pg 8.1. Don't even ask me how it was in the 7.4 days when
we have maybe 10% of the data! :-)