Re: postgresql performace degrading after a while

On Mon, Jan 30, 2006 at 09:28:14AM +0200, Ron Marom wrote:
> Indeed I forgot to mention that I AM vacuuming the database using a
> daemon every few hours; however this seems not to be the issue this
> time, as when the CPU consumptions went up I tried to vacuum manually
> and this seemed to take no affect.

You may be suffering from index bloat. Newer versions mostly deal with
this but old ones don't. Check the data directory for files that keep
growing. Cross-reference with relfilenode in pg_class. Also, vacuuming
once every few hours for such a commonly updated table seems not
enough, maybe you need to do it more often. What about your FSM
settings?

If it's index bloat, simply running REINDEX over the table should fix
it. In general, VACUUM FULL + REINDEX should have pretty much the same
effect as a dump/restore..

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.