In PostgreSQL 18, there are nearly 16 postgres configuration parameters related to autovacuum. But when it comes to postgres version 19, it increased by 22. So, in postgres version 19, there are nearly 6 more postgres configuration parameters related to autovacuum.
Autovacuum related parameters in postgres 18.
postgres=# show autovacuum
autovacuum autovacuum_max_workers autovacuum_vacuum_cost_limit autovacuum_vacuum_scale_factor
autovacuum_analyze_scale_factor autovacuum_multixact_freeze_max_age autovacuum_vacuum_insert_scale_factor autovacuum_vacuum_threshold
autovacuum_analyze_threshold autovacuum_naptime autovacuum_vacuum_insert_threshold autovacuum_worker_slots
autovacuum_freeze_max_age autovacuum_vacuum_cost_delay autovacuum_vacuum_max_threshold autovacuum_work_mem
Autovacuum related parameters in postgres 18.
cybrosys@postgres=# show autovacuum
autovacuum autovacuum_max_parallel_workers autovacuum_vacuum_cost_limit autovacuum_vacuum_score_weight
autovacuum_analyze_scale_factor autovacuum_max_workers autovacuum_vacuum_insert_scale_factor autovacuum_vacuum_threshold
autovacuum_analyze_score_weight autovacuum_multixact_freeze_max_age autovacuum_vacuum_insert_score_weight autovacuum_worker_slots
autovacuum_analyze_threshold autovacuum_multixact_freeze_score_weight autovacuum_vacuum_insert_threshold autovacuum_work_mem
autovacuum_freeze_max_age autovacuum_naptime autovacuum_vacuum_max_threshold
autovacuum_freeze_score_weight autovacuum_vacuum_cost_delay autovacuum_vacuum_scale_factor
New parameters in postgresql 19 related to the autovacuum.
- Autovacuum_max_parallel_workers
- Autovacuum_vacuum_score_weight
- Autovacuum_vacuum_insert_score_weight
- Autovacuum_analyze_score_weight
- Autovacuum_freeze_score_weight
- autovacuum_multixact_freeze_score_weight
In PostgreSQL version 18, we have autovacuum, but that does not work in parallel. Also, in PostgreSQL version 18, we have parallel vacuum that can be manually executed by the user based on table bloat-like scenarios. Now in PostgreSQL 19, we have a good feature named parallel autovacuum.
1. autovacuum_max_parallel_workers
SHOW autovacuum_max_parallel_workers;
Result :
autovacuum_max_parallel_workers
---------------------------------
0
(1 row)
Check more about this parameter from pg_settings.
select * from pg_settings where name = 'autovacuum_max_parallel_workers';
Result :
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------
name | autovacuum_max_parallel_workers
setting | 0
unit |
category | Vacuuming / Automatic Vacuuming
short_desc | Maximum number of parallel workers that can be used by a single autovacuum worker.
extra_desc |
context | sighup
vartype | integer
source | default
min_val | 0
max_val | 1024
enumvals |
boot_val | 0
reset_val | 0
sourcefile |
sourceline |
pending_restart | f
Purpose:
This parameter is mainly used to set the workers for the parallel execution of the autovacuum process. In postgres, there is a live monitoring view named pg_stat_progress_vacuum to see the current execution of vacuum related metadata.
select * from pg_stat_progress_vacuum;
Result :
-[ RECORD 1 ]--------+------------------
pid | 6156
datid | 5
datname | postgres
relid | 41947
phase | vacuuming indexes
heap_blks_total | 41562
heap_blks_scanned | 41562
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuple_bytes | 67108864
dead_tuple_bytes | 1507328
num_dead_item_ids | 2278205
indexes_total | 1
indexes_processed | 0
delay_time | 0
2. autovacuum_vacuum_score_weight
show autovacuum_vacuum_score_weight;
Result:
autovacuum_vacuum_score_weight
--------------------------------
1
(1 row)
Check more about this parameter from pg_settings.
select * from pg_settings where name = 'autovacuum_vacuum_score_weight';
Result:
-[ RECORD 1 ]---+--------------------------------------------------------------
name | autovacuum_vacuum_score_weight
setting | 1
unit |
category | Vacuuming / Automatic Vacuuming
short_desc | Scaling factor of vacuum score for autovacuum prioritization.
extra_desc |
context | sighup
vartype | real
source | default
min_val | 0
max_val | 10
enumvals |
boot_val | 1
reset_val | 1
sourcefile |
sourceline |
pending_restart | f
In postgres source code, you can see this parameter declaration in the file named autovacuum.c
File path :
src/backend/postmaster/autovacuum.c
You can see this parameter declaration like this
double autovacuum_vacuum_score_weight = 1.0;
In PostgreSQL version 19, autovacuum had a score-based schedule to determine the order in which tables are vacuumed. You can see the new view named pg_stat_autovacuum_scores.
select * from pg_stat_autovacuum_scores limit 5;
Result :
relid | schemaname | relname | score | xid_score | mxid_score | vacuum_score | vacuum_insert_score | analyze_score | do_vacuum | do_analyze | for_wraparound
-------+------------+---------------+-------+-----------+------------+--------------+---------------------+---------------+-----------+------------+----------------
2619 | pg_catalog | pg_statistic | 1e-07 | 1e-07 | 0 | 0 | 0 | 0 | f | f | f
1247 | pg_catalog | pg_type | 1e-07 | 1e-07 | 0 | 0 | 0 | 0 | f | f | f
2836 | pg_toast | pg_toast_1255 | 1e-07 | 1e-07 | 0 | 0 | 0 | 0 | f | f | f
4171 | pg_toast | pg_toast_1247 | 1e-07 | 1e-07 | 0 | 0 | 0 | 0 | f | f | f
2830 | pg_toast | pg_toast_2604 | 1e-07 | 1e-07 | 0 | 0 | 0 | 0 | f | f | f
(5 rows)
This system view shows the internal scores computed by the autovacuum scheduler. Instead of treating VACUUM, ANALYZE, and FREEZE as separate triggers, it calculates a score for each table and uses it to decide which tables should be serviced first.
score =
vacuum_score × autovacuum_vacuum_score_weight
+ vacuum_insert_score × autovacuum_vacuum_insert_score_weight
+ analyze_score × autovacuum_analyze_score_weight
+ xid_score × autovacuum_freeze_score_weight
+ mxid_score × autovacuum_multixact_freeze_score_weight
Fields and it’s purposes:
- relid : The unique id of the relation ( table name )
- Schemaname : The name of the schema where the table is stored.
- Relname : The name of the table.
- score : The final score used by the autovacuum process.
- Xid_score : The score related to the transaction id.
- Mxid_score : The score related to the multi transaction id.
- Vacuum_score : This is the score related to the dead tuples.
- vacuum_insert_score : The score related to the heavy insert activity.
- Analyze_score : The score related to the statistics
- Do_vacuum : This boolean field represents the decision of doing vacuum.
- do_analyze : This boolean field represents the decision of doing analyze during the vacuum process.
- for_wraparound : This represents that the table needs an emergency anti-wraparound vacuum.
3. autovacuum_vacuum_insert_score_weight
show autovacuum_vacuum_insert_score_weight;
Result:
autovacuum_vacuum_insert_score_weight
---------------------------------------
1
(1 row)
Check more about this parameter from pg_settings.
select * from pg_settings where name = 'autovacuum_vacuum_insert_score_weight';
Result:
-[ RECORD 1 ]---+---------------------------------------------------------------------
name | autovacuum_vacuum_insert_score_weight
setting | 1
unit |
category | Vacuuming / Automatic Vacuuming
short_desc | Scaling factor of vacuum insert score for autovacuum prioritization.
extra_desc |
context | sighup
vartype | real
source | default
min_val | 0
max_val | 10
enumvals |
boot_val | 1
reset_val | 1
sourcefile |
sourceline |
pending_restart | f
Purpose:
This is the main parameter that actually indicates the score related to the heavy insert activities on tables.
4. autovacuum_analyze_score_weight
show autovacuum_analyze_score_weight;
Result:
autovacuum_analyze_score_weight
---------------------------------
1
(1 row)
Check more about this parameter from pg_settings.
select * from pg_settings where name = 'autovacuum_analyze_score_weight';
Result:
-[ RECORD 1 ]---+---------------------------------------------------------------
name | autovacuum_analyze_score_weight
setting | 1
unit |
category | Vacuuming / Automatic Vacuuming
short_desc | Scaling factor of analyze score for autovacuum prioritization.
extra_desc |
context | sighup
vartype | real
source | default
min_val | 0
max_val | 10
enumvals |
boot_val | 1
reset_val | 1
sourcefile |
sourceline |
pending_restart | f
Purpose:
This parameter controls how much importance the autovacuum scheduler gives to ANALYZE operations when computing a table's overall autovacuum score.
5. autovacuum_freeze_score_weight
show autovacuum_freeze_score_weight;
Result:
autovacuum_freeze_score_weight
--------------------------------
1
(1 row)
Check more about this parameter from pg_settings.
select * from pg_settings where name = 'autovacuum_freeze_score_weight';
Result:
-[ RECORD 1 ]---+--------------------------------------------------------------
name | autovacuum_freeze_score_weight
setting | 1
unit |
category | Vacuuming / Automatic Vacuuming
short_desc | Scaling factor of freeze score for autovacuum prioritization.
extra_desc |
context | sighup
vartype | real
source | default
min_val | 0
max_val | 10
enumvals |
boot_val | 1
reset_val | 1
sourcefile |
sourceline |
pending_restart | f
Purpose:
This parameter controls how much importance the autovacuum scheduler gives to transaction ID (XID) aging when deciding which table to vacuum next.
6. autovacuum_multixact_freeze_score_weight
show autovacuum_multixact_freeze_score_weight;
Result:
autovacuum_multixact_freeze_score_weight ------------------------------------------ 1(1 row)
Check more about this parameter from pg_settings.
select * from pg_settings where name = 'autovacuum_multixact_freeze_score_weight';
Result:
-[ RECORD 1 ]---+------------------------------------------------------------------------
name | autovacuum_multixact_freeze_score_weight
setting | 1
unit |
category | Vacuuming / Automatic Vacuuming
short_desc | Scaling factor of multixact freeze score for autovacuum prioritization.
extra_desc |
context | sighup
vartype | real
source | default
min_val | 0
max_val | 10
enumvals |
boot_val | 1
reset_val | 1
sourcefile |
sourceline |
pending_restart | f
This parameter controls how much importance the autovacuum scheduler gives to MultiXact ID aging when deciding which table to vacuum next.
When comparing PostgreSQL 18 and PostgreSQL 19, PostgreSQL 19 includes useful features related to autovacuum. The parallel execution of autovacuum and new scoring based autovacuum gives a new faster way of cleaning dead tuples effectively and reusing that space for future inserts. The 6 new autovacuum parameters give a new way of doing autovacuum and the new view named pg_stat_autovacuum_scores, gives clear insight about the prioritization of autovacuum on the tables.