Re: postgresql meltdown on PlanetMath.org

I don't know what your definition of "high" is, but I do find that
turnover can degrade performance over time.  Perhaps one of the devs can
enlighten me, but I have a database that turns over ~100,000 rows/day that
does appear to slowly get worse.  The updates are done in batches and I
"VACUUM" and "VACUUM ANALYZE" after each batch (three/day) but I found
that over time simple queries would start to hit the disk more and more.

A "select count(*) FROM tblwordidx" initially took about 1 second to
return a count of 2 million but after a few months it took several minutes
of really hard HDD grinding.  Also, the database only had a couple hundred
megs of data in it, but the db was taking up 8-9 GB of disk space.  I'm
thinking data fragmentation is ruining cache performance?  When I did a
dump restore and updated from 7.2.1 to 7.3.1 queries were zippy again.  
But, now it is starting to slow...  I have yet to measure the effects of a
VACUUM FULL, however.  I'll try it an report back...


Logan Bowers

On Sun, 16 Mar 2003, Aaron Krowne wrote:


> I've done it here and there, especially when things seem slow.  Never
> seems to help much; the data turnover isn't high.
>