This PostgreSQL tutorial explains how to use the PostgreSQL VACUUM command with syntax and examples.
The VACUUM statement is used to reclaim storage by removing obsolete data or tuples from the PostgreSQL database.
The syntax for the VACUUM statement in PostgreSQL is:
VACUUM [FULL] [FREEZE] [VERBOSE] [table_name ];
VACUUM [FULL] [FREEZE] [VERBOSE]
ANALYZE table_name [ (col1, col2, ... col_n) ];
Parameters or Arguments
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.
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.
Optional. If specified, an activity report will be printed detailing the vacuum activity for each table.
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.
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.
- 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.
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.
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:
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.
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.
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:
totn=# 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.
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