Re: [GENERAL] How often do I need to reindex tables?

[Vivek Khera]

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

Just an FYI ... I remembered what prompted the cron job.


and tried some stuff here and there and found that reindex restored
performance.  I didn't look at actual size at that time.

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

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! :-)

smime.p7s
Description: S/MIME cryptographic signature