PostgreSQL: HOT UPDATE, fillfactor и борьба с bloat без боли

Если в вашей базе данных много UPDATE и периодически падает производительность из‑за раздувшихся таблиц и индексов, значит пришло время подружиться с HOT UPDATE и грамотно выбрать fillfactor. В этой статье разберём, когда HOT срабатывает, как именно fillfactor влияет на вероятность HOT и на bloat, как диагностировать и устранять раздувание, и как настроить autovacuum, чтобы поддерживать устойчивую скорость чтения и записи.

Коротко о главном: что такое HOT UPDATE

HOT (Heap Only Tuple) — это оптимизация PostgreSQL, при которой UPDATE создаёт новую версию строки в том же heap page, не трогая индексы, если обновляемые поля не участвуют ни в одном индексе (включая INCLUDE-колонки). Результат — существенно меньше работы по обновлению индексов, меньше I/O и снижение шансов на быстрый рост индексов и таблицы.

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

Отсюда два практических следствия:

• Если вы изменяете колонку, которая есть в любом индексе (даже в INCLUDE), HOT невозможен — придётся обновлять индексы.
• Даже если изменяются только неиндексируемые колонки, нужен запас свободного места на странице — это и настраивает fillfactor.

Fillfactor: пространство для HOT и баланс между записью и чтением

fillfactor определяет, до какой плотности заполняются страницы таблицы при вставках и переписывании строк. По умолчанию — 100, то есть страницы заполняются полностью. Для обновляемых таблиц это часто плохо: при UPDATE потребуется разместить новую версию строки, а свободного места не окажется — HOT не сработает, запись уйдёт в «другую» страницу, индексы переадресуются, и таблица начнёт «расползаться» по диску.

Установка fillfactor на уровне 70–90 оставляет свободное место в каждом page для будущих обновлений и повышает шанс HOT. Но есть компромисс: чем меньше fillfactor, тем больше таблица на диске и больше страниц нужно прочитать при сканировании. Поэтому оптимум зависит от соотношения чтение/запись, характера UPDATE и размера строк.

Рекомендации по первым шагам:

• Таблицы с частыми UPDATE немалых полей: начните с fillfactor=80, дальше измеряйте HOT rate и корректируйте.
• Таблицы с редкими UPDATE: fillfactor=90–95 или оставьте дефолт и наблюдайте.
• Широкие строки (много/большие колонки): ниже fillfactor понадобится чаще, чтобы поместить новую версию в тот же page.

Откуда появляется bloat

Bloat — раздувание физического размера таблиц и индексов за счёт накопления «мёртвых» версий строк, фрагментации и пустот. Причины:

• Частые UPDATE/DELETE без своевременного VACUUM/прюнинга.
• Долгоживущие транзакции удерживают «xmin», мешая очистке.
• Отсутствие свободного места на страницах при fillfactor=100 ломает HOT и увеличивает запись в новые страницы.
• Обновление индексируемых колонок порождает рост индексов; обычный VACUUM индексы не сжимает.

Даже когда HOT срабатывает, он создаёт цепочки версий внутри страницы (HOT chain), которые надо периодически «подрезать». Это делает VACUUM (и фоновая pruning-логика), но только если транзакции не держат старые снимки.

Как понять, работает ли HOT

Смотрите статистику по таблицам. В pg_stat_user_tables есть счётчики n_tup_upd и n_tup_hot_upd. Сравнение даст долю HOT-обновлений:

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_rate
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 20;

Идеально — hot_rate близко к 100% для «горячих» таблиц, где UPDATE не должен трогать индексы. Если HOT почти не срабатывает, причины две: либо меняются индексируемые поля, либо в страницах нет места из-за слишком высокого fillfactor.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

Измеряем и оцениваем bloat

Простейшая прикидка — сопоставить размер таблицы с количеством «живых» кортежей и средней длиной строки. Для точнее — используйте расширения диагностики:

• pgstattuple: даёт оценку доли dead space и dead tuples для таблиц и индексов.
• pageinspect: позволяет заглянуть в страницы, посмотреть HOT chain и детали размещения.
• pg_freespace (опционально): оценка свободного места по страницам.

Примеры запросов:

-- Включить расширение (разово на базу)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Оценить bloat таблицы
SELECT * FROM pgstattuple('public.orders');

-- Оценить индекс
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT * FROM pgstatindex('public.orders_pkey');

Если доля пустого места/«мёртвых» кортежей велика, а VACUUM регулярно отрабатывает, значит вы либо упираетесь в долгоживущие транзакции, либо в «не-HOT» UPDATE (меняются индексируемые поля), либо fillfactor слишком высок и новая версия не помещается в страницу.

Условия HOT: проверьте индексы

HOT невозможен, если обновляются колонки, входящие в любой индекс — в том числе INCLUDE-колонки покрывающих индексов. Частая ошибка — добавлять в индекс «на всякий случай» поля вроде updated_at или статусы для покрывающих запросов. Цена — почти полное исчезновение HOT и рост индексов.

Практический рецепт:

• Анализируйте, какие колонки действительно нужны в индексах. Часто отдельный индекс под read-запросы без часто меняющихся полей будет выгоднее покрывающего индекса с INCLUDE.
• Используйте частичные или выражения в индексах, если это снижает частоту обновления ключей.
• Пересмотрите multi-column индексы: если одно поле меняется часто, разбейте индексы по задачам запросов.

Настройка fillfactor для таблиц и индексов

Для таблиц:

ALTER TABLE public.orders SET (fillfactor = 80);
-- Чтоб применить к существующим строкам, нужен перезапись: VACUUM FULL/CLUSTER/pg_repack
VACUUM (VERBOSE, ANALYZE) public.orders;

Важно: изменение fillfactor само по себе не переписывает таблицу; новый параметр влияет на будущие вставки и переписывания строк. Чтобы «применить» его к текущим страницам и создать запас свободного места, таблицу нужно переписать. Варианты:

• VACUUM FULL: блокирует таблицу, но даёт компактный результат.
• CLUSTER по индексу: также блокирует и упорядочивает.
• Онлайн-перепаковка инструментами, которые работают без долгой блокировки (например, расширение для репака). Требуется дополнительное место.

Для индексов (BTREE):

ALTER INDEX public.orders_status_idx SET (fillfactor = 90);
REINDEX (CONCURRENTLY) INDEX public.orders_status_idx;

У индексов BTREE дефолтный fillfactor обычно 90, но если у вас много случайных вставок и обновлений, уменьшение может сократить число дорогих page split на горячих уровнях. Однако для монотонно возрастающих ключей (правый конец B-дерева) это помогает ограниченно — горячая страница всё равно будет часто переписываться.

Autovacuum, pruning и долгие транзакции

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

• Включите логирование долгих циклов автоочистки, чтобы видеть отработку по большим таблицам.
• Не допускайте транзакций/курьеров, которые живут часами и держат старые снимки — VACUUM не сможет пометить версии строк как «видимые всем».
• На больших и активно обновляемых таблицах задайте индивидуальные пороги autovacuum.

Пер-табличная настройка автовака:

ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

Смысл: запускать чистку и анализ чаще, чем по умолчанию, но в щадящем режиме, чтобы успевать подрезать HOT chain и выносить мусор, не допуская лавинообразного роста. Подробно см. детальный разбор настройки autovacuum и индексов.

Диагностика долгих транзакций:

SELECT pid, usename, state, xact_start, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY age DESC
LIMIT 10;

Если видите многочасовые транзакции — это главный кандидат на источник bloat. Чаще всего помогают таймауты транзакций/идл-курьеров и дисциплина в приложении.

Индексы: блоатятся тоже и VACUUM их не «сжимает»

Стандартный VACUUM не уменьшает физический размер BTREE индексов. Поэтому если у вас много UPDATE, особенно затрагивающих индексируемые поля, индексы будут расти. Поддерживайте их в форме:

• Проверяйте состояние через pgstatindex из pageinspect.
• Периодически выполняйте REINDEX (CONCURRENTLY) для больших и часто меняющихся индексов.
• Пересмотрите набор индексов: уберите неиспользуемые и лишние покрывающие.

Пример:

REINDEX (CONCURRENTLY) TABLE public.orders;

Это позволит переупаковать индексы без долгой эксклюзивной блокировки таблицы.

CASCADE-эффекты: TOAST, широкие строки и HOT

Если строки широкие, часть данных уходит в TOAST-таблицу. Обновление больших полей может приводить к дополнительной записи в TOAST и влиять на вероятность HOT (меньше шансов уместиться в page). Полезный приём — снизить fillfactor не только у основной таблицы, но и у её TOAST-таблицы:

ALTER TABLE public.documents SET (fillfactor = 80);
ALTER TABLE public.documents ALTER TOAST TABLE SET (fillfactor = 80);

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

План действий: от измерения к улучшениям

1. Измерьте долю HOT: n_tup_hot_upd / n_tup_upd и выберите таблицы-кандидаты.
2. Проверьте индексы: действительно ли нужны часто меняющиеся колонки в ключах и INCLUDE. Уберите лишнее.
3. Подберите fillfactor: начните с 80 для горячих таблиц, 90 — для умеренных.
4. Перепакуйте таблицы и индексы: VACUUM FULL или онлайн‑репак, затем REINDEX (CONCURRENTLY).
5. Настройте пер-табличный autovacuum и включите метрики/логи.
6. Ограничьте долгоживущие транзакции и навсегда заведите мониторинг их возраста.
7. Повторите цикл измерений через 1–2 недели под рабочей нагрузкой.

Как выбирать fillfactor с умом: методика под нагрузку

Оптимальный fillfactor — не догма, а величина под конкретный паттерн UPDATE и размер строк:

• Если доля HOT ниже 60% и вы уверены, что обновляются только неиндексируемые поля, уменьшайте fillfactor шагом по 5 пунктов, переписывайте таблицу и измеряйте снова.
• Если hot_rate уже высок (90%+), но bloat растёт — посмотрите на долгие транзакции и пороги autovacuum.
• Если после снижения fillfactor чтение по сканам подорожало, ищите компромисс ближе к 85–90 и компенсируйте планами запросов и индексами.

Паттерны обновлений и особенности

Типовые случаи:

• Счётчики/штампы: обновляются часто, но обычно не должны быть в индексах. Уберите их из INCLUDE и ключей — вырастет доля HOT.
• Статусные флаги: если они участвуют в фильтрации, подумайте о частичном индексе только по активным значениям; так уменьшите частоту индексации при UPDATE статуса.
• Монотонные ключи: индексы по времени/ID с автоинкрементом — следите за bloat правого края BTREE и периодически REINDEX.
• Широкие строки: снизьте fillfactor и реже обновляйте крупные поля.

Vacuum: какой, когда и зачем

VACUUM без FULL очищает «мёртвые» версии и обновляет карты видимости, но не уменьшает файл таблицы и индексы. Это основной рабочий инструмент, его должно запускать autovacuum часто и незаметно.

VACUUM FULL переписывает таблицу и реально уменьшает размер файла, но блокирует доступ на запись (и, как правило, на чтение). Используйте как редкую «тяжёлую артиллерию» вне пиков.

ANALYZE после значительных изменений пересчитывает статистику планировщика. Как правило, запускается вместе с VACUUM. Если нужна изолированная среда для PostgreSQL с предсказуемыми IOPS и RAM, поднимайте базу на VDS.

Нюансы вычисления bloat и метрик

Сырые оценки по relpages и reltuples примерно показывают ситуацию, но обращайте внимание на:

• Размер страницы (обычно 8 КБ) и выравнивание, заголовки и TOAST — «полезная» плотность меньше, чем кажется.
• Нерегулярные паттерны UPDATE дают «островки» пустоты даже при низком fillfactor.
• Высокий HOT rate не отменяет нужду в регулярном VACUUM: цепочки версий надо подрезать.

Пример дорожной карты для произвольной таблицы

Предположим, есть events с 200 млн строк, частые UPDATE поля processed_at, а индекс покрывающий: (tenant_id, created_at) INCLUDE (processed_at, status). Доля HOT — 5%, таблица и индексы растут.

1. Делаем отдельный индекс под чтение без часто меняющихся полей: (tenant_id, created_at); пересобираем запросы на его использование.
2. Удаляем INCLUDE полей processed_at и status из покрывающего индекса; либо заменяем на частичный индекс по активным статусам.
3. ALTER TABLE events SET (fillfactor=80) и переписываем таблицу онлайн‑репаком или вне пика через VACUUM FULL.
4. REINDEX (CONCURRENTLY) TABLE events после стабилизации нагрузки.
5. Настройка пер-табличного autovacuum (scale_factor=0.03, порог ≈ несколько миллионов кортежей, повышенный cost_limit).
6. Мониторим: HOT rate → 90%+, размер таблицы стабилизировался, индекс перестал расти лавинообразно.

Что ещё может помочь

• Партиционирование по времени или ключу – локализует обновления и чистку, ускоряет VACUUM и REINDEX отдельных партиций. См. руководство по партиционированию в PostgreSQL.
• Изменение модели данных – перенос часто меняющихся полей в отдельную таблицу «состояний» уменьшает churn основной.
• Актуальная статистика – на активных таблицах ужесточить autovacuum_analyze_scale_factor и повысить default_statistics_target при необходимости.

Итоги

HOT UPDATE — ваш главный союзник в PostgreSQL при интенсивных обновлениях. Чтобы он работал, исключите из индексов часто меняющиеся поля и оставьте место для новой версии строки через разумный fillfactor. Регулярный VACUUM, корректно настроенный autovacuum и дисциплина транзакций сохранят таблицы и индексы в форме. И, как всегда, измеряйте: смотрите на n_tup_hot_upd, оценку bloat, время запросов и общий I/O — и подбирайте параметры под вашу фактическую нагрузку.