Паттерны и антипаттерны индексирования

Что делать и чего избегать с индексами: over-indexing и write-amplification, неиспользуемые и дублирующие индексы, bloat и обслуживание, covering под горячие запросы, online-создание без блокировок, индексы под пагинацию — и антипаттерн «индекс на всё»

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

Это четвёртая статья серии «Индексы в базах данных». Опирается на структуры из первой статьи и практику выбора индекса из второй; реализации по конкретным БД — в третьей.

В статье

Цена индекса на запись: write-amplification
Неиспользуемые и дублирующие индексы
Index bloat и обслуживание
Covering и index-only под горячие запросы
Online-создание индекса без блокировок
Индексы под пагинацию и сортировку: keyset vs offset
Антипаттерн «индекс на всё»
Что дальше

Цена индекса на запись: write-amplification

Каждый INSERT и каждый UPDATE индексируемой колонки обновляет не только таблицу, но и все индексы, которые на неё ссылаются. Если индексов пять, вставка одной строки — это не одна операция записи, а до шести: одна в heap и по одной в каждый индекс (плюс WAL для каждой из них). Это и называется write-amplification — усиление записи количеством индексов.

На стенде это видно не как абстрактная оценка, а как измеренная зависимость: одни и те же 500 000 строк (побитово идентичные во всех прогонах — данные сгенерированы детерминированно, setseed(0.42) перед каждой вставкой) вставлялись в таблицу с разным числом индексов — 0, 1, 3 и 6. Единственная переменная — число индексов на таблице в момент вставки:

Числа по всем четырём точкам (среднее двух прогонов, идентичных по размеру — генерация детерминирована):

Индексов	Время вставки (среднее)	Итоговый размер
0	~1459 мс	56 МБ
1	~2439 мс	69 МБ
3	~5748 мс	110 МБ
6	~8934 мс	135 МБ

От нуля индексов до шести время вставки выросло примерно в 6 раз, размер — примерно в 2.4 раза. Обе метрики монотонны — ни одна точка не выбивается из тренда ни в одном из двух прогонов. Но рост неравномерен: скачок от 3 к 6 индексам заметно больше, чем от 0 к 3, — и причина не только в том, что индексов стало вдвое больше. Состав шестииндексного сценария: btree(a), btree(b), btree(d), btree(c), функциональный btree по выражению a % 1000, и GIN по jsonb-колонке e. GIN исторически самый дорогой тип индекса по CPU и I/O при построении — он не просто добавляет запись указателя, а на каждый INSERT разбирает документ на отдельные ключи и обновляет списки для каждого из них. Так что скачок 3→6 — это не «три лишних B-tree», а два B-tree-подобных индекса плюс один GIN, который сам по себе вносит основной вклад в прирост времени и размера.

Оговорка, которая важна при переносе этих цифр в собственные расчёты: абсолютное время вставки — host-зависимая метрика (диск, файловая система, конкуренция за I/O с другими процессами на машине), и между двумя прогонами на одном и том же стенде разброс составил около 10–20%. Важен не абсолютный миллисекунд, а форма зависимости — монотонный рост обеих метрик с числом индексов и непропорционально высокая цена GIN-подобных структур. Это и есть write-amplification в чистом виде: каждый добавленный индекс — это не бесплатная опция «на всякий случай», а постоянный налог на каждую будущую запись в таблицу.

Неиспользуемые и дублирующие индексы

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

SELECT
  schemaname, relname AS table, indexrelname AS index,
  idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 за время жизни статистики (с последнего сброса счётчиков или рестарта сервера — важно проверить pg_stat_reset() не сбрасывал её недавно, иначе ноль ничего не значит) — сильный кандидат на удаление. Первичные ключи и уникальные ограничения стоит исключать из первого прохода отдельно: у них может не быть idx_scan, но они всё ещё обеспечивают constraint, а не только читаются планировщиком.

Дублирующие и перекрывающиеся индексы — другая категория той же болезни. Составной индекс (a, b) делает избыточным отдельный индекс (a) — по left-prefix (см. статью #2) (a) покрывается первой колонкой (a, b) целиком, для любого запроса, где (a) был бы полезен. Но (a, b) не делает избыточным индекс (b) — по той же причине left-prefix, обратный порядок недоступен без отдельной структуры. Практический список для ревизии:

индекс на одну колонку, которая является префиксом уже существующего составного индекса — почти всегда лишний;
два по сути идентичных индекса, отличающихся только INCLUDE-колонками или порядком сортировки (ASC/DESC), если ни один запрос не требует именно обратного порядка;
уникальный индекс и обычный по тому же набору колонок — обычный избыточен, уникальный уже обеспечивает и поиск, и constraint.

pg_stat_user_indexes даёт факт использования, но не объясняет причину отсутствия использования — индекс может быть невостребован и потому, что запрос его не касается, и потому, что он написан так, что индекс не виден планировщику (несовпадение выражения, каст типа — разобрано в статье #2). Проверка требует смотреть оба источника вместе, а не полагаться на один счётчик.

Index bloat и обслуживание

Индексы, как и таблицы в PostgreSQL, раздуваются со временем: MVCC не перезаписывает строку на месте при UPDATE, а создаёт новую версию, и записи индекса, указывающие на старые мёртвые версии, остаются в структуре индекса до тех пор, пока VACUUM их не уберёт. При интенсивных UPDATE/DELETE и отстающем autovacuum индекс постепенно заполняется мёртвыми записями, физически растёт на диске, но не становится от этого быстрее — наоборот, дерево становится менее компактным, а страницы — менее заполненными полезными данными.

Обычный VACUUM место операционной системе не возвращает: он помечает мёртвое пространство как свободное для повторного использования самим PostgreSQL внутри тех же файлов, поэтому физический размер файла на диске не уменьшается (единственное исключение — полностью пустые страницы в самом хвосте файла: их VACUUM может усечь и отдать ОС). Чтобы реально сжать индекс, нужен VACUUM FULL (полная перестройка под ACCESS EXCLUSIVE, то есть с блокировкой и чтений, и записей) либо REINDEX. Оба дорогие, но блокируют по-разному, и это важно не путать: REINDEX без модификатора блокирует записи в таблицу, но формально не чтения — ACCESS EXCLUSIVE берётся на сам перестраиваемый индекс, а не на таблицу. Только «формально не блокирует чтения» не означает «читатели не заметят»: планировщик, строя план запроса к таблице, берёт блокировку на каждый её индекс, поэтому на практике перестройка может задерживать почти любые запросы к этой таблице — не только те, что пошли бы именно через перестраиваемый индекс. Для прода это обычно неприемлемо вдвойне: и писать в таблицу нельзя всё время перестройки, и читатели рискуют встать. Решение — REINDEX CONCURRENTLY (начиная с PostgreSQL 12): строит новую копию индекса параллельно со старой, не блокируя чтение и запись таблицы, и подменяет старую копию новой атомарно в конце. Цена — вдвое больше места на диске на время перестройки (обе копии существуют одновременно) и более долгое время выполнения, чем блокирующий REINDEX.

Полный разбор природы bloat через MVCC, тюнинга autovacuum и того, как отличить table bloat от index bloat на практике, — отдельная тема статьи «Партиционирование, VACUUM и борьба с bloat в PostgreSQL»готовится, с 2 октября; здесь важно запомнить связь: чем больше индексов на таблице с высокой частотой UPDATE, тем больше индексов нужно мониторить на bloat отдельно от самой таблицы — обслуживание масштабируется вместе с числом индексов, а не только их создание.

Covering и index-only под горячие запросы

Покрывающий индекс — паттерн, а не универсальное правило: добавлять INCLUDE-колонки стоит только под конкретный горячий запрос, который выполняется достаточно часто, чтобы окупить цену — увеличенный размер индекса и лишнюю работу на каждый UPDATE включённых колонок, даже если они не участвуют в поиске. Как это работает и что даёт Index Only Scan на практике (0.090 мс против 0.114 мс на обычном Index Scan, при условии актуальной карты видимости), подробно разобрано на живом стенде в статье #2, «Как выбирать и использовать индексы».

Практическое правило здесь простое: покрывающий индекс — это оптимизация под измеренный горячий путь, а не что-то, что добавляют по умолчанию «раз уж всё равно индекс создаём». Каждая лишняя INCLUDE-колонка — это ещё один пункт в той же таблице write-amplification выше, просто не в виде отдельного индекса, а в виде утолщения существующего.

Online-создание индекса без блокировок

Обычный CREATE INDEX берёт блокировку SHARE на таблицу — она не мешает чтению, но блокирует INSERT/UPDATE/DELETE на всё время построения индекса, что на таблице в сотни мегабайт или гигабайт означает заметное окно недоступности записи в проде. CREATE INDEX CONCURRENTLY решает эту проблему: строит индекс в два прохода без удержания блокирующего лока, позволяя записи продолжаться параллельно.

Цена — не только время (CONCURRENTLY заметно медленнее обычного построения, потому что не может использовать некоторые оптимизации однопроходного построения) но и надёжность: если построение по какой-то причине прерывается (например, обнаружен конфликт уникальности при построении уникального индекса), CONCURRENTLY оставляет после себя невалидный индекс — он виден в \d и pg_indexes, но помечен INVALID в pg_index.indisvalid и не используется планировщиком. Такой индекс нужно явно удалить (DROP INDEX) и попробовать снова после устранения причины конфликта — он не исчезает и не чинится сам. Второй нюанс: CREATE INDEX CONCURRENTLY нельзя выполнить внутри транзакционного блока (BEGIN/COMMIT) — команда сама управляет несколькими транзакциями внутри своего выполнения.

Практическое правило: на таблице, которая принимает продовую запись, CONCURRENTLY — это не опция для галочки, а обязательный режим создания и пересоздания индексов, если только не запланировано окно обслуживания с остановкой записи. Тот же принцип применим к REINDEX CONCURRENTLY, разобранному выше.

Индексы под пагинацию и сортировку: keyset vs offset

OFFSET-пагинация (ORDER BY id LIMIT 20 OFFSET 10000) требует от планировщика пройти и отбросить все OFFSET строк перед тем, как отдать нужные — независимо от того, есть индекс по колонке сортировки или нет. Индекс ускоряет сам проход по порядку (не нужен отдельный Sort), но не устраняет расточительность: на глубоких страницах (OFFSET в десятки и сотни тысяч) время растёт линейно с глубиной пагинации, потому что база физически проходит и отбрасывает каждую пропущенную строку.

Keyset-пагинация (её также называют cursor-based или seek-пагинацией) заменяет OFFSET на условие в WHERE: вместо «пропусти 10000 строк» — «дай строки после той, на которой остановился прошлый запрос» (WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20). При наличии составного индекса именно по колонкам сортировки (created_at, id) такой запрос — это всегда Index Scan с постоянной стоимостью независимо от глубины пагинации: планировщик сразу находит нужную точку в дереве индекса по значению курсора и идёт от неё, не проходя предыдущие страницы вообще. Разница особенно заметна на страницах в глубине выдачи — там, где OFFSET-вариант уже деградировал, keyset остаётся такой же быстрой операцией, как первая страница.

Плата за keyset — не в производительности, а в интерфейсе: нельзя напрямую прыгнуть на произвольный номер страницы («страница 500 из 1000»), только двигаться вперёд/назад от текущей позиции курсора. Для лент, бесконечной прокрутки и API с курсорами (что типично для мобильных и SPA-клиентов) это не ограничение, а естественная модель; для классической постраничной навигации с номерами страниц — требует компромисса или гибридного подхода (offset для первых страниц, keyset глубже).

В обоих случаях индекс должен точно соответствовать порядку ORDER BY, включая направление (DESC) каждой колонки — иначе тот же принцип left-prefix и совпадения выражения из статьи #2 не даёт использовать индекс для сортировки, и план возвращается к отдельному узлу Sort поверх результата.

Антипаттерн «индекс на всё»

Симметричная ошибка over-indexing — индекс на каждую колонку таблицы «на всякий случай», без связи с реальными запросами. Показатели того, что команда попала в этот антипаттерн:

число индексов на таблице растёт с каждым спринтом, но pg_stat_user_indexes показывает, что большая часть из них имеет idx_scan, близкий к нулю;
индекс создан под запрос, который выполняется раз в квартал в отчёте, а не под горячий путь приложения — write-amplification платится на каждой записи ради редкого чтения;
на таблице есть и составной индекс (a, b, c), и отдельные индексы (a), (a, b) — все три покрываются первым, если ни один запрос не использует (a) или (a, b) отдельно от (c);
индекс есть, но планировщик его игнорирует (низкая селективность, каст типа, функция без expression-индекса — см. статью #2), а команда реагирует созданием ещё одного индекса вместо диагностики через EXPLAINготовится, с 6 октября.

Правильный порядок работы — обратный: сначала найти горячие запросы (через pg_stat_statements или профилирование продовой нагрузки), затем спроектировать минимальный набор индексов, покрывающий именно их, а не индексировать каждую колонку, которая теоретически может встретиться в WHERE. Индекс, который никогда не читается, не бесплатен просто потому, что он «на всякий случай», — он платит write-amplification на каждой записи ровно так же, как индекс, который используется тысячу раз в секунду.

Что дальше

Эта статья закрыла эксплуатационную сторону индексирования: цена индекса на запись на измеренных числах, поиск неиспользуемых и дублирующих индексов, обслуживание bloat, паттерны covering/keyset и online-создание без блокировок — и симметричная ошибка индексирования всего подряд. Последний угол серии — как язык приложения и ORM независимо от качества самих индексов способны свести их пользу к нулю: N+1-запросы, неявные приведения типов в параметрах JDBC/pgx, забытые PreparedStatement — статья #5, «Индексы и языки/ORM»готовится, с 30 июля.