Introductory PostgreSQL Administration Tasks, Page 4
Running VacuumThere are two reasons to run vacuum: to remove dead space and to update index statistics.
When a record is deleted in pgsql, the record is flagged as deleted but not removed from disk. Create a table with 100,000 records in it, and then delete all the data in the table. If you examine the file system, you will notice the file is still quite large. Also, when users modify data, instead of modifying the record as it stands, the record is deleted and a new record is added to the end of the table. Deletes and updates create dead spots, or holes, in your database. Vacuum, when run regularly, removes these dead spaces in your database. Depending on how volatile your database is, you may want to run vacuum weekly or evendaily.
The second reason to run vacuum is to analyze index coverage statistics of the database. These statistics are used to determine the best query plan to use when running queries against the database. Unlike other systems, which manage statistics on the fly, pgsql leaves the statistics gathering up to the administrator. To do this, you run vacuum with the --analyze command line option. This command should be run at least once a day, and possibly more, depending on what you do with your database. (Did you just import a million records? Analyze. Did you just delete 500,000 records? Analyze.)
You will know it's time to analyze your tables when a query suddenly takes a great while to process.
Links to InformationPostgreSQL Web site http://www.postgresql.org
PostgreSQL v7.0 Administration Documentation http://postgresql.readysetnet.com/users-lounge/docs/7.0/admin/
About AuthorDamond Walker works at Black Oak Computer Services Inc, of Newark, Del., as a Lotus Notes/Domino and SQL Server consultant. When not pushing bytes for his employer, he pushes strollers with his wife and their twin daughters.