Introduction to PostgreSQL Vacuum
In PostgreSQL, whenever we perform a delete operation or update the records that lead to obsolete dead tuple formation, then in reality, that records are not physically deleted and are still present in the memory and consume the space required by them. These tuples are removed, and space is available for another usage only after VACUUM is done in PostgreSQL that reclaims the storage space occupied by dead tuples. Hence, it is required that you perform the VACUUM frequently, especially on the most often used and manipulated tables on which most of the operations are performed.
If we perform a vacuum without specifying any parameter, then all the tables present in that database are vacuumed if the user is a superuser or only those tables that the current user is permitted to perform vacuum on it. If the parameter containing the table name is specified, then only that particular table is vacuumed. The vacuum is not present in SQL.
The vacuum is often accompanied by analyzing that helps in maintaining statistics about the database that is further stored in the pg_statistic table. These statistics are used for finding out the execution plans that are most efficient for performance. Analyze can be done only for specific columns if you want and can specify the list of the columns in which you want to perform the analysis. Statistics of only those columns are collected that is specified in analyze’s parameter.
Syntax and Parameters
Syntax and parameters of postgresql vacuum are given below:
Syntax:
VACUUM [({FULL | FREEZE | VERBOSE | ANALYZE}[, ...])] [tablename [(columnname [,...])]]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ tablename ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ tablename [ (columnname [, ...] ) ] ]
Parameters:
There are other parameters that are specified in the vacuum method.
- Tablename: It is an optional parameter. It is the name of the table on which you want to perform a vacuum or analyze. The default value is all tables in the current database.
- Columnname: This is the names of columns for which you can perform vacuum and analyze and are optional in nature. The default value evaluates all the table columns to be analyzed when no particular column name is specified.
- FULL: This is the optional parameter which, when not specified, the vacuum done frees up space for some other users in the same table. It does not block reading and writing operations on the table on which vacuum is being performed, and tables are not locked. The operating system is not returned with an empty space.
However, when we mention FULL and perform full vacuum at that time, the tables on which we are performing vacuum are completely locked, and no operations are allowed on them. The whole directory or file is written to a new location with only saved changes and permanently deleting the dead tuples; the operating system is returned with extra space as compared to a non-full vacuum. But this process is slow in execution and needs to be done only when no operations are being performed on the database, usually at midnight when users do not access the system.
Performing vacuum operation with FULL involves too many I/O operations being performed. So the user may find the performance of the application slower and cause problems while using the application. Hence, it is not recommended to perform FULL vacuum operation in a daily routine. However, in some special cases, like when the operating system requires more memory, then the full vacuum can be done as it shrinks the memory usage and memory occupied by the database and some frequently used/manipulated tables. - FREEZE: Whenever we specify the freeze option, the tuples are aggressively frozen, and this is the same in functionality as specifying the vacuum_freeze_min_age to zero.
- VERBOSE: Information about the tables and vacuum related details for all the tables for which operation is being performed is printed on the console if the VERBOSE option is used. In another case, only the names of the table on which operation is being done are printed.
- ANALYZE: It updates the statistics in pg_statistic that are required and used for building the most efficient execution strategies.
Examples of PostgreSQL Vacuum
Let us login using my Postgres user and then open my psql command-prompt using the following commands –
sudo su – postgres
Enter the password of the superuser if you are logging in using the superuser. In my case, Postgres is my superuser.
psql
enter the password if set by you while accessing the psql.
The output will be somewhat like the following if your default database is Postgres.
Let us see all the tables present in my postgres database using \dt command that results in the following output –
\dt
Now, suppose we want to perform vacuum on educba table and print all the vacuum operation related details in the report format then we will use the following command-prompt
VACUUM VERBOSE educba;
No row was found containing a dead tuple. Hence 0 dead rows were there, and There was 0 unused item identifiers message gets printed along with other details.
Now, let us check the contents of the table educba using the following query statement –
select * from educba;
Now, we will delete the row with course_duration as 30 and technology_name as javascript using the following query statement –
delete from educba where course_duration=30;
As can be seen, one row was deleted. This deleted row is not deleted from the physical memory unless we perform a vacuum operation on it. If we perform vacuum operation on the educba table, then the tuple with javascript as technology_name is an unused and dead tuple. Hence, that will be cleaned and removed from the physical memory of the database. Performing the following query now results in the following output –
VACUUM VERBOSE educba;
Now, it says removed 1-row versions in 1 page that stands for dead tuple that we deleted. Other information related to the performed vacuum operation also gets displayed.
Recommended Articles
This is a guide to PostgreSQL Vacuum. Here we also discuss the introduction and Syntax, and parameters of postgresql vacuum along with different examples and its code implementation. You may also have a look at the following articles to learn more –
