Какие минусы у индексов кроме занимаемого места

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

Понимаете ли вы, во что индекс обходится базе при записи, обслуживании и эксплуатации, а не только на диске. Следом часто спрашивают, как найти ненужные индексы и как добавить индекс на большую таблицу без простоя.

Как отвечать

Минусы, кроме места:

• замедление записи: каждый INSERT, DELETE и UPDATE индексированной колонки обновляет все индексы таблицы. В PostgreSQL изменение индексированной колонки ещё и отключает HOT-обновления, и новая версия строки попадает во все индексы;
• больше WAL и репликации: каждое изменение индекса пишется в журнал, растут объём WAL, отставание реплик и размер бэкапа;
• блокировки при создании: обычный CREATE INDEX в PostgreSQL блокирует запись в таблицу на всё время построения. На проде нужен CREATE INDEX CONCURRENTLY, он дольше и не работает внутри транзакции, а в MySQL проверяют, что DDL идёт online;
• раздувание и фрагментация: после массовых обновлений и удалений B-tree распухает, помогает REINDEX CONCURRENTLY. Случайные ключи вроде UUID v4 в кластерном индексе InnoDB дают постоянные расщепления страниц;
• память: индексы конкурируют с данными за shared_buffers или buffer pool, горячие данные вытесняются;
• конкуренция на уникальных индексах: параллельные вставки одинакового ключа ждут друг друга;
• ошибки планировщика: при устаревшей статистике база может выбрать индекс там, где полный скан дешевле. Лечится ANALYZE, а не пересозданием индекса.

Для Java-стека добавьте практику: индексы создаются миграциями Flyway или Liquibase, а не через hibernate.ddl-auto, а @Index в @Table влияет только на генерацию схемы Hibernate. Неиспользуемые индексы ищут через pg_stat_user_indexes с idx_scan = 0.

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

Говорят, что индексы «устаревают» и их надо регулярно пересоздавать. Устаревает статистика, а пересоздание нужно только при раздувании.

Добавляют индекс миграцией без CONCURRENTLY на большую таблицу и блокируют запись на проде на всё время построения.

Не учитывают массовую загрузку: при заливке миллионов строк быстрее удалить индексы, загрузить данные и построить индексы заново.