Vacuum and Autovacuum in PostgreSQL

In this article, we will understand the detailed concept of Vacuum & Autovacuum along with practical examples of the same.

Vacuum

Whenever we run an UPDATE command, the system does not change the actual data, but it inserts new data in reference to the old data and mark the row as deleted (i.e. UPDATE operation = DELETE + INSERT).

Whenever DELETE operations are performed, it marks the existing tuple as DEAD instead of physically removing those tuples.

So each DELETE and UPDATE command will result in one DEAD tuple, which is never going to be used. These dead tuples will lead to unnecessary extra space usage even though the same or less number of effective records. This is also called space bloating in PostgreSQL. VACUUM is the maintenance process that takes care of dealing with a DEAD tuple (rows).

SYNTAX : VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE table_name [ (col1, col2, … col_n) ];

VACUUM reclaims storage occupied by dead tuples (deleted or updated rows). ANALYZE will collect stats for each table.

Plain Vacuum (without FULL) simply reclaims space and can operate in parallel with normal reading and writing of the table. Reclaimed storage space is not given back to the operating system rather they are just defragmented within the same page, so they are just available to be re-used by future data insertion within the same table.

VACUUM FULL locks the entire table rewrites the entire contents of the table into a new file with no space, allows unused space to be returned to the OS. Unlike VACUUM, FULL VACUUM does not allow parallel operation as it takes an exclusive lock on the relation getting FULL VACUUMed.

In the below example when we updated 82 rows. The dead tuples (row) showed: 82 and after vacuum operation, dead tuple (rows) cleared. Similarly after deleting operation on 82 rows. The dead tuples (row) showed: 82 and after vacuum and analyze operation dead tuple (rows) cleared and last analyze column updated.

[root@test-machine02 bin]# su - postgres
Last login: Sun Jun  6 14:29:41 +03 2021 on pts/1
-bash-4.2$ psql
psql (13.2)
Type "help" for help.

postgres=# \connect dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=#
dvdrental=# create table vac_test as select * from pg_tables;
SELECT 82

dvdrental=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='vac_test';
 n_dead_tup | last_vacuum | last_analyze | n_tup_upd | n_tup_del | n_tup_hot_upd | relname  | seq_scan | idx_scan
------------+-------------+--------------+-----------+-----------+---------------+----------+----------+----------
          0 |             |              |         0 |         0 |             0 | vac_test |        0 |
(1 row)

dvdrental=# update vac_test set tableowner='test_owner';
UPDATE 82

dvdrental=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='vac_test';
 n_dead_tup | last_vacuum | last_analyze | n_tup_upd | n_tup_del | n_tup_hot_upd | relname  | seq_scan | idx_scan
------------+-------------+--------------+-----------+-----------+---------------+----------+----------+----------
         82 |             |              |        82 |         0 |             0 | vac_test |        1 |
(1 row)

dvdrental=#
dvdrental=# vacuum verbose vac_test;
INFO:  vacuuming "public.vac_test"
INFO:  "vac_test": found 0 removable, 82 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4928
There were 82 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

dvdrental=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='vac_test';
 n_dead_tup |          last_vacuum          | last_analyze | n_tup_upd | n_tup_del | n_tup_hot_upd | relname  | seq_scan | idx_scan
------------+-------------------------------+--------------+-----------+-----------+---------------+----------+----------+----------
          0 | 2021-06-08 10:34:42.419414+03 |              |        82 |         0 |             0 | vac_test |        1 |
(1 row)

dvdrental=# delete from vac_test;
DELETE 82

dvdrental=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='vac_test';
 n_dead_tup |          last_vacuum          | last_analyze | n_tup_upd | n_tup_del | n_tup_hot_upd | relname  | seq_scan | idx_scan
------------+-------------------------------+--------------+-----------+-----------+---------------+----------+----------+----------
         82 | 2021-06-08 10:34:42.419414+03 |              |        82 |        82 |             0 | vac_test |        2 |
(1 row)

dvdrental=# vacuum full verbose analyze vac_test;
INFO:  vacuuming "public.vac_test"
INFO:  "vac_test": found 82 removable, 0 nonremovable row versions in 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  analyzing "public.vac_test"
INFO:  "vac_test": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM

dvdrental=#  select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='vac_test';
 n_dead_tup |          last_vacuum          |         last_analyze          | n_tup_upd | n_tup_del | n_tup_hot_upd | relname  | seq_scan | idx_scan
------------+-------------------------------+-------------------------------+-----------+-----------+---------------+----------+----------+----------
          0 | 2021-06-08 10:34:42.419414+03 | 2021-06-08 10:35:56.531942+03 |        82 |        82 |             0 | vac_test |        3 |
(1 row)

dvdrental=#

AUTOVACUUM

AUTOVACUUM daemon is an optional feature that automatically vacuums the database so that you don’t have to manually run the VACUUM statement. The AUTOVACUUM daemon is enabled in the default configuration.

The AUTOVACUUM daemon is made up of multiple processes that reclaim storage by removing obsolete data or tuples from the database. It checks for tables that have a significant number of inserted, updated, or deleted records and vacuums these tables based on the configuration settings below.

                 name                  |  setting  | unit |                                        short_desc
---------------------------------------+-----------+------+-------------------------------------------------------------------------------------------
autovacuum                            | on        |      | Starts the autovacuum subprocess.
autovacuum_max_workers                | 3         |      | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_naptime                    | 60        | s    | Time to sleep between autovacuum runs.
autovacuum_work_mem                   | -1        | kB   | Sets the maximum memory to be used by each autovacuum worker process.

autovacuum_analyze_scale_factor       | 0.1       |      | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold          | 50        |      | Minimum number of tuple inserts, updates, or deletes prior to analyze.

autovacuum_vacuum_scale_factor        | 0.2       |      | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold           | 50        |      | Minimum number of tuple updates or deletes prior to vacuum.

autovacuum_vacuum_cost_delay          | 2         | ms   | When the autovacuum process is about to be executed, the cost of vacuum execution is evaluated. If the value set by autovacuum_vacuum_cost_limit is exceeded, there is a delay. The delay time is autovacuum_vacuum_cost_delay. If the value is -1, the vacuum_cost_delay value is used, and the default value is 20 ms.
autovacuum_vacuum_cost_limit          | -1        |      | This value is the evaluation threshold of the autovacuum process. The default is -1, which means to use the “vacuum_cost_limit” value. If the cost evaluated during the execution of the autovacuum process exceeds autovacuum_vacuum_cost_limit, the autovacuum process will sleep.

autovacuum_vacuum_insert_scale_factor | 0.2       |      | Number of tuple inserts prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_insert_threshold    | 1000      |      | Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums.

log_autovacuum_min_duration           | -1        | ms   | This parameter is used to record the execution time of autovacuum. When the execution time of autovaccum exceeds the setting of the log_autovacuum_min_duration parameter, the information of autovacuum is recorded in the log. The default is “-1”, which means no recording.

autovacuum_freeze_max_age             | 200000000 |      | Set the upper limit of XID that needs to be forced to clean up the database.

In the below example, we have performed a delete operation on 82 rows. By default, autovacuum daemon wakes every 1 min (defined by autovacuum_naptime) and invokes three workers (defined by autovacuum_max_works), As we have default value autovacuum_vacuum_threshold & autovacuum_analyze_threshold to 50. This means for every 50 inserts, updates, or deletes on the table autovacuum will be performed.

dvdrental=# create table autovac_test as select * from pg_tables;
SELECT 82
dvdrental=# select n_dead_tup ,last_autovacuum,last_autoanalyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='autovac_test';
 n_dead_tup | last_autovacuum |       last_autoanalyze        | n_tup_upd | n_tup_del | n_tup_hot_upd |   relname    | seq_scan | idx_scan
------------+-----------------+-------------------------------+-----------+-----------+---------------+--------------+----------+----------
          0 |                 | 2021-06-08 10:58:25.308147+03 |         0 |         0 |             0 | autovac_test |        0 |
(1 row)

dvdrental=#
dvdrental=# delete from autovac_test;
DELETE 82
dvdrental=# select n_dead_tup ,last_autovacuum,last_autoanalyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='autovac_test';
 n_dead_tup | last_autovacuum |       last_autoanalyze        | n_tup_upd | n_tup_del | n_tup_hot_upd |   relname    | seq_scan | idx_scan
------------+-----------------+-------------------------------+-----------+-----------+---------------+--------------+----------+----------
         82 |                 | 2021-06-08 10:58:25.308147+03 |         0 |        82 |             0 | autovac_test |        1 |
(1 row)

dvdrental=# select n_dead_tup ,last_autovacuum,last_autoanalyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='autovac_test';
 n_dead_tup |        last_autovacuum        |       last_autoanalyze        | n_tup_upd | n_tup_del | n_tup_hot_upd |   relname    | seq_scan | idx_scan
------------+-------------------------------+-------------------------------+-----------+-----------+---------------+--------------+----------+----------
          0 | 2021-06-08 10:59:25.282226+03 | 2021-06-08 10:59:25.283068+03 |         0 |        82 |             0 | autovac_test |        1 |
(1 row)

dvdrental=#

[root@test-machine02 u01]#  ps -ef|grep post |grep autovacuum
postgres  88815  88808  0 May20 ?        00:09:59 postgres: autovacuum launcher
[root@test-machine02 u01]#

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DBsGuru