In PostgreSQL, sometimes some records engage too much space because of various reasons, such as dead records or records with older versions, etc. In such cases, the Postgres VACUUM command can be used to vacate that storage. The VACUUM statement regains the storage by deleting obsolete tables, tuples, indexes, etc.

This post will present a detailed working mechanism and usage of the VACUUM command through practical examples. So, let’s start.

What is VACUUM and How to Use it in PostgreSQL?

It is a command-line utility that vacates the space engaged by obsolete records, tuples, etc. The VACUUM command optimizes the performance of the Postgres databases, records, etc.

Syntax

The below syntax will guide you on how to use the VACUUM command in Postgres:

VACCUM [FULL] [FREEZE] [VERBOSE] (tab_name);

Here, the tab_name is optional, if you specify the tab_name, then only that specific table will be vacuumed; however, if you don’t specify the table’s name, then all the tables of the selected database will be vacuumed.

Which Parameters Does PostgreSQL Accept for the VACUUM Command?

FULL, FREEZE, and VERBOSE are the optional arguments that VACUUM can accept. All these options serve different functionalities.

FULL: It is used to write the full content of a table into a new file. It assists the users in regaining all the unused space.

FREEZE: It is very much similar to the FULL option in terms of applicability. When the vacuum operation is performed, all the records are frozen.

VERBOSE: If the VERBOSE option is used with the VACUUM command, then the output will be in a more detailed format.

When to Use VACUUM Command in PostgreSQL?

Vacuuming in PostgreSQL requires a lot of CPU & I/O compute memory; therefore, the VACUUM command should ONLY be used in moderately active applications.

Key Points

Some key points and good practices regarding the VACUUM command are listed below that will assist you in understanding the working of the VACUUM command in a better way:

- Skipping the table/column name from the VACUUM command will vacuum the entire database.

- A database keeps the original records when you update a table. VACUUM deletes that old records/tuples and frees up space in the Postgres database.

- Only those users can process a request for the VACUUM command who have access privileges.

- Only tables with VACUUM permissions can be vacuumed.

VACUUM commands cannot be run within transactions.

Let’s implement it practically.

Practical Implementation of VACUUM Command in Postgres.

The VACUUM command can be implemented on databases, tables, etc. This section will show you various use cases of the VACUUM command:

Example #1: How to Regain the Unused Space Using VACUUM Command?

Let’s learn how to use the VACUUM command to free up unused space so that the same table can use it. It wouldn’t reduce the database size.

Firstly, establish a connection with the desired database and then apply the VACUUM command on the example database:

\c example;

Now, run the VACUUM command:

VACUUM;

Output retrieves “VACUUM”, proving that the command frees up the storage within each table, and now the available storage can be reused. To verify the working of the VACUUM command, run the below query:

SELECT relname, n_dead_tup 
FROM pg_stat_user_tables;

• Here, pg_stat_user_tables is a built-in table in Postgres used to find the number of dead rows in a particular table.
• “relname” and “n_dead_tup” represents the table’s columns that describe the relation name and number of dead tuples, respectively.

The output verifies that each table of the targeted table has been vacuumed successfully.

Example #2: How to Get the Details of the Vacuumed Database?

Let’s run the VACUUM command with the VERBOSE option to get the details of the vacuumed database:

VACUUM VERBOSE;

The output shows that the VERBOSE option provides the details of the vacuumed data.

Example #3: How to Free Unused Space and Optimize the Database?

Run the VACUUM command with the FULL option to free the unused space and minimize the database file:

VACUUM FULL;

The example database has been vacuumed successfully.

Example #4: How to Use VACUUM Command on a Table?

Specify the table name with the VACUUM command to vacuum only a specific table. The following command will free the unused space from the bike_details table:

VACUUM FULL VERBOSE bike_details;

As we used FULL and VERBOSE with the VACUUM command, so, all the unused space was regained, and a vacuum activity report was also produced. Run the following command to verify the working of the VACUUM command with respect to a specific table:

SELECT n_dead_tup
FROM pg_stat_user_tables
WHERE relname='bike_details';

The above-given query will retrieve the number of dead rows in the bike_details table:

The output proves that the bike_details table has been vacuumed successfully. It shows that the bike_details table has zero dead row.

What is AUTOVACUUM, and How to Use it in Postgres?

The Postgres database provides an automated VACUUM and ANALYZE command named AUTOVACUUM. It reduces the user’s efforts as it automates the execution process. By default, Postgres activates the autovacuum command; however, you can manually deactivate it using the following query:

ALTER TABLE tab_name SET(autovacuum_enabled = false);

Let's deactivate the autovacuum command for the article_details table:

ALTER TABLE article_details SET(autovacuum_enabled = false);

That was all the basics regarding Postgres VACUUM Command.

Conclusion

In PostgreSQL, VACUUM is a command-line utility that vacates the space engaged by obsolete records, tuples, etc. The VACUUM command optimizes the performance of the Postgres databases, records, etc. Through practical examples, this post explained how to optimize the performance of the databases and tables using the VACUUM command.