Table of Contents Vacuum Vacuum DB Autovacuum Vacuumlo Create vacuumlo crontab on a project server

Thera are two vacuum types on postgresql. The normal vacuum (vacuumdb) to remove dead tuples and the vacuumlo to remove obsolete lo (LargeObjec -> oids)

VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

With no parameter, VACUUM processes every table in the current database that the current user has permission to vacuum. With a parameter, VACUUM processes only that table.

VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected table. This is a handy combination form for routine maintenance scripts. See ANALYZE for more details about its processing.

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed.

When the option list is surrounded by parentheses, the options can be written in any order. Without parentheses, options must be specified in exactly the order shown above. Prior to PostgreSQL 9.0, the unparenthesized syntax was the only one supported. It is expected that all new options will be supported only in the parenthesized syntax.

To run vacuum manually you can just execute the query in pgadmin:

   VACUUM FULL ANALYZE VERBOSE

or from console

   vacuumdb  -v -U project -h localhost -p 5432 project

PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples.

The settings of autovacuum are stored in /etc/postgresql/9.1/main/postgresql.conf. In default case, autovacuum is enabled in postgres 9.1 and run automatically!

So, for vacuumdb on project there is nothing to do!

Vacuumlo is a simple utility program that will remove any "orphaned" large objects from a PostgreSQL database. An orphaned large object (LO) is considered to be any LO whose OID does not appear in any oid or lo data column of the database.

To run vacuumlo manualy:

   vacuumlo -v -n -U project -h localhost -p 5432 project

The functionality is not part of autovacuum! For project we need a cron job to run vacuulo periodically!

To run vacuumlo we need a additional debian package:

   apt-get install postgresql-contrib-9.1

To create a crontab on debian you ca as root user:

   root@project-NS:~# crontab -e

to open the file to define crontabs. Then just add the line

   0 * * * * vacuumlo -U project -h localhost -p 5432 project

and save the file. Vacuumlo should run now every hour.

You can check that with

   grep CRON /var/log/syslog