Что мешает VACUUM в PostgreSQL: горизонт xmin и четыре его держателя

VACUUM отработал, а в VACUUM VERBOSE стоит «are dead but not yet removable»: мёртвые версии строк остались на месте. VACUUM и autovacuum, фазы которого разобраны отдельно, удаляют версию, только если её уже не увидит ни один снимок, а граница считается одним числом, горизонтом xmin: самым старым xmin среди всех, кто может ещё читать или писать. Его держат открытая транзакция, слот репликации, подготовленная транзакция и реплика с hot_standby_feedback. Почему долгий снимок раздувает таблицу, уже разобрано; здесь видно, откуда берётся число и как одним запросом найти того, кто его держит.

Как сервер считает горизонт

Число считает ComputeXidHorizons в procarray.c (тег REL_18_6). Для каждого процесса из массива берётся более старое из xmin его снимка и его собственного xid:

		xid = UINT32_ACCESS_ONCE(other_xids[index]);
		xmin = UINT32_ACCESS_ONCE(proc->xmin);

		/*
		 * Consider both the transaction's Xmin, and its Xid.
		 *
		 * We must check both because a transaction might have an Xmin but not
		 * (yet) an Xid; conversely, if it has an Xid, that could determine
		 * some not-yet-set Xmin.
		 */
		xmin = TransactionIdOlder(xmin, xid);

		/* if neither is set, this proc doesn't influence the horizon */
		if (!TransactionIdIsValid(xmin))
			continue;

Транзакция, которая что-то записала, держит горизонт своим xid, даже когда снимка нет. Процесс без обоих значений горизонт не трогает. Слоты добавляются после цикла (строки 1897–1911):

	 * Check whether there are replication slots requiring an older xmin.
	 */
	h->shared_oldest_nonremovable =
		TransactionIdOlder(h->shared_oldest_nonremovable, h->slot_xmin);
	h->data_oldest_nonremovable =
		TransactionIdOlder(h->data_oldest_nonremovable, h->slot_xmin);

	/*
	 * The only difference between catalog / data horizons is that the slot's
	 * catalog xmin is applied to the catalog one (so catalogs can be accessed
	 * for logical decoding). Initialize with data horizon, and then back up
	 * further if necessary. Have to back up the shared horizon as well, since
	 * that also can contain catalogs.
	 */
	h->shared_oldest_nonremovable_raw = h->shared_oldest_nonremovable;

xmin слота сдвигает горизонт и данных, и общих таблиц. catalog_xmin попадает только в горизонт каталогов, и опыт 2 это подтверждает. Реплика без слота видна как процесс walsender со своим backend_xmin: комментарий выше цикла по процессам (строки 1849–1851) объясняет, что флаг PROC_AFFECTS_ALL_HORIZONS нужен именно для обратной связи горячего резерва, и тогда процесс влияет на горизонт независимо от базы.

DBA / 01koddo

Четыре держателя горизонта xmin и граница очистки VACUUM

Опыт 1: одна открытая транзакция

docker network create dba-xmin-net
docker run -d --name dba-xmin-horizon --network dba-xmin-net -e POSTGRES_PASSWORD=pg postgres:18 \
  -c wal_level=logical -c autovacuum=off -c max_prepared_transactions=5
docker exec -i dba-xmin-horizon psql -U postgres

Фоновый autovacuum выключен на весь опыт, чтобы он не чистил таблицу сам. Таблица на миллион строк; в соседнем окне psql открыта транзакция и оставлена без COMMIT:

CREATE TABLE t (id int PRIMARY KEY, v int);
INSERT INTO t SELECT i, 0 FROM generate_series(1, 1000000) i;
VACUUM t;
-- соседнее окно: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM t;
SELECT pid, state, backend_xmin, age(backend_xmin) AS xmin_age
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid();
UPDATE t SET v = v + 1;
VACUUM VERBOSE t;

 pid |        state        | backend_xmin | xmin_age 
-----+---------------------+--------------+----------
 144 | idle in transaction |          759 |        0
(1 row)

UPDATE 1000000
tuples: 0 removed, 2000000 remain, 1000000 are dead but not yet removable
removable cutoff: 759, which was 1 XIDs old when operation ended

В выводе VACUUM VERBOSE оставлены две строки. Миллион версий, оставленных UPDATE, мёртвы, но граница очистки стоит на xmin чужой сессии: 759. Закрыть её можно из другого окна, pg_terminate_backend(144), и повторить VACUUM VERBOSE t:

tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 760, which was 0 XIDs old when operation ended

Горизонт ушёл с 759 на 760, и тот же VACUUM удалил миллион версий. Когда держателя нет, а таблица всё равно раздута, измерение и перепаковка описаны в статье про bloat.

Опыт 2: четыре держателя одним запросом

Нужна реплика на втором контейнере в той же сети. pg_basebackup сам создаёт слот replica_slot; max_prepared_transactions на реплике не меньше, чем на primary, иначе она не запустится:

docker exec dba-xmin-horizon bash -c 'echo "host replication all all scram-sha-256" >> $PGDATA/pg_hba.conf'
docker exec dba-xmin-horizon psql -U postgres -c "SELECT pg_reload_conf()"
docker run -d --name dba-xmin-horizon-replica --network dba-xmin-net --user postgres --entrypoint bash -e PGPASSWORD=pg postgres:18 -c \
  'pg_basebackup -h dba-xmin-horizon -U postgres -D "$PGDATA" -R -X stream -c fast -C -S replica_slot && exec postgres -c hot_standby_feedback=on -c max_prepared_transactions=5'

Держатели создаются по очереди, между ними сделано по сто коротких транзакций (UPDATE t SET v = v WHERE id = 2 с COMMIT в цикле), чтобы возраст у каждого был свой:

1. логический слот: SELECT pg_create_logical_replication_slot('cdc_slot', 'test_decoding');
2. на реплике BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM t; и 10 секунд ожидания: обратная связь уходит раз в wal_receiver_status_interval, у которого значение по умолчанию 10 секунд;
3. на primary сессия BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM t; без завершения;
4. BEGIN; UPDATE t SET v = v + 1 WHERE id = 1; PREPARE TRANSACTION 'order_import';.

Запрос собирает все источники в одну таблицу и сортирует по возрасту:

SELECT holder, who, xmin, age(xmin) AS xmin_age
FROM (
  SELECT 'backend' AS holder, pid || ' ' || backend_type || ' ' || state AS who, backend_xmin AS xmin
  FROM pg_stat_activity
  WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid()
  UNION ALL
  SELECT 'slot xmin', slot_name, xmin FROM pg_replication_slots WHERE xmin IS NOT NULL
  UNION ALL
  SELECT 'slot catalog_xmin', slot_name, catalog_xmin FROM pg_replication_slots WHERE catalog_xmin IS NOT NULL
  UNION ALL
  SELECT 'prepared xact', gid, transaction FROM pg_prepared_xacts
) h
ORDER BY age(xmin) DESC;

      holder       |                  who                   | xmin | xmin_age 
-------------------+----------------------------------------+------+----------
 slot catalog_xmin | cdc_slot                               | 2478 |      401
 slot xmin         | replica_slot                           | 2578 |      301
 backend           | 859 client backend idle in transaction | 2678 |      201
 prepared xact     | order_import                           | 2778 |      101
(4 rows)

Реплика видна в строке slot xmin, потому что обратная связь пишется в слот; строки walsender в таблице нет. Теперь UPDATE 10 000 строк и VACUUM VERBOSE t:

tuples: 100 removed, 885853 remain, 10300 are dead but not yet removable
removable cutoff: 2578, which was 303 XIDs old when operation ended

Граница 2578 равна xmin слота реплики. Самый старый в таблице catalog_xmin слота cdc_slot (2478) её не сдвинул: пользовательским таблицам он не мешает. Мёртвых версий 10 300: 10 000 от UPDATE и 300 от цикла по id = 2.

Теперь держатели снимаются по одному, после каждого идёт VACUUM VERBOSE t без новых UPDATE: слот сбрасывается через pg_drop_replication_slot, транзакции на реплике и primary завершаются через pg_terminate_backend, подготовленная откатывается. Перед шагами 2 и 5 пауза 12 секунд ради обратной связи. Строки --- — подписи шагов, остальное вывод VACUUM VERBOSE:

--- 1: DROP логического слота
tuples: 0 removed, 885853 remain, 10300 are dead but not yet removable
removable cutoff: 2578, which was 303 XIDs old when operation ended
--- 2: конец транзакции на реплике
tuples: 100 removed, 885753 remain, 10200 are dead but not yet removable
removable cutoff: 2678, which was 203 XIDs old when operation ended
--- 3: завершить сессию на primary
tuples: 100 removed, 885653 remain, 10100 are dead but not yet removable
removable cutoff: 2778, which was 103 XIDs old when operation ended
--- 4: ROLLBACK PREPARED
tuples: 1 removed, 885653 remain, 10100 are dead but not yet removable
removable cutoff: 2778, which was 103 XIDs old when operation ended
--- 5: через 12 секунд
tuples: 10100 removed, 875553 remain, 0 are dead but not yet removable
removable cutoff: 2881, which was 0 XIDs old when operation ended

Горизонт идёт по лестнице 2578, 2678, 2778, 2881: каждый шаг убирает самого старого держателя, и граница переходит к следующему. Десять тысяч версий от UPDATE остаются в таблице до самого шага 5, потому что их удалила транзакция с xid больше любого из держателей: пока жив хоть один, версия видна его снимку. Удаление слота cdc_slot на шаге 1 ничего не сдвинуло: он не держал данные. Шаг 4 показывает задержку обратной связи: подготовленная транзакция уже откатана, но реплика ещё не прислала новый xmin, и граница стоит на 2778 до следующего сообщения.

Если слота нет, реплика с hot_standby_feedback держит горизонт процессом walsender. Для этого на реплике ALTER SYSTEM SET primary_slot_name = '', на primary pg_drop_replication_slot('replica_slot'), снова открытая транзакция на реплике. Тот же запрос показывает:

 holder  |         who          | xmin | xmin_age 
---------+----------------------+------+----------
 backend | 968 walsender active | 2881 |      100
(1 row)

Опыт 3: что отпускает горизонт само

idle_in_transaction_session_timeout обрывает сессию, которая простаивает с открытой транзакцией. Новая сессия с порогом три секунды и паузой шесть секунд перед следующей командой:

SET idle_in_transaction_session_timeout = '3s';
BEGIN;
SELECT count(*) FROM t;
-- пауза 6 секунд, затем SELECT 1;

FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly

Остальной вывод (результат count) опущен.

Для слота предел ставит max_slot_wal_keep_size, и он считается по WAL, а не по xmin. Создаётся физический слот p_slot, реплика переводится на него (ALTER SYSTEM SET primary_slot_name = 'p_slot' на реплике), на ней снова открыта транзакция, потом реплика останавливается командой docker stop dba-xmin-horizon-replica. Предел 64 МБ:

ALTER SYSTEM SET max_slot_wal_keep_size = '64MB';
SELECT pg_reload_conf();
UPDATE t SET v = v + 1 WHERE id BETWEEN 100 AND 10099;
SELECT slot_name, active, xmin, wal_status, invalidation_reason FROM pg_replication_slots;
VACUUM VERBOSE t;
UPDATE t SET v = v + 1 WHERE id <= 600000;
CHECKPOINT;
SELECT slot_name, active, xmin, wal_status, invalidation_reason FROM pg_replication_slots;
VACUUM VERBOSE t;

К десяти тысячам версий добавлены сто из цикла по id = 2, как в опыте 2. Первая пара результатов, после остановки реплики:

 slot_name | active | xmin | wal_status | invalidation_reason 
-----------+--------+------+------------+---------------------
 p_slot    | f      | 2986 | reserved   | 
(1 row)

tuples: 0 removed, 944292 remain, 10100 are dead but not yet removable
removable cutoff: 2986, which was 101 XIDs old when operation ended

Вторая, после UPDATE 600 000 строк и CHECKPOINT:

 slot_name | active | xmin | wal_status | invalidation_reason 
-----------+--------+------+------------+---------------------
 p_slot    | f      | 2986 | lost       | wal_removed         
(1 row)

tuples: 610100 removed, 964408 remain, 0 are dead but not yet removable
removable cutoff: 3088, which was 0 XIDs old when operation ended

Остановленная реплика держит горизонт столько, сколько жив её слот: xmin 2986 стоит, пока слот не потеряет WAL. После 600 000 обновлений слот стал lost с причиной wal_removed, и тот же VACUUM удалил 610 100 версий. Столбец xmin у потерянного слота остался заполненным, поэтому по одному ему держателя искать нельзя: смотри wal_status и invalidation_reason. Цена освобождения в том, что слот потерял WAL, нужный реплике для догона.

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

• Ищи держателя запросом, а не по размеру таблицы. Четыре источника в одном запросе дают возраст каждого; сверяй их xmin с removable cutoff из VACUUM VERBOSE. Совпавший с границей и есть тот, кого нужно разбирать.
• Неактивный слот держит горизонт без единой сессии. Опыт 3: остановленная реплика держала xmin 2986, пока слот не потерял WAL. Проверяй active и inactive_since в pg_replication_slots.
• Подготовленная транзакция видна только в pg_prepared_xacts. В pg_stat_activity её нет, а order_import держала горизонт 2778 до ROLLBACK PREPARED; закрывай через COMMIT PREPARED или ROLLBACK PREPARED.
• hot_standby_feedback — размен. Он убирает отмену запросов на реплике, но переносит риск раздувания на primary; документация описывает это так же. Обратная связь идёт раз в 10 секунд, поэтому горизонт отпускает с задержкой.
• Пороги ставят до аварии. idle_in_transaction_session_timeout оборвал сессию через три секунды простоя; значение для продакшена выбирай по самым долгим честным транзакциям приложения. max_slot_wal_keep_size защищает диск и горизонт ценой слота. В PostgreSQL 18 есть и idle_replication_slot_timeout (по умолчанию 0, выключен); в опыте он не проверялся.

Чек-лист

1. Запусти запрос из опыта 2 на своей базе и запиши самый старый xmin и его возраст.
2. Сравни возраст с removable cutoff последнего VACUUM VERBOSE на самой раздутой таблице: совпадение называет держателя.
3. Найди неактивные слоты (active = false) и подготовленные транзакции из pg_prepared_xacts: у каждой строки должен быть владелец.
4. Задай idle_in_transaction_session_timeout ролям приложения и проверь, что ни одна нормальная операция его не превышает.
5. Убери контейнеры вместе с данными и сеть: docker rm -f -v dba-xmin-horizon dba-xmin-horizon-replica && docker network rm dba-xmin-net.