BUG #1747: PostgreSQL 'hangs' after prolonged usage

The following bug has been logged online:

Bug reference: 1747
Logged by: Wojciech Sobczuk
Email address: wojtek(at)nemo(dot)pl
PostgreSQL version: 8.0.3
Operating system: FreeBSD 5.4-RELEASE
Description: PostgreSQL 'hangs' after prolonged usage
Details:

Hello,

I have a very busy dual-RAID1 machine, doing tens up to hundreds of queries
per second. After around 6 days of running at that kind of a load
postgresql starts processing queries very very slowly (load jumps to 40 from
2) which brings my system to it's knees. The only thing which helps then is
hard-restarting postgresql - and then it works fine for another 6 days.
Rebooting the machine has no influence on the performance so it seems to be
a postgresql issue.

My database takes up 9GB on the hdd and has millions of rows in around 40
relations (4-5 are really large, the rest is small).

I use a bit of triggers, but besides that it's just standard SQL queries
(called from Java using the 8.0 JDBC driver).

Please let me know if you're able to reproduce this bug - you just need to
put postgresql at a very high load for a long time (that's my first guess, I
don't have an enviroment in which I could do such tests, and even if I did I
woudln't know what to look for).

Any ideas how to fix it?

Thanks