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 ).
Make DBA Life Easy 