PostgreSQL: VACUUM Statement

In this PostgreSQL post explains how to use the PostgreSQL VACUUM command with syntax and examples.

Description

The VACUUM statement is used to reclaim storage by removing obsolete data or tuples from the PostgreSQL database.

Syntax

The syntax for the VACUUM statement in PostgreSQL is:

VACUUM [FULL] [FREEZE] [VERBOSE] [table_name ];

OR

VACUUM [FULL] [FREEZE] [VERBOSE]
ANALYZE table_name [ (col1, col2, ... col_n) ];

Parameters or Arguments

FULL

Optional. If specified, the database writes the full contents of the table into a new file. This reclaims all unused space and requires an exclusive lock on each table that is vacuumed.

FREEZE

Optional. If specified, the tuples are aggressively frozen when the table is vacuumed. This is the default behavior when FULL is specified, so it is redundant to specify both FULL and FREEZE.

VERBOSE

Optional. If specified, an activity report will be printed detailing the vacuum activity for each table.

ANALYZE

Optional. If specified, the statistics used by the planner will be updated. These statistics are used to determine the most efficient plan for executing a particular query.

table_name

Optional. If specified, only the table listed will be vacuumed. If not specified, all tables in the database will be vacuumed.

col1, col2, ... col_n

Optional. If specified, these are the columns that will be analyzed.

Note

• Each time you perform an update on a table, the original record is kept in the database. A vacuum will remove these old records (ie: tuples) and reduce the size of the PostgreSQL database.
• You can only those vacuum tables in which you have VACUUM permissions.
• You can not run a VACUUM command within a transaction.

Example

In PostgreSQL, the process of vacuuming is a regular activity that must be performed to remove old, obsolete tuples and minimize the size of your database file.

Let's look at an example of how to use the VACUUM statement in PostgreSQL.

Reclaim Space to be Reused by Same Table

This first example shows how to reclaim space so that the unused space can be used by the same table. It does not reduce the size of the PostgreSQL database file as the space is not reclaimed by the operating system, only by the table from which the space was allocated.

For example:

VACUUM;

This example would vacuum all tables within the database. It would free up the space within each table and leave the space available to be reused by the same table. It does not return the space to the operating system, therefore, the size of the database file would not be reduced.

Reclaim Space and Minimize Database File

If you wanted to vacuum all tables and minimize the database file by returning the unused space to the operating system, you would run the following vacuum statement:

VACUUM FULL;

This example would rewrite all tables into a new file, thus requiring an exclusive lock on each table. The database file would be minimized as all of the unused space is reclaimed back to the operating system.

Reclaim Space on a Table

Next, let's look at how to vacuum a specific table, instead of the entire database.

For example:

VACUUM products;

This example would vacuum only the products table. It would free up the space within the products table and leave the space available to be used by only the products table. The size of the database file would not be reduced.

If you wanted to allocate the unused space back to the operating system, you would have to add the FULL option to the VACUUM statement as follows:

VACUUM FULL products;

This would not only free up the unused space in the products table, but it would also allow the operating system to reclaim the space and reduce the database size.

Vacuum Activity Report

Finally, you can add the VERBOSE option to the VACUUM command to display an activity report of the vacuum process.

For example:

VACUUM FULL VERBOSE products;

This would perform a full vacuum of the products table. Let's show you what you can expect to see as output for a vacuum activity report:

dbd=# VACUUM FULL VERBOSE products;
INFO:  vacuuming "public.products"
INFO:  "products": found 4 removable, 5 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.04 sec.
VACUUM

This activity report will display the tables that are vacuumed as well as the details and time taken to perform the vacuum operation.

Next, learn more about the AUTOVACUUM feature starting in PostgreSQL 8.1