Index bloat: почему разрастаются индексы

Вася из отдела отчётности зашёл к Максу уже не с криками о помощи, а с ноутбуком под мышкой и сосредоточенным видом.

— Макс, отвлеку на минуту? — Вася развернул экран. — Помнишь историю с дисками? Нам их всё-таки добавили, но я тут на досуге прикрутил самодельный мониторинг размеров таблиц. И вот, смотри, какая странная штука.

Он указал на график таблицы operations. Синяя линия — объём данных — росла плавно. А вот красная линия — размер индексов — рванула вверх.

— Таблица выросла на 10%, а индексы на все 40, — Вася недоумённо почесал затылок. — Почему так происходит?

Макс отставил кружку и внимательно посмотрел на цифры.

— Похоже на разбухание, но одного графика размера для диагноза мало, — объяснил Макс. — UPDATE создаёт новую версию строки. Если меняются индексируемые значения или HOT update невозможен, появляются и новые индексные записи. Старые записи позднее очищаются, а освободившиеся страницы могут повторно использоваться, но файл индекса не обязан уменьшаться.

— И что же, это место просто пропадает? — уточнил Вася.

— Сначала сравним рост данных и индекса, характер обновлений, fillfactor и реальное заполнение страниц. Для точной диагностики пригодится расширение pgstattuple, если оно разрешено. Большой индекс может быть совершенно здоровым, а свободное место — резервом для будущих вставок.

Вася нахмурился:

— И как это лечить? Опять всё блокировать и пересоздавать вручную?

— Зачем? Для индексов есть штатный инструмент, — Макс быстро набрал команду в консоли:

REINDEX INDEX CONCURRENTLY idx_operations_status;

— Если измерения подтвердят bloat и перестроение действительно окупится, CONCURRENTLY позволит сохранить обычные чтения и изменения таблицы на большей части операции. Но это не «никаких блокировок»: будут короткие фазы переключения и ожидание конфликтующих транзакций. Понадобится место под новую копию индекса, вырастут I/O, CPU и WAL.

Спустя час Вася снова заглянул в кабинет, на этот раз сияя, как свежесозданный индекс без фрагментации.

— Макс, реально сработало! — Вася победно поднял большой палец вверх. — Место освободилось, ничего не блокируется. Первый индекс сжался почти в 4 раза! Я там уже скрипт на коленке набросал, чтобы вообще по всей базе пройтись и всё перестроить. Ура-а-а! Теперь моя база вдвое больше свободного места запасет!

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

— Ты только всё сразу не запускай, хозяйственный ты наш, — осадил он его пыл. — А то диски от такого счастья вскипят. С индексами ведь всё как с нашими привычками. Мы их заводим, чтобы быстрее принимать решения, но со временем они обрастают лишним багажом и старыми версиями самих себя. Прям как legacy-код, который никто не решается удалить.

— Намекаешь, что нам тоже нужен такой REINDEX? — усмехнулся Вася, уже открывая консоль на своём ноутбуке.

— Обязательно, — кивнул Макс. — Иногда полезно оставить только то, что работает сейчас, и выкинуть лишний груз из старых убеждений.

Что запомнить:

• Сигнал для проверки: индекс растёт значительно быстрее данных или заметно ухудшаются планы и чтение страниц.

• Возможные причины: изменения индексируемых столбцов, отсутствие HOT update, неудачный fillfactor, паттерн вставок и удалений.

• Решение после измерений: REINDEX INDEX CONCURRENTLY создаёт новую компактную копию с высокой доступностью, но не устраняет причину повторного роста.

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