Когда строится и перестраивается индекс в базе данных

Что проверяют

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

Как отвечать

Разведите три разных момента:

• создание: CREATE INDEX строит структуру один раз, прочитав таблицу целиком. Обычная форма берёт блокировку, которая не пускает запись в таблицу на всё время построения. В PostgreSQL для прода используют CREATE INDEX CONCURRENTLY: он идёт в два прохода, работает дольше, не может выполняться внутри транзакции и при неудаче оставляет индекс в состоянии INVALID, который надо удалить и построить заново;
• поддержание: при записи индекс не перестраивается, он обновляется точечно. INSERT добавляет ссылки во все индексы, DELETE помечает версию строки мёртвой, UPDATE создаёт новую версию строки и новые ссылки во всех индексах, кроме HOT-обновления, когда изменённые колонки не входят ни в один индекс и на странице есть место. Отсюда смысл fillfactor;
• перестроение: делают из-за раздувания, а не по расписанию. Это REINDEX CONCURRENTLY (доступен с PostgreSQL 12) или pg_repack. Современные версии раздуваются заметно меньше: в 13 появилась дедупликация одинаковых ключей в B-дереве, в 14 удаление мёртвых ссылок снизу вверх.

Добавьте практику: при массовой загрузке дешевле сначала залить данные, а индекс построить после; неиспользуемые индексы видно по idx_scan в pg_stat_user_indexes; статистика для планировщика это отдельная от индекса вещь, её обновляет ANALYZE и автовакуум.

Если база другая, скажите об этом: в SQL Server это ALTER INDEX ... REBUILD и REORGANIZE, в MySQL перестроение таблицы через OPTIMIZE TABLE.

Где спотыкаются

Говорят, что индекс перестраивается после каждого изменения. Обновляется только затронутая часть дерева.

Добавляют индекс на большую боевую таблицу обычным CREATE INDEX и останавливают запись на всё время построения.

Ставят REINDEX в крон «для профилактики». Правильнее следить за автовакуумом и фактическим раздуванием и перестраивать по показаниям.