6 Vacuum and Autovacuum

6.1 Vacuum

Page pruning happens very fast, but it frees only part of the space that can be potentially reclaimed. Operating within a single heap page, it does not touch upon indexes (or vice versa, it cleans up an index page without affecting the table).

Routine vacuuming,¹ which is the main vacuuming procedure, is performed by the VACUUM command.² It processes the whole table and eliminates both outdated heap tuples and all the corresponding index entries.

Vacuuming is performed in parallel with other processes in the database system. While being vacuumed, tables and indexes can be used in the usual manner, both for read and write operations (but concurrent execution of such commands as CREATE INDEX, ALTER TABLE, and some others is not allowed). (p. 204)

To avoid scanning extra pages, PostgreSQL uses a visibility map. Pages tracked in this map are skipped since they are sure to contain only the current tuples, so a page will only be vacuumed if it does not appear in this map. If all the tuples remaining in a page after vacuuming are beyond the database horizon, the visibility map is refreshed to include this page. (p. 27)

The free space map also gets updated to reflect the space that has been cleared.

Let’s create a table with an index on it:

=> CREATE TABLE vac(
  id integer,
  s char(100)
)
WITH (autovacuum_enabled = off);
=> CREATE INDEX vac_s ON vac(s);

The autovacuum_enabled storage parameter turns off autovacuum; we are doing it here solely for the purpose of experimentation to precisely control vacuuming start time.

Let’s insert a row and make a couple of updates:

=> INSERT INTO vac(id,s) VALUES (1,'A');
=> UPDATE vac SET s = 'B';
=> UPDATE vac SET s = 'C';

Now the table contains three tuples:

=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax  | hhu | hot | t_ctid
-------+--------+-------+-------+-----+-----+--------
 (0,1) | normal | 826 c | 827 c |     |     | (0,2)
 (0,2) | normal | 827 c | 828   |     |     | (0,3)
 (0,3) | normal | 828   | 0 a   |     |     | (0,3)
(3 rows)

Each tuple is referenced from the index:

=> SELECT * FROM index_page('vac_s',1);
 itemoffset | htid  | dead
------------+-------+------
           1 | (0,1) | f
           2 | (0,2) | f
           3 | (0,3) | f
(3 rows)

Vacuuming has removed all the dead tuples, leaving only the current one:

=> VACUUM vac;
=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax | hhu | hot | t_ctid
-------+--------+-------+------+-----+-----+--------
 (0,1) | unused |       |      |     |     |
 (0,2) | unused |       |      |     |     |
 (0,3) | normal | 828 c | 0 a  |     |     | (0,3)
(3 rows)

In the case of page pruning, the first two pointers would be considered dead, but here they have the unused status since no index entries are referring to them now:

=> SELECT * FROM index_page('vac_s',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,3) | f
(1 row)

Pointers with the unused status are treated as free and can be reused by new row versions.

Now the heap page appears in the visibility map; we can check it using the pg_visibility extension:

=> CREATE EXTENSION pg_visibility;
=> SELECT all_visible
FROM pg_visibility_map('vac',0);
 all_visible
-------------
 t
(1 row)

The page header has also received an attribute showing that all its tuples are visible in all snapshots:

=> SELECT flags & 4 > 0 AS all_visible
FROM page_header(get_raw_page('vac',0));
 all_visible
-------------
 t
(1 row)

6.2 Database Horizon Revisited

Vacuuming detects dead tuples based on the database horizon. This concept is so fundamental that it makes sense to get back to it once again.

Let’s restart our experiment from the very beginning:

=> TRUNCATE vac;
=> INSERT INTO vac(id,s) VALUES (1,'A');
=> UPDATE vac SET s = 'B';

But this time, before updating the row, we are going to open another transaction that will hold the database horizon (it can be almost any transaction, except for a virtual one executed at the Read Committed isolation level). For example, this transaction can modify some rows in another table. (p. 88)

=> BEGIN;
=> UPDATE accounts SET amount = 0;

=> UPDATE vac SET s = 'C';

Now our table contains three tuples, and the index contains three references. Let’s vacuum the table and see what changes:

=> VACUUM vac;
=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax  | hhu | hot | t_ctid
-------+--------+-------+-------+-----+-----+--------
 (0,1) | unused |       |       |     |     |
 (0,2) | normal | 833 c | 835 c |     |     | (0,3)
 (0,3) | normal | 835 c | 0 a   |     |     | (0,3)
(3 rows)
=> SELECT * FROM index_page('vac_s',1);
 itemoffset | htid  | dead
------------+-------+------
           1 | (0,2) | f
           2 | (0,3) | f
(2 rows)

While the previous run left only one tuple in the page, now we have two of them: VACUUM has decided that version (0,2) cannot be removed yet. The reason is the database horizon, which is defined by an unfinished transaction in this case:

=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          834
(1 row)

We can use the VERBOSE clause when calling VACUUM to observe what is going on:

=> VACUUM VERBOSE vac;
INFO:  vacuuming "public.vac"
INFO:  table "vac": found 0 removable, 2 nonremovable row versions
in 1 out of 1 pages
DETAIL:  1 dead row versions cannot be removed yet, oldest xmin: 834
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

The output shows the following information:

• VACUUM has detected no tuples that can be removed (0 removable).

• Two tuples must not be removed (2 nonremovable).

• One of the nonremovable tuples is dead (1 dead), the other is in use.

• The current horizon respected by VACUUM (oldest xmin) is the horizon of the active transaction.

Once the active transaction completes, the database horizon moves forward, and vacuuming can continue:

=> COMMIT;

=> VACUUM VERBOSE vac;
INFO:  vacuuming "public.vac"
INFO:  scanned index "vac_s" to remove 1 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  table "vac": removed 1 dead item identifiers in 1 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "vac_s" now contains 1 row versions in 2 pages
DETAIL:  1 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently
reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  table "vac": found 1 removable, 1 nonremovable row versions
in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 836
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

VACUUM has detected and removed a dead tuple beyond the new database horizon.

Now the page does not contain any outdated row versions; the only version remaining is the current one:

=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax | hhu | hot | t_ctid
-------+--------+-------+------+-----+-----+--------
 (0,1) | unused |       |      |     |     |
 (0,2) | unused |       |      |     |     |
 (0,3) | normal | 835 c | 0 a  |     |     | (0,3)
(3 rows)

The index also contains only one entry:

=> SELECT * FROM index_page('vac_s',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,3) | f
(1 row)

6.3 Vacuum Phases

The mechanism of vacuuming seems quite simple, but this impression is misleading. After all, both tables and indexes have to be processed concurrently, without blocking other processes. To enable such operation, vacuuming of each table is carried out in several phases.³

It all starts with scanning a table in search of dead tuples; if found, they are first removed from indexes and then from the table itself. If too many dead tuples have to be vacuumed in one go, this process is repeated. Eventually, heap truncation may be performed.

Heap Scan

In the first phase, a heap scan is performed.⁴ The scanning process takes the visibility map into account: all pages tracked in this map are skipped because they are sure to contain no outdated tuples. If a tuple is beyond the horizon and is not required anymore, its ID is added to a special tid array. Such tuples cannot be removed yet because they may still be referenced from indexes.

The tid array resides in the local memory of the VACUUM process; the size of the allocated memory chunk is defined by the maintenance_work_mem parameter. The whole chunk is allocated at once rather than on demand. However, the allocated memory never exceeds the volume required in the worst-case scenario, so if the table is small, vacuuming may use less memory than specified in this parameter. (default: 64MB)

Index Vacuuming

The first phase can have two outcomes: either the table is scanned in full, or the memory allocated for the tid array is filled up before this operation completes. In any case, index vacuuming begins.⁵ In this phase, each of the indexes created on the table is fully scanned to find all the entries that refer to the tuples registered in the tid array. These entries are removed from index pages.

If there are several indexes bigger than the min_parallel_index_scan_size value, they can be vacuumed by background workers running in parallel. Unless the level of parallelism is explicitly defined by the parallel N clause, VACUUM launches one worker per suitable index (within the general limits imposed on the number of background workers).⁷ One index cannot be processed by several workers. (default: 512kB) (v. 13)

During the index vacuuming phase, PostgreSQL updates the free space map and calculates statistics on vacuuming. However, this phase is skipped if rows are only inserted (and are neither deleted nor updated) because the table contains no dead tuples in this case. Then an index scan will be forced only once at the very end, as part of a separate phase of index cleanup.⁸

The index vacuuming phase leaves no references to outdated heap tuples in indexes, but the tuples themselves are still present in the table. It is perfectly normal: index scans cannot find any dead tuples, while sequential scans of the table rely on visibility rules to filter them out.

Heap Vacuuming

Then the heap vacuuming phase begins.⁹ The table is scanned again to remove the tuples registered in the tid array and free the corresponding pointers. Now that all the related index references have been removed, it can be done safely.

The space recovered by VACUUM is reflected in the free space map, while the pages that now contain only the current tuples visible in all snapshots are tagged in the visibility map.

If the table was not read in full during the heap scan phase, the tid array is cleared, and the heap scan is resumed from where it left off last time.

Heap Truncation

Vacuumed heap pages contain some free space; occasionally, you may be lucky to clear the whole page. If you get several empty pages at the end of the file, vacuuming can “bite off” this tail and return the reclaimed space to the operating system. It happens during heap truncation,¹⁰ which is the final vacuum phase.

Heap truncation requires a short exclusive lock on the table. To avoid holding other processes for too long, attempts to acquire a lock do not exceed five seconds. (p. 204)

Since the table has to be locked, truncation is only performed if the empty tail takes at least $\frac{1}{16}$ of the table or has reached the length of 1,000 pages. These thresholds are hardcoded and cannot be configured.

If, despite all these precautions, table locks still cause any issues, you can disable truncation altogether using the vacuum_truncate and toast.vacuum_truncate storage parameters. (v. 12)

6.4 Analysis

When talking about vacuuming, we have to mention yet another task that is closely related to it, even though there is no formal connection between them. It is analysis,¹¹ or gathering statistical information for the query planner. The collected statistics include the number of rows (pg_class.reltuples) and pages (pg_class.relpages) in relations, data distribution within columns, and some other information. (p. 271)

You can run the analysis manually using the ANALYZE command,¹² or combine it with vacuuming by calling VACUUM ANALYZE. However, these two tasks are still performed sequentially, so there is no difference in terms of performance.

Automatic vacuum and analysis are set up in a similar way, so it makes sense to discuss them together.

6.5 Automatic Vacuum and Analysis

Unless the database horizon is held up for a long time, routine vacuuming should cope with its work. But how often do we need to call the VACUUM command?

If a frequently updated table is vacuumed too seldom, it will grow bigger than desired. Besides, it may accumulate too many changes, and then the next VACUUM run will have to make several passes over the indexes.

If the table is vacuumed too often, the server will be busy with maintenance instead of useful work.

Furthermore, typical workloads may change over time, so having a fixed vacuuming schedule will not help anyway: the more often the table is updated, the more often it has to be vacuumed.

This problem is solved by autovacuum,¹³ which launches vacuum and analysis processes based on the intensity of table updates.

About the Autovacuum Mechanism

When autovacuum is enabled (autovacuum configuration parameter is on), the autovacuum launcher process is always running in the system. This process defines the autovacuum schedule and maintains the list of “active” databases based on usage statistics. Such statistics are collected if the track_counts parameter is enabled. Do not switch off these parameters, otherwise autovacuum will not work. (default: on) (default: on)

Once in autovacuum_naptime, the autovacuum launcher starts an autovacuum worker¹⁴ for each active database in the list (these workers are spawned by postmaster, as usual). Consequently, if there are N active databases in the cluster, N workers are spawned within the autovacuum_naptime interval. But the total number of autovacuum workers running in parallel cannot exceed the threshold defined by the autovacuum_max_workers parameter. (default: 1min) (default: 3)

Once started, the background worker connects to the specified database and builds two lists:

• the list of all tables, materialized views, and TOAST tables to be vacuumed

• the list of all tables and materialized views to be analyzed (TOAST tables are not analyzed because they are always accessed via an index)

Then the selected objects are vacuumed or analyzed one by one (or undergo both operations), and once the job is complete, the worker is terminated.

Automatic vacuuming works similar to the manual one initiated by the VACUUM command, but there are some nuances:

• When manual vacuuming is performed, tuple IDs are accummulated in a memory chunk of the maintenance_work_mem size. Using the same limit for autovacuum is undesirable, as it can lead to excessive memory consumption: there may be several autovacuum workers running in parallel, and each of them will get maintenance_work_mem of memory at once. Instead, PostgreSQL provides a separate memory limit for autovacuum processes, which is defined by the autovacuum_work_mem parameter.

By default, the autovacuum_work_mem parameter falls back on the regular limit of maintenance_work_mem, so if the autovacuum_max_workers value is high, you may have to adjust the autovacuum_work_mem value accordingly. (default: -1)

• Concurrent processing of several indexes created on one table can be performed only by manual vacuuming; using autovacuum for this purpose would result in a large number of parallel processes, so it is not allowed.

If a worker fails to complete all the scheduled tasks within the autovacuum_naptime interval, the autovacuum launcher spawns another worker to be run in parallel in that database. The second worker will build its own lists of objects to be vacuumed and analyzed and will start processing them. There is no parallelism at the table level; only different tables can be processed concurrently.

Which Tables Need to be Vacuumed?

You can disable autovacuum at the table level—although it is hard to imagine why it could be necessary. There are two storage parameters provided for this purpose, one for regular tables and the other for TOAST tables:

• autovacuum_enabled

• toast.autovacuum_enabled

In usual circumstances, autovacuum is triggered either by accumulation of dead tuples or by insertion of new rows. (p. 129)

Dead tuple accumulation. Dead tuples are constantly being counted by the statistics collector; their current number is shown in the system catalog table called pg_stat_all_tables.

It is assumed that dead tuples have to be vacuumed if they exceed the threshold defined by the following two parameters:

• autovacuum_vacuum_threshold, which specifies the number of dead tuples (an absolute value) (default: 50)

• autovacuum_vacuum_scale_factor, which sets the fraction of dead tuples in a table (default: 0.2)

Vacuuming is required if the following condition is satisfied:

pg_stat_all_tables.n_dead_tup >

autovacuum_vacuum_threshold +

autovacuum_vacuum_scale_factor × pg_class.reltuples

The main parameter here is of course autovacuum_vacuum_scale_factor: its value is important for large tables (and it is large tables that are likely to cause the majority of issues). The default value of 20 % seems too big and may have to be significantly reduced.

For different tables, optimal parameter values may vary: they largely depend on the table size and workload type. It makes sense to set more or less adequate initial values and then override them for particular tables using storage parameters:

• autovacuum_vacuum_threshold and toast.autovacuum_vacuum_threshold

• autovacuum_vacuum_scale_factor and toast.autovacuum_vacuum_scale_factor

Row insertions. If rows are only inserted and are neither deleted nor updated, the table contains no dead tuples. But such tables should also be vacuumed to freeze heap tuples in advance and update the visibility map (thus enabling index-only scans). (v. 13) (p. 123) (p. 337)

A table will be vacuumed if the number of rows inserted since the previous vacuuming exceeds the threshold defined by another similar pair of parameters:

• autovacuum_vacuum_insert_threshold (default: 1000)

• autovacuum_vacuum_insert_scale_factor (default: 0.2)

The formula is as follows:

pg_stat_all_tables.n_ins_since_vacuum >

autovacuum_vacuum_insert_threshold +

autovacuum_vacuum_insert_scale_factor × pg_class.reltuples

Like in the previous example, you can override these values at the table level using storage parameters:

• autovacuum_vacuum_insert_threshold and its TOAST counterpart

• autovacuum_vacuum_insert_scale_factor and its TOAST counterpart

Which Tables Need to Be Analyzed?

Automatic analysis needs to process only modified rows, so the calculations are a bit simpler than those for autovacuum.

It is assumed that a table has to be analyzed if the number of rows modified since the previous analysis exceeds the threshold defined by the following two configuration parameters:

• autovacuum_analyze_threshold (default: 50)

• autovacuum_analyze_scale_factor (default: 0.1)

Autoanalysis is triggered if the following condition is met:

pg_stat_all_tables.n_mod_since_analyze >

autovacuum_analyze_threshold +

autovacuum_analyze_scale_factor × pg_class.reltuples

To override autoanalysis settings for particular tables, you can use the same-name storage parameters:

• autovacuum_analyze_threshold

• autovacuum_analyze_scale_factor

Since TOAST tables are not analyzed, they have no corresponding parameters.

Autovacuum in Action

To formalize everything said in this section, let’s create two views that show which tables currently need to be vacuumed and analyzed.¹⁵ The function used in these views returns the current value of the passed parameter, taking into account that this value can be redefined at the table level:

=> CREATE FUNCTION p(param text, c pg_class) RETURNS float
AS $$
  SELECT coalesce(
    -- use storage parameter if set
    (SELECT option_value
     FROM   pg_options_to_table(c.reloptions)
     WHERE  option_name = CASE
              -- for TOAST tables the parameter name is different
              WHEN c.relkind = 't' THEN 'toast.' ELSE ''
            END || param
    ),
    -- else take the configuration parameter value
    current_setting(param)
  )::float;
$$ LANGUAGE sql;

This is how a vacuum-related view can look like:

=> CREATE VIEW need_vacuum AS
WITH c AS (
  SELECT c.oid,
    greatest(c.reltuples, 0) reltuples,
    p('autovacuum_vacuum_threshold', c) threshold,
    p('autovacuum_vacuum_scale_factor', c) scale_factor,
    p('autovacuum_vacuum_insert_threshold', c) ins_threshold,
    p('autovacuum_vacuum_insert_scale_factor', c) ins_scale_factor
  FROM pg_class c
  WHERE c.relkind IN ('r','m','t')
)
SELECT st.schemaname || '.' || st.relname AS tablename,
  st.n_dead_tup AS dead_tup,
  c.threshold + c.scale_factor * c.reltuples AS max_dead_tup,
  st.n_ins_since_vacuum AS ins_tup,
  c.ins_threshold + c.ins_scale_factor * c.reltuples AS max_ins_tup,
  st.last_autovacuum
FROM pg_stat_all_tables st
  JOIN c ON c.oid = st.relid;

The max_dead_tup column shows the number of dead tuples that will trigger autovacuum, whereas the max_ins_tup column shows the threshold value related to insertion.

Here is a similar view for analysis:

=> CREATE VIEW need_analyze AS
WITH c AS (
  SELECT c.oid,
    greatest(c.reltuples, 0) reltuples,
    p('autovacuum_analyze_threshold', c) threshold,
    p('autovacuum_analyze_scale_factor', c) scale_factor
  FROM pg_class c
  WHERE c.relkind IN ('r','m')
)
SELECT st.schemaname || '.' || st.relname AS tablename,
  st.n_mod_since_analyze AS mod_tup,
  c.threshold + c.scale_factor * c.reltuples AS max_mod_tup,
  st.last_autoanalyze
FROM pg_stat_all_tables st
  JOIN c ON c.oid = st.relid;

The max_mod_tup column shows the threshold value for autoanalysis.

To speed up the experiment, we will be starting autovacuum every second:

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

Let’s truncate the vac table and then insert 1,000 rows. Note that autovacuum is turned off at the table level.

=> TRUNCATE TABLE vac;
=> INSERT INTO vac(id,s)
  SELECT id, 'A' FROM generate_series(1,1000) id;

Here is what our vacuum-related view will show:

=> SELECT * FROM need_vacuum WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]---+-----------
tablename       | public.vac
dead_tup        | 0
max_dead_tup    | 50
ins_tup         | 1000
max_ins_tup     | 1000
last_autovacuum |

The actual threshold value is max_dead_tup = 50, although the formula listed above suggests that it should be 50 + 0.2 × 1000 = 250. The thing is that statistics on this table are not available yet since the INSERT command does not update it:

=> SELECT reltuples FROM pg_class WHERE relname = 'vac';
 reltuples
-----------
        -1
(1 row)

The pg_class.reltuples value is set to −1; this special constant is used instead of zero to differentiate between a table without any statistics and a really empty table that has already been analyzed. For the purpose of calculation, the negative value is taken as zero, which gives us 50 + 0.2 × 0 = 50. (v. 14)

The value of max_ins_tup = 1000 differs from the projected value of 1,200 for the same reason.

Let’s have a look at the analysis view:

=> SELECT * FROM need_analyze WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]----+-----------
tablename        | public.vac
mod_tup          | 1006
max_mod_tup      | 50
last_autoanalyze |

We have updated (inserted in this case) 1,000 rows; as a result, the threshold is exceeded: since the table size is unknown, it is currently set to 50. It means that autoanalysis will be triggered immediately when we turn it on:

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

Once the table analysis completes, the threshold is reset to an adequate value of 150 rows.

=> SELECT reltuples FROM pg_class WHERE relname = 'vac';
 reltuples
-----------
      1000
(1 row)

=> SELECT * FROM need_analyze WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]----+------------------------------
tablename        | public.vac
mod_tup          | 0
max_mod_tup      | 150
last_autoanalyze | 2023-03-06 14:00:45.533464+03

Let’s get back to autovacuum:

=> SELECT * FROM need_vacuum WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]---+-----------
tablename       | public.vac
dead_tup        | 0
max_dead_tup    | 250
ins_tup         | 1000
max_ins_tup     | 1200
last_autovacuum |

The max_dead_tup and max_ins_tup values have also been updated based on the actual table size discovered by the analysis.

Vacuuming will be started if at least one of the following conditions is met:

• More than 250 dead tuples are accumulated.

• More than 200 rows are inserted into the table. (v. 13)

Let’s turn off autovacuum again and update 251 rows so that the threshold value is exceeded by one:

=> ALTER TABLE vac SET (autovacuum_enabled = off);
=> UPDATE vac SET s = 'B' WHERE id <= 251;
=> SELECT * FROM need_vacuum WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]---+-----------
tablename       | public.vac
dead_tup        | 251
max_dead_tup    | 250
ins_tup         | 1000
max_ins_tup     | 1200
last_autovacuum |

Now the trigger condition is satisfied. Let’s enable autovacuum; after a while, we will see that the table has been processed, and its usage statistics has been reset:

=> ALTER TABLE vac SET (autovacuum_enabled = on);
=> SELECT * FROM need_vacuum WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]---+------------------------------
tablename       | public.vac
dead_tup        | 0
max_dead_tup    | 250
ins_tup         | 0
max_ins_tup     | 1200
last_autovacuum | 2023-03-06 14:00:51.736815+03

6.6 Managing the Load

Operating at the page level, vacuuming does not block other processes; but nevertheless, it increases the system load and can have a noticeable impact on performance.

Vacuum Throttling

To control vacuuming intensity, PostgreSQL makes regular pauses in table processing. After completing about vacuum_cost_limit units of work, the process falls asleep and remains idle for the vacuum_cost_delay time interval. (default: 200) (default: 0)

The default zero value of vacuum_cost_delay means that routine vacuuming actually never sleeps, so the exact vacuum_cost_limit value makes no difference. It is assumed that if administrators have to resort to manual vacuuming, they are likely to expect its completion as soon as possible.

If the sleep time is set, then the process will pause each time it has spent vacuum_cost_limit units of work on page processing in the buffer cache. The cost of each page read operation is estimated at vacuum_cost_page_hit units if the page is found in the buffer cache, (p. 147) (default: 1)

or vacuum_cost_page_miss units otherwise.¹⁶ If a clean page is dirtied by vacuum, it adds another vacuum_cost_page_dirty units.¹⁷ (default: 2) (default: 20)

If you keep the default value of the vacuum_cost_limit parameter, VACUUM can process up to 200 pages per cycle in the best-case scenario (if all the pages are cached, and no pages are dirtied by VACUUM) and only nine pages in the worst case (if all the pages are read from disk and become dirty).

Autovacuum Throttling

Throttling for autovacuum¹⁸ is quite similar to VACUUM throttling. However, autovacuum can be run with a different intensity as it has its own set of parameters:

• autovacuum_vacuum_cost_limit (default: -1)

• autovacuum_vacuum_cost_delay (default: 2ms)

If any of these parameters is set to −1, it falls back on the corresponding parameter for regular VACUUM. It means that the autovacuum_vacuum_cost_limit parameter relies on the vacuum_cost_limit value by default.

Autovacuum work units are limited to autovacuum_vacuum_cost_limit per cycle, and since they are shared between all the workers, the overall impact on the system remains roughly the same, regardless of their number. So if you need to speed up autovacuum, both the autovacuum_max_workers and autovacuum_vacuum_cost_limit values should be increased proportionally.

If required, you can override these settings for particular tables by setting the following storage parameters:

• autovacuum_vacuum_cost_delay and toast.autovacuum_vacuum_cost_delay

• autovacuum_vacuum_cost_limit and toast.autovacuum_vacuum_cost_limit

6.7 Monitoring

If vacuuming is monitored, you can detect situations when dead tuples cannot be removed in one go, as references to them do not fit the maintenance_work_mem memory chunk. In this case, all the indexes will have to be fully scanned several times. It can take a substantial amount of time for large tables, thus creating a significant load on the system. Even though queries will not be blocked, extra I/O operations can seriously limit system throughput.

Such issues can be corrected either by vacuuming the table more often (so that each run cleans up fewer tuples) or by allocating more memory.

Monitoring Vacuum

When run with the VERBOSE clause, the VACUUM command performs the cleanup and displays the status report, whereas the pg_stat_progress_vacuum view shows the current state of the started process. (v. 9.6)

There is also a similar view for analysis (pg_stat_progress_analyze), even though it is usually performed very fast and is unlikely to cause any issues. (v. 13)

Let’s insert more rows into the table and update them all so that VACUUM has to run for a noticeable period of time:

=> TRUNCATE vac;

=> INSERT INTO vac(id,s)
  SELECT id, 'A' FROM generate_series(1,500000) id;

=> UPDATE vac SET s  = 'B';

For the purpose of this demonstration, we will limit the amount of memory allocated for the tid array by 1 MB:

=> ALTER SYSTEM SET maintenance_work_mem = '1MB';

=> SELECT pg_reload_conf();

Launch the VACUUM command and query the pg_stat_progress_vacuum view several times while it is running:

=> VACUUM VERBOSE vac;
    => SELECT * FROM pg_stat_progress_vacuum \gx
    -[ RECORD 1 ]------+------------------
    pid                | 14531
    datid              | 16391
    datname            | internals
    relid              | 16479
    phase              | vacuuming indexes
    heap_blks_total    | 17242
    heap_blks_scanned  | 3009
    heap_blks_vacuumed | 0
    index_vacuum_count | 0
    max_dead_tuples    | 174761
    num_dead_tuples    | 174522
    => SELECT * FROM pg_stat_progress_vacuum \gx
    -[ RECORD 1 ]------+------------------
    pid                | 14531
    datid              | 16391
    datname            | internals
    relid              | 16479
    phase              | vacuuming indexes
    heap_blks_total    | 17242
    heap_blks_scanned  | 17242
    heap_blks_vacuumed | 6017
    index_vacuum_count | 2
    max_dead_tuples    | 174761
    num_dead_tuples    | 150956

In particular, this view shows:

• phase—the name of the current vacuum phase (I have described the main ones, but there are actually more of them¹⁹)

• heap_blks_total—the total number of pages in a table

• heap_blks_scanned—the number of scanned pages

• heap_blks_vacuumed—the number of vacuumed pages

• index_vacuum_count—the number of index scans

The overall vacuuming progress is defined by the ratio of heap_blks_vacuumed to heap_blks_total, but you have to keep in mind that it changes in spurts because of index scans. In fact, it is more important to pay attention to the number of vacuum cycles: if this value is greater than one, it means that the allocated memory was not enough to complete vacuuming in one go.

You can see the whole picture in the output of the VACUUM VERBOSE command, which has already finished by this time:

INFO:  vacuuming "public.vac"
INFO:  scanned index "vac_s" to remove 174522 row versions

index vacuum

DETAIL:  CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.05 s
INFO:  table "vac": removed 174522 dead item identifiers in

table

3009 pages

vacuum

DETAIL:  CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.07 s
INFO:  scanned index "vac_s" to remove 174522 row versions

index vacuum

DETAIL:  CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.05 s
INFO:  table "vac": removed 174522 dead item identifiers in

table

3009 pages

vacuum

DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
INFO:  scanned index "vac_s" to remove 150956 row versions

index vacuum

DETAIL:  CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.04 s
INFO:  table "vac": removed 150956 dead item identifiers in

table

2603 pages

vacuum

DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "vac_s" now contains 500000 row versions in
932 pages
DETAIL:  500000 index row versions were removed.
433 index pages were newly deleted.
433 index pages are currently deleted, of which 0 are
currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  table "vac": found 500000 removable, 500000
nonremovable row versions in 17242 out of 17242 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest
xmin: 851
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.20 s, system: 0.03 s, elapsed: 0.53 s.
VACUUM

All in all, there have been three index scans; each scan has removed 174,522 pointers to dead tuples at the most. This value is defined by the number of tid pointers (each of them takes 6 bytes) that can fit into an array of the maintenance_work_mem size. The maximum size possible is shown by pg_stat_progress_vacuum.max_dead_tuples, but the actually used space is always a bit smaller. It guarantees that when the next page is read, all its pointers to dead tuples, no matter how many of them are located in this page, will fit into the remaining memory.

Monitoring Autovacuum

The main approach to monitoring autovacuum is to print its status information (which is similar to the output of the VACUUM VERBOSE command) into the server log for further analysis. If the log_autovacuum_min_duration parameter is set to zero, all autovacuum runs are logged: (default: -1)

=> ALTER SYSTEM SET log_autovacuum_min_duration = 0;

=> SELECT pg_reload_conf();

=> UPDATE vac SET s = 'C';
UPDATE 500000

postgres$ tail -n 13 /home/postgres/logfile
2023-03-06 14:01:13.727 MSK [17351] LOG:  automatic vacuum of table
"internals.public.vac": index scans: 3
pages: 0 removed, 17242 remain, 0 skipped due to pins, 0
skipped frozen
tuples: 500000 removed, 500000 remain, 0 are dead but not
yet removable, oldest xmin: 853
index scan needed: 8622 pages from table (50.01% of total)
had 500000 dead item identifiers removed
index "vac_s": pages: 1428 in total, 496 newly deleted, 929
currently deleted, 433 reusable
avg read rate: 12.404 MB/s, avg write rate: 14.810 MB/s
buffer usage: 46038 hits, 5670 misses, 6770 dirtied
WAL usage: 40390 records, 15062 full page images, 89188595
bytes
system usage: CPU: user: 0.31 s, system: 0.33 s, elapsed:
3.57 s
2023-03-06 14:01:14.117 MSK [17351] LOG:  automatic analyze of table
"internals.public.vac"
avg read rate: 41.081 MB/s, avg write rate: 0.020 MB/s
buffer usage: 15355 hits, 2035 misses, 1 dirtied
system usage: CPU: user: 0.14 s, system: 0.00 s, elapsed:
0.38 s

To track the list of tables to be vacuumed and analyzed, you can use the need_vacuum and need_analyze views, which we have already reviewed. If this list grows, it means that autovacuum does not cope with the load and has to be sped up by either reducing the gap (autovacuum_vacuum_cost_delay) or increasing the amount of work done between the gaps (autovacuum_vacuum_cost_limit). It is not unlikely that the degree of parallelism will also have to be increased (autovacuum_max_workers).