Bloat индексов PostgreSQL: как измерить и когда делать REINDEX

Индекс на 40 ГБ не обязательно раздут, а индекс на 2 ГБ не обязательно здоров. Размер показывает, сколько места занял файл, но не объясняет, чем заполнены его страницы и оправдан ли этот объём данными. Bloat — это часть физической структуры, которая уже не приносит пользы текущей нагрузке: мёртвые записи, пустое место в слабо заполненных страницах или страницы, оставшиеся после прежнего объёма данных.

Если пока непонятно, зачем индексу отдельный файл и как он участвует в плане запроса, сначала прочитайте разбор видов индексов и EXPLAIN. Здесь не повторяем выбор B-tree, GIN и составных ключей, а занимаемся уже существующим индексом в production.

SQL / 01koddo

Размер индекса — сигнал для проверки

Большой индекс и раздутый индекс — разные вещи

pg_relation_size честно возвращает размер основного файла индекса. Внутри могут быть полезные ключи для большой таблицы, внутренние страницы дерева, намеренно оставленное место из-за fillfactor, мёртвые записи и свободные участки. Функция не разделяет эти категории.

Поэтому единичный снимок отвечает только на вопрос «сколько места индекс занимает сейчас»:

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  pg_relation_size(indexrelid) AS index_bytes,
  idx_scan,
  last_idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

У двух одинаковых по размеру индексов вывод может быть противоположным. Один вырос вместе с таблицей и постоянно обслуживает запросы. Второй остался после массового удаления, почти не читается и вытесняет полезные страницы из кэша. Нужны история и контекст нагрузки, а не сравнение с чужим числом.

Сохраняйте index_bytes по расписанию и сопоставляйте его с размером таблицы, числом строк и изменениями приложения. Один всплеск после загрузки данных нормален; рост индекса при стабильном количестве живых строк уже даёт повод проверить страницы.

Откуда берётся свободное место

PostgreSQL работает по MVCC: UPDATE создаёт новую физическую версию строки, пока старую ещё могут видеть другие транзакции. Если обновление не проходит как HOT, новая версия получает новые записи в индексах таблицы. Некоторое время старая и новая записи существуют рядом. B-tree умеет удалять часть такого мусора сам и во время VACUUM, поэтому поток обновлений не означает автоматический рост файла.

Рост начинается, когда новые записи не помещаются на листовой странице. B-tree делит её и переносит часть ключей на новую страницу; иногда разделение поднимается по дереву выше. Это штатный механизм, а не повреждение индекса. Более низкий fillfactor даже оставляет место специально, чтобы сгладить будущие разделения страниц. Для B-tree значение по умолчанию равно 90, поэтому формула «100 минус плотность равно bloat» неверна уже на свежем индексе.

Удаление тоже не складывает оставшиеся ключи немедленно в минимальный файл. Полностью опустевшие страницы B-tree PostgreSQL сможет использовать повторно. Но если на каждой странице осталось по несколько ключей, страницы продолжают принадлежать индексу. Именно такой профиль — удалить почти все ключи в каждом диапазоне, но не все — документация PostgreSQL приводит как случай неэффективного использования места.

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

Что исправляет VACUUM, а что остаётся

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

Освободившееся место становится доступно для повторного использования внутри индекса. Но обычный VACUUM не уплотняет все редкие листовые страницы в новый компактный файл. Если цель — физически пересобрать индекс без прежних пустых и почти пустых страниц, для этого служит REINDEX.

Перед пересборкой проверьте, что VACUUM вообще успевает за нагрузкой — если не успевает, REINDEX даст лишь временный эффект и bloat вернётся; как настраивают autovacuum и что смотреть в его статистике, разобрано отдельно:

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup,
  n_dead_tup,
  n_tup_upd,
  n_tup_hot_upd,
  n_tup_del,
  last_autovacuum,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Большой n_dead_tup, давно не запускавшийся autovacuum или длинная транзакция, которая удерживает старый снимок, указывают сначала на проблему очистки. Значения n_live_tup и n_dead_tup приблизительные, а счётчики изменений накопительные: сравнивайте снимки за одинаковые интервалы. REINDEX уменьшит конкретный индекс, но не устранит причину, и рост повторится.

Первый уровень диагностики: системная статистика

pg_stat_user_indexes безопасен для регулярного мониторинга: он не обходит все страницы индекса. Помимо размера смотрите на idx_scan, last_idx_scan, idx_tup_read и idx_tup_fetch.

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  last_idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Эти счётчики описывают обращения, а не физическое состояние страниц. Нулевой idx_scan не доказывает ни bloat, ни бесполезность индекса: статистику могли недавно сбросить, индекс может обслуживать редкий критичный запрос или поддерживать ограничение. Кроме того, один узел плана способен выполнить несколько поисков по индексу, поэтому idx_scan не равен числу SQL-запросов.

Практичная задача этого уровня — составить короткий список кандидатов. В него попадают крупные индексы, которые растут быстрее таблицы, связаны с интенсивными UPDATE/DELETE и создают измеримую цену: больше чтений с диска, растущую задержку запросов или нехватку места. Маленькие стабильные индексы сканировать подробно незачем.

Второй уровень: pgstattuple и pgstatindex

pgstattuple — поставляемое вместе с PostgreSQL расширение, а не случайная формула из gist. Его устанавливают один раз в базу с подходящими правами:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Для B-tree функция pgstatindex показывает физический размер, число листовых, пустых и удалённых страниц, среднюю плотность листьев и их фрагментацию:

SELECT
  index_size,
  leaf_pages,
  empty_pages,
  deleted_pages,
  avg_leaf_density,
  leaf_fragmentation
FROM pgstatindex('public.orders_created_at_idx'::regclass);

pgstattuple для того же индекса отдельно показывает долю мёртвых записей и свободного места:

SELECT
  table_len,
  tuple_percent,
  dead_tuple_percent,
  free_percent
FROM pgstattuple('public.orders_created_at_idx'::regclass);

Обе функции собирают результат постранично и не дают мгновенный согласованный снимок: параллельные изменения успевают повлиять на цифры. Полный обход крупного индекса создаёт I/O-нагрузку. По умолчанию выполнение доступно роли pg_stat_scan_tables и суперпользователям; не выдавайте это право приложению ради дашборда. Запускайте подробную проверку для выбранного кандидата, желательно вне пика, и сравнивайте одинаковые метрики в динамике.

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

Для GIN, GiST, BRIN и Hash не переносите правила B-tree. У расширения есть отдельные функции для части методов, а официальная документация прямо отмечает, что bloat индексов не-B-tree исследован хуже. Базовая тактика для них — следить за физическим размером и производительностью на своей нагрузке.

Почему порог «20%» ничего не решает

В документации PostgreSQL нет правила «делать REINDEX после 20% bloat». Под этим процентом в разных скриптах скрываются разные величины: free_percent, доля мёртвых записей, отклонение от расчётного размера или рост относительно прошлого снимка. Сравнивать их как одну метрику нельзя.

Даже одинаковая доля свободного места имеет разную цену. Десять процентов индекса на 500 МБ — 50 МБ, а десять процентов индекса на 2 ТБ — около 200 ГБ. Первый может целиком помещаться в кэше и не влиять на запросы; второй может требовать лишних чтений и не оставлять места для резервного индекса при concurrent-пересборке. Решение определяют абсолютный объём, скорость роста, профиль нагрузки и доступный диск.

Наконец, часть свободного места заложена fillfactor. У B-tree по умолчанию листовые страницы при сборке заполняются до 90%, чтобы будущие вставки и обновления реже вызывали page split. Называть весь остаток bloat — значит считать нормальную настройку дефектом.

Когда REINDEX оправдан

REINDEX пересобирает индекс из данных таблицы и заменяет старую физическую структуру новой. Команда оправдана, если одновременно выполняются три условия:

1. Подробная проверка показывает много пространства в пустых или слабо заполненных страницах, а не только большой файл.
2. Абсолютный объём лишнего места заметен для диска, кэша или времени запросов.
3. После оценки обычного потока изменений индекс не вернётся к тому же размеру сразу после пересборки.

Для небольшой таблицы в окне обслуживания достаточно обычной команды:

REINDEX INDEX public.orders_created_at_idx;

Обычный REINDEX дешевле concurrent-варианта, но блокирует записи в родительскую таблицу. Из-за ACCESS EXCLUSIVE на индексе он также блокирует практически все новые запросы к таблице: планировщик пытается получить ACCESS SHARE для каждого её индекса, даже если итоговый план этот индекс не использует. В production это операция для согласованного окна, даже если ожидаемое время кажется коротким.

REINDEX CONCURRENTLY: меньше блокировок, больше работы

На горячей таблице используют concurrent-вариант:

REINDEX INDEX CONCURRENTLY public.orders_created_at_idx;

Он не удерживает блокировку, запрещающую обычные INSERT, UPDATE и DELETE на всё время работы. Цена — два прохода по таблице, ожидание транзакций, больше CPU и I/O и обычно более долгое выполнение. Одновременно на одной таблице допускается только одна concurrent-сборка индекса.

Старый и новый индексы некоторое время существуют вместе. До запуска убедитесь, что в табличном пространстве есть место как минимум ещё под один индекс сопоставимого размера, а также запас под WAL и временные файлы. Проверяйте свободное место на том хранилище, где физически лежит индекс, а не только общий объём диска сервера.

У команды есть ограничения:

• REINDEX CONCURRENTLY нельзя выполнять внутри блока транзакции;
• системные каталоги нельзя пересобирать concurrent-режимом;
• индексы, поддерживающие exclusion constraints, этим режимом не пересобираются;
• при ошибке может остаться индекс с суффиксом _ccnew или _ccold и статусом INVALID; он занимает место, а новый invalid-индекс ещё и создаёт накладные расходы на запись, пока его не удалить по инструкции из документации.

Прогресс обычной и concurrent-пересборки виден в pg_stat_progress_create_index:

SELECT
  pid,
  command,
  phase,
  blocks_done,
  blocks_total,
  tuples_done,
  tuples_total
FROM pg_stat_progress_create_index;

После команды повторите снимки размера и pgstatindex, а также тот же EXPLAIN (ANALYZE, BUFFERS) для проблемного запроса. Уменьшившийся файл без изменения latency тоже бывает полезен, если вернул место или улучшил попадание в кэш. Если не изменилось ничего значимого, периодическая пересборка такого индекса не нужна.

Как выбрать действие

Главное правило короткое: измеряйте не «процент bloat», а цену лишних страниц для этой системы. Тогда REINDEX перестаёт быть ритуалом по расписанию и становится редкой операцией с проверяемым результатом.