Зачем нужен 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 «Базы данных».