Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention. Check out this how-to article for more information.
1. We have determined that the database needs to be VAACUMed and ANALYZEd. VACUUM is a postgres routine maintenance command which essentially defragments your database. It needs to be run every 1 billion transactions. You are running at 5 transactions/sec right now, and have processed about 468,000,000 transactions since the last VACUUM or initdb, so my guess is that VACUUM has never been run on this data. While a backup of the database consumes 29mb, the size of the data directory is over 700mb. This lends some support to my vacuum hypothesis. VACUUM is also necessary for ANALYZE to run. ANALYZE updates the query planner for the actual data layout, so if one does not run ANALYZE, one can expect query performance to drop over time. From my research, I expect a performance increase when this routine maintenence has been accomplished.
The database has not been tuned to the size of the system. Almost all settings are at the default, which are not optimal for a 4GB system. With some further research, I can change the tuning parameters to make the database faster. This may not be necessary if #1 makes performance good enough. If necessary, it should be possible to tune the database so the entire data set fits in memory.
Postgres is currently at version 8.0.3. This version was released 2005-05-09. I recommend that this be upgraded, at a minimum to 8.0.26, the last bugfix release in the 8.0 stream. The upgrade may require reindexing of some tables and may be conducted in place, however it may be less troublesome to dump the database, initdb, and restore.
Join the community of 500,000 technology professionals and ask your questions.