Skip to content

06 Lesson 06: MVCC / Autovacuuming

Native Markdown source page: MVCC / Autovacuuming
Note

This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. HTML source: dba2_06_mvcc_autovacuum.html Archive: DBA2-handouts-16-en.zip PDF pages: 20

Page 1: MVCC / Autovacuuming

© 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.

Page 2: Topics

  • Autovacuuming
  • Autoanalysis
  • Autovacuuming Setup

Page 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

Speaker notes

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 starting worker processes. 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).

https://postgrespro.com/docs/postgresql/16/routine-vacuuming#AUTOVACU UM

Page 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
    • ?

Speaker notes

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.

Page 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

Speaker notes

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

Page 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

Speaker notes

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.

Page 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/runtime-config-autovacuum

https://postgrespro.com/docs/postgresql/16/sql-createtable

Page 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

Speaker notes

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.

Page 9: Autovacuum Worker

Settings for throttling

  • common limit
  • for all worker
  • autovacuum_vacuum_cost_limit = −1
  • (−1 means vacuum_cost_limit = 200)
  • processes
  • ↑
  • 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/runtime-config-resource#RUNTI ME-CONFIG-RESOURCE-VACUUM-COST

Page 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

Speaker notes

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.

Page 11: Autovacuuming

Create a table with 1000 rows:

=> CREATE DATABASE mvcc_autovacuum;
CREATE DATABASE
=> \c mvcc_autovacuum
You are now connected to database "mvcc_autovacuum" as user "student".
=> CREATE TABLE tvac(
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  n numeric
);
CREATE TABLE
=> INSERT INTO tvac(n) SELECT 1 FROM generate_series(1,1000);
INSERT 0 1000

Set autovacuum parameters. Short nap time to see results immediately:

=> ALTER SYSTEM SET autovacuum_naptime = 1;
ALTER SYSTEM

One percent of rows:

=> ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.01;
ALTER SYSTEM

Zero threshold:

=> ALTER SYSTEM SET autovacuum_vacuum_threshold = 0;
ALTER SYSTEM

Set autoanalysis parameters as well. Two percent of rows:

=> ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;
ALTER SYSTEM

Zero threshold:

=> ALTER SYSTEM SET autovacuum_analyze_threshold = 0;
ALTER SYSTEM

Reload the configuration:

=> SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Let’s wait a little for autoanalysis to trigger. Create a view showing whether our table needs vacuuming. Here we consider only dead tuples, but condition for inserted rows can be added similarly.

Page 12: Page 12

=> CREATE VIEW vacuum_v AS
WITH params AS (
  SELECT (SELECT setting::integer
          FROM   pg_settings
          WHERE  name = 'autovacuum_vacuum_threshold') AS vacuum_threshold,
         (SELECT setting::float
          FROM   pg_settings
          WHERE  name = 'autovacuum_vacuum_scale_factor') AS vacuum_scale_factor
)
SELECT st.relname,
       st.n_dead_tup dead_tup,
       (p.vacuum_threshold + p.vacuum_scale_factor*c.reltuples)::integer max_dead_tup,
       st.n_dead_tup > (p.vacuum_threshold + p.vacuum_scale_factor*c.reltuples)::integer need_vacuum,
       st.last_autovacuum
FROM   pg_stat_all_tables st,
       pg_class c,
       params p
WHERE  c.oid = st.relid
AND    c.relname = 'tvac';
CREATE VIEW

Currently the table does not require vacuuming (there are no dead tuples) and it has not been vacuumed yet:

=> SELECT * FROM vacuum_v;
 relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum
---------+----------+--------------+-------------+-----------------
 tvac    |        0 |           10 | f           |
(1 row)

We can create a similar view for the analysis:

=> CREATE VIEW analyze_v AS
WITH params AS (
  SELECT (SELECT setting::integer
          FROM   pg_settings
          WHERE  name = 'autovacuum_analyze_threshold') as analyze_threshold,
         (SELECT setting::float
          FROM   pg_settings
          WHERE  name = 'autovacuum_analyze_scale_factor') as analyze_scale_factor
)
SELECT st.relname,
       st.n_mod_since_analyze mod_tup,
       (p.analyze_threshold + p.analyze_scale_factor*c.reltuples)::integer max_mod_tup,
       st.n_mod_since_analyze > (p.analyze_threshold + p.analyze_scale_factor*c.reltuples)::integer need_analyze,
       st.last_autoanalyze
FROM   pg_stat_all_tables st,
       pg_class c,
       params p
WHERE  c.oid = st.relid
AND    c.relname = 'tvac';
CREATE VIEW

The view shows that the table does not require analysis; autoanalysis has already been performed:

=> SELECT * FROM analyze_v;
 relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze
---------+---------+-------------+--------------+-------------------------------
 tvac    |       0 |          20 | f            | 2026-06-11 17:32:05.179336+03
(1 row)

Let’s disable autovacuum at table level and update 11 rows (more than 1%):

=> ALTER TABLE tvac SET (autovacuum_enabled = off);
ALTER TABLE
=> UPDATE tvac SET n = n + 1 WHERE id <= 11;
UPDATE 11

Check the views:

=> SELECT * FROM vacuum_v;

Page 13: Page 13

 relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum
---------+----------+--------------+-------------+-----------------
 tvac    |       11 |           10 | t           |
(1 row)
=> SELECT * FROM analyze_v;
 relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze
---------+---------+-------------+--------------+-------------------------------
 tvac    |      11 |          20 | f            | 2026-06-11 17:32:05.179336+03
(1 row)

As we can see, the table requires autovacuum. Enable autovacuum for the table and wait a few seconds…

=> ALTER TABLE tvac SET (autovacuum_enabled = on);
ALTER TABLE
=> SELECT * FROM vacuum_v;
 relname | dead_tup | max_dead_tup | need_vacuum |       last_autovacuum
---------+----------+--------------+-------------+------------------------------
 tvac    |        0 |           10 | f           | 2026-06-11 17:32:10.67801+03
(1 row)
=> SELECT * FROM analyze_v;
 relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze
---------+---------+-------------+--------------+-------------------------------
 tvac    |      11 |          20 | f            | 2026-06-11 17:32:05.179336+03
(1 row)

Autovacuum has processed the table. The number of obsolete versions is again zero. At the same time, autoanalysis was not performed. Update 11 more rows:

=> ALTER TABLE tvac SET (autovacuum_enabled = off);
ALTER TABLE
=> UPDATE tvac SET n = n + 1 WHERE id <= 11;
UPDATE 11
=> SELECT * FROM vacuum_v;
 relname | dead_tup | max_dead_tup | need_vacuum |       last_autovacuum
---------+----------+--------------+-------------+------------------------------
 tvac    |       11 |           10 | t           | 2026-06-11 17:32:10.67801+03
(1 row)
=> SELECT * FROM analyze_v;
 relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze
---------+---------+-------------+--------------+-------------------------------
 tvac    |      22 |          20 | t            | 2026-06-11 17:32:05.179336+03
(1 row)

Now both autovacuum and autoanalysis should trigger. Let’s check it:

=> ALTER TABLE tvac SET (autovacuum_enabled = on);
ALTER TABLE

Wait for a few seconds…

=> SELECT * FROM vacuum_v;

Page 14: Page 14

 relname | dead_tup | max_dead_tup | need_vacuum |        last_autovacuum
---------+----------+--------------+-------------+-------------------------------
 tvac    |        0 |           10 | f           | 2026-06-11 17:32:15.876718+03
(1 row)
=> SELECT * FROM analyze_v;
 relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze
---------+---------+-------------+--------------+-------------------------------
 tvac    |       0 |          20 | f            | 2026-06-11 17:32:15.879415+03
(1 row)

Everything is correct, both operations have completed. The created views can be used for monitoring tables waiting for vacuum and analysis by removing the table name condition. For completeness, they should account for the storage parameters of individual tables.

=> ALTER TABLE tvac SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE
=> SELECT unnest(reloptions) FROM pg_class WHERE relname = 'tvac';
               unnest
-------------------------------------
 autovacuum_enabled=on
 autovacuum_vacuum_scale_factor=0.01
(2 rows)

Page 15: 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

Speaker notes

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.

Page 16: 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

Page 17: Practice

  1. Set autovacuum to trigger when 10% of rows change, autovacuum_naptime is 1 second.
    1. Create a table containing a large number of rows.
    1. Update 5–6% of random rows twenty times with a few seconds delay between updates. Perform each update in a separate transaction.
    1. How many times did autovacuum run? How much did the table bloat? Do the results match expectation? How can you explain them?

Page 18: 1. Autovacuuming Setup

We configure autovacuum to trigger when 10% of rows are changed.

=> ALTER SYSTEM SET autovacuum_vacuum_threshold = 0;
ALTER SYSTEM
=> ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM

We set the worker processes to be launched once per second:

=> ALTER SYSTEM SET autovacuum_naptime = '1s';
ALTER SYSTEM
=> SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

2. Table

=> CREATE DATABASE mvcc_autovacuum;
CREATE DATABASE
=> \c mvcc_autovacuum
You are now connected to database "mvcc_autovacuum" as user "student".
=> CREATE TABLE t(n integer);
CREATE TABLE
=> INSERT INTO t(n) SELECT 1 FROM generate_series(1,100_000);
INSERT 0 100000

3. Data Update

Initial table size:

=> SELECT pg_size_pretty(pg_table_size('t'));
 pg_size_pretty
----------------
 3568 kB
(1 row)

We update the table with delays of several seconds between commands.

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5407
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5566
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5500
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5563
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5470
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

Page 19: Page 19

UPDATE 5542
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5440
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5580
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5513
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5583
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5568
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5466
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5600
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5526
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5600
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5507
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5416
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5465
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5440
=> UPDATE t SET n = n + 1 WHERE random() < 0.055;
UPDATE 5493

4. Bloat Estimate

Table statistics (autovacuum_count shows how many times autovacuum was performed):

=> SELECT * FROM pg_stat_all_tables WHERE relname='t' \gx

Page 20: Page 20

-[ RECORD 1 ]-------+------------------------------
relid               | 16391
schemaname          | public
relname             | t
seq_scan            | 20
last_seq_scan       | 2026-06-11 17:38:35.218418+03
seq_tup_read        | 2000000
idx_scan            |
last_idx_scan       |
idx_tup_fetch       |
n_tup_ins           | 100000
n_tup_upd           | 110245
n_tup_del           | 0
n_tup_hot_upd       | 78914
n_tup_newpage_upd   | 31331
n_live_tup          | 100000
n_dead_tup          | 5493
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         |
last_autovacuum     | 2026-06-11 17:38:34.176293+03
last_analyze        |
last_autoanalyze    | 2026-06-11 17:38:35.280606+03
vacuum_count        | 0
autovacuum_count    | 4
analyze_count       | 0
autoanalyze_count   | 10

Let’s see the change in table size:

=> SELECT pg_size_pretty(pg_table_size('t'));
 pg_size_pretty
----------------
 4416 kB
(1 row)

In total, about 110,000 rows have changed, which is 110% of the table size. Consequently, we would expect autovacuum to trigger 10–11 times. However, autovacuum ran approximately half as frequently, since a significant part of the updates was optimized by pruning (in our case, HOT update: n_tup_hot_upd field). The table didn’t double in size; it grew only by about a third. In theory, 5–6% would be enough, but: The free space map is not updated during HOT update, and autovacuum triggers only at the second pass. HOT updates create additional redirect item identifiers on pages, with small tuple sizes like in our example, this effect becomes noticeable. The free space map stores approximate rather than precise information.