PostgreSQL Automatic Vacuuming

Why Vacuum ?

In PostgreSQL, when rows are updated or deleted from a table, dead rows are created and left behind, vacuum daemon processes will clean those dead tuples ( rows) periodically based on parameter setting for vacuum process. Generally the following four reasons explain why vacuum is needed:

  • To recover or reuse disk space occupied by updated or deleted rows.
  • To update data statistics used by the PostgreSQL query planner.
  • To update the visibility map, which speeds up index-only scans.
  • To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

Automatic Vacuuming Parameters

There are following 14 configuration parameters for PostgreSQL 11. For all the details, please refer to  Automatic Vacuuming.

postgres=> select name, setting,unit 
           from pg_settings 
            where name like '%autovacuum%';
                name                 |  setting  | unit
-------------------------------------+-----------+------
 autovacuum                          | on        |
 autovacuum_analyze_scale_factor     | 0.05      |
 autovacuum_analyze_threshold        | 50        |
 autovacuum_freeze_max_age           | 200000000 |
 autovacuum_max_workers              | 3         |
 autovacuum_multixact_freeze_max_age | 400000000 |
 autovacuum_naptime                  | 15        | s
 autovacuum_vacuum_cost_delay        | 20        | ms
 autovacuum_vacuum_cost_limit        | 200       |
 autovacuum_vacuum_scale_factor      | 0.1       |
 autovacuum_vacuum_threshold         | 50        |
 autovacuum_work_mem                 | -1        | kB
 log_autovacuum_min_duration         | 10000     | ms
 rds.force_autovacuum_logging_level  | info      |
(14 rows)

Now we have a look at the two most important parameters of automatic vacuuming.

                name                 |  setting  | unit
-------------------------------------+-----------+------
 autovacuum_vacuum_scale_factor      | 0.1       |
 autovacuum_vacuum_threshold         | 50        |

We still use table TEST as an example to demonstrate how those two parameters work together.

We intentionally modified autovacuum_vacuum_threshold value from default 50 to 5 for easy testing purpose.

postgres=> ALTER TABLE test SET (autovacuum_enabled = true,autovacuum_vacuum_threshold=5);
ALTER TABLE

postgres=> \d+ test
                                   Table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
Options: autovacuum_analyze_threshold=5, autovacuum_enabled=true, autovacuum_vacuum_threshold=5


postgres=> select relname,n_tup_ins , n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
from pg_stat_all_tables where relname='test'; \gx
 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum |         last_analyze          |       last_autoanalyze        | vacuum_count | autovacuum_count
 | analyze_count | autoanalyze_count
---------+-----------+-----------+-----------+------------+------------+-------------+-----------------+-------------------------------+-------------------------------+--------------+-----------------
-+---------------+-------------------
 test    |        12 |         0 |         0 |         12 |          0 |             |                 | 2020-10-06 03:31:12.055461+00 | 2020-10-06 10:21:34.177395+00 |            0 |                0
 |             1 |                 2
(1 row)


-[ RECORD 1 ]-----+------------------------------
relname           | test
n_tup_ins         | 12
n_tup_upd         | 0
n_tup_del         | 0
n_live_tup        | 12
n_dead_tup        | 0
last_vacuum       |
last_autovacuum   |
last_analyze      | 2020-10-06 03:31:12.055461+00
last_autoanalyze  | 2020-10-06 10:21:34.177395+00
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 1
autoanalyze_count | 2


postgres=> delete from test where id>=6;
DELETE 7

postgres=> select relname,n_tup_ins , n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
from pg_stat_all_tables where relname='test'; \gx
 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum |        last_autovacuum        |         last_analyze          |       last_autoanalyze        | vacuum_count | au
tovacuum_count | analyze_count | autoanalyze_count
---------+-----------+-----------+-----------+------------+------------+-------------+-------------------------------+-------------------------------+-------------------------------+--------------+---
---------------+---------------+-------------------
 test    |        12 |         0 |         7 |          5 |          0 |             | 2020-10-06 10:28:35.355256+00 | 2020-10-06 03:31:12.055461+00 | 2020-10-06 10:28:35.375704+00 |            0 |
             1 |             1 |                 3
(1 row)


-[ RECORD 1 ]-----+------------------------------
relname           | test
n_tup_ins         | 12
n_tup_upd         | 0
n_tup_del         | 7
n_live_tup        | 5
n_dead_tup        | 0
last_vacuum       |
last_autovacuum   | 2020-10-06 10:28:35.355256+00
last_analyze      | 2020-10-06 03:31:12.055461+00
last_autoanalyze  | 2020-10-06 10:28:35.375704+00
vacuum_count      | 0
autovacuum_count  | 1
analyze_count     | 1
autoanalyze_count | 3

postgres=>

After deleted 7 of 12 records from TEST table, it triggers both auto analyzing and auto vacuuming immediately.

It triggers auto analyzing, because deleted records ( 7 ) > autovacuum_analyze_scale_factor( 0.05 ) *table records (12) + autovacuum_analyze_threshold (5 ).

It triggers auto vacuuming, because deleted records ( 7 ) > autovacuum_vacuum_scale_factor( 0.1 ) *table records(12) + autovacuum_vacuum_threshold (5 ).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.