Как работает fillfactor и как его настраивать вручную в PostgreSQL

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

Сегодня рассмотрим, как работает fillfactor в PostgreSQL — тот самый параметр, который никто не трогает, пока таблицы не начинают раздуваться как на дрожжах. Разберём, зачем он нужен, что происходит при UPDATE, когда стоит менять его вручную и как не наломать дров.

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

Ты можешь представить себе таблицу как стопку листов. На каждом листе — строки. А теперь представь, что тебе нужно вставить или обновить строку, но на листе больше нет места. Приходится создавать новый лист. И вот этот overhead, эта своебразная перепаковка страницы — она стоит дорого. Особенно в OLTP-нагрузках.

Вот тут полезен fillfactor.

fillfactor — это параметр, который говорит PostgreSQL: оставь немного места на каждой странице, пожалуйста, заранее. То есть если ты ставишь fillfactor = 80, то Postgres будет заполнять каждую страницу на 80%, оставляя 20% под будущие апдейты.

По дефолту fillfactor = 100. Т.е без запаса. А значит, если часто происходят UPDATE или HOT UPDATE, можно влететь в проблему — Postgres не сможет обновить строку “на месте” и вынужден будет делать tuple forwarding (и создавать новый tuple), что ведёт к фрагментации и росту таблицы.

Пример настройки:

CREATE TABLE users (   id serial PRIMARY KEY,   name text,   email text ) WITH (fillfactor = 80);

Такую штуку можно и менять post-factum:

ALTER TABLE users SET (fillfactor = 70);

Но это не перезаписывает существующие страницы. Только новые будут следовать новому правилу.

Так же у каждого индекса тоже есть свой fillfactor.

CREATE INDEX idx_email ON users(email) WITH (fillfactor = 90);

UPDATE строки может породить ещё и обновление всех связанных индексов. А значит, fillfactor стоит трогать не только на таблицах, но и на индексах.

Что происходит при UPDATE и как связан fillfactor

Теперь откроем капот. Допустим, ты делаешь вот такой запрос:

UPDATE users SET name = 'Artem 2.0' WHERE id = 1;

Если строка помещается на ту же страницу и никакие индексы не задеваются — Postgres пытается сделать HOT update. Это дешевенький способ обновления: он просто пишет новую версию строки на той же странице и помечает старую как dead tuple.

Но… чтобы это произошло, должно быть:

• достаточно места на текущей странице;

• не затронут ни один индекс (иначе нужно обновлять и его).

Если fillfactor = 100, то скорее всего места не будет. А значит будет обычный update:

• создается новый tuple на новой странице

• старый помечается как dead

• индекс обновляется

• таблица пухнет

Смотрим руками:

-- включаем track_io_timing для анализа SET track_io_timing = on;  -- делаем VACUUM FULL для чистоты VACUUM FULL users;  -- обновляем строку UPDATE users SET name = 'Test 123' WHERE id = 1;  -- смотрим, что произошло SELECT * FROM pg_stat_user_tables WHERE relname = 'users';

Или вообще можно использовать pgstattuple, чтобы увидеть степень фрагментации:

CREATE EXTENSION IF NOT EXISTS pgstattuple;  SELECT * FROM pgstattuple('users');

Если dead tuples — не очень, то fillfactor — хороший способ заранее расставить ловушки.

Когда стоит занижать fillfactor вручную

Не трогай fillfactor, если не уверен в профиле нагрузки. По дефолту он справедлив — 100%. Но вот несколько сценариев, где может спасти:

1. Частые UPDATE’ы фиксированных строк

2. Типичный кейс — статус заказа. Меняется несколько байт, но меняется часто.

3. Frequent UPSERT паттерны
   Особенно если UPSERT делают UPDATE чаще, чем INSERT.

4. Суровые OLTP-нагрузки
   Где каждый микросервис по 100 раз в секунду пишет в те же записи (а ты ещё решил shard’ить вручную, да?).

5. Частые UPDATE по jsonb / text
   Где строка может даже слегка вырасти.

И вот тут ты ставишь, скажем:

ALTER TABLE orders SET (fillfactor = 75);

И наблюдаешь, как VACUUM стал бегать реже, таблица меньше пухнет, а latency запросов падает.

Но обратная сторона: таблица станет больше в среднем на 25%. Плотность хранения упадёт. Значит — больше страниц, больше I/O.

Три кейса

Частые UPDATE по полю статуса

Таблица orders. Поля: id, status, created_at, updated_at. В течение жизни статус может меняться 5–10 раз. А status — это VARCHAR(20).

Решение:

CREATE TABLE orders (   id serial PRIMARY KEY,   status varchar(20),   created_at timestamptz,   updated_at timestamptz ) WITH (fillfactor = 80);

Почему 80:
20% страницы оставляем под «будущие жизни» строк. Этого хватает в большинстве случаев для 1–2 HOT-обновлений без создания новых tuple.

Таблица справочника, INSERT-heavy, UPDATE-редкий

Таблица products_catalog. В ней 10 миллионов строк. Новые товары добавляются каждый день, но обновление — раз в полгода.

Решение:

CREATE TABLE products_catalog (   id bigint PRIMARY KEY,   name text,   category text,   price numeric ) WITH (fillfactor = 100);

Почему 100:

Зачем оставлять свободное место, если обновлений почти нет? Мы хотим максимальную плотность, чтобы не тратить дисковое пространство зря. Это даст минимальный размер таблицы и лучший cache hit ratio по страницам.

А если поставим 80, то будем просто сжигать диск, увеличим размер таблицы на 20% — и ради чего? Ни latency, ни write amplification не улучшатся, наоборот.

Очередь задач, запись + удаление

Таблица jobs_queue. Каждая запись создаётся, обрабатывается и удаляется. Частые INSERT и DELETE, очень высокая скорость записи и удаления.

При частом DELETE страница остаётся с dead tuples, и без fillfactor Postgres не сможет их использовать под новые вставки.

Решение:

CREATE TABLE jobs_queue (   id serial PRIMARY KEY,   payload jsonb,   created_at timestamptz ) WITH (fillfactor = 70);

Почему 70:
Postgres будет держать свободное место, чтобы быстрее использовать его под новые записи.

Еще можно добавить индекс с меньшим fillfactor, если часто используем выборку по дате:

CREATE INDEX idx_jobs_created_at ON jobs_queue (created_at) WITH (fillfactor = 90);

---

Делитесь в комментариях, в каких кейсах вы вручную настраивали fillfactor, и что это вам дало. Уверен, у многих накопился интересный опыт — будет полезно всем.

---

Если вы читаете про fillfactor, значит, уже не боитесь заглянуть под капот PostgreSQL. А хотите пойти ещё дальше?

Приходите на открытые уроки, где эксперты разбирают реальные кейсы по оптимизации БД, индексам и работе с высокими нагрузками:

• Особенности реализации запросов в PostgreSQL — 9 июля в 20:00

• SQL против бардака в данных: поиск по шаблону и регулярные выражения — 15 июля в 20:00

• Мониторинг Postgres: Prometheus+Grafana — 23 июля в 20:00

А ещё — пройдите вступительное тестирование, чтобы понять, насколько вы готовы к полноценному обучению и подойдёт ли курс «PostgreSQL для администраторов баз данных и разработчиков» именно вам.