- publishing free software manuals
PostgreSQL Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 204 pages
ISBN 0954612043
RRP £13.95 ($24.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

9.1 Routine Vacuuming

PostgreSQL's VACUUM command must be run on a regular basis for several reasons:

  1. To recover or reuse disk space occupied by updated or deleted rows.
  2. To update data statistics used by the PostgreSQL query planner.
  3. To protect against loss of very old data due to transaction ID wraparound.

The frequency and scope of the VACUUM operations performed for each of these reasons will vary depending on the needs of each site. Therefore, database administrators must understand these issues and develop an appropriate maintenance strategy. This section concentrates on explaining the high-level issues; for details about command syntax and so on, see the VACUUM reference page.

The standard form of VACUUM can run in parallel with production database operations. Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function as normal, though you will not be able to modify the definition of a table with commands such as ALTER TABLE ADD COLUMN while it is being vacuumed. Also, VACUUM requires a substantial amount of I/O traffic, which can cause poor performance for other active sessions. There are configuration parameters that can be adjusted to reduce the performance impact of background vacuuming--see section 4.4.4 Cost-Based Vacuum Delay.

An automated mechanism for performing the necessary VACUUM operations has been added in PostgreSQL 8.1. See section 9.1.4 The auto-vacuum daemon.

ISBN 0954612043PostgreSQL Reference Manual - Volume 3 - Server Administration GuideSee the print edition