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:

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