Vacuum and Autovacuum in PostgreSQL

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.

 149 Total Views,  4 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

14 − three =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

NOTE: Excuse us for spammer/promoter i.e don't join the group for spam, will be kicked off without warnings.

Thanks.
Team DBsGuru.

Share Learn Grow!

Welcome to DBsGuru! We wish you a very healthy day, hope and pray things to go in a good way for all of humanity. Stay safe!

We encourage technology experts to contribute share technical knowledge in form of writing technical articles/blogs, SQL commands for daily usage (basic to a high level), Carrier guidance on any technology, and become an author.

We have a ready platform for you with no profit no loss (as of now, in the future you may also earn revenue) if you are ready to contribute to writing articles, click on the registration link and the article will be published as an individual contributor on your name.

Click here for registration

Thanks,
Team DBsGuru
We Commit We Deliver