Могут ли индексы вредить производительности базы данных

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

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

Как отвечать

Да, могут, и стоит сразу назвать механизмы, а не общее «замедляют запись»:

• запись: каждый INSERT и DELETE обновляет все индексы таблицы. При пакетной вставке через Hibernate с hibernate.jdbc.batch_size сетевые круги уже сэкономлены, и время уходит на поддержку индексов;
• HOT-обновления в PostgreSQL: если изменённая колонка не входит ни в один индекс и на странице есть место, UPDATE не трогает индексы вовсе. Индекс на часто меняемом updated_at или status отключает эту оптимизацию для всех обновлений;
• память и раздувание: индексы конкурируют с данными за shared_buffers, а после массовых обновлений разрастаются и требуют REINDEX;
• плохая селективность: по колонке с парой значений планировщик может выбрать индекс, прочитать половину таблицы вразброс и проиграть последовательному чтению;
• блокировки при создании: обычный CREATE INDEX блокирует запись в таблицу на всё время построения, на проде нужен CREATE INDEX CONCURRENTLY.

Покажите, как это диагностировать: EXPLAIN (ANALYZE, BUFFERS) на реальном запросе, счётчик idx_scan в pg_stat_user_indexes, по которому видны индексы с нулём обращений, и pg_stat_statements для самых дорогих запросов. В Java-проекте полезно добавить, что индексы, объявленные через @Table(indexes = ...), в прод лучше катить миграциями Flyway или Liquibase, а не через hbm2ddl.

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

Отвечают «индексы только ускоряют» или ограничиваются фразой про запись, не называя ни одного способа проверить, нужен ли индекс.

Дублируют индексы: отдельный индекс по a при уже существующем составном (a, b) почти всегда лишний, он только удорожает запись.

Создают индекс на большой таблице прямо в миграции без CONCURRENTLY и кладут сервис на время построения. Причём CONCURRENTLY не работает внутри транзакции, такую миграцию запускают отдельно, в Liquibase через runInTransaction="false".