# Lesson 06: MVCC / Autovacuuming > Native Markdown source page: MVCC / Autovacuuming --- LLMS index: [llms.txt](/en/llms.txt) --- > [!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](https://edu.postgrespro.com/16/dba2-16-en/dba2_06_mvcc_autovacuum.html) > **Archive:** [DBA2-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA2-handouts-16-en.zip) > **PDF pages:** 20 ## Page 1: MVCC / Autovacuuming {#source-page-001} ### 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. ## Page 2: Topics {#source-page-002} - Autovacuuming - Autoanalysis - Autovacuuming Setup ## Page 3: Autovacuuming {#source-page-003} **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). UM ## Page 4: Autovacuum Launcher {#source-page-004} **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 {#source-page-005} **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 {#source-page-006} **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 {#source-page-007} **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. ## Page 8: Autovacuum Worker {#source-page-008} **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 {#source-page-009} **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. ME-CONFIG-RESOURCE-VACUUM-COST ## Page 10: Autovacuum Worker {#source-page-010} **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 {#source-page-011} Create a table with 1000 rows: ```sql => CREATE DATABASE mvcc_autovacuum; ``` ```sql CREATE DATABASE ``` ```sql => \c mvcc_autovacuum ``` ```text You are now connected to database "mvcc_autovacuum" as user "student". ``` ```sql => CREATE TABLE tvac( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, n numeric ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO tvac(n) SELECT 1 FROM generate_series(1,1000); ``` ```sql INSERT 0 1000 ``` Set autovacuum parameters. Short nap time to see results immediately: ```sql => ALTER SYSTEM SET autovacuum_naptime = 1; ``` ```sql ALTER SYSTEM ``` One percent of rows: ```sql => ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.01; ``` ```sql ALTER SYSTEM ``` Zero threshold: ```sql => ALTER SYSTEM SET autovacuum_vacuum_threshold = 0; ``` ```sql ALTER SYSTEM ``` Set autoanalysis parameters as well. Two percent of rows: ```sql => ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02; ``` ```sql ALTER SYSTEM ``` Zero threshold: ```sql => ALTER SYSTEM SET autovacuum_analyze_threshold = 0; ``` ```sql ALTER SYSTEM ``` Reload the configuration: ```sql => SELECT pg_reload_conf(); ``` ```text 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 {#source-page-012} ```sql => 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'; ``` ```sql CREATE VIEW ``` Currently the table does not require vacuuming (there are no dead tuples) and it has not been vacuumed yet: ```sql => SELECT * FROM vacuum_v; ``` ```text 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: ```sql => 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'; ``` ```sql CREATE VIEW ``` The view shows that the table does not require analysis; autoanalysis has already been performed: ```sql => SELECT * FROM analyze_v; ``` ```text 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%): ```sql => ALTER TABLE tvac SET (autovacuum_enabled = off); ``` ```sql ALTER TABLE ``` ```sql => UPDATE tvac SET n = n + 1 WHERE id <= 11; ``` ```sql UPDATE 11 ``` Check the views: ```sql => SELECT * FROM vacuum_v; ``` ## Page 13: Page 13 {#source-page-013} ```text relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+----------------- tvac | 11 | 10 | t | (1 row) ``` ```sql => SELECT * FROM analyze_v; ``` ```text 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... ```sql => ALTER TABLE tvac SET (autovacuum_enabled = on); ``` ```sql ALTER TABLE ``` ```sql => SELECT * FROM vacuum_v; ``` ```text relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+------------------------------ tvac | 0 | 10 | f | 2026-06-11 17:32:10.67801+03 (1 row) ``` ```sql => SELECT * FROM analyze_v; ``` ```text 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: ```sql => ALTER TABLE tvac SET (autovacuum_enabled = off); ``` ```sql ALTER TABLE ``` ```sql => UPDATE tvac SET n = n + 1 WHERE id <= 11; ``` ```sql UPDATE 11 ``` ```sql => SELECT * FROM vacuum_v; ``` ```text relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+------------------------------ tvac | 11 | 10 | t | 2026-06-11 17:32:10.67801+03 (1 row) ``` ```sql => SELECT * FROM analyze_v; ``` ```text 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: ```sql => ALTER TABLE tvac SET (autovacuum_enabled = on); ``` ```sql ALTER TABLE ``` Wait for a few seconds... ```sql => SELECT * FROM vacuum_v; ``` ## Page 14: Page 14 {#source-page-014} ```text relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+------------------------------- tvac | 0 | 10 | f | 2026-06-11 17:32:15.876718+03 (1 row) ``` ```sql => SELECT * FROM analyze_v; ``` ```text 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. ```sql => ALTER TABLE tvac SET (autovacuum_vacuum_scale_factor = 0.01); ``` ```sql ALTER TABLE ``` ```sql => SELECT unnest(reloptions) FROM pg_class WHERE relname = 'tvac'; ``` ```text unnest ------------------------------------- autovacuum_enabled=on autovacuum_vacuum_scale_factor=0.01 (2 rows) ``` ## Page 15: Tuning Approach {#source-page-015} **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 {#source-page-016} **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 {#source-page-017} 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? ## Page 18: 1. Autovacuuming Setup {#source-page-018} We configure autovacuum to trigger when 10% of rows are changed. ```sql => ALTER SYSTEM SET autovacuum_vacuum_threshold = 0; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1; ``` ```sql ALTER SYSTEM ``` We set the worker processes to be launched once per second: ```sql => ALTER SYSTEM SET autovacuum_naptime = '1s'; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` ### 2. Table ```sql => CREATE DATABASE mvcc_autovacuum; ``` ```sql CREATE DATABASE ``` ```sql => \c mvcc_autovacuum ``` ```text You are now connected to database "mvcc_autovacuum" as user "student". ``` ```sql => CREATE TABLE t(n integer); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t(n) SELECT 1 FROM generate_series(1,100_000); ``` ```sql INSERT 0 100000 ``` ### 3. Data Update Initial table size: ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text pg_size_pretty ---------------- 3568 kB (1 row) ``` We update the table with delays of several seconds between commands. ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5407 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5566 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5500 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5563 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5470 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ## Page 19: Page 19 {#source-page-019} ```sql UPDATE 5542 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5440 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5580 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5513 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5583 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5568 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5466 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5600 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5526 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5600 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5507 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5416 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5465 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5440 ``` ```sql => UPDATE t SET n = n + 1 WHERE random() < 0.055; ``` ```sql UPDATE 5493 ``` ### 4. Bloat Estimate Table statistics (autovacuum\_count shows how many times autovacuum was performed): ```sql => SELECT * FROM pg_stat_all_tables WHERE relname='t' \gx ``` ## Page 20: Page 20 {#source-page-020} ```text -[ 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: ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text 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.