How Postgres Auto vacuum works
The Autovacuum is the background process in Postgres and is enabled by default. However, you can get the settings of Autovacuum by navigating to the postgresql.conf file.
As discussed earlier, the auto vacuum performs both vacuuming and analysis. Postgres analyzes and vacuums a table based on the threshold described below:
For the automatic vacuuming threshold of a table, the following equation is utilized:
And to get the automatic analyzing threshold, the following equation does the job:
The terminologies used in the above sections are described in the upcoming section.
How to Configure Postgres Auto Vacuum
The Auto vacuum functionality of Postgres depends on the settings and options available inside the postgresql.conf file. With the help of the description provided below, you can change the parameters to tune the Auto vacuum functionality.
The following parameters have the key role in defining policy for Postgres autovacuum.
- autovacuum: Shows the default status for auto vacuum call-up.
- autovacuum_naptime: The parameter’s default value is the 60s (1min), which indicates the difference between each auto vacuum wakeup.
- autovacuum_max_workers: The processes that are vacuumed after naptime.
- autovacuum_vacuum_scale_factor: This factor directs the auto vacuum to start the process when the percentage of the value specified is changed. It is by default set to 20%, which means the auto vacuum will come into action when 20% of the tables/records are updated.
- autovacuum_vacuum_threshold: This parameter ensures that auto vacuum must run after specific(by default, it is 50) numbers of tables are updated/changed.
- autovacuum_analyze_scale_factor: This factor refers to the analysis process that an auto vacuum performs. When a specific percentage of records is updated/changed, the table performs the analysis.
- autovacuum_analyze_threshold: When specific numbers of tables encounter updates, and the threshold is met, the auto vacuum starts analyzing the table, and the default value of this parameter is 50.
To edit the file, you can open it in a nano editor by using the command stated below (the location of postgresql.conf file may vary in your case):
How to configure auto vacuum parameters for a table
The postgresql.conf file deals with the global changes to the autovacuum parameters. However, you can tune parameters for a single table. For instance, we have used the following parameters for the linuxhint table of database mydb:
- autovacuum_vacuum_scale_factor = 0.2
- autovacuum_vacuum_threshold = 30
- autovacuum_analyze_scale_factor= 0.2
- autovacuum_analyze_threshold = 20
These parameters will be passed with ALTER TABLE statement of PowerShell and the ALTER TABLE will be applied on linuxhint table as shown below:
It can be calculated manually by inserting the values in the equations given in the above section and is as follows. Suppose the linuxhint table contains 10 tuples:
The VACUUM operation of Autovacuum is calculated as shown below. The output shows that the VACUUM operation of Autovacuum will be performed when the number of obsolete records reaches 31.
Similarly, the ANALYZE operation of Autovacuum on linuxhint table will be carried out when the number of insertions/deletions/updating equals or exceeds 21.
Why Auto vacuum
Looking at the working and the above discussion, the following benefits of Auto Vacuum can be extracted:
- The space is managed effectively by deleting the dead cells
- The analyzing and vacuuming time are saved as it is carried out automatically
- The table bloating does not occur as deletion of dead tuples occurs automatically and thus the table does not overflow.
- Autovacuum can be carried out in parallel manners. For example, Autovacuum does not lock the table as the manual vacuum does.
The auto vacuum functionality of Postgres allows you to perform the vacuum and analyze the tables of a database. Even though one can vacuum and analyze manually, the automated vacuum will do both. This article provides detailed information about the auto vacuum functionality of Postgres. Postgres calculates threshold values for each table and then decides whether to VACUUM or ANALYZE that table. Moreover, we have provided Postgres’s equations to calculate the VACCUM and ANALYZE threshold.