PostgreSQL VACUUM statement

Preamble

PostgreSQL VACUUM statement is used to restore the storage by removing outdated data or tuples from a PostgreSQL database.

The syntax for the VACUUM statement in PostgreSQL

VACUUM [FULL] [FREEZE] [VERBOSE] [tab_name ];

OR

VACUUM [FULL] [FREEZE] [VERBOSE]
ANALYZE tab_name [ (col1_id, col2_id,... col_n_id) ];

Parameters and arguments of the statement

• FULL – Optional. If specified, the database shall write the entire table contents into a new file. This releases all unused space and requires an exclusive lock for each table that is cleared.
• FREEZE – Optional. If specified, the tuples are aggressively frozen when cleaning the table. This is the default behavior if 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 scheduler will be updated. These statistics are used to determine the most efficient plan to execute a particular query.
• tab_name – Optional. If specified, only the specified table will be cleared. If not specified, all tables in the database shall be cleared.
• col1_id, col2_id,… col_n_id – Optional. If specified, these shall be the columns to be analyzed.

Note:

• Each time you perform a table update, the original record shall be saved in the database. The vacuum will delete these old records (ie tuples) and reduce the size of the PostgreSQL database.
• You can only use vacuum tables in which you have VACUUM rights.
• You cannot run the VACUUM command in a transaction.

In PostgreSQL, the cleaning process is a common action to perform in order to remove old outdated tuples and minimize the database file size.

Example of using the VACUUM statement in PostgreSQL

Restore space for reuse in the same table.

This first example shows how to free up space so that unused space can be reused by the same table. This does not reduce the size of the PostgreSQL database file, since space is not freed up by the operating system, but only by the table from which space was allocated.
For example:

VACUUM;

This example will clear all tables in the database. This will free up space inside each table and leave the space available for reuse by the same table. It does not return space to the operating system, so the database file size will not be reduced.

Restore space and minimize the database file

If you want to clear all tables and minimize the database file by returning unused space to the operating system, you must run the following VACUUM operator:

VACUUM FULL;

In this example, all tables will be overwritten into a new file, which will require an exclusive lock on each table. The database file will be kept to a minimum as all unused space will be returned to the operating system.

Restore space for a table

Next, let’s see how to clear a particular table, not the entire database.
For example:

VACUUM products;

This example will clear only the products table. This will clear the space in the products table and leave the space available for use only for the products table. The database file size will not be reduced.

If you want to allocate unused space back to the operating system, you need to add the FULL option to the VACUUM statement as follows:

VACUUM FULL products;

This will not only free up unused space in the products table but also allow the operating system to free up space and reduce the size of the database.

VACUUM activity report

Finally, you can add the VERBOSE option to the VACUUM command to display the VACUUM process activity report.
For example:

VACUUM FULL VERBOSE products;

This will perform a complete cleaning of the products table. Let’s show you what you can see as output data for the VACUUM activity report:

trizor=# VACUUM FULL VERBOSE products;
INFO: vacuuming "public.products".
INFO: "products": found 4 removable, 5 nonremovable row versions in 1 page
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 have been cleared by VACUUM as well as detailed information and the time required to perform the cleaning operation.

What is a vacuum in PostgreSQL: Postgresql Vacuum

Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.