MVCC
Autovacuuming
16
Copyright
© Postgres Professional, 2016–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Autovacuuming
Autoanalysis
Autovacuuming Setup
3
Autovacuuming
Operates similarly to plain vacuuming
Performed periodically
for tables with a certain number of changes
including toast tables
Autovacuum launcher process
always running
controls launch of worker processes
Autovacuum worker processes
started by the postmaster process at the request of the autovacuum launcher
connect to a given database, iterate through and clean tables
Autovacuuming is a mechanism that allows to start plain vacuuming at
certain points in time, based on the number of changes in tables. This
process is more convenient and efficient than cron-based scheduling, as it
dynamically adapts to system activity.
When autovacuuming is enabled, the system continuously runs an
autovacuum launcher, which is responsible for starting9worker9processes.
The actual vacuuming is performed by autovacuum worker processes,
several instances of which can run in parallel.
Autovacuum worker processes are started by the postmaster process upon
request from the autovacuum launcher (since the postmaster is responsible
for spawning all new processes).
4
Autovacuum Launcher
Periodically launches a worker process into each active database
once in autovacuum_naptime
several processes can operate on the same database
total number of worker processes ≤ autovacuum_max_workers
Settings
autovacuum = on
track_counts = on
autovacuum_naptime = 60 s
autovacuum_max_workers = 3
both should be enabled
?
The autovacuum launcher process makes a list of databases with recorded
activity (specifically, databases with usage statistics being collected).
A new worker process is launched once per autovacuum_naptime for each
database from the list (that is, if there are N databases, processes will be
spawned N times more frequently). If a worker process has not cleaned up
the entire database during the autovacuum_naptime, another worker
process will be assigned to the same database, and they will run
concurrently. The total number of worker processes is limited by the
autovacuum_max_workers parameter.
For autovacuuming to function the autovacuum and track_counts
parameters must be enabled. The latter turns on the collection of usage
statistics.
5
Autovacuum Worker
Makes a list of database objects for vacuuming
number of dead row versions exceeds
autovacuum_vacuum_threshold +
autovacuum_vacuum_scale_factor × number of rows in the table
number of inserted rows exceeds
autovacuum_vacuum_insert_threshold +
autovacuum_vacuum_insert_scale_factor × number of rows in the table
regular tables and toast tables, materialized views are included
temporary tables are not included
The worker process connects to the specified database and makes a list of
all tables, materialized views, and toast tables that require vacuuming.
Objects require vacuuming when they have accumulated a significant
number of obsolete (dead) row versions.
Objects where a significant number of new rows have been inserted since
the last vacuum are also processed. It is important for insert-only tables:
they also require vacuuming to update the visibility map and freeze old row
versions (discussed in the Freezing lesson).
The exact formulas are shown on the slide. Each formula uses two
parameters: one (*_threshold) defines an absolute value, while the other
(*_scale_factor) specifies a fraction of rows.
The number of rows is determined approximately, according to statistics:
●
Total rows: pg_class.reltuples
●
Dead rows: pg_stat_all_tables.n_dead_tup
●
Inserted rows: pg_stat_all_tables.n_ins_since_vacuum
6
Autovacuum Worker
Makes a list of database objects for analysis
the number of modified rows exceeds
autovacuum_analyze_threshold +
autovacuum_analyze_scale_factor × number of rows in the table
includes regular tables and materialized views
does not include toast tables and temporary tables
Sequentially cleans up and/or analyzes selected objects
several processes can run simultaneously in the same database
no parallel execution occurs at the table level
It also makes a list of all tables and materialized views that require analysis.
Toast tables are not analyzed.
Analysis is triggered for objects where a significant number of rows have
changed since the previous analysis. It is determined by statistics:
pg_stat_all_tables.n_mod_since_analyze.
After making both lists, the worker process sequentially performs vacuuming
and/or analysis on the selected objects and terminates upon completion.
Several processes can run simultaneously in one database, processing
different tables in parallel. There is no parallel execution at the table level.
7
Autovacuum Worker
Autovacuuming settings
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_insert_scale_factor = 0.2
Table storage parameters
autovacuum_enabled
toast.autovacuum_enabled
and configuration parameters with identical names
↓
↓?
The default settings trigger autovacuum when 20% of the table's rows are
modified or when 20% of new rows are inserted. It represents a relatively
high threshold: autovacuum will run less frequently but it will take longer to
complete, particularly for large tables. In most scenarios, these percentages
should be reduced.
If necessary, these settings can be overridden for individual tables by
changing table storage parameters:
CREATE TABLE ... WITH (parameter=value)
Moreover, the parameters can be configured separately for toast tables.
Additionally, autovacuum can be disabled for individual tables.
8
Autovacuum Worker
Autoanalysis settings
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
Table storage parameters
autovacuum_analyze_threshold
autovacuum_analyze_scale_factor
no analysis for toast tables
?
The default settings trigger autovacuum when the table changes by 10 %.
If necessary, these parameters can be configured for individual tables using
storage parameters.
Toast tables are not analyzed, so there are no corresponding parameters for
them.
9
common limit
for all worker
processes
Autovacuum Worker
Settings for throttling
autovacuum_vacuum_cost_limit = −1
(−1 means vacuum_cost_limit = 200)
autovacuum_vacuum_cost_delay = 2ms
(−1 means vacuum_cost_delay = 0)
vacuum_cost_page_hit, vacuum_cost_page_miss, vacuum_cost_page_dirty
Table storage parameters
autovacuum_vacuum_cost_limit
toast.autovacuum_vacuum_cost_limit
autovacuum_vacuum_cost_delay
toast.autovacuum_vacuum_cost_delay
↑
Autovacuum throttling is identical to vacuum throttling. However, there are
additional parameters autovacuum_vacuum_cost_limit and
autovacuum_vacuum_cost_delay. When not set to −1, they override the
vacuum_cost_limit and vacuum_cost_delay parameters.
To improve autovacuum performance, we can increase the
autovacuum_vacuum_cost_limit. However, note that the limit set by this
parameter is shared across all worker processes. Therefore, when
increasing autovacuum_max_workers, we should also increase
autovacuum_vacuum_cost_limit.
These parameters can also be specified for individual tables using storage
parameters.
10
Autovacuum Worker
Memory settings
autovacuum_work_mem = −1
(−1 means maintenance_work_mem = 64MB)
settings are applied to each worker process,
memory is allocated in full immediately
more memory reduces overprocessing of index pages
Monitoring
log_autovacuum_min_duration = 10min
pg_stat_progress_vacuum
In addition to settings that determine when and how autovacuum operates,
we can adjust the memory allocated to autovacuum worker processes.
By default, the memory is limited by the maintenance_work_mem
parameter, which affects not only autovacuum, but also all other
maintenance operations. Usually this parameter can be set to a sufficiently
high value, since there are not so many background processes. However,
the number of autovacuum worker processes (controlled by the
autovacuum_max_workers parameter) can be large, and memory is
allocated immediately (not by demand). For this reason, we can set a
separate limit for autovacuum worker processes using the
autovacuum_work_mem parameter.
As discussed in the Vacuuming lesson, the vacuum procedure can operate
with minimal memory. However, if a table has indexes, insufficient value can
lead to repeated scans of the same index pages, significantly slowing down
autovacuum. The goal is to determine a minimal amount of memory that
prevents repeated index scans.
For monitoring, we use log_autovacuum_min_duration parameter
(autovacuum operations that last longer than its value will be registered in
the server log), and the pg_stat_progress_vacuum view, which provides
real-time progress information.
It is important to note that increasing memory is often less effective than
lowering the vacuum threshold to reduce the amount of data processed per
operation.
12
Tuning Approach
Balance between bloating and overhead
iterative process
Key parameters
autovacuum_vacuum_scale_factor — processing frequency
autovacuum_max_workers — parallel execution
autovacuum_vacuum_cost_limit — processing speed
individual tuning of critical tables using storage parameters
Monitoring
bloating
queued tables awaiting vacuuming
I/O load during vacuuming
Autovacuuming is controlled by a large number of interdependent
parameters. In a well-tuned system, autovacuuming prevents tables from
excessive bloating while avoiding unnecessary overhead.
Finding the balance is an iterative process. We need to set reasonable initial
values based on the size and nature of the use of tables, conduct
continuous monitoring and make adjustments accordingly.
Increasing threshold/scale_factor values leads to greater bloating, but
vacuuming is performed less frequently and in large chunks (spiky loads are
possible). Total overhead is lower. Reducing the parameters leads to more
frequent processing in small chunks. Total overhead increases, but bloating
is reduced. The cost_limit/cost_delay parameters help mitigate spiky loads
by intentionally throttling the speed of vacuuming.
The value set by the threshold/scale_factor parameters determines only the
desired trigger point for vacuuming. With a large number of heavily modified
tables, the vacuuming may take considerable time to complete the pass and
will not start processing the next table immediately. In such case, it is
necessary either to increase vacuum speed (if it was reduced by the
cost_limit/cost_delay parameters), or to increase the number of parallel
workers by the max_workers parameter (which will lead to an increase in
overhead).
Starting with version 16, autovacuum processes apply parameter changes
during the processing of a relation. Therefore, we can speed up an already
running vacuuming of a large table on the fly.
13
Takeaways
Autovacuuming triggers table vacuuming and analysis,
dynamically responding to data changes
Autovacuuming allows fine tuning both at the system level
and for individual tables
The goal of autovacuum tuning is to find a balance, which
typically requires an iterative approach
14
Practice
1. Set autovacuum to trigger when 10 % of rows change,
autovacuum_naptime is 1 second.
2. Create a table containing a large number of rows.
3. Update 5–6% of random rows twenty times with a few seconds
delay between updates. Perform each update in a separate
transaction.
4. How many times did autovacuum run? How much did the table
bloat? Do the results match expectation? How can you explain
them?
