Какие аргументы против установки индекса на все колонки таблицы

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

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

Как отвечать

Аргументы выстройте от самого весомого:

• запись дорожает: каждый INSERT и DELETE обновляет все индексы таблицы. В PostgreSQL UPDATE без HOT-оптимизации тоже кладёт новую версию строки во все индексы, а HOT срабатывает, только если не изменилась ни одна индексированная колонка. Индекс на каждой колонке выключает его почти для любого обновления;
• место и память: индексы вместе легко весят больше таблицы, вытесняют данные из shared buffers и раздувают бэкапы;
• пользы меньше, чем кажется: одиночные индексы не заменяют составной. Запросу where status = ? and created_at > ? order by created_at нужен индекс (status, created_at), а два отдельных в лучшем случае дадут bitmap-объединение без сортировки;
• низкая селективность: индекс по boolean или статусу из трёх значений планировщик проигнорирует и прочитает таблицу целиком;
• обслуживание: больше индексов значит больше bloat, дольше VACUUM и миграции. Обычный CREATE INDEX на живой таблице блокирует запись, в PostgreSQL для этого есть CREATE INDEX CONCURRENTLY.

Теперь Java-сторона. Индексы описывают в миграциях Flyway или Liquibase, а не получают из spring.jpa.hibernate.ddl-auto. Аннотация @Index внутри @Table полезна как документация, но источник правды это миграции. Кандидатов берут из реальных запросов: SQL-лог Hibernate, pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS). Лишние видны в pg_stat_user_indexes по нулевому idx_scan.

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

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

Не отличают одиночный индекс от составного и считают, что индексы по a и по b покроют запрос по обоим.

Забывают, что внешние ключи в PostgreSQL индекс сами не получают. Индекс на колонке FK как раз нужен почти всегда, иначе удаление из родительской таблицы и джойны сканируют дочернюю целиком.