HOT-обновления и fillfactor

Идея

Обычный UPDATE создаёт новую версию строки с новым ctid, и каждый индекс таблицы должен получить запись на этот ctid. Для таблицы с несколькими индексами это дорого, даже если менялось одно неиндексируемое поле.

HOT-обновление обходит это: если индексируемые колонки не тронуты, старые индексные записи всё ещё ведут «в эту строку», и переписывать их не нужно. Новая версия живёт в той же странице, образуя HOT-цепочку, а индекс находит актуальную версию, пройдя по цепочке от корня.

Два условия

HOT срабатывает, когда выполнены оба:

1. ни одна колонка из любого индекса таблицы не изменилась;
2. новая версия помещается в ту же страницу, что и старая.

Нарушено первое - индексы получают новую запись. Нарушено второе (нет места) - новая версия уходит в другую страницу, и HOT невозможен, потому что цепочка не пересекает границу страницы.

Счётчики

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd

FROM pg_stat_all_tables WHERE relname = 'acc';

• n_tup_hot_upd - обновления, оставшиеся HOT (индексы не тронуты);
• n_tup_newpage_upd (PG16+) - обновления, ушедшие в новую страницу из-за нехватки места;
• доля n_tup_hot_upd / n_tup_upd - тот самый hot_update_ratio, который хочется держать высоким на часто обновляемых таблицах.

fillfactor

По умолчанию fillfactor = 100: страница забивается полностью, резерва под новые версии нет, и почти любой UPDATE выталкивает версию в другую страницу. Снижение fillfactor оставляет в странице свободное место:

ALTER TABLE acc SET (fillfactor = 75);

Значения 75-90 типичны для горячих таблиц. Важно: настройка действует только на страницы, заполняемые после изменения; уже плотные страницы остаются такими, пока их не перепишут (VACUUM FULL, CLUSTER).

Цена индекса на горячую колонку

Как только колонка попадает в индекс, любой её UPDATE перестаёт быть HOT - и все индексы таблицы получают новую запись. Поэтому лишний индекс на часто меняющуюся колонку дорог не только местом, но и потерей HOT. Подчищает HOT-цепочки heap-pruning.