Зачем нужен fillfactor в PostgreSQL

Привет, Хабр!

Один параметр PostgreSQL может похоронить вашу производительность, если вы о нём забудете — это fillfactor. Почему однократная настройка числа приводит к неожиданным page split, bloat и мучительному откату запросов? Давайте разбираться.

Что такое fillfactor и зачем он нужен

fillfactor — это параметр хранения, задающий, на сколько процентов должна быть заполнена каждая страница при вставке строк в таблицу или индекс. Значение может быть от 10 до 100, где 100 означает «заполнить страницу полностью» без оставления свободного пространства для апдейтов.

По дефолту у таблиц fillfactor=100, а у B‑tree индексах — fillfactor=90. Такой подход в целом оптимален для почти неизменяемых данных, но оборачивается против вас, когда строки часто обновляются.

Почему плотность заполнения влияет на UPDATE

Когда в PostgreSQL выполняется UPDATE, старый кортеж помечается мёртвым, а новая версия пишется на свободное место (если оно есть) или на новую страницу. Если страницы полностью заполнены (fillfactor=100), для каждой обновлённой строки:

1. Page split. При нехватке места страница делится пополам, создаётся новая, и часть кортежей уходит туда.

2. Рост количества страниц. Таблица и связанные индексы раздуваются, ведь каждая страница может быть заполнена лишь частично.

3. Увеличение I/O и времени сканирования. Объём физически хранимых страниц растёт, что тормозит seq scan и индексные операции.

В итоге ваши UPDATE превращаются в медленные процедуры, а место на диске улетает в воздух без шанса на восстановление.

HOT-updates:

PostgreSQL умеет оптимизировать UPDATE, если новая версия кортежа помещается на ту же страницу — это HOT‑update. При настройке fillfactor ниже 100 вы оставляете буфер для таких апдейтов, и новая версия строки пишетcя рядом, без изменения индексов и page split.

Когда HOT‑update срабатывает:

• Таблица использует MVCC и старая версия не видна активным транзакциям.

• Новая запись помещается на ту же страницу, где лежала старая.

• Индексные ключи не меняются (иначе придётся обновлять и индекс).

Для сильно обновляемых таблиц fillfactor=70–90 — адекватное и практическое решение.

Как задать fillfactor

При создании таблицы

CREATE TABLE orders (
    order_id   SERIAL PRIMARY KEY,
    status     TEXT,
    updated_at TIMESTAMPTZ
) WITH (
    fillfactor = 85
);
-- Таблица будет заполняться лишь на 85%, оставляя 15% на HOT-updates.

Для существующей таблицы

ALTER TABLE orders
    SET (fillfactor = 85);

-- Необходимо выполнить полную перезапись, чтобы параметр вступил в силу:
VACUUM FULL orders;

Для индекса

ALTER INDEX idx_orders_updated
    SET (fillfactor = 90);

-- Перестроим индекс без блокировки на запись:
REINDEX INDEX CONCURRENTLY idx_orders_updated;

Страница будет иметь место для новых версий кортежей и снизят число page split.

Влияние на autovacuum и индексный bloat

fillfactor тесно связан с механизмом autovacuum:

При низком fillfactor меньшее число мёртвых кортежей появляется на странице, и autovacuum может пропустить её, снижая накладные расходы на уборку. Однако слишком низкий fillfactor увеличивает общее число страниц, и autovacuum должен сканировать их все, что замедляет обслуживание.

Индексный bloat тоже зависим от fillfactor.

Если вы оставите мало свободного места (>95), при массовых апдейтах индексы будут часто дробиться, увеличивая глубину B‑tree и замедляя индексные сканирования. Оптимальный баланс в районе 85–90 помогает сохранить индекс компактным и снизить фрагментацию.

Мониторинг и диагностика bloat

Чтобы понять, насколько ваша таблица раздутa, используйте стандартные представления и расширения:

Быстрый просмотр метрик обновлений

SELECT
  schemaname, relname,
  n_tup_upd, n_tup_hot_upd,
  n_live_tup, n_dead_tup
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 10;

Здесь n_tup_hot_upd показывает число HOT‑update, а n_dead_tup — сколько мертвых строк ожидают уборки.

Оценка реального bloat через pgstattuple

-- Установите расширение, если ещё не установлено:
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Запрос оценки bloat для конкретной таблицы:
SELECT
  table_len, tuple_len, dead_tuple_len,
  ROUND(100 * dead_tuple_len / table_len::numeric, 2) AS dead_pct
FROM pgstattuple('public.orders');

dead_pct > 20% — явный сигнал к увеличению частоты VACUUM или пересмотру fillfactor.

Скрипты для общей картины bloat

Можно запланировать еженедельный job, который сканирует все таблицы через pgstattuple_approx или аналитические запросы на основе статистики; это предпочтительнее полного сканирования всех страниц каждый раз.

Прочие нюансы

1. Анализируйте паттерн нагрузки. Если таблица более 10% операций — UPDATE, задумайтесь о fillfactor=85–90.

2. Не переборщите с низким fillfactor. Для редко обновляемых таблиц fillfactor=100 эффективнее по дисковому пространству и кэшу.

3. Комбинируйте с настройками autovacuum:

   ALTER TABLE orders
     SET (
       autovacuum_vacuum_threshold = 1000,
       autovacuum_vacuum_scale_factor = 0.05
     );

   Получим запуск VACUUM после 5% изменений или 1000 модификаций whichever earlier

4. Реинжекции индексов. Для таблиц с очень большим индексным bloat периодически выполняйте:

   REINDEX TABLE orders CONCURRENTLY;

Заключение

Fillfactor — это простой способ удержать таблицы с частыми обновлениями. Смотрите статистику в pg_stat_all_tables и pgstattuple, подбирайте fillfactor вместе с autovacuum и будет вам счастье. А какой у вас опыт настройки этого параметра?

---

Блокировки в PostgreSQL и MS SQL Server могут стать причиной серьёзных проблем с производительностью и стабильностью. На открытом уроке 20 мая мы разберём типы блокировок, как предотвращать дедлоки и минимизировать их влияние. Примеры, лучшие практики и инструменты диагностики помогут вам повысить эффективность работы с СУБД. Присоединяйтесь, чтобы улучшить навыки управления блокировками.

А освоить все необходимые навыки для профессиональной работы с реляционными и нереляционными БД можно на онлайн-курсе Otus «Базы данных».