Гайды

Индексы в PostgreSQL: типы, мониторинг и типичные ошибки

B-tree, GIN, GiST, BRIN, частичные и выраженческие индексы, pg_stat_user_indexes, bloat, REINDEX CONCURRENTLY и работа с планировщиком.

~10 мин чтения

Базы данных PostgreSQL Производительность SQL

Индексы в PostgreSQL: мониторинг, bloat и выбор типа

Индекс ускоряет чтение, но стоит места на диске и замедляет INSERT/UPDATE/DELETE. Ниже — как смотреть статистику, находить малоиспользуемые и раздутые индексы, какие типы индексов к чему подходят, и как не «стрелять из пушки по воробьям».

Мониторинг: `pg_stat_user_indexes`

«Мёртвые» индексы (редко используются)

Индекс «мёртвый», если по нему почти нет обращений, а запросы обходятся без него. Стартовый запрос для поиска кандидатов:

sql

SELECT relname AS table_name,
       indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan AS index_scan_count
FROM pg_stat_user_indexes
WHERE idx_scan < 50
ORDER BY idx_scan ASC,
         pg_relation_size(indexrelid) DESC;

Порог idx_scan < 50 — условность: для еженедельного отчёта индекс может почти не светиться, но быть нужным. Всегда сопоставляйте с бизнес-логикой и планом обслуживания перед DROP INDEX.

Bloat индексов

После массовых UPDATE/DELETE индексы накапливают «мёртвые» версии и раздуваются. Ориентир: если оценочный bloat 20–30%+, имеет смысл планировать перестроение. Используйте проверенные скрипты проверки bloat (в т.ч. от сообщества PostgreSQL), разделы мониторинга в pgAdmin или внешние инструменты.

Перестроение без долгой блокировки записи

Начиная с PostgreSQL 12, для продакшена предпочтительно:

sql

REINDEX INDEX CONCURRENTLY your_index_name;

Для создания тяжёлого индекса на живой базе:

sql

CREATE INDEX CONCURRENTLY idx_example ON your_table (your_column);

CONCURRENTLY снимает риск долгих эксклюзивных блокировок на запись, но операция может занять дольше и требует аккуратности при ошибках (см. документацию по «invalid index»).

Типичные ошибки

Ошибка	К чему приводит	Лучшее решение
Индекс «на будущее» без профиля запросов	Лишний размер и стоимость записи	Сначала логи, `EXPLAIN (ANALYZE, BUFFERS)`, `pg_stat_statements`.
Индекс с низкой селективностью (например, пол с двумя значениями)	Планировщик выберет Seq Scan	Индексировать поля с высокой кардинальностью; partial index под подмножество строк.
Индекс не покрывает условие `WHERE`	Дополнительные чтения из таблицы (bitmap heap scan и т.п.)	Подправить ключ, `INCLUDE`, или выражение под реальный запрос.
Без `CONCURRENTLY` на большой таблице	Долгие блокировки, простой приложения	`CREATE INDEX CONCURRENTLY` / `REINDEX CONCURRENTLY`.
Устаревшая статистика	Плохие оценки кардинальности, «левые» планы	`ANALYZE` и настройка autovacuum под нагрузку.

Какой индекс когда

Если ваш запрос…	…то ваш выбор
…ищет точные совпадения, диапазоны или требует сортировки (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`)	B-tree (универсальный вариант)
…часто обходит таблицу для поиска в строках (полнотекст, `jsonb`, массивы)	GIN
…оперирует геоданными или «ближайшим соседом»	GiST
…анализирует огромные упорядоченные ряды (логи, время)	BRIN
…ищет только равенство, без сортировки; узкий кейс	Hash (ограничения по операторам и версиям)
…работает с подмножеством строк (`WHERE status = 'active'`)	Partial index
…фильтрует по выражению (`lower(email) = …`)	Expression index
…требует сложных запретов пересечений интервалов	Exclusion constraint

Частые проблемы и решения

EXPLAIN всё ещё показывает Seq Scan — если запрос выбирает заметную долю таблицы (>5–10% — ориентир, не закон), Seq Scan может быть оптимальным. Проверьте ANALYZE, условия WHERE, неявные приведения типов и соответствие индекса предикату.
Индекс раздулся — мониторинг bloat, затем REINDEX CONCURRENTLY или pg_repack для более тонкого обслуживания.
После волн UPDATE всё тормозит — bloat + autovacuum; пересмотр индексов и горячих запросов.
Неясно, какого индекса не хватает — включите pg_stat_statements и ищите запросы с большим total_exec_time, shared_blks_read или blk_read_time — это главные кандидаты на индексацию.

Итог

Создавайте индексы под реальные запросы, следите за pg_stat_user_indexes, используйте CONCURRENTLY в проде и не забывайте про статистику (ANALYZE). Следующий шаг — партиционирование и шардирование, когда одна машина упирается в ресурсы.

Чек-лист

Перед CREATE INDEX — план EXPLAIN (ANALYZE, BUFFERS) на репрезентативных данных.
На проде — CONCURRENTLY для создания и REINDEX INDEX CONCURRENTLY для перестроения.
Раз в спринт — отчёт по idx_scan и размеру индексов; кандидаты на DROP согласовать с владельцем домена.
После крупных миграций данных — ANALYZE целевых таблиц.
Для горячих запросов рассмотреть covering index (INCLUDE) вместо «широкого» btree без нужды.

Дальше: EXPLAIN и ANALYZE · Партиционирование · Мониторинг PostgreSQL