Настройка autovacuum в PostgreSQL 18: порог, потолок, cost_limit и insert-only

Таблица на 4 млн строк с заводским autovacuum_vacuum_scale_factor = 0.2 копит 800 050 мёртвых версий, прежде чем autovacuum её очистит, а начавшись, идёт от 13 до 38 секунд: бюджет ввода-вывода общий для воркеров. Формулу порога разбирает основа про VACUUM и autovacuum. Здесь она проверяется на таблицах, у которых порог меняли тремя способами, и измеряется, что определяет скорость очистки.

DBA / 01koddo

Порог autovacuum на таблице в 4 000 000 строк: три настройки

Что сравнивает планировщик autovacuum

Решение принимает relation_needs_vacanalyze (autovacuum.c, строки 3122–3127, тег REL_18_6):

		vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
		if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh)
			vacthresh = (float4) vac_max_thresh;

		vacinsthresh = (float4) vac_ins_base_thresh +
			vac_ins_scale_factor * reltuples * pcnt_unfrozen;

Порог по мёртвым версиям — это autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples, обрезанный сверху autovacuum_vacuum_max_threshold. Потолок появился в PostgreSQL 18 (заметки о выпуске). Запуск происходит при n_dead_tup > vacthresh, строгое сравнение. На больших таблицах потолок срабатывает рано:

SELECT n AS reltuples,
       least(100000000, 50 + 0.2  * n)::bigint AS "scale 0.2",
       least(100000000, 50 + 0.01 * n)::bigint AS "scale 0.01"
FROM (VALUES (1000000), (10000000), (100000000), (1000000000)) AS v(n);

 reltuples  | scale 0.2 | scale 0.01 
------------+-----------+------------
    1000000 |    200050 |      10050
   10000000 |   2000050 |     100050
  100000000 |  20000050 |    1000050
 1000000000 | 100000000 |   10000050
(4 rows)

На миллиарде строк заводские 0,2 дали бы 200 000 050; потолок по умолчанию режет порог до 100 000 000. Сто миллионов версий до старта очистки — много, поэтому для таких таблиц значения задают явно.

Опыт 1: когда стартует autovacuum

docker run -d --name dba-autovacuum-tuning -e POSTGRES_PASSWORD=pg postgres:18 \
  -c autovacuum_naptime=5s -c log_autovacuum_min_duration=0 -c track_io_timing=on
docker exec -i dba-autovacuum-tuning psql -U postgres

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('autovacuum_naptime','autovacuum_max_workers','autovacuum_worker_slots',
  'autovacuum_vacuum_threshold','autovacuum_vacuum_scale_factor','autovacuum_vacuum_max_threshold',
  'autovacuum_vacuum_cost_delay','autovacuum_vacuum_cost_limit','vacuum_cost_limit') ORDER BY name;

              name               |  setting  | unit 
---------------------------------+-----------+------
 autovacuum_max_workers          | 3         | 
 autovacuum_naptime              | 5         | s
 autovacuum_vacuum_cost_delay    | 2         | ms
 autovacuum_vacuum_cost_limit    | -1        | 
 autovacuum_vacuum_max_threshold | 100000000 | 
 autovacuum_vacuum_scale_factor  | 0.2       | 
 autovacuum_vacuum_threshold     | 50        | 
 autovacuum_worker_slots         | 16        | 
 vacuum_cost_limit               | 200       | 
(9 rows)

autovacuum_naptime здесь 5 секунд вместо заводской минуты (документация), иначе пришлось бы ждать. Три одинаковые таблицы по 4 млн строк (по 230 МБ). Отличаются только параметры хранения; autovacuum_analyze_scale_factor = 10 отключает autoanalyze, чтобы ANALYZE не пересчитал по выборке счётчик мёртвых и не сдвинул момент запуска:

CREATE TABLE t_default (id int PRIMARY KEY, v int, pad text) WITH (autovacuum_enabled = off);
-- то же для t_scale и t_cap
INSERT INTO t_default SELECT i, 0, repeat('x', 20) FROM generate_series(1, 4000000) i;
VACUUM (ANALYZE) t_default;
ALTER TABLE t_default SET (autovacuum_enabled = on, autovacuum_analyze_scale_factor = 10);
ALTER TABLE t_scale   SET (autovacuum_enabled = on, autovacuum_analyze_scale_factor = 10, autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE t_cap     SET (autovacuum_enabled = on, autovacuum_analyze_scale_factor = 10, autovacuum_vacuum_max_threshold = 100000);

Запрос ниже считает порог каждой таблицы по reltuples и параметрам хранения; им же удобно проверять чужую базу:

SELECT c.relname, c.reltuples::bigint AS reltuples, s.n_dead_tup,
       least(coalesce(o.maxt, current_setting('autovacuum_vacuum_max_threshold')::float8),
             current_setting('autovacuum_vacuum_threshold')::float8
             + coalesce(o.sf, current_setting('autovacuum_vacuum_scale_factor')::float8) * c.reltuples)::bigint AS threshold,
       s.autovacuum_count
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
CROSS JOIN LATERAL (
  SELECT max(option_value::float8) FILTER (WHERE option_name = 'autovacuum_vacuum_scale_factor') AS sf,
         max(option_value::float8) FILTER (WHERE option_name = 'autovacuum_vacuum_max_threshold') AS maxt
  FROM pg_options_to_table(c.reloptions)) o
WHERE c.relname LIKE 't\_%' AND c.relkind = 'r' ORDER BY c.relname;

В t_default обновляется каждая пятая строка (800 000), в двух других каждая двадцатая (200 000). Меняется неиндексированный столбец v. Через 3 секунды после обновлений и ещё через 45:

  relname  | reltuples | n_dead_tup | threshold | autovacuum_count 
-----------+-----------+------------+-----------+------------------
 t_cap     |   4000000 |     200000 |    100000 |                0
 t_default |   4000000 |     800000 |    800050 |                0
 t_scale   |   4000000 |     200000 |     40050 |                0

  relname  | reltuples | n_dead_tup | threshold | autovacuum_count 
-----------+-----------+------------+-----------+------------------
 t_cap     |   4000000 |          0 |    100000 |                1
 t_default |   4000000 |     800000 |    800050 |                0
 t_scale   |   4000000 |          0 |     40050 |                1

t_scale и t_cap очищены, t_default стоит с 800 000 мёртвых версий при пороге 800 050 и не запускается. Теперь ровно 51 версия сверху:

DELETE FROM t_default WHERE id % 5 = 1 AND id <= 255;
SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 't_default';

DELETE 51
 n_dead_tup
------------
     800051

DELETE зафиксирован в 00:36:59 (время хоста), а в pg_stat_progress_vacuum таблица t_default появилась в 00:37:00. После очистки reltuples стал 3 999 949, порог пересчитался в 800 040. Порог зависит от оценки reltuples, а её обновляют сами VACUUM и ANALYZE.

Опыт 2: что определяет скорость очистки

Скорость задаёт бюджет vacuum_cost_limit (фазы, из которых складывается время очистки, разобраны в статье про VACUUM изнутри): воркер набирает «стоимость» страниц и спит autovacuum_vacuum_cost_delay, когда накопит лимит. Если воркеров несколько, лимит делится между ними (AutoVacuumUpdateCostLimit, строки 1744–1756):

		/* Only balance limit if no cost-related storage parameters specified */
		if (pg_atomic_unlocked_test_flag(&MyWorkerInfo->wi_dobalance))
			return;

		Assert(vacuum_cost_limit > 0);

		nworkers_for_balance = pg_atomic_read_u32(&AutoVacuumShmem->av_nworkersForBalance);

		/* There is at least 1 autovac worker (this worker) */
		if (nworkers_for_balance <= 0)
			elog(ERROR, "nworkers_for_balance must be > 0");

		vacuum_cost_limit = Max(vacuum_cost_limit / nworkers_for_balance, 1);

Воркер с лимитом из параметров хранения таблицы в деление не входит и берёт свой лимит целиком (строки 1733–1734, документация). Три запуска на тех же t_scale и t_cap, каждый раз 200 000 мёртвых версий:

UPDATE t_scale SET v = 2 WHERE id % 20 = 1;                                             -- a: один воркер
UPDATE t_scale SET v = 3 WHERE id % 20 = 2; UPDATE t_cap SET v = 3 WHERE id % 20 = 2;  -- b: два воркера сразу
ALTER TABLE t_cap SET (autovacuum_vacuum_cost_limit = 2000);
UPDATE t_cap SET v = 4 WHERE id % 20 = 3;                                               -- c: один воркер, лимит 2000

docker logs dba-autovacuum-tuning 2>&1 | grep -E 'automatic vacuum of|avg write|elapsed'

21:38:13.521 UTC [1647] LOG:  automatic vacuum of table "postgres.public.t_scale": index scans: 1
	avg read rate: 23.841 MB/s, avg write rate: 25.625 MB/s
	system usage: CPU: user: 0.64 s, system: 0.49 s, elapsed: 13.37 s
21:38:56.982 UTC [1849] LOG:  automatic vacuum of table "postgres.public.t_cap": index scans: 1
	avg read rate: 10.315 MB/s, avg write rate: 13.731 MB/s
	system usage: CPU: user: 0.93 s, system: 0.68 s, elapsed: 31.83 s
21:38:57.690 UTC [1820] LOG:  automatic vacuum of table "postgres.public.t_scale": index scans: 1
	avg read rate: 14.573 MB/s, avg write rate: 14.564 MB/s
	system usage: CPU: user: 1.11 s, system: 1.12 s, elapsed: 37.54 s
21:39:43.461 UTC [2079] LOG:  automatic vacuum of table "postgres.public.t_cap": index scans: 1
	avg read rate: 102.935 MB/s, avg write rate: 106.154 MB/s
	system usage: CPU: user: 0.50 s, system: 0.32 s, elapsed: 3.29 s

Второй воркер не ускорил работу: на каждую таблицу пришлась половина бюджета, и пара шла в 2,4–2,8 раза дольше одиночной. Половина бюджета объясняет удвоение; остаток, вероятно, — конкуренция двух воркеров за диск, отдельно она не измерялась. Десятикратный лимит сократил очистку такого же объёма в 4 раза и записал 106 МБ/с, это нагрузка на диск, которую нужно согласовать с остальными запросами.

Опыт 3: таблица только со вставками

Для INSERT порог другой: autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor × reltuples × (1 − relallfrozen / relpages), то есть порог растёт вместе с долей страниц, не отмеченных как замороженные (документация). Новая таблица в 1 млн строк, после загрузки целиком замороженная:

CREATE TABLE t_ins (id int PRIMARY KEY, v int, pad text);
INSERT INTO t_ins SELECT i, 0, repeat('x', 20) FROM generate_series(1, 1000000) i;
VACUUM (FREEZE, ANALYZE) t_ins;

SELECT c.reltuples::bigint, c.relpages, c.relallfrozen, s.n_ins_since_vacuum, s.autovacuum_count,
       (current_setting('autovacuum_vacuum_insert_threshold')::float8
        + current_setting('autovacuum_vacuum_insert_scale_factor')::float8
          * c.reltuples * (1 - c.relallfrozen::float8 / c.relpages))::bigint AS ins_threshold
FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid WHERE c.relname = 't_ins';

 reltuples | relpages | relallfrozen | n_ins_since_vacuum | autovacuum_count | ins_threshold 
-----------+----------+--------------+--------------------+------------------+---------------
   1000000 |     7353 |         7353 |                  0 |                0 |          1000

Все страницы заморожены, поэтому порог 1000, а не 1000 + 0.2 × 1 000 000 = 201 000. Вставка 1000 строк и 30 секунд ожидания:

INSERT INTO t_ins SELECT i, 0, 'x' FROM generate_series(1000001, 1001000) i;
-- через 30 секунд повтори SELECT выше, затем:
INSERT INTO t_ins VALUES (1001001, 0, 'x');

Результат первой проверки: n_ins_since_vacuum = 1000, autovacuum_count = 0, запуска нет (условие строгое, как и у мёртвых). После ещё одной строки:

 reltuples | relpages | relallfrozen | n_ins_since_vacuum | autovacuum_count | ins_threshold 
-----------+----------+--------------+--------------------+------------------+---------------
   1000993 |     7359 |         7352 |                  0 |                1 |          1190

2026-10-27 21:40:40.185 UTC [2144] LOG:  automatic vacuum of table "postgres.public.t_ins": index scans: 0
	pages: 0 removed, 7359 remain, 7 scanned (0.10% of total), 0 eagerly scanned

Вакуум пропустил замороженные страницы и просмотрел 7 из 7359; новый порог стал 1190. Такая очистка дешёвая: она нужна, чтобы страницы новых данных попали в карту видимости и были заморожены до того, как возраст таблицы заставит делать аварийную очистку против wraparound.

Что из этого следует

Главная мысль: на большой таблице порог задаёт доля scale_factor, а скорость очистки задаёт общий бюджет cost_limit. Менять нужно оба, и каждый отдельно.

• Для таблицы в миллионы и миллиарды строк задай порог явно: scale_factor на таблицу (на 4 млн строк с 0,01 порог 40 050 вместо 800 050) или потолок autovacuum_vacuum_max_threshold (100 000 вместо 800 050). Потолок удобен, когда таблицы растут: он не зависит от размера.
• Лишние воркеры (autovacuum_max_workers, в PostgreSQL 18 его меняют без перезапуска сервера в пределах autovacuum_worker_slots) очистку не ускорят: бюджет общий. Скорость даёт autovacuum_vacuum_cost_limit и autovacuum_vacuum_cost_delay.
• Лимит на отдельной таблице выводит её воркера из деления. Несколько таких таблиц могут писать одновременно с полным лимитом каждая, и общий предел перестаёт действовать.
• Опыт шёл с autovacuum_naptime = 5s. При заводской минуте базу осматривают раз в минуту (документация), и старт после пересечения порога откладывается на этот интервал.
• У замороженной append-only таблицы порог вставок 1000, а очистка просматривает 7 страниц из 7359. Пока страницы не заморожены, порог вставок считается от доли незамороженных, и очистка просмотрит их все.

Чек-лист

1. Посчитай порог каждой крупной таблицы запросом из опыта 1 (учитывая reloptions, reltuples и потолок) и сравни с n_dead_tup.
2. Для таблицы, где порог в процентах слишком велик, задай ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...) или autovacuum_vacuum_max_threshold и проверь, что autovacuum_count растёт.
3. Включи log_autovacuum_min_duration и сравни elapsed и avg write rate с бюджетом диска, прежде чем поднимать cost_limit.
4. Не клади лимит cost_limit в параметры многих таблиц сразу: каждая такая таблица работает вне общего бюджета.
5. Удали контейнер вместе с его томом: docker rm -f -v dba-autovacuum-tuning.