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:
1 | VACUUM; |
1 | VACUUM FULL; |
1 | VACUUM FREEZE; |
1 | VACUUM VERBOSE; |
1 | VACUUM TABLE_NAME ; |
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 ofvacuum_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.
1 | VACUUM; |
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.
1 | VACUUM FULL; |
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:
1 | VACUUM employees; |
List the activity report of the vacuum verbose
The following example uses the VERBOSE option, which prints an activity report of the vacuum process:
1 | VACUUM FULL VERBOSE employees; |
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:
1 2 | VACUUM FULL VERBOSE ANALYZE employees ( first_name, last_name, department_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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started