Место на диске съели индексы, а не данные: раздувание b-tree, которое autovacuum не лечит

Короткий ответ: если PostgreSQL занял диск, сначала разделите тело таблицы и её индексы — pg_relation_size против pg_indexes_size. При нагрузке из массовых вставок и удалений раздуваются именно индексы: b-tree освобождает только полностью опустевшие страницы, а полупустые оставляет в дереве навсегда. Плотность падает с дефолтных 90% до 13–15%, то есть индекс занимает вшестеро больше нужного. Autovacuum это не чинит ни при каких настройках — лечит только REINDEX. А привычный VACUUM FULL в такой ситуации опасен: он пишет вторую копию таблицы рядом и требует свободного места размером с неё.

Началось всё с алерта про диск на стенде. Закончилось тем, что «мало места» оказалось не проблемой места, а проблемой скорости: на проде нашёлся индекс с плотностью в районе 15%, по которому идёт основной поток чтения. Каждое обращение к нему разбирает страницы, заполненные на одну шестую.

Кластер тот же, что в истории про conflict with recovery и разборе bootstrap.dcs в Patroni: PostgreSQL 15 под Patroni, синхронная реплика, Laravel-приложение сверху. Там же описана и нагрузка, которая всё это создаёт, — крон, раз в четверть часа переписывающий крупную таблицу целиком.

Коротко (TL;DR)

• Диагноз: раздуты индексы при здоровом теле таблицы. Проверяется одним запросом — pg_relation_size (heap) отдельно от pg_indexes_size.
• Причина: b-tree переиспользует только полностью пустые страницы. Страница, с которой удалили все ключи кроме пары, остаётся в дереве занятой. PostgreSQL не сливает соседние разреженные страницы и не перебалансирует дерево.
• Мера: avg_leaf_density из pgstatindex. Ориентир — 90%, дефолтный fillfactor для b-tree. Всё, что заметно ниже, — раздувание.
• Лечение: REINDEX INDEX CONCURRENTLY по одному индексу. Пиковый оверхед — размер одного нового индекса, а не всей таблицы.
• Чего не делать: VACUUM FULL ради индексов. Он пишет новую копию таблицы и не отпускает старую до конца операции — при нехватке места добьёт диск в ноль.
• Грабля, которая портит данные: уникальный индекс с idx_scan = 0 не мёртвый. Проверка уникальности при вставке — не сканирование, счётчик она не трогает.
• Прогноз выигрыша: размер × плотность / 90. На практике попадает в единицы процентов.

Почему «база распухла» — это два разных диагноза

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

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

Тело на четыре гигабайта при двух десятках миллионов строк — норма, тут лечить нечего. Вся масса в индексах, соотношение примерно шесть к одному. Разделяющий запрос занимает одну строку:

1
2
3
4
5
6
7
8

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS heap,
       pg_size_pretty(pg_indexes_size(relid))        AS idx,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

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

This method also requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete.

Вот тут и вылезает арифметика, из-за которой очевидный путь закрыт. На модельном стенде диск на 100 ГБ, занято 80, свободно 20. Таблица — 30. Свободного места меньше, чем размер таблицы, а VACUUM FULL требует положить рядом полную копию. Он не «долго отработает», он упрётся в ноль свободных байт на середине и оставит базу в интересном состоянии.

REINDEX идёт по одному индексу: пиковый оверхед — размер одного нового индекса, и место возвращается после каждого шага. Разница между «нужно 30 ГБ разом» и «нужно 4 ГБ на шаг» — это разница между «нельзя» и «можно прямо сейчас».

Autovacuum не сломан, и это самое контринтуитивное

Место не возвращается не потому, что autovacuum не работает, а потому что он в принципе не делает того, что здесь нужно. Инстинкт подсказывает обратное, и я на него потратил время: полез проверять, не отключён ли автовакуум, нет ли зависшей транзакции, нет ли забытого репликационного слота, удерживающего horizon.

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

Дальше стало понятно, почему. Тут работают три независимых механизма, и все три — штатное поведение, а не поломка.

Первый: VACUUM освобождает место внутри файла, а не отдаёт его операционной системе. Документация формулирует это с единственным исключением:

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained.

«pages at the end of a table» — ключевое. При хаотичной перезаписи пустые страницы разбросаны по всему файлу, а не собираются в хвосте. Условие не выполняется практически никогда.

Второй, и это корень: b-tree не сливает полупустые страницы. Раздел «Routine Reindexing» описывает ровно наш случай:

B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility of inefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated. Therefore, a usage pattern in which most, but not all, keys in each range are eventually deleted will see poor use of space. For such usage patterns, periodic reindexing is recommended.

Обратите внимание на точность формулировки: полностью опустевшие страницы переиспользуются. А вот страница, с которой удалили все ключи кроме пары, остаётся занятой — и такой останется навсегда. PostgreSQL не объединяет соседние разреженные страницы и не перебалансирует дерево. Плотность падает и не восстанавливается. Никакая настройка автовакуума этого не меняет, потому что автовакуум тут вообще ни при чём.

Третий — акселератор: HOT-обновления не работают там, где нужнее всего. Если UPDATE не трогает индексируемых колонок и на странице есть место, PostgreSQL обновляет строку, не касаясь индексов. На горячей таблице пересчёта доля HOT округляется до 0.0%: на миллиард обновлений быстрым путём проходят единицы миллионов. Значит каждое обновление дописывало новую запись в каждый индекс таблицы — а их там не один и не два.

Механизм два объясняет, почему раздувание не рассасывается. Механизм три — почему оно накапливается так быстро.

Есть и четвёртый фактор, из-за которого часть таблиц не обслуживается вовсе. autovacuum_vacuum_scale_factor по умолчанию равен 0.2 — документация описывает его как «a fraction of the table size to add to autovacuum_vacuum_threshold», и уточняет: «The default is 0.2 (20% of table size)». Возьмём таблицу на десять миллионов строк: порог — два миллиона мёртвых строк. Накопилась четверть миллиона. До порога далеко, autovacuum_count остаётся нулём, и таблица не вакуумировалась ни разу за всё время жизни. Не потому что сломалось, а потому что так настроено по умолчанию.

Как измерить раздувание, а не гадать по размеру

Мерить надо плотность листовых страниц, и у неё есть точный публичный ориентир. avg_leaf_density из pgstatindex показывает, насколько плотно упакованы листья b-tree. Эталон — 90%, и это не эмпирическое наблюдение, а дефолт: документация CREATE INDEX говорит «B-trees use a default fillfactor of 90». Свежепостроенный индекс набивает листья ровно до этого значения.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT i.indexrelname,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS sz,
       round(s.avg_leaf_density::numeric, 1) AS density
FROM (SELECT indexrelid, indexrelname
      FROM pg_stat_user_indexes
      ORDER BY pg_relation_size(indexrelid) DESC
      LIMIT 20) i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam AND am.amname = 'btree',
LATERAL pgstatindex(i.indexrelid) s
ORDER BY s.avg_leaf_density ASC;

Зная эталон, выигрыш считается заранее: размер × плотность / 90. Индекс на гигабайт с плотностью 15% ужмётся примерно до 170 МБ. На стенде я сверил прогноз с фактом — разошлось на единицы процентов, чего для планирования более чем достаточно.

Два предостережения по замеру. Во-первых, pgstatindex полностью сканирует индекс, и на лидере под нагрузкой это заметно; лучше снимать на реплике — файлы там те же, а расширение приезжает репликацией. Во-вторых, фильтр по amname = 'btree' обязателен: для не-b-tree индексов функция даст либо ошибку, либо бессмысленный результат, да и документация честно признаёт, что раздувание в них исследовано плохо.

Уникальный индекс с нулём обращений — не мёртвый

Самая дорогая ошибка в этой истории — та, которую я почти совершил: удалить индексы с idx_scan = 0 как неиспользуемые. На горячей таблице таких набралось больше половины от всех её индексов, окно статистики — больше полугода. Соблазн очевидный.

Часть из них оказалась уникальными, подпирающими ограничения целостности. Причина расхождения — в определении счётчика. Документация описывает idx_scan как «Number of index scans initiated on this index»: он считает сканирования. А проверка уникальности при вставке — не сканирование, это отдельный путь внутри вставки в индекс. Счётчик она не трогает никогда.

Получается индекс, который по всем метрикам мёртв, а фактически единственный не пускает дубликаты в расчёты. Удали его — и данные начнут портиться молча, а заметят это через недели, когда сойдутся не те цифры.

Поэтому в запросе поиска неиспользуемых индексов фильтр по уникальности обязателен:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT i.relname AS tbl, i.indexrelname AS idx,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS sz, i.idx_scan
FROM pg_stat_user_indexes i
JOIN pg_index x ON x.indexrelid = i.indexrelid
WHERE i.idx_scan = 0
  AND NOT x.indisunique          -- без этой строки запрос опасен
  AND pg_relation_size(i.indexrelid) > 5 * 1024 * 1024
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- ноль без окна наблюдения не значит ничего
SELECT stats_reset, now() - stats_reset AS window
FROM pg_stat_database WHERE datname = current_database();

Рядом живёт вторая ловушка того же класса: счётчики pg_stat_* ведутся на каждом узле отдельно. Реплика считает свои сканирования сама, и если часть чтения уходит на неё, лидер об этом не знает. Я проверил лидер и один standby, взятый из инвентаря, — и молча предположил, что standby единственный. Вывод оказался верным, но обоснован он не был. Список узлов надо брать из самой базы:

1

SELECT client_addr, state, sync_state FROM pg_stat_replication;

Сначала перечислить подписчиков запросом, потом обойти каждого — и только после этого говорить «индекс не используется».

Грабли при самой перестройке

Перестройка на живом кластере ломается не на SQL, а на обвязке вокруг него — на Patroni, на миграциях приложения и на синхронной реплике. Четыре места, где я споткнулся или мог споткнуться.

REINDEX CONCURRENTLY оставляет мусор при сбое, и суффикс говорит, что делать. Если перестройка упала, в базе остаётся невалидный индекс. Документация различает два случая: суффикс _ccnew — это недостроенный новый индекс, его надо удалить и повторить попытку; суффикс _ccold — это старый, который не смогли удалить, и тогда перестройка уже удалась, надо просто дропнуть остаток. Путать их дорого: во втором случае «повторю REINDEX» — лишняя работа на часы. К суффиксу может добавляться цифра (_ccnew1, _ccold2). Проверка невалидных индексов должна стоять первым шагом любой автоматизации:

1
2
3

SELECT c.relname
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

Patroni затрёт ALTER SYSTEM. Параметр применили, SHOW подтверждает, после переключения значение прежнее. Patroni сам управляет postgresql.conf и перезаписывает его из своей конфигурации в DCS. Менять надо через patronictl edit-config — подробнее об этом слое в разборе bootstrap.dcs.

Laravel оборачивает миграцию в транзакцию, и только на PostgreSQL. DROP INDEX CONCURRENTLY внутри транзакционного блока запрещён, и миграция падает. В коде миграции этого не видно, потому что решение принимается во фреймворке: в Migration.php объявлено public $withinTransaction = true, а мигратор проверяет пару условий сразу:

1
2
3
4

$this->getSchemaGrammar($connection)->supportsSchemaTransactions()
    && $migration->withinTransaction
        ? $connection->transaction($callback)
        : $callback();

Тонкость в первом условии. PostgresGrammar объявляет protected $transactions = true, а MySqlGrammar этого свойства не переопределяет. То есть одна и та же миграция на MySQL пройдёт, а на PostgreSQL упадёт — и разработчик, который тестировал локально не на той СУБД, узнает об этом на деплое. Лечится одной строкой в классе миграции:

1

public $withinTransaction = false;

Синхронная реплика превращает перестройку в задержки приложения. REINDEX выглядит локальной операцией на лидере, но порождает WAL объёмом примерно с сам индекс, а при synchronous_commit = on каждый коммит ждёт подтверждения от standby. Перестройка многогигабайтного индекса гонит этот объём по сети, и время отклика приложения растёт. Отсюда — по одному индексу, в непиковое окно, с проверкой лага между шагами и с оглядкой на wal_compression.

Отдельно про WAL: после серии перестроек каталог pg_wal разбухнет, и CHECKPOINT его не ужмёт. Я попробовал — не сработало, и не должно было. Если max_wal_size выставлен, скажем, в 4 ГБ, каталог упрётся ровно в этот потолок: это не протечка, а штатное поведение. PostgreSQL держит сегменты преаллоцированными и сжимает каталог постепенно, при спаде нагрузки. Гоняться за этими гигабайтами бессмысленно.

Два одинаковых индекса под разными именами

Если на таблице два уникальных индекса по одному набору колонок, а обращения идут только к одному — это, скорее всего, дубликат, но проверять надо не по списку колонок. Картина выглядит так: у одного индекса сканирования идут миллиардами, у другого ровно ноль.

Разный порядок колонок в UNIQUE сбивает с толку, хотя для уникальности он не значит ничего: UNIQUE (a, b) и UNIQUE (b, a) запрещают ровно одни и те же дубликаты. Но и совпадение колонок ещё не делает индексы одинаковыми. Сравнивать надо весь набор свойств:

1
2
3
4
5
6
7

SELECT i.relname, x.indisunique, x.indnullsnotdistinct,
       x.indnatts, x.indnkeyatts,
       x.indcollation::text, x.indclass::text, x.indoption::text,
       (x.indpred IS NOT NULL) AS partial,
       (x.indexprs IS NOT NULL) AS expr
FROM pg_index x JOIN pg_class i ON i.oid = x.indexrelid
WHERE x.indrelid = 'имя_таблицы'::regclass;

Что здесь важно и почему: indclass — классы операторов, indcollation — сортировки, indoption — направление и NULLS FIRST/LAST, indnkeyatts против indnatts покажет спрятанный INCLUDE, indpred и indexprs выдадут частичный индекс или индекс по выражению, а indnullsnotdistinct (появился в PostgreSQL 15) — трактовку NULL в уникальности. Если совпало всё, индексы действительно взаимозаменяемы — и удалять безопасно любой из двух, включая тот, на который приходятся все сканирования: планировщик прозрачно перейдёт на близнеца. Проверьте заодно attnotnull на колонках — при NOT NULL вопрос NULL-семантики снимается совсем.

И последняя отложенная грабля: удалённый индекс вернётся с ближайшим деплоем, если удаление сделано руками в базе, а объявление осталось в миграции или в схеме для тестов. Перед удалением — поиск имени по репозиторию, само удаление — только миграцией.

Что сделать, по шагам

Порядок такой, чтобы дорогие и необратимые шаги стояли после дешёвых и проверяемых.

1. Разделите тело и индексы запросом выше. Если раздут heap — это другая болезнь и другое лечение.
2. Поставьте pgstattuple и снимите avg_leaf_density по крупнейшим b-tree индексам. Замер делайте на реплике.
3. Посчитайте выигрыш: размер × плотность / 90. Так вы поймёте, стоит ли овчинка выделки, ещё до первой блокировки.
4. Проверьте окно статистики через stats_reset. Без него нули в idx_scan не значат ничего.
5. Найдите неиспользуемые индексы с фильтром AND NOT indisunique. Уникальные оценивайте только по бизнес-смыслу, не по счётчику.
6. Обойдите все узлы кластера, перечислив их через pg_stat_replication, а не по инвентарю.
7. Перестраивайте по одному: REINDEX INDEX CONCURRENTLY на проде, обычный REINDEX на стендах. Между шагами смотрите лаг реплики.
8. Заведите мониторинг плотности, а не только свободного места. К моменту дискового алерта индексы деградируют уже месяцами.

Пункт 8 стоит последним, а по важности он первый: место — это симптом, который заметил мониторинг, а не болезнь.

Итог

Раздувание индексов — не поломка и не недосмотр администратора, а осознанный компромисс PostgreSQL. Слияние полупустых страниц b-tree стоило бы блокировок и перебалансировок на горячем пути, и его сознательно не делают. Отсюда единственный корректный вывод: перестройку индексов надо планировать, как планируют бэкапы, а не ждать, что автоматика справится сама.

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

И про метрики. idx_scan = 0 на уникальном индексе — хороший пример того, как метрика не знает о смысле. Она честно отвечает на свой вопрос («сколько было сканирований»), а мы читаем в ней ответ на другой («нужен ли этот индекс»). Прежде чем удалять что-то по счётчику, стоит выяснить, что именно счётчик считает — и, главное, чего он не считает.

Первоисточники: Routine Reindexing, Routine Vacuuming, REINDEX, VACUUM, параметры autovacuum, CREATE INDEX и fillfactor, pgstattuple, Monitoring Statistics, Migration.php в laravel/framework, PostgresGrammar.php.