March 2, 2021
Hot Topics:

Introductory PostgreSQL Administration Tasks

  • By Damond Walker
  • Send Email »
  • More Articles »

Running Vacuum

There 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 Information

PostgreSQL Web site http://www.postgresql.org

PostgreSQL v7.0 Administration Documentation      http://postgresql.readysetnet.com/users-lounge/docs/7.0/admin/

About Author

Damond 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

This article was originally published on December 28, 2000

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date