Индексы нужны, чтобы ускорять доступ к данным. Они позволяют PostgreSQL избегать full scan по таблице и заметно сокращают время выполнения запросов в read-heavy нагрузках.
По реальному прод-опыту мы видели, что грамотно спроектированные, точечные индексы могут ускорять запросы в 5 раз и больше, особенно на больших транзакционных таблицах.
Но индексы не бесплатны.
В этом посте разберем, какие проблемы могут создавать неиспользуемые индексы и как удалять их в проде безопасно, с планом отката.
1. Почему большие неиспользуемые индексы со временем становятся проблемой
Со временем такие индексы могут незаметно ухудшать производительность базы. Вот типовые эффекты, которые встречаются в проде.
1.1. Медленнее INSERT, UPDATE и DELETE
Каждая запись должна обновить все индексы таблицы, включая те, которыми запросы никогда не пользуются.
1.2. Больше нагрузки на VACUUM и autovacuum
В индексах так же копятся мертвые tuple, как и в таблицах. Их тоже нужно вакуумить, из-за чего растут I/O и время работы vacuum.
1.3. Дольше окна обслуживания
Операции вроде VACUUM и REINDEX выполняются тем дольше, чем больше индексов и чем они крупнее.
1.4. Пустая трата диска и “засорение” кеша
Большие неиспользуемые индексы едят место на диске и могут вытеснять полезные данные из shared_buffers, снижая эффективность кеша.
Поэтому периодически находить и аккуратно удалять неиспользуемые индексы в проде реально полезно, но только через контролируемый и хорошо проверенный процесс.
2. Как безопасно удалить неиспользуемые индексы в PostgreSQL
Ниже чеклист в стиле “можно в прод”, который стоит пройти перед удалением любого индекса.
2.1. Проверь, когда в последний раз сбрасывали статистику
Если статистику недавно reset’нули, индекс может выглядеть “неиспользуемым”, хотя он нужен под нагрузкой.
SELECT
datname,
stats_reset
FROM pg_stat_database
WHERE datname = current_database();
Старый
stats_reset (или NULL, то есть статистику никогда не сбрасывали) дает больше доверия метрикам использования индексов.2.2. Проверь, не обслуживает ли индекс constraint
Большой индекс может быть “нулевым” по статистике, но удалять его нельзя, если он обеспечивает PRIMARY KEY, UNIQUE или FOREIGN KEY constraint.
PostgreSQL использует такие индексы для гарантий целостности и не даст их удалить, пока не удалишь сам constraint.
SELECT
i.relname AS index_name,
c.conname AS constraint_name,
c.contype AS constraint_type,
c.conrelid::regclass AS table_name
FROM pg_constraint c
JOIN pg_class i ON i.oid = c.conindid
WHERE i.relname = '<IDX_NAME>';
Если запрос вернул строки, этот индекс удалять нельзя.
2.3. Проверь статистику использования индекса
Так ты подтверждаешь, что планировщик реально не использовал индекс при выполнении запросов.
SELECT
s.indexrelname AS index_name,
s.relname AS table_name,
s.idx_scan,
s.idx_tup_read,
s.idx_tup_fetch
FROM pg_stat_user_indexes s
WHERE s.indexrelname = '<IDX_NAME>';
Все счетчики должны быть
0.3. Подготовь откат
Перед удалением всегда сохрани DDL индекса, чтобы быстро пересоздать при необходимости.
SELECT pg_get_indexdef('<IDX_NAME>'::regclass) AS create_index_sql;Сохрани результат как часть rollback-плана.
4. Удаляй безопасно
DROP INDEX CONCURRENTLY не блокирует чтение и запись по таблице, поэтому подходит для прода.DROP INDEX CONCURRENTLY <IDX_NAME>;
Если после удаления увидишь деградацию, откатный DDL можно использовать, чтобы пересоздать индекс concurrently без потери доступности.
5. Итог
Удаление неиспользуемых индексов может ощутимо улучшить производительность и упростить обслуживание, но только если действовать аккуратно.
Не полагайся на статистику в одиночку: проверяй constraints, понимай паттерны нагрузки и всегда готовь rollback.
В проде корректность и стабильность важнее скорости “уборки”.
👉 @SQLPortal