HOT и fillfactor в PostgreSQL: UPDATE без правки индексов

Таблица обновляется постоянно, а индексы на ней растут, хотя индексированные столбцы никто не менял. Каждый обычный UPDATE в PostgreSQL создаёт новую версию строки, и каждой версии нужна запись в каждом индексе. HOT-обновление обходит это: новая версия встаёт на ту же страницу, индексы не получают ничего, а старые версии убираются без VACUUM. Условие простое: нужное место на странице, и это место зависит от fillfactor.

Условие HOT

В heap_update (heapam.c, строки 4236–4243, тег REL_18_6) обновление, признанное HOT, метит обе версии:

	if (use_hot_update)
	{
		/* Mark the old tuple as HOT-updated */
		HeapTupleSetHotUpdated(&oldtup);
		/* And mark the new tuple as heap-only */
		HeapTupleSetHeapOnly(heaptup);
		/* Mark the caller's copy too, in case different from heaptup */
		HeapTupleSetHeapOnly(newtup);
	}

Выше в той же функции use_hot_update становится true, только если новая версия попала в ту же страницу (newbuf == buffer) и ни один изменённый столбец не входит в индексы (!bms_overlap(modified_attrs, hot_attrs), строка 4123). Исключение с PostgreSQL 16 — индексы-сводки вроде BRIN: их столбцы в hot_attrs не входят, и такое обновление остаётся HOT с пометкой summarized_update (строки 4134–4135). По README.HOT «входит в индекс» значит любое упоминание столбца в определении индекса, в том числе в предикате частичного индекса. Старая версия получает флаг HEAP_HOT_UPDATED (0x4000 в t_infomask2), новая — HEAP_ONLY_TUPLE (0x8000): на неё не указывает ни одна запись индекса.

DBA / 01

Одна запись индекса на цепочку версий, и что с ней делает pruning

Три версии строки на одной странице, но индекс хранит только (0,1). Pruning не трогает индекс: он превращает первую позицию в redirect и освобождает 28 промежуточных, поэтому страница снова вмещает новые версии.

Опыт 1: цепочка на странице

docker run -d --name dba-hot-updates-fillfactor -e POSTGRES_PASSWORD=pg postgres:18
docker exec -i dba-hot-updates-fillfactor psql -U postgres

Одна строка с широким столбцом pad (чтобы страница заполнялась за десятки обновлений); автовакуум на таблице выключен, поэтому все следы pruning’а ниже сделаны только обновлениями. Дважды меняется неиндексированный note:

SELECT version();
CREATE EXTENSION pageinspect;
CREATE TABLE chain (id int PRIMARY KEY, note text, pad text) WITH (autovacuum_enabled = off);
INSERT INTO chain VALUES (1, 'v1', repeat('x', 200));
UPDATE chain SET note = 'v2';
UPDATE chain SET note = 'v3';
SELECT lp, lp_flags, lp_off, t_ctid, t_xmin, t_xmax,
       (t_infomask2 & x'4000'::int) <> 0 AS hot_updated,
       (t_infomask2 & x'8000'::int) <> 0 AS heap_only
FROM heap_page_items(get_raw_page('chain', 0));
SELECT itemoffset, ctid FROM bt_page_items('chain_pkey', 1);

PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

 lp | lp_flags | lp_off | t_ctid | t_xmin  | t_xmax  | hot_updated | heap_only
----+----------+--------+--------+---------+---------+-------------+-----------
  1 |        1 |   7952 | (0,2)  | 2000846 | 2000847 | t           | f
  2 |        1 |   7712 | (0,3)  | 2000847 | 2000848 | t           | t
  3 |        1 |   7472 | (0,3)  | 2000848 |       0 | f           | t

 itemoffset | ctid
------------+-------
          1 | (0,1)

Три версии: t_ctid ведёт (0,1) → (0,2) → (0,3). Первая версия HOT_UPDATED, вторая и HOT_UPDATED, и HEAP_ONLY, третья только HEAP_ONLY. В листе индекса одна запись (0,1), то есть в начало цепочки: поиск идёт от неё по t_ctid, пока видит HOT_UPDATED, и все шаги остаются в одной странице.

Опыт 2: pruning без VACUUM

Версия занимает 236 байт. \gexec выполняет каждый UPDATE отдельной транзакцией (автокоммит):

SELECT format('UPDATE chain SET note = %L', 'v' || i) FROM generate_series(4, 31) i \gexec
SELECT lower, upper, upper - lower AS free FROM page_header(get_raw_page('chain', 0));
UPDATE chain SET note = 'v32';
SELECT lower, upper, upper - lower AS free FROM page_header(get_raw_page('chain', 0));
SELECT lp, lp_flags, lp_off, lp_len, t_ctid, t_xmin, t_xmax
FROM heap_page_items(get_raw_page('chain', 0)) WHERE lp IN (1, 2, 3, 31);
SELECT count(*) AS unused FROM heap_page_items(get_raw_page('chain', 0)) WHERE lp_flags = 0;
SELECT itemoffset, ctid FROM bt_page_items('chain_pkey', 1);

 lower | upper | free
-------+-------+------
   148 |   752 |  604

 lower | upper | free
-------+-------+------
   148 |  7712 | 7564

 lp | lp_flags | lp_off | lp_len | t_ctid | t_xmin  | t_xmax
----+----------+--------+--------+--------+---------+---------
  1 |        2 |     31 |      0 |        |         |
  2 |        1 |   7712 |    236 | (0,2)  | 2000877 |       0
  3 |        0 |      0 |      0 |        |         |
 31 |        1 |   7952 |    236 | (0,2)  | 2000876 | 2000877

 unused
--------
     28

 itemoffset | ctid
------------+-------
          1 | (0,1)

После 31 версии на странице оставалось 604 байта. Последнее обновление нашло через индекс страницу, увидело нехватку места и вычистило её прямо в этом запросе: свободно стало 7 564 байта. lp_flags = 2 — LP_REDIRECT: у первой позиции нет строки, а в lp_off лежит номер позиции, куда она перенаправляет (31). Позиции 3–30 (28 штук) свободны. Индекс не менялся: запись (0,1) по-прежнему ведёт в lp 1, а оттуда цепочка идёт 1 → 31 → 2. Версия v32 заняла освободившуюся позицию 2.

Порог, при котором pruning вообще запускается, задаёт heap_page_prune_opt (pruneheap.c, строки 238–242):

	minfree = RelationGetTargetPageFreeSpace(relation,
											 HEAP_DEFAULT_FILLFACTOR);
	minfree = Max(minfree, BLCKSZ / 10);

	if (PageIsFull(page) || PageGetHeapFreeSpace(page) < minfree)

Страницу чистят, когда на ней свободно меньше Max(место_по_fillfactor, BLCKSZ / 10), то есть меньше 819 байт при размере страницы 8 192. Поэтому fillfactor 100 и 90 запускают pruning одинаково (10 % от 8 192 — те же 819): fillfactor решает только, сколько места остаётся после загрузки.

Опыт 3: fillfactor 90 на пакетном UPDATE

Две одинаковые таблицы по 100 000 строк: первичный ключ, индекс по grp, неиндексированный counter и 50 байт pad. Различается fillfactor; автовакуум выключен, чтобы видеть только pruning. Затем один оператор обновляет каждую десятую строку, 10 000 штук (counter не входит ни в один индекс):

CREATE TABLE b100 (id int PRIMARY KEY, grp int, counter int, pad text) WITH (fillfactor = 100, autovacuum_enabled = off);
CREATE TABLE b90  (id int PRIMARY KEY, grp int, counter int, pad text) WITH (fillfactor = 90,  autovacuum_enabled = off);
CREATE INDEX b100_grp ON b100 (grp);
CREATE INDEX b90_grp  ON b90  (grp);
INSERT INTO b100 SELECT i, i % 1000, 0, repeat('x', 50) FROM generate_series(1, 100000) i;
INSERT INTO b90  SELECT i, i % 1000, 0, repeat('x', 50) FROM generate_series(1, 100000) i;
CHECKPOINT;
SELECT pg_current_wal_lsn() AS l0 \gset
UPDATE b100 SET counter = counter + 1 WHERE id % 10 = 0;
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), :'l0') AS b100_wal_bytes;
SELECT pg_current_wal_lsn() AS l0 \gset
UPDATE b90 SET counter = counter + 1 WHERE id % 10 = 0;
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), :'l0') AS b90_wal_bytes;
SELECT pg_stat_force_next_flush();
SELECT relname, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname IN ('b100','b90') ORDER BY 1 DESC;
SELECT relname, pg_relation_size(oid) AS bytes FROM pg_class WHERE relname IN ('b100','b100_grp','b90','b90_grp') ORDER BY 1 DESC;

 b100_wal_bytes
----------------
       15328544

 b90_wal_bytes
---------------
      10045680

 relname | n_tup_upd | n_tup_hot_upd
---------+-----------+---------------
 b90     |     10000 |         10000
 b100    |     10000 |             0

  relname  |  bytes
-----------+----------
 b90_grp   |   901120
 b90       | 10371072
 b100_grp  |   991232
 b100      | 10240000

Размеры до обновления: b100 9 314 304 байт, b90 10 371 072 (на 11,3 % больше с самой загрузки), оба индекса grp по 901 120. В b100 страницы набиты до отказа, новой версии некуда встать, и все 10 000 обновлений обычные: таблица выросла до 10 240 000 (+113 страниц), индекс grp до 991 232, WAL 15 328 544 байта. В b90 на каждой странице оставалось 10 % свободного места, все 10 000 обновлений HOT, ни таблица, ни индекс не выросли, WAL на 34 % меньше (10 045 680 байт).

Обновление проиндексированного столбца HOT не делает, как бы много места ни было. На b90 обновление grp по другим десяти тысячам строк:

UPDATE b90 SET grp = grp + 1 WHERE id % 10 = 2;
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'b90';

 n_tup_upd | n_tup_hot_upd
-----------+---------------
     20000 |         10000

Счётчик n_tup_upd вырос на 10 000, а n_tup_hot_upd остался на 10 000: ни одного нового HOT.

Опыт 4: миллион одиночных UPDATE

Теперь стационарная нагрузка: те же схемы (таблицы ff100 и ff90, 100 000 строк), миллион случайных обновлений counter через pgbench внутри контейнера, по одному оператору в транзакции. Для скорости synchronous_commit = off; автовакуум выключен на обеих таблицах:

docker exec dba-hot-updates-fillfactor sh -c 'printf "\\\\set id random(1, 100000)\nUPDATE ff100 SET counter = counter + 1 WHERE id = :id;\n" > /tmp/upd_ff100.sql'
docker exec -e PGOPTIONS='-c synchronous_commit=off' dba-hot-updates-fillfactor \
  pgbench -U postgres -n -f /tmp/upd_ff100.sql -c 4 -j 4 -t 250000 postgres

Тот же запуск с ff90 вместо ff100. Результаты:

 relname | n_tup_upd | n_tup_hot_upd
---------+-----------+---------------
 ff100   |   1000000 |        993589
 ff90    |   1000000 |       1000000

  relname   |  bytes
------------+----------
 ff100      |  9936896
 ff100_grp  |   974848
 ff90       | 10371072
 ff90_grp   |   901120

WAL за прогон: 190 030 072 байта у ff100 и 185 802 880 у ff90, на 2,2 % меньше. Тут fillfactor 100 почти не проиграл: 99,36 % обновлений HOT, 6 411 не-HOT дали +76 страниц таблицы и +9 страниц индекса grp. Объясняется это pruning’ом из опыта 2: он возвращал место страницы, и следующая версия вставала туда же. Это согласуется с документацией: без запаса HOT всё равно случается, пока на странице хоть иногда находится место. То же верно для одной горячей строки: в опыте про MVCC 200 000 обновлений остались в одной странице, а под долгим снимком pruning ничего не вычищает, и таблица выросла до 885 страниц.

Опыт 5: ALTER TABLE SET fillfactor

Таблица создаётся с обычным fillfactor, потом он меняется командой ALTER TABLE, и только затем идут обновления:

CREATE TABLE c (id int PRIMARY KEY, grp int, counter int, pad text) WITH (autovacuum_enabled = off);
INSERT INTO c SELECT i, i % 1000, 0, repeat('x', 50) FROM generate_series(1, 100000) i;
ALTER TABLE c SET (fillfactor = 90);
UPDATE c SET counter = counter + 1 WHERE id % 10 = 0;
SELECT pg_stat_force_next_flush();
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'c';
VACUUM FULL c;
UPDATE c SET counter = counter + 1 WHERE id % 10 = 1;
SELECT pg_stat_force_next_flush();
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'c';

 n_tup_upd | n_tup_hot_upd
-----------+---------------
     10000 |             4

 n_tup_upd | n_tup_hot_upd
-----------+---------------
     20000 |         10004

Второе обновление дало 10 000 HOT (10 004 минус 4). Размер таблицы вырос с 9 314 304 до 10 371 072 байт: VACUUM FULL расставил строки с запасом 10 %.

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

Следи за долей HOT. n_tup_hot_upd / n_tup_upd из pg_stat_user_tables показывает, сколько обновлений обошлись без индексов. Низкая доля на таблице с частыми обновлениями — повод искать индексируемый столбец в SET или нехватку места.
Не индексируй столбцы, которые часто меняются. Счётчик, статус, updated_at в индексе превращают каждое обновление в не-HOT: опыт 3 с grp дал 0 новых HOT из 10 000. Индекс по такому столбцу стоит каждой записи.
fillfactor 90 нужен таблицам с пакетными обновлениями. Он платит 11,3 % размера сразу и возвращает их, когда оператор меняет десятую часть строк: 10 000 HOT против 0, WAL на 34 % меньше. На случайных одиночных обновлениях стационарной нагрузки выигрыш был 2,2 % WAL при 99,36 % HOT уже без запаса.
ALTER TABLE ... SET (fillfactor = 90) не переставляет существующие страницы. Опыт 5: сразу после ALTER на готовой таблице из 10 000 обновлений HOT были 4, после VACUUM FULL (перепаковка с новым fillfactor) — 10 000 из 10 000. VACUUM FULL переписывает таблицу целиком под эксклюзивной блокировкой, поэтому на живой базе его запускают в окно обслуживания.
Долгая транзакция обесценивает pruning. Он по-прежнему запускается, но версии, видимые чьему-то снимку, не убираются, цепочка растёт и страница заполняется. Это видно по backend_xmin в pg_stat_activity, а чистить страницы тогда придётся уже VACUUM.

Чек-лист

Посчитай n_tup_hot_upd * 100.0 / n_tup_upd для самых часто обновляемых таблиц.
Найди индексы на столбцах, которые попадают в SET: pg_indexes и pg_stat_user_indexes.idx_scan. Если индекс не читается, он только удорожает обновления.
Для таблиц с пакетными обновлениями задай fillfactor = 90 при создании и перепакуй существующие.
После изменения мерь ту же долю заново: без цифры до и после изменение не доказано.
Убери контейнер вместе с данными: docker rm -f -v dba-hot-updates-fillfactor.

HOT-обновления и fillfactor в PostgreSQL: UPDATE без правки индексов