Optimize PostgreSQL with vacuum

To optimize your PostgreSQL database, run a built-in utility called "vacuum."

Normal use of database software often creates data overhead that needs to be cleaned periodically in order to ensure optimal speed and stability. This overhead is usually the result of temporary files and indexes that the database creates (analogous to a fragmented hard disk.)

The vacuum utility runs on a live database and, like the backup command, can be scripted to run nightly or at minimal server load times.

  1. To vacuum the CollabNet TeamForge database, run the vacuum command as the PostgreSQL user.
    • vacuumdb -h `hostname` -U <DATABASE_USERNAME> -z <DATABASE_NAME>
    For example, using the default values in the site-options.conf file:
    • vacuumdb -h `hostname` -U ctfuser -z ctfdb
  2. To set up automatic vacuuming of the database based on activity statistics, set up auto-vacuuming according to these instructions: http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html#AUTOVACUUM.