В предыдущих уроках мы строили индексы и наблюдали их с лучшей стороны. В этом — финальная глава модуля — посмотрим, что с индексом происходит со временем: как он деградирует, как Postgres его поддерживает, и что делать, когда обычной поддержки не хватает. Тема для каждого DBA болезненная: «индекс на 50 GiB, тормозит» — частая причина инцидентов.

Откуда берётся bloat в индексе

Index bloat — это накопление мёртвого пространства в индексе. Источники:

1. DELETE. При удалении строки запись в индексе сразу не удаляется — она помечается как dead (через флаг). Реально освобождается только при VACUUM. До VACUUM лист физически содержит и dead, и live records.

2. UPDATE без HOT. Если UPDATE меняет колонку, по которой есть индекс, Postgres создаёт новый heap-tuple + новую запись в индексе. Старая запись в индексе остаётся как dead. Это удваивает рост индекса на каждое такое UPDATE.

3. Page splits после bursting’а INSERT. Когда страницы делятся пополам при random insert (см. урок 3), мы получаем half-empty страницы. После последующих delete’ов и updates они становятся ещё разреженнее.

4. Не дошедший VACUUM. Если autovacuum отстаёт от нагрузки (типично на heavy-write таблицах), dead-tuples копятся, индекс продолжает «надуваться».

В результате через несколько месяцев продакшен-нагрузки индекс может быть в 2-5 раз больше, чем оптимальный. Каждый Index Scan читает лишние страницы. Каждый split становится дороже. Buffer cache забивается dead-tuples.

Что VACUUM делает с индексом

1. Сбор TID мёртвых heap-tuples. Сканируется heap, накапливается массив TID, которые помечены как dead.
2. Сканирование индексов. Для каждого индекса проходим лист за листом, и в каждом сравниваем t_tid записей с массивом dead TID. Если TID есть в массиве — отмечаем index tuple как dead (флаг LP_DEAD) или удаляем (если включено index_cleanup).

Это полный скан индекса, даже если dead-tuples — 0.1%. Что важно понимать:

• VACUUM с большими таблицами и индексами дорог — потому что один проход по всему индексу обязателен.
• Postgres 14 ввёл bypass index cleanup: если dead-tuples < 2% от размера таблицы, индексы не сканируются. Это огромная экономия для горячих таблиц.
• VACUUM не уплотняет страницы индекса. Лист с 100 dead-tuples и 50 live остаётся с теми же занятыми 150 слотами. Освобождается только TID на удаление, а не место на странице.

Что вакуум не делает

• Не уменьшает размер индекса. Файлы остаются того же размера.
• Не пересобирает страницы. Half-empty leaf останется half-empty.
• Не возвращает место операционной системе (кроме редких случаев truncation при VACUUM FULL).

Чтобы реально вернуть место и уплотнить — нужен REINDEX или VACUUM FULL (последний блокирует таблицу).

REINDEX vs REINDEX CONCURRENTLY

REINDEX пересоздаёт индекс с нуля: читает все live-данные из heap, строит новый B+tree, заменяет старый. Размер становится оптимальным (с применением suffix truncation, deduplication, fillfactor), плотность листьев максимальна.

Проблема обычного REINDEX: он берёт ACCESS EXCLUSIVE lock на таблицу. Никаких SELECT и DML, пока пересборка не закончится. На терабайтной таблице — часы простоя. В продакшене так нельзя.

Postgres 12 добавил

1. Создаёт новый «invisible» индекс рядом со старым.
2. Сканирует таблицу, заполняет новый индекс (с lock SHARE, не блокирующий чтение/запись).
3. Дожидается завершения всех старых snapshot’ов.
4. Атомарно переключает имя: новый становится боевым, старый помечается как INVALID.
5. Удаляет старый.

В процессе таблица доступна для чтения и записи. Минимальный momentary lock — на момент атомарного swap’а, миллисекунды.

Минусы:

• В 2-3x медленнее обычного REINDEX (потому что нужно отслеживать одновременные DML).
• Требует двойное место на время операции (старый + новый индексы существуют параллельно).
• В случае ошибки остаётся INVALID индекс — его надо вручную удалить и попробовать снова.

В продакшене всегда используй REINDEX CONCURRENTLY. Исключение — recovery-сценарии и одноразовые миграции.

Когда нужен REINDEX

Не всегда. Хороший autovacuum + правильный access pattern → индексы остаются здоровыми годами. Реальные триггеры для REINDEX:

1. pgstattuple.pgstatindex(idx) показывает avg_leaf_density < 50%.
2. Размер индекса значительно больше теоретического. Считаешь rows × avg_entry_size, сравниваешь с pg_relation_size. Bloat ratio > 2x — кандидат на REINDEX.
3. Random insert pattern + миграция на sequence (см. урок 3) — старый bloat не уйдёт сам.
4. Major upgrade (PG 11 → PG 13+): чтобы получить suffix truncation и deduplication, нужно пересоздать индексы.

Диагностика: pgstattuple

pgstattuple — расширение, доступное в стандартной поставке. Подключается через CREATE EXTENSION pgstattuple.

Что смотрим:

• idx_scan = 0 — индекс никем не используется. Кандидат на DROP.
• idx_scan высокий, idx_tup_read ≈ idx_tup_fetch — индекс эффективен (мало false positives на heap fetch).
• idx_tup_read >> idx_tup_fetch — индекс отдаёт много строк, которые потом отбрасываются на heap-stage. Возможно, нужен другой индекс или partial.

Если есть pgstattuple:

SELECT * FROM pgstatindex('orders_pkey');

Ключевые поля:

• version — версия страницы B-tree.
• tree_level — высота дерева (0 = только root).
• index_size — размер в байтах.
• internal_pages, leaf_pages, empty_pages, deleted_pages — распределение по типам.
• avg_leaf_density — средняя плотность листьев в процентах. 70-90% — здоровый индекс, 40-60% — пора REINDEX.
• leaf_fragmentation — степень фрагментации. 0% — идеально, >50% — плохо (много random I/O при range scan).

DROP неиспользуемых индексов

Часто в проектах накапливаются индексы, которые никто не использует — наследие от старых фичей, экспериментов, миграций. Они занимают место и замедляют INSERT (каждый INSERT обновляет все индексы).

Запрос на «найди неиспользуемые индексы»:

SELECT
  schemaname || '.' || indexrelname AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'  -- не трогаем primary keys
ORDER BY pg_relation_size(indexrelid) DESC;

Но не дроп’ай сразу. Statistics сбрасываются при pg_stat_statements_reset() и при restart. Перед DROP проверь, что idx_scan = 0 держится минимум неделю на production. Иначе можешь случайно дропнуть индекс, который используется раз в месяц для отчёта.

Page deletion: что освобождает VACUUM

Postgres различает два состояния «удалённой» index-страницы:

1. BTP_HALF_DEAD — страница помечена к удалению, но ещё ссылается родитель. Никто новых данных туда не пишет, старые данные ещё доступны.
2. BTP_DELETED — полностью удалена, освобождена. Ссылок из родителя нет, она в free space map и может быть переиспользована.

Между этими двумя состояниями проходит несколько фаз VACUUM. Если страница потеряла все live-записи (все TID в ней оказались dead), VACUUM сначала помечает её half-dead, потом отслеживает, что все активные snapshot’ы её больше не видят, и только потом окончательно удаляет.

Удалённые страницы переиспользуются для следующих splits. То есть индекс не растёт «бесконечно» — освобождённое место будет занято. Но и не сжимается: файл индекса не truncate-ится, пустые страницы в конце файла не возвращаются ОС. Только REINDEX даёт сжатие физического файла.

Эта механика делает VACUUM «дешёвой поддержкой»: если у тебя нагрузка относительно стабильна (равное число live и dead), индекс будет колебаться около устойчивого размера благодаря переиспользованию.

VACUUM FULL: тяжёлая артиллерия

VACUUM FULL table — переписывает таблицу и все её индексы с нуля. Эффект — как REINDEX для всех индексов одновременно + уплотнение heap. Берёт ACCESS EXCLUSIVE lock на таблицу. Время = время полной перезаписи таблицы.

В продакшене обычно не применяется. Альтернатива — pg_repack (extension), который делает то же самое без долгой блокировки. На больших таблицах pg_repack — единственный реалистичный способ уплотнить bloated heap.

Демонстрация: смотрим VACUUM в действии

pk_before и pk_after обычно одинаковые — VACUUM не уплотняет страницы. Чтобы реально уменьшить размер, нужен REINDEX.

Если до REINDEX было ~3 MiB, после может стать ~1.5 MiB. Это и есть «возврат пространства».

Autovacuum: дефолтные пороги

Autovacuum запускается, когда:

• n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup (по умолчанию 50 + 0.2 × N).

То есть для таблицы в 1M строк autovacuum запустится после ~200К dead-tuples. На горячих таблицах это слишком редко — типичная настройка autovacuum_vacuum_scale_factor = 0.05 (5%) для критичных таблиц через ALTER TABLE.

Postgres 13 добавил autovacuum_vacuum_insert_threshold — порог по числу вставок (не только delete/update). Это важно для append-only таблиц: на них не запускался VACUUM (нет dead-tuples), но visibility map устаревала. Теперь после autovacuum_vacuum_insert_threshold = 1000 (или scale) запустится VACUUM, который обновит VM — критично для Index Only Scan.

Runbook: подозрение на bloat

Приведу мысленный runbook, который можно применять, когда подозреваешь bloat. Шаги в порядке безопасности:

1. Подтвердить наличие bloat.

-- проверь использование индекса
SELECT idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'events_ts_idx';

-- pgstattuple (если установлено)
SELECT * FROM pgstatindex('events_ts_idx');

Смотри avg_leaf_density. Меньше 60% — bloat подтверждён. Больше 80% — индекс здоров.

2. Проверить, что autovacuum работает.

SELECT relname, last_autovacuum, last_vacuum, n_live_tup, n_dead_tup,
       n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'events';

last_autovacuum старше 24-48 часов на горячей таблице — autovacuum отстаёт. Поправь autovacuum_vacuum_scale_factor для конкретной таблицы.

3. REINDEX CONCURRENTLY.

REINDEX INDEX CONCURRENTLY events_ts_idx;

На больших индексах это может занять часы. Запускай в окно низкой нагрузки. Мониторь pg_stat_progress_create_index — он показывает прогресс в реальном времени (PG 12+).

4. Verify.

SELECT pg_size_pretty(pg_relation_size('events_ts_idx'));
SELECT * FROM pgstatindex('events_ts_idx');

Размер уменьшился? avg_leaf_density поднялась до 80-90%? — успех.

5. Тюнинг на будущее.

Если bloat быстро возвращается — корень в access pattern. Что делать:

• Усилить autovacuum (scale_factor 0.05 или ниже).
• Понизить fillfactor (резерв под inserts).
• Рассмотреть партиционирование (для time-series данных).
• Заменить UUID v4 на UUID v7 или BIGSERIAL (см. урок 3).

pg_repack: production-friendly альтернатива

pg_repack — extension от NTT, который делает «VACUUM FULL без долгого lock’а». Принцип работы:

1. Создаёт «теневую» таблицу с тем же схемой.
2. Триггер на оригинальной таблице копирует все INSERT/UPDATE/DELETE в теневую.
3. Бэкграунд-процесс копирует существующие данные в теневую.
4. После завершения копирования — короткий exclusive lock на момент swap’а.
5. Старая таблица дропается.

Эффект — heap и индексы полностью пересобраны, без долгого даунтайма. Минусы: требует двойное место, нагружает I/O в процессе, иногда сбоит на сложных схемах (foreign keys, materialized views).

Аналогичный механизм встроен в Postgres через CLUSTER — но он берёт ACCESS EXCLUSIVE и применим только в окне maintenance.

Чек-лист

• Index bloat копится от DELETE, UPDATE без HOT, page splits, отставшего autovacuum.
• VACUUM удаляет dead-tuples из индекса (помечает или удаляет), но не уплотняет страницы и не возвращает место.
• REINDEX пересоздаёт индекс с нуля, применяя suffix truncation/dedup/fillfactor. Блокирует таблицу.
• REINDEX CONCURRENTLY (PG 12+) — то же без блокировки. Стандарт для production.
• Диагностика: pgstattuple.pgstatindex() → avg_leaf_density. Здоровый индекс: 70-90%, требует REINDEX: меньше 60%.
• pg_stat_user_indexes.idx_scan = 0 ≥ неделя — кандидат на DROP.
• На append-only таблицах VACUUM нужен для visibility map (PG 13+ — autovacuum_vacuum_insert_threshold).
• pg_repack — production-friendly альтернатива VACUUM FULL для уплотнения heap.