PostgreSQL индексы: когда создавать и какие

Пошаговое руководство по выбору, созданию и анализу индексов в PostgreSQL с рабочими командами и выводами. Примерное время выполнения полного набора шагов — 90–150 минут.

Индекс в PostgreSQL — это структура данных, ускоряющая поиск строк без необходимости полного сканирования таблицы. Индексы заметно снижают I/O при селектах по ключевым колонкам, работают как вспомогательный механизм для уникальности и для ускорения соединений (JOIN).

Ожидаемый эффект: для селекта по колонке с правильно подобранным индексом время ответа может упасть с сотен миллисекунд до единиц миллисекунд на выборках до нескольких миллионов строк. Снижение задержки сопровождается платой: дополнительные записи при INSERT/UPDATE и затраты дискового пространства (индекс может занимать от нескольких процентов до превышать размер таблицы в отдельных случаях).

Какие бывают?

• B-tree — стандартный индекс для равенств и диапазонов; подходит для большинства задач (PostgreSQL B-tree оптимизирован с 2020 по 2026). Примеры: первичные ключи, WHERE col =/>, <.
• GIN — индекс для массивов, JSONB и полнотекстового поиска; эффективен для операторов @>, ? и для trigram-поиска с pg_trgm.
• GiST — обобщённая структура для геопространственных данных (PostGIS), диапазонов и специальных типов.
• BRIN — компактный индекс для больших таблиц с физически коррелированными данными (логический порядок минимален); размер индекса обычно в килобайтах, выгоден при таблицах >10 GB.
• HASH — ограниченное применение; в современных версиях PostgreSQL HASH полезен редко, B-tree предпочтительнее.

Когда не надо?

Индекс не нужен, если колонка имеет очень низкую кардионость (например, boolean или колонка с 95% NULL/одним значением) — индекс даст мало селективности и только добавит накладные расходы на запись. Индекс также лишний для таблиц, которые полностью перезаписываются на каждый апдейт (т.н. временные таблицы), или для колонок, по которым запросы редки и не критичны по латентности.

Типичные признаки избыточных индексов: более 30% размера таблицы в сумме индексов, сильно замедленные INSERT/UPDATE/DELETE, большое количество неиспользуемых индексов по результатам pg_stat_user_indexes за период 30 дней.

Шаг 1: подготовка тестовой БД

Команды создают Docker-контейнер с PostgreSQL 17. Запустите локально для репликации примеров. Время запуска: ~8–12 секунд на SSD.

docker run --name pg17-test -e POSTGRES_PASSWORD=pass -p 5432:5432 -d postgres:17

Пояснение: команда использует официальный образ postgres:17 (~470 MB в 2025) и открывает порт 5432. Контейнер будет в фоне.

Ожидаемый вывод:

""

Типичная ошибка и фикс:

Ошибка: "port is already allocated"
Фикс: освобождайте локальный 5432 или пробросьте на другой порт, например -p 5433:5432 и подключайтесь на 5433.

Шаг 2: создать тестовую таблицу и наполнить данными

Создадим таблицу orders с миллионом строк с распределением дат и user_id, чтобы демонстрировать разные типы индексов. Время вставки зависит от системы; на 4 vCPU, NVMe примерно 4–10 минут для 1 000 000 строк при COPY/pgbench.

psql -h localhost -U postgres -p 5432 -c "CREATE DATABASE idx_test;"
psql -h localhost -U postgres -p 5432 -d idx_test -c "CREATE TABLE orders (
  id serial PRIMARY KEY,
  user_id integer,
  status varchar(20),
  created_at timestamptz,
  amount numeric(10,2),
  payload jsonb
);"

# Заполним 1M строк с помощью генерации в SQL (примерно 5-10 минут)
psql -h localhost -U postgres -d idx_test -c "INSERT INTO orders (user_id, status, created_at, amount, payload)
SELECT (random()*10000)::int, (ARRAY['new','paid','canceled'])[ (random()*3+1)::int ],
  now() - (random()*interval '365 days'), (random()*5000)::numeric(10,2), jsonb_build_object('k', md5(random()::text))
FROM generate_series(1,1000000);")

Пояснение: таблица моделирует реальную рабочую нагрузку, где user_id — высокая кардионость, status — низкая.

Ожидаемый вывод (успех):

INSERT 0 1000000

Типичная ошибка и фикс:

Ошибка: "out of memory" при одновременной вставке большого объема
Фикс: разбейте вставки на батчи по 100K, увеличьте work_mem/maintenance_work_mem или используйте COPY из файла.

Шаг 3: базовый B-tree индекс и проверка его эффекта

Создадим B-tree по колонке user_id и сравним EXPLAIN ANALYZE до и после. Создание индекса на 1M строк в CONCURRENTLY займёт ~20–90 секунд на NVMe с 4 vCPU; без CONCURRENTLY быстрее, но блокирует DDL кратко.

-- без CONCURRENTLY (быстрее на тестовой базе)
psql -d idx_test -c "CREATE INDEX idx_orders_user_id ON orders (user_id);"

-- Проверка плана до/после (пример запроса)
psql -d idx_test -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1234 LIMIT 10;"

Ожидаемый успешный вывод после создания индекса (фрагмент EXPLAIN ANALYZE):

Index Scan using idx_orders_user_id on orders  (cost=0.43..8.59 rows=1 width=200) (actual time=0.20..0.50 rows=10 loops=1)
Planning Time: 0.5 ms
Execution Time: 0.6 ms

Типичная ошибка и фикс:

Ошибка: "CONCURRENTLY cannot be executed inside a transaction block"
Фикс: Выполните CREATE INDEX CONCURRENTLY из отдельного psql-сеанса без BEGIN/COMMIT или используйте EXPLICIT transaction: не поддерживается; просто запустите команду отдельно.

Шаг 4: полнотекстовый поиск и GIN (pg_trgm) индекс

Для поиска по тексту, часто используют pg_trgm + GIN. Установим расширение pg_trgm и создадим индекс GIN с оператором gin_trgm_ops для ускорения LIKE/ILIKE/~~* запросов. Время создания на 1M строк — 2–6 минут в 2026 на 4 vCPU.

psql -d idx_test -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
psql -d idx_test -c "CREATE INDEX idx_orders_payload_trgm ON orders USING gin ((payload->>'k') gin_trgm_ops);"

psql -d idx_test -c "EXPLAIN ANALYZE SELECT id FROM orders WHERE (payload->>'k') ILIKE '%ab12c%';"

Ожидаемый вывод (фрагмент):

Bitmap Heap Scan on orders  (cost=...) (actual time=12.3..13.7 rows=5 loops=1)
Bitmap Index Scan on idx_orders_payload_trgm  (cost=...) (actual time=11.5..11.5 rows=5 loops=1)
Execution Time: 14.0 ms

Типичная ошибка и фикс:

Ошибка: "extension "pg_trgm" does not exist"
Фикс: подключитесь как суперпользователь или роль с правом CREATE EXTENSION: psql -U postgres -d idx_test -c "CREATE EXTENSION pg_trgm;"

Шаг 5: BRIN индекс для больших коррелированных таблиц

BRIN эффективен, если данные физически упорядочены по колонке (например, created_at). Создание BRIN индекса на 1M строк занимает обычно 1–5 секунд и индекс занимает около 10–50 KB в зависимости от page_range. BRIN полезен для архивации.

psql -d idx_test -c "CREATE INDEX idx_orders_created_brIN ON orders USING brin (created_at);"
psql -d idx_test -c "EXPLAIN ANALYZE SELECT count(*) FROM orders WHERE created_at > now() - interval '30 days';"

Ожидаемый вывод при наличии корреляции (фрагмент):

Aggregate  (cost=...)
  ->  Bitmap Heap Scan on orders  (cost=... actual time=...)
Bitmap Index Scan on idx_orders_created_brin  (cost=... actual time=...)
Execution Time: 25.4 ms

Типичная ошибка и фикс:

Ошибка: "BRIN index not used, sequential scan chosen"
Фикс: проверьте корреляцию данных командой VACUUM ANALYZE и убедитесь, что созданный BRIN соответствует порядку вставки; если данные сильно разрознены, BRIN не даст выигрыша.

Шаг 6: измерение размера индексов и использования

Измерим размер индексов и найдем неиспользуемые. Команды быстрые: pg_total_relation_size на нескольких таблицах — доли секунды.

psql -d idx_test -c "SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan FROM pg_stat_user_indexes WHERE schemaname = 'public' AND relname = 'orders' ORDER BY pg_relation_size(indexrelid) DESC;"

psql -d idx_test -c "SELECT pg_size_pretty(pg_total_relation_size('orders')) AS table_total, pg_size_pretty(pg_total_relation_size('orders') - pg_relation_size('orders')) AS indexes_total;"

Ожидаемый вывод (пример):

indexrelname | size  | idx_scan
---------------+-------+---------
 idx_orders_payload_trgm | 48 MB | 120
 idx_orders_user_id      | 24 MB | 5400
 idx_orders_created_brin | 32 kB | 10

 table_total | indexes_total
-------------+---------------
  300 MB     | 72 MB

Типичная ошибка и фикс:

Ошибка: "relation \"pg_stat_user_indexes\" does not exist"
Фикс: выполните запрос в базе данных (psql -d idx_test) и убедитесь, что статистика обновлена: запуск VACUUM ANALYZE на целевой таблице.

Шаг 7: удаление и реорганизация индексов в production

Удалять индексы нужно аккуратно: сначала проверьте отсутствие использования (idx_scan = 0) и убедитесь в отсутствии критичных зависимостей. Для удаления индекса без блокировки SELECT используйте DROP INDEX CONCURRENTLY. Время удаления — от секунд до минут в зависимости от размера и активности.

-- Проверка использования: если idx_scan=0 за 30 дней, можно рассматривать удаление
psql -d idx_test -c "SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname='orders';"

-- Удаление безопасно
psql -d idx_test -c "DROP INDEX CONCURRENTLY IF EXISTS idx_orders_payload_trgm;"

Ожидаемый вывод при удалении:

DROP INDEX

Типичная ошибка и фикс:

Ошибка: "cannot drop index concurrently in a read-only transaction"
Фикс: выполните DROP INDEX CONCURRENTLY в отдельной сессии без BEGIN; не запускайте внутри транзакции.

Шаг 8: REINDEX, VACUUM и поддержание индексов

Индексы фрагментируются при массовой модификации данных. REINDEX восстанавливает структуру индекса, CONCURRENTLY доступен для REINDEX (начиная с PostgreSQL 12+ для некоторых операций). Планируйте REINDEX в окна низкой нагрузки. REINDEX полной таблицы на 1M строк может занять от 30 секунд до нескольких минут в зависимости от индексов.

# REINDEX конкретного индекса (блокирует кратко)
psql -d idx_test -c "REINDEX INDEX idx_orders_user_id;"

# VACUUM ANALYZE для обновления статистики и уменьшения bloat
psql -d idx_test -c "VACUUM (VERBOSE, ANALYZE) orders;"

Ожидаемый вывод (фрагмент):

REINDEX INDEX

VACUUMing "public.orders"
...  done
ANALYZE

Типичная ошибка и фикс:

Ошибка: "could not reindex: index contains unexpected data"
Фикс: выполните pg_dump, восстановление и проверку или используйте REINDEX CONCURRENTLY если версия поддерживает; при повреждении — восстановление из бэкапа.

Инструменты и метрики для контроля (короткий набор)

• pg_stat_user_indexes — отслеживайте idx_scan, idx_tup_read.
• pg_total_relation_size, pg_relation_size — оценка места на диске.
• EXPLAIN (ANALYZE, BUFFERS) — измерение I/O и CPU в запросах (BUFFERS доступен с psql). Пример: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
• pg_repack — онлайн-реорганизация таблиц/индексов (внешний инструмент), версия 1.x; удобен для уменьшения bloat без долгого downtime.

Внутренние ссылки для дальнейшего изучения: статья по мониторингу DevOps и материалы по проектированию БД в Базы данных.

Частые вопросы

как понять, какой индекс даст выигрыш?

Используйте EXPLAIN (ANALYZE, BUFFERS) для реального запроса: если до создания индекса план — Sequential Scan с высокой стоимостью и после создания индекс используется (Index Scan/Bitmap Index Scan) и время выполнения падает существенно (в разы или до сотен миллисекунд), индекс оправдан. Смотрите также pg_stat_user_indexes.idx_scan: активное использование индекса подтверждает его пользу за период наблюдения (7–30 дней). При малой кардионости (низкой селективности) индекс может не использоваться и только увеличит нагрузку на записи.

что делать с индексом, который занимает много места, но редко используется?

Сначала подтвердите низкое использование через pg_stat_user_indexes и мониторинг за 30 дней. Затем тестово удалите индекс в staging или сделайте DROP INDEX CONCURRENTLY в production в окно низкой нагрузки. Перед удалением снимите бэкап схемы. Если индекс нужен для редких, но критичных запросов (например, аналитика SLA), рассмотрите перенос этих запросов в read-replica и хранение индекса только там.

почему B-tree часто лучше HASH?

B-tree индекс покрывает операции равенства и диапазонов и в современных версиях PostgreSQL имеет стабильную производительность и надежность; HASH раньше имел ограничения и был менее надежен при crash recovery. Сейчас HASH может быть полезен в узких сценариях, но B-tree чаще проще в сопровождении и поддерживает больше операторов без дополнительных настроек.

сколько индексов слишком много?

Точного числа нет, но практическое правило: суммарный размер индексов не должен превышать 20–50% размера таблицы для OLTP; если индексы съедают >50% и замедляют записи, это сигнал к ревизии. Оцените влияние на throughput INSERT/UPDATE/DELETE и проведите нагрузочные тесты перед добавлением очередного индекса.

когда использовать BRIN вместо B-tree?

BRIN эффективен, если данные в таблице физически коррелированы с колонкой индекса (например, временные метки при последовательных вставках). BRIN значительно компактнее и быстрее строится. Используйте BRIN для больших исторических таблиц (>10 GB), где селективность запроса относительно узкая по временным диапазонам, и где хранение большого B-tree нецелесообразно.