The Postgres records may sometimes occupy space more than the data present in a database. The vacuum command of Postgres is exercised to vacate the extra space occupied by tables/indexes in a database. This extra space issue is caused by either dead records or records having older versions. With the help of the vacuum command, you can avoid the delay in the scanning of records. In this post, the VACUUM command of Postgres is explained briefly to reuse the space occupied by old/deleted records.
Pre-requisites
To use the VACUUM command, you must have Postgres installed on your Linux system. Alongside the installation, a valid database and some tables are also needed to apply this command.
In our case, the following instances of Postgres are used:
- linuxhint refers to the name of the database used to apply the Postgres VACUUM command.
- The table of the linuxhint database being used here is named employee
How does the VACUUM command work
As mentioned earlier, the VACUUM command is used to free up the space caused by the records that are useless. This section briefly describes the usage of the VACUUM command on several instances of Postgres.
Before digging into depth, let’s have a look at the syntax of the VACUUM command that is described below.
Syntax
The VACUUM command can be applied to the databases, tables, and columns of tables (if required). Moreover, there is a long list of options that can be used with the vacuum command to encounter the space issue caused by records. The upcoming section presents examples that explain the usage of the vacuum command alongside the supported options.
How to use the VACUUM command
Firstly, we have to connect to the Postgres database where the VACUUM operation is required. The following three commands lead us to connect with linuxhint (our Postgres database):
Log in as a Postgres user, access the Postgres console and connect to linuxhint database:
$ psql
# \c linuxhint
Starting from the very first step, let’s apply vacuum to the linuxhint database. For that, the command written below is executed.
Note : If no option is passed, the vacuum command automatically applies to the whole database.
As the output returns “VACUUM” that means the command is executed successfully but it does not display any execution process.
Using the verbose option
It is recommended to use a vacuum command with a verbose option to get the details of the execution as well.
Let’s execute the vacuum command with the verbose option to clarify the difference between a simple vacuum command and a vacuum verbose.
Using the VACUUM command on a table
To vacate the extra space on a single table, the vacuum command is exercised by specifying the table name. For this, we have experienced the vacuum command on the employee table of the linuxhint database.
Using full option
The VACUUM generally vacates the space and makes it usable for upcoming records (does not add the space to OS). However, if the full option is used then it would return the space to the OS. During the execution of the VACUUM command, other Postgres queries can also be executed in parallel. However, if the full option of the VACUUM command is used then it won’t allow you to execute any other query until the completion of the VACUUM process. The command provided below assists in this regard:
Using a VACUUM with freeze option
The applicability of the freeze option is similar to that of the full option. It freezes all the records while performing the vacuum operation. The below-stated command exercises the vacuum with freeze.
Using ANALYZE with VACUUM command
ANALYZE is another Postgres command to strengthen the querying plans of Postgres. The VACUUM command can be used along with the ANALYZE command to build a querying plan after vacuuming the database/table. For instance, the below stated command vacuums and analyzes the linuxhint database.
It is observed from the output that each table of columns is vacuumed first and then analyzed. The same pattern is followed until all the tables are vacuumed and analyzed.
Conclusion
The VACUUM command in Postgres resolves the space issue caused by the dead records or the older versions of the records. To combat this, Postgres provides support for the VACUUM command that resolves this problem and removes garbage records. This article briefly explains the usage of VACUUM with a list of supported options that extend the functionality of VACUUM commands. To concrete the ground for the vacuum command, we have experienced its usage by presenting some examples and each example refers to the different functionality of the VACUUM command.
