How to Use the PostgreSQL Vacuum

Introduction

When you update a table or delete a record in PostgreSQL, “dead” tuples are left behind. Over time, these obsolete tuples can result in a lot of wasted disk space. Fortunately, you can clean up your database and reclaim space with the help of the PostgreSQL VACUUM statement. Much like a real-life vacuum, this statement cleans up the virtual debris left behind by updates and deletions. In this article, we’ll take a closer look at the PostgreSQL VACUUM statement and learn how to use it.

Prerequisites

In order to get the most value from this tutorial, you’ll need to have PostgreSQL installed on your computer. You should also have some knowledge of PostgreSQL to follow along with the examples we’ll be presenting in this article.

PostgreSQL VACUUM Statement Syntax

There are a few different parameters available when you use the PostgreSQL VACUUM statement:

PostgreSQL Vacuum Statement Parameters and Arguments

Let’s look at each of these parameters in detail:

• FULL – When this parameter is used, it recovers all the unused space; however, it exclusively locks the tables and takes much longer to execute, since it needs to write a new copy of the table that is vacuumed.

• FREEZE – When this parameter is used, Postgres aggressively marks tuples as “frozen”, which is the same as setting the value of vacuum_freeze_min_age to zero.

• VERBOSE – This parameter prints the activity report of the table that is vacuumed.

• ANALYZE – This parameter will update the statistics that the planner uses to determine the most efficient query plan.

• table – When a table is specified, only that table will be vacuumed; otherwise, all the tables will be vacuumed.

• column – When a column is listed, it will be analyzed. The ANALYZE option must be specified when a column list is provided.

PostgreSQL Vacuum Statement Examples

Now that we have a better understanding of the VACUUM statement and its parameters, let’s look at a few examples of how it works.

Reusing the space on the same table

In the example shown below, PostgreSQL will vacuum all the tables, allowing them to be reused. However, it’s important to note that this use of the VACUUM statement only makes the space available for re-use by the table itself. The reclaimed space is not made available to the operating system.

Reusing the space and minimize the database

Using VACUUM FULL will vacuum all the tables and will rewrite each table to a new copy. This operation is a good choice when a large amount of space needs to be reclaimed.

Reusing the space on the table

In the next example, we specify a table named “employees”. This is the only table that will be vacuumed:

List the activity report of the vacuum verbose

The following example uses the VERBOSE option, which prints an activity report of the vacuum process:

The activity report will contain information about the table that has been vacuumed, details of rows that cannot yet be removed and CPU statistics for the query.

For our next example, let’s add the ANALYZE option and specify a column name:

The output will provide information on the employees table.

Conclusion

If you’re looking for ways to optimize the performance of your PostgreSQL database, the VACUUM command can help you clean up your tables and reclaim space that had been occupied by obsolete tuples. In this article, we provided an overview of the PostgreSQL VACUUM statement and reviewed the various parameters available for use. We also provided several examples of the VACUUM command in action. With this tutorial to use as a guide, you’ll be able to utilize the VACUUM statement to keep your own PostgreSQL database running at peak performance.