HOT-обновления в PostgreSQL и управление нагрузкой

6.8.3. HOT-обновления в PostgreSQL и управление нагрузкой#

6.8.3.1. Область применения#

Документ ориентирован на технические таблицы ERP-систем:

  • Таблицы свёрток и агрегатов.

  • Регистры и промежуточные расчётные данные.

  • Таблицы состояний, балансов, накоплений.

  • Staging- и buffer-таблицы.

Для таких таблиц характерны:

  • Большое количество операций UPDATE.

  • Повторное обновление одних и тех же строк.

  • Высокая чувствительность к bloat, WAL и частоте VACUUM.

Для пользовательских таблиц (справочники, транзакции) HOT обычно не критичен и редко требует настройки.

6.8.3.2. Как работает UPDATE в PostgreSQL#

6.8.3.2.1. Ключевой факт#

Примечание

Любой обычный UPDATE в PostgreSQL обновляет все индексы таблицы, даже если изменяемые столбцы в индексах не участвуют.

Это следует из MVCC-модели: при обновлении создаётся новая версия строки с новым TID (ctid). Поскольку индексы хранят ссылки на TID, они обязаны быть обновлены.

6.8.3.2.2. Почему так происходит#

  • UPDATE не изменяет строку на месте.

  • Создаётся новая версия строки.

  • Новая версия получает новый TID.

  • Все индексы ссылаются на TID, а не на данные.

Следовательно, даже обновление неиндексированного числового поля приводит к обновлению всех индексов.

6.8.3.2.3. Иллюстрация обычного UPDATE#

Heap
+-+
| (5,12) version 1 |
+-+
| (8,42) version 2 |  <-- новая версия строки
+-+

Indexes (любые, даже 100 штук)
idx_a -> (5,12)  [устарело]
idx_b -> (5,12)  [устарело]
idx_c -> (5,12)  [устарело]

После UPDATE:

idx_a -> (8,42)  [актуально]
idx_b -> (8,42)  [актуально]
idx_c -> (8,42)  [актуально]

6.8.3.3. HOT: единственное исключение#

HOT (Heap-Only Tuple) — оптимизация, позволяющая выполнять UPDATE без обновления индексов.

6.8.3.3.1. Суть HOT#

HOT возможен, если PostgreSQL может:

  • Сохранить старый TID в индексах.

  • Связать новую версию строки с предыдущей внутри одной heap-страницы.

6.8.3.3.2. UPDATE в HOT-режиме#

Heap page
+--+
| (17,3) version 1         |  <-- индекс указывает сюда
|        |                 |
|        v HOT chain       |
| (17,4) version 2 (HOT)   |
+--+

Indexes
idx_a -> (17,3)
idx_b -> (17,3)
idx_c -> (17,3)

Индекс продолжает указывать на старый TID. При чтении PostgreSQL проходит HOT-цепочку и находит актуальную версию строки.

Примечание

Это единственная причина, по которой HOT не требует обновления индексов.

6.8.3.4. Когда HOT невозможен#

HOT невозможен, если индекс больше не может безопасно указывать на старый TID.

6.8.3.4.1. Expression-индексы#

CREATE INDEX ON t ((payload->>'status'));

Если UPDATE изменяет результат выражения, индекс обязан указывать на новую версию строки -> HOT невозможен.

6.8.3.4.2. Partial-индексы#

CREATE INDEX ON t(id) WHERE active;

Если UPDATE меняет значение условия WHERE, строка может быть добавлена в индекс или удалена из него -> HOT невозможен.

6.8.3.4.3. Недостаток места на heap-странице#

Даже при логической возможности HOT он не сработает, если:

  • fillfactor = 100;

  • страницы плотно заполнены;

  • новая версия строки не помещается на той же странице.

В этом случае создаётся новая heap-строка -> HOT невозможен.

6.8.3.4.4. Триггеры, изменяющие индексируемые поля#

Типовой сценарий:

  • UPDATE меняет техническое поле.

  • BEFORE UPDATE-триггер обновляет updated_at.

  • Столбец updated_at индексирован.

HOT невозможен, несмотря на «безопасный» SQL.

6.8.3.5. Диагностика: поиск таблиц без HOT#

6.8.3.5.1. Основной запрос#

Используйте статистику PostgreSQL для выявления проблемных таблиц:

SELECT
    relname                         AS table_name,
    n_tup_upd                       AS updates,
    n_tup_hot_upd                   AS hot_updates,
    round(
        100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0),
        2
    )                               AS hot_ratio_percent
FROM pg_stat_user_tables
WHERE n_tup_upd > 100000
ORDER BY hot_ratio_percent ASC;

6.8.3.5.2. Интерпретация результатов#

  • hot_ratio < 20% — таблица активно обновляется без HOT.

  • hot_ratio 70–95% — оптимально для технических таблиц.

  • hot_ratio = 0 — HOT принципиально невозможен или таблица спроектирована неверно.

6.8.3.5.3. Косвенные признаки#

Большое количество «мёртвых» строк при активных UPDATE также указывает на отсутствие HOT:

SELECT
    relname,
    n_tup_upd,
    n_dead_tup
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_dead_tup DESC;

6.8.3.6. Управление HOT: fillfactor таблицы#

6.8.3.6.1. Что делает fillfactor#

Параметр fillfactor задаёт, сколько места PostgreSQL оставляет свободным на heap-страницах для будущих UPDATE.

Пример:

ALTER TABLE tech_table SET (fillfactor = 70);

Это означает, что ~30% страницы остаётся свободной, что повышает вероятность размещения новых версий строк на той же странице.

6.8.3.6.2. Рекомендуемые значения#

Тип таблицы

fillfactor

Пользовательские

100

Технические

60–80

Очень «горячие»

50–60

Внимание

fillfactor — это компромисс между производительностью UPDATE и размером таблицы. Применяйте его только для таблиц с высокой нагрузкой.

6.8.3.6.3. Важное ограничение#

Команда:

ALTER TABLE ... SET (fillfactor = X);

  • Не перепаковывает существующие данные.

  • Эффект проявляется постепенно, по мере выполнения UPDATE.

  • Для немедленного эффекта используйте VACUUM FULL, CLUSTER или pg_repack.

6.8.3.7. Fillfactor индексов#

6.8.3.7.1. Что это и зачем#

Индексы (особенно B-tree) имеют собственный параметр fillfactor. По умолчанию он равен 90.

Внимание

fillfactor индекса не влияет на возможность HOT. HOT определяется только heap-страницей.

Однако при отсутствии HOT каждый UPDATE обновляет все индексы. Низкий fillfactor индекса снижает частоту page split и рост bloat.

6.8.3.7.2. Рекомендации#

Для технических таблиц с активными UPDATE:

  • Устанавливайте fillfactor индексов в диапазоне 70–80.

  • Особенно актуально для крупных B-tree-индексов.

Пример:

ALTER INDEX idx_big SET (fillfactor = 75);
REINDEX INDEX idx_big;

Это не ускоряет HOT, но снижает ущерб, когда HOT невозможен.

6.8.3.8. Итоги#

  • Любой обычный UPDATE обновляет все индексы таблицы.

  • HOT — единственный механизм, позволяющий избежать этого.

  • HOT критичен для технических таблиц с массовыми UPDATE.

  • Основной рычаг управления HOT — fillfactor таблицы.

  • Снижение fillfactor — осознанный компромисс между размером и производительностью.

  • fillfactor индексов не влияет на HOT, но важен для контроля bloat при отсутствии HOT.

Содержание