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.
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.
Page 4 of 4