Обслуживание индексов: bloat, reindex, pg_stat

Индекс на таблице orders вырос до 40 GB, хотя сама таблица занимает 8 GB. Запросы по статусу заказа стали втрое медленнее за полгода - и никто не понял почему, пока не посмотрел на bloat ratio 5x.

• **Shopify** регулярно обнаруживает на high-write таблицах (inventory, orders) bloat 300-500% после 6 месяцев работы без обслуживания - REINDEX CONCURRENTLY в maintenance window снижает p99 latency запросов с 800ms до 200ms.
• **GitLab** при аудите БД нашёл 47 неиспользуемых индексов суммарным объёмом 1.2 TB на ci_builds - после удаления INSERT throughput вырос на 23% и autovacuum перестал отставать.
• **Heroku Postgres** автоматически отправляет алерт когда индекс не использовался более 14 дней и занимает более 1 GB - это позволяет командам экономить десятки гигабайт в месяц без ручного аудита.
• **Notion** после добавления B-tree deduplication (PG13) на индексах `block_type` и `space_id` уменьшил суммарный размер индексов на 35% - экономия 800 GB на prod кластере из 12 шардов.

Index Bloat

**Index bloat** - это «разбухание» индексного файла: страницы, которые раньше хранили живые записи, теперь заняты мёртвыми (dead) тьюплами, но место не возвращается. Каждый UPDATE или DELETE оставляет старую версию строки в heap и соответствующую запись в индексе; VACUUM убирает их из heap, но индексные страницы при этом не уплотняются автоматически.

На практике: таблица `orders` у Shopify после года интенсивных апдейтов статусов может иметь 12 GB индекс на колонке `status`, тогда как «живые» данные занимают 3 GB. Bloat-ratio 4x означает, что каждый index scan читает втрое больше страниц, чем нужно.

Bloat особенно разрушителен для B-tree индексов на часто обновляемых колонках (статусы, счётчики, timestamps). Индексы на append-only таблицах (логи, события) практически не bloat-ятся.

Reindex Concurrently

`REINDEX` полностью пересоздаёт индекс из актуальных данных, устраняя bloat. Проблема: обычный `REINDEX INDEX myidx` блокирует таблицу на запись на всё время перестройки - от минут до часов на больших таблицах. `REINDEX CONCURRENTLY` (PostgreSQL 12+) работает без блокировки записей, создавая новый индекс рядом со старым.

REINDEX CONCURRENTLY нельзя запускать внутри транзакции. При сбое оставляет «инвалидный» индекс с суффиксом `_ccnew` - его нужно дропнуть вручную через `DROP INDEX CONCURRENTLY`.

Gitlab при выкатке миграций на таблицах с 50M+ строк использует именно `REINDEX CONCURRENTLY` в отдельных background jobs с мониторингом через `pg_stat_progress_create_index`. Среднее время пересборки индекса на таблице 100 GB - 45-90 минут.

pg_stat_user_indexes

`pg_stat_user_indexes` - системное представление, которое накапливает статистику использования каждого индекса с момента последнего `pg_stat_reset()`. Ключевые поля: `idx_scan` (сколько раз индекс задействован в планах), `idx_tup_read` (прочитано index entries), `idx_tup_fetch` (фактически получено heap строк).

Важно: статистика накапливается с момента запуска сервера или последнего `SELECT pg_stat_reset()`. Индекс с `idx_scan = 0` после перезапуска - ещё не повод его дропать; нужна статистика минимум за 2-4 недели нагрузки.

Unused Indexes

Неиспользуемые индексы - это скрытые потребители ресурсов. Каждый индекс замедляет INSERT/UPDATE/DELETE (нужно обновить структуру), потребляет место на диске и в shared_buffers, а autovacuum тратит время на его поддержку. В Basecamp обнаружили 34 неиспользуемых индекса на таблице `events` общим объёмом 280 GB - после удаления INSERT throughput вырос на 18%.

Перед удалением: создать индекс с `CREATE INDEX IF NOT EXISTS ... ;` в BEGIN/ROLLBACK для проверки синтаксиса, проверить UNIQUE/FK constraints (такие индексы нужны даже без сканирований), и сделать backup плана через `pg_dump --schema-only`.

B-tree Deduplication

**B-tree deduplication** (PostgreSQL 13+) - механизм, позволяющий хранить несколько одинаковых ключей в одной «posting list» записи вместо отдельных записей на каждую строку. На индексах с низкой кардинальностью (статусы, типы, boolean) это сокращает размер индекса в 5-10 раз.

Deduplication автоматически отключается для индексов с INCLUDE-колонками (covering indexes), для индексов на типах без оператора сравнения, и для уникальных индексов.

Ключевые идеи

• **Index bloat** накапливается при UPDATE/DELETE: VACUUM помечает мёртвые записи, но не уплотняет индексные страницы - bloat ratio 3-5x это типичная картина через год на active таблицах.
• **REINDEX CONCURRENTLY** - единственный способ устранить bloat без блокировки prod; временно хранит старый и новый индекс одновременно, при сбое оставляет `_ccnew`-артефакт.
• **pg_stat_user_indexes** даёт данные по использованию, но нужно минимум 2-4 недели типичной нагрузки; уникальные индексы нужны даже при idx_scan = 0.
• **B-tree deduplication** (PG13+) сокращает размер low-cardinality индексов в 5-10 раз автоматически, включена по умолчанию.

Связанные темы

Обслуживание индексов пересекается с несколькими ключевыми областями PostgreSQL:

• VACUUM и autovacuum — VACUUM - первая линия обороны против bloat в heap; понимание его ограничений объясняет, зачем нужен REINDEX
• EXPLAIN ANALYZE — Единственный способ убедиться, что индекс реально используется в нужных запросах перед его удалением
• Типы индексов PostgreSQL — Bloat и deduplication применимы к B-tree; для GiST/GIN/BRIN механика обслуживания отличается

Вопросы для размышления

• Как часто нужно проверять bloat на высоконагруженных таблицах вашего проекта, и есть ли у вас автоматизированный мониторинг этого показателя?
• Какие индексы в вашей схеме наиболее вероятно являются кандидатами на удаление, и как бы вы безопасно проверили эту гипотезу?
• B-tree deduplication включена по умолчанию в PG13+ - стоит ли явно её включать на кластерах PG12 через REINDEX?

Связанные уроки

• db-09-indexes-btree