Just to dig up an old thread from last month:
In case anyone was wondering we finally got a free day to put in the new
version of the software, and it's greatly improved the performance. The
solutions we employed were as follows:
- recompile everything with ecpg -t for auto-commit
- vacuum run by cron every 15 minutes
- vacuum full analyze AND a reindex of the table in question run by cron
once per day
I also went through and double-checked everywhere where we opened a
cursor to make sure it was always closed when we were done.
This seems to have fixed up the endlessly growing indexes and slower and
slower updates. Thanks to everyone who helped out.
Alison