В статье описан алгоритм вакуумирования PostgreSQL и приводится сравнение числа сканирований индексов в 17 версии PostgreSQL и предыдущих версиях.

Есть пять фаз вакуумирования каждой таблицы, mwiew, toast и индексов на них: SCAN_HEAP, VACUUM_INDEX, VACUUM_HEAP, INDEX_CLEANUP, VACUUM TRUNCATE. Помимо них есть подготовительная фаза инициализации и завершающая фаза.

Фазы вакуумирования

Сначала строится список таблиц, которые будут очищаться. Эта подготовительная фаза цикла автовакуума называется инициализацией (initializing).

Перед обработкой каждой таблицы (в том числе TOAST-таблицы) открывается транзакция и создается моментальный снимок. Это позволяет быстрее снимать блокировки и сдвигаться горизонту базы данных.

На таблицу устанавливается блокировка ShareUpdateExclusive. Если блокировка не может быть установлена, то транзакция завершается и освобождаются установленные блокировки. Если блокировка получена, то она распространяется на сессию в которой выполняется вакуумирование (вызываетя функция LockRelationIdForSession(..)), чтобы вакуумировать TOAST-таблицу в отдельной транзакции и не ждать получения блокировки.

TOAST таблицы не будут анализироваться, так как доступ к строкам TOAST всегда идёт по TOAST-индексу и статистика на TOAST бесполезна. Сессия переключается на работу под владельцем таблицы, чтобы функции в индексе выполнялись из под владельца таблицы, так как параметры конфигурации могут устанавливаться на роль-владельца и влиять на результат выполнения функций.

Очистка индексов может использовать параллельные процессы, но один индекс вакуумирует только один рабочий процесс. Таблица (секции таблицы) всегда сканируется одним процессом, в котором выполняется вакуум. Если планируется (по каждой таблице отдельно) параллельная очистка индексов, то под хранение идентификаторов строк (TID, каждый по 6 байт) которые уже помечены в блоках битом-подсказкой LP_DEAD (это делает HOT) и будут помечены вакуумом на первой фазе его выполнения, выделяется разделяемая память (dynamic shared memory). Если не планируется, то локальная память процесса, который вакуумирует таблицу. Использование разделяемой памяти не влияет на производительность, так как блокировки не нужны: в нее пишет только основной вакуумирующий процесс.

Первая фаза: под накопление TID мертвых строк (LP_DEAD) выделяется память в размере autovacuum_work_mem (если он установлен в -1, то maintenance_work_mem).

Сканируются блоки таблицы в которых могут быть мертвые строки (блоки менялись с предыдущего вакуума, проверяется по карте видимости), если только не установлена опция VACUUM (DISABLE_PAGE_SKIPPING). У автовакуума нет такого параметра. Строки, вышедшие за горизонт базы помечаются как мертвые и их TID (вместе с уже помеченными в блоке ранее HOT) сохраняются в памяти.

Если памяти не хватит, первая фаза приостанавливается, идет переход на вторую фазу, потом возврат на первую фазу и на первой фазе продолжают сканироваться блоки дальше.

При этом вторая фаза будет выполняться полностью - сканировать все индексы ещё раз. Поэтому стоит устанавливать объем памяти для вакуума или настраивать частоту вакуумирования так, чтобы вакуум выполнялся в один проход. До 17 версии если maintenance_work_mem или autovacuum_work_mem были больше 1Гб, то под хранение идентификаторов строк (TID) использовался 1Гб, TID хранились в виде списка и поиск был неэффективным. Начиная с 17 версии используется префиксное дерево (radix tree) с компактным хранением (path compression). Объем памяти для хранения TID уменьшился в ~20 раз.

По умолчанию вакуумирование использует опцию INDEX_CLEANUP AUTO. Это означает, что если блоков, в которых есть хоть одна dead строка наберется меньше 2% (BYPASS_THRESHOLD_PAGES=0.02) от всех блоков в таблице и одновременно память под TID (6 байт) мертвых строк будет меньше, чем 32Мб (2^25/6=5592400 строк до 17 версии), то остальные фазы не выполняются и индексы не сканируются. Второе условие нужно для больших таблиц. Условие в 17 версии:

TidStoreMemoryUsage(vacrel->dead_items) < (32L*1024*1024))

Без этого достаточно бы было в таблице обновить или удалить строку и сканировались бы все индексы на таблице, что долго и трудоемко, а результат был бы никакой. Карты видимости (и заморозки) у индексов нет, индексные записи не замораживаются и не имеют xmin, xmax. Небольшое число мертвых строк получается, когда активно работает HOT cleanup (нужно стремиться, чтобы в основном он и освобождал место). Если INDEX_CLEANUP OFF или на уровне таблицы установлен параметр VACUUM_INDEX_CLEANUP OFF, то индексы не сканируются. Это имеет смысл, если нужно как можно быстрее просканировать блоки таблицы и пометить строки как мертвые (LP_DEAD), чтобы избежать переполнения счетчика транзакций.Отключается: VACUUM (INDEX_CLEANUP ON).

Расчёт памяти под идентификаторы строк (tuple ID) для вакуумирования

Число мертвых строк определяет верхнюю границу, то есть максимум сколько может быть удалено строк при вакуумировании. Это число заполняется всеми процессами обновлении строк таблицы, не требует анализа и актуально:

select schemaname, relname, n_dead_tup, n_live_tup from pg_stat_user_tables where relname = 'test';
 schemaname | relname | n_dead_tup | n_live_tup 
------------+---------+------------+------------
 public     | test    |    1000000 |   10000000

Память под хранение идентификаторов строк до 17 версии расчитывается по формуле: maintenance_work_mem = n_dead_tup * 6. Но не больше 1 гигабайта.

В примере: maintenance_work_mem = 1000000*6=6000000 байт.

Если установить: set maintenance_work_mem='1MB';

то будет 6000000/(1024*1024)=5.74 что даёт 6 проходов по индексам. Округление в большую сторону, так как проходов целое число. Пример:

vacuum verbose test; 
INFO:  vacuuming "postgres.public.test"
INFO:  finished vacuuming "postgres.public.test": index scans: 6
...

В 17 версии память кореллирует с n_dead_tup, но точной зависимости нет. Требуемый объем памяти стал меньше в ~20 раз по сравнению с предыдущими версиями PostgreSQL.

Начиная с 17 версии PostgreSQL, если на таблицу нет индексов опции игнорируются, второй фазы нет, первая фаза совмещается с третьей: вместо установки LP_DEAD место которое занимает строка (вместе с маской где ставится бит LP_DEAD) освобождается, а указатель в заголовке блока становится UNUSED, благодаря этому объем записи в WAL может уменьшиться ( https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c120550ed ).

На второй фазе в индексах очищаются ссылки на TID, сохраненные в памяти на первой фазе. Сканирование каждого индекса выполняются в физическом порядке и полностью все блоки: от первого блока первого файла слоя main до последнего блока последнего файла слоя main. В первом (по нулевому смещению) блоке индекса всегда находится блок метаданных. Начиная со второго, блоки индекса могут быть листовыми, внутенними (промежуточными), исключенными из логической структуры индекса («дерева»), а корневой блок может физически расположиться ближе к концу файла. Вакуум сканирует блоки в физическом порядке и сверяется со списком (или префиксным деревом в 17 версии) TID. Если вакуум находит в блоке TID из списка, то очищает индексную запись. Очистив запись в блоки, на которые вела ссылка (если это внутренний или корневой блок), вакуум продолжает обход в физическом порядке. Если в каком-то блоке все записи будут очищены, то такой блок не исключается в этой итерации вакуумирования из структуры индекса, так как одновременно с вакуумом по структуре индекса проходят серверные процессы и пока вакуум проходит по индексу могла бы возникнуть ситуация, когда вакуум исключил блок, а на записи в этот блок ведёт ссылка из другого блока до которого вакуум еще не дошёл. Пустой блок будет исключён из логической структуры индекса (дерева) в следующем цикле вакуума. При этом исключённый блок физически находится в файле основного слоя индекса (обычно в середине файла) и может быть включён в дерево (в любое место дерева) в будущем.

На третьей фазе в блоках таблиц очищаются TID с LP_DEAD, собранные в памяти на первой фазе: слоты в заголовке блоков становятся пригодными для использования (UNUSED). Если очистятся слоты в конце заголовка блока, то место может стать свободным и заголовок блока уменьшиться (truncate the line pointer array).

Для очистки описатель буфера блока блокируется в режиме Exclusive. Блоки в которых на первой фазе не было обнаружено LP_DEAD не читаются.

Замораживаются строки, которые можно заморозить, fsm и vm обновляются или создаются если их не было. В комментариях к функции lazy_scan_heap() файла vacuumlazy.c написано: prunes each page in the heap, and considers the need to freeze remaining tuples with storage (not including pages that can be skipped using the visibility map). Also performs related maintenance of the FSM and visibility map.

Если получить Exclusive блокировку на описатель буфера не удастся, то по умолчанию устанавливается блокировка Share, блок не очищается, а читается.

Если вакуум был запущен в агрессивном режиме ("aggressive mode"), то вакуум будет ждать получения Exclusive на описатель буфера.

Агрессивным режимом команды VACUUM называется использование параметра SKIP_LOCKED FALSE или FREEZE или DISABLE_PAGE_SKIPPING.

Цель агрессивного режима - обработать все блоки с целью заморозки. SKIP_LOCKED FALSE предназначен для случаев когда картам видимости и заморозки нельзя доверять, есть подозрение на их повреждение, он включает в себя FREEZE только по всем блокам.

Смысл пропуска блоков в том, чтобы ускорить вакуумирование таблицы, иначе автовакуум может не успеть обработать все запланированные таблицы. Также если описатель блока заблокирован, то есть вероятность того, что содержимое блока всё ещё меняется.

На четвертой фазе вызывается документированная ( https://postgrespro.ru/docs/postgresql/17/index-functions ) функция индексного метода доступа amvacuumcleanup(..), которая может выполнить какие-то полезные действия в зависимости от типа индекса. Например, освободить пустые страницы индекса. Выходное значение функции - статистика по индексу, которая используется для вывода VERBOSE или в диагностический лог. Эта функция также вызывается в конце фазы ANALYZE.

Пятая фаза вызывается только, если объем пустых блоков в конце последнего файла основного слоя таблицы больше 8Мб (макрос REL_TRUNCATE_MINIMUM). Выполняется функцией lazy_truncate_heap(..), которая пытается получить монопольную блокировку на таблицу, но если не сможет получить блокировку за VACUUM_TRUNCATE_LOCK_TIMEOUT=5 секунд, то усечение не выполняется. Пятая фаза может отключаться на уровне таблицы и отдельно для TOAST-таблицы параметрами VACUUM_TRUNCATE и TOAST.VACUUM_TRUNCATE, а также для команды VACUUM (TRUNCATE false). Установка параметра конфигурации old_snapshot_threshold (убран в 17 версии) отключает пятую фазу.

Завершающая фаза ваккумирования включает в себя финальные действия: параметры сессии, если менялись в теле функций индексов то они восстанавливаются. Сессия возвращается в исходного пользователя. Транзакция фиксируется. С основной таблицы или материализованного представления снимается блокировка.

Если обрабатывалась TOAST-таблица, то блокировка с основной таблицы не снимается и выполняется обработка TOAST с открытием и фиксацией транзакции, после чего снимается блокировка с основной таблицы.

В финальной фазе обновляется pg_database.datfrozenxid и по возможности усекается pg_xact. Это можно отключить параметром SKIP_DATABASE_STATS false.Анализ выполняется отдельно от вакуума, даже если совместить в одной команде VACUUM (ANALYZE) анализ выполнится после вакуума.

Вакуум в 17 версии PostgreSQL

В 17 версии TID строк LP_DEAD более эффективное как по использованию памяти, так и при поиске TID при сканировании индексов на второй фазе. Если памяти не хватит, то используется несколько проходов, а это резкая деградация производительности. При очистке блоков тестовой таблицы размером 3.4Гб и настройках экземпляра по умолчанию aintenance_work_mem=64MB:

CREATE TABLE test AS SELECT * FROM generate_series(1, 100000000) x(id);
CREATE INDEX ON test(id);
UPDATE test SET id = id - 1;

вакуум в 16 версии выполнит очистку в 9 проходов (по 64Мб памяти) и за 770 секунд:

LOG:  automatic vacuum of table "postgres.public.test": index scans: 9 
WAL usage: 2316372 records, 1189127 full page images, 2689898432 bytes 
system usage: CPU: user: 78.21 s, system: 30.06 s, elapsed: 773.23 s

в 17 версии за 1 проход, 37Мб и 620 секунд (на 20% быстрее):

WAL usage: 2316363 records, 1431435 full page images, 2586981769 bytes 
system usage: CPU: user: 78.84 s, system: 47.42 s, elapsed: 619.04 s

Время выполнения вакуума - это время удержания горизонта базы данных.

Число проходов указывается в столбце index_vacuum_count представления pg_stat_progress_vacuum.

Число сканирований индексов при вакуумировании

Основное преимущество вакуума 17 версии не в уменьшении времени работы, а в существенно меньших: потреблении памяти, вероятности повторного прохода по всем индексам таблицы. Установим для вакуума 2Мб памяти и проверим сколько вакуум сделает проходов по индексу в 17 версии и предыдущей.

1) Подсоединимся к 17 версии PosqgreSQL и выполним следующий тест с уменьшенным до 2Мб размером памяти на вакуумирование:

postgres=# \timing on \\
alter system set maintenance_work_mem='2MB';
select pg_reload_conf();
drop table if exists test;
create table test with (autovacuum_enabled=off) as select * from  generate_series(1, 10000000) x(id);
create index on test(id);
update test set id = id - 1;
checkpoint;
vacuum verbose test;
drop table test;
alter system reset maintenance_work_mem;
alter system reset max_wal_size;
select pg_reload_conf();
checkpoint;
INFO:  vacuuming "postgres.public.test"
INFO:  finished vacuuming "postgres.public.test": index scans: 2
...
VACUUM
Time: 29543.681 ms (00:29.544)

Выполнено 2 прохода по индексу.

2) Выполним тот же тест на 16 версии PostgreSQL:

INFO:  vacuuming "postgres.public.test"
INFO:  finished vacuuming "postgres.public.test": index scans: 29
...
system usage: CPU: user: 19.79 s, system: 20.94 s, elapsed: 81.60 s
VACUUM
Time: 81608.506 ms (01:21.609)

Число проходов по индексу index scans: 29 , а на 17 версии index scans: 2.

Время вакуумирования существенно увеличилось.

При выполнении вакуумирования можно вывести в лог кластера распределение памяти серверного процесса или autovacuum worker. Основная часть памяти будет выделяться в транзакционном контексте TopTransactionContext.

При повторных выполнениях вывода в лог пока работает вакуум будет меняться только занятая память транзакционного контекста. Выделенная память, пока транзакция работает, не меняется.

Вакуум и автовакуум обрабатывают каждую таблицу (в том числе TOAST) в отдельной транзакции. Перед обработкой каждой таблицы создается моментальный снимок, который удерживает горизонт базы данных до завершения обработки таблицы и её TOAST таблицы.

3) По умолчанию ваккумирование использует опцию INDEX_CLEANUP AUTO. Это означает, что если блоков, в которых есть хоть одна строка dead наберется меньше 2% от всех блоков в таблице и одновременно память под TID (6 байт) dead строк будет меньше, чем 32Мб (2^25/6=5592400 строк до 17 версии), то остальные фазы не выполняются и индексы не сканируются. Обновление 1 млн.строк превысит эти границы.

Желательно, чтобы число сканирований индексов было 1, то есть индексы сканировались за один проход. Для этого устанавливают значение параметра autovacuum_work_mem таким, чтобы структура с идентификаторами помеченных к удалению строк таблицы, которую заполняет автовакуум, помещалась в выделенную этим параметром память. Так как объем помечаемых к удалению старых версий строк обычно зависит от времени (скорость их появления обычно одинакова в течение рабочего времени), то также следят за тем, чтобы цикл автовакуума не работал существенно дольше чем обычно. Если цикл автовакуума увеличится, то за время цикла накопится больше изменившихся строк. Также таблица может быть заблокирована и автовакумм пропустит обработку в цикле. Поэтому, команды, уровень блокировки которых мешает работать автовакуму (ошибка: LOG: skipping vacuum of "test" --- lock not available) стараются давать нечасто. Пример такой команды: ANALYZE test.

4) Как рассчитывать число проходов по индексам в зависимости от числа строк, подлежащих чистке до 17 версии? Как расчитывать maintenance_work_mem или autovacuum_work_mem, чтобы был один проход по индексам, если известно число строк, которые будут вычищены до 17 версии?

Число строк, которые могут быть удалены заполняются при работе процессов в столбце pg_stat_all_tables.n_dead_tup. Это верхняя оценка для числа удаляемых строк, то есть максимум сколько может быть удалено.

Память под хранение идентификаторов строк рассчитывается по формуле: maintenance_work_mem = n_dead_tup * 6. Но не больше 1 гигабайта.

При превышении какого числа удаляемых строк на 16 версии будет 2 прохода по индексам независимо от выделенной памяти? 179 миллиона строк (1Гб/6=178956970 строк).

В 17 версии память кореллирует с n_dead_tup, но точной зависимости нет. Требуемый объем памяти меньше в ~20 раз.

Алгоритм вакуумирования в 17 версии стал намного более эффективным и была устранена основная проблема долгого вакуумирования таблиц - повторное сканирование индексов.

Семь фаз вакуумирования в PostgreSQL