Пошаговое руководство по выбору, созданию и анализу индексов в PostgreSQL с рабочими командами и выводами. Примерное время выполнения полного набора шагов — 90–150 минут.
Статья была полезной?
Индекс в PostgreSQL — это структура данных, ускоряющая поиск строк без необходимости полного сканирования таблицы. Индексы заметно снижают I/O при селектах по ключевым колонкам, работают как вспомогательный механизм для уникальности и для ускорения соединений (JOIN).
Ожидаемый эффект: для селекта по колонке с правильно подобранным индексом время ответа может упасть с сотен миллисекунд до единиц миллисекунд на выборках до нескольких миллионов строк. Снижение задержки сопровождается платой: дополнительные записи при INSERT/UPDATE и затраты дискового пространства (индекс может занимать от нескольких процентов до превышать размер таблицы в отдельных случаях).
Индекс не нужен, если колонка имеет очень низкую кардионость (например, boolean или колонка с 95% NULL/одним значением) — индекс даст мало селективности и только добавит накладные расходы на запись. Индекс также лишний для таблиц, которые полностью перезаписываются на каждый апдейт (т.н. временные таблицы), или для колонок, по которым запросы редки и не критичны по латентности.
Типичные признаки избыточных индексов: более 30% размера таблицы в сумме индексов, сильно замедленные INSERT/UPDATE/DELETE, большое количество неиспользуемых индексов по результатам pg_stat_user_indexes за период 30 дней.
Команды создают 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.Создадим таблицу 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 из файла.Создадим 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: не поддерживается; просто запустите команду отдельно.Для поиска по тексту, часто используют 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;"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 не даст выигрыша.Измерим размер индексов и найдем неиспользуемые. Команды быстрые: 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 на целевой таблице.Удалять индексы нужно аккуратно: сначала проверьте отсутствие использования (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; не запускайте внутри транзакции.Индексы фрагментируются при массовой модификации данных. 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 если версия поддерживает; при повреждении — восстановление из бэкапа.
Скриншот вывода EXPLAIN ANALYZE с использованием индексного скана
Скриншот таблицы с размерами индексов и таблицы из pg_total_relation_size
Внутренние ссылки для дальнейшего изучения: статья по мониторингу 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 индекс покрывает операции равенства и диапазонов и в современных версиях PostgreSQL имеет стабильную производительность и надежность; HASH раньше имел ограничения и был менее надежен при crash recovery. Сейчас HASH может быть полезен в узких сценариях, но B-tree чаще проще в сопровождении и поддерживает больше операторов без дополнительных настроек.
Точного числа нет, но практическое правило: суммарный размер индексов не должен превышать 20–50% размера таблицы для OLTP; если индексы съедают >50% и замедляют записи, это сигнал к ревизии. Оцените влияние на throughput INSERT/UPDATE/DELETE и проведите нагрузочные тесты перед добавлением очередного индекса.
BRIN эффективен, если данные в таблице физически коррелированы с колонкой индекса (например, временные метки при последовательных вставках). BRIN значительно компактнее и быстрее строится. Используйте BRIN для больших исторических таблиц (>10 GB), где селективность запроса относительно узкая по временным диапазонам, и где хранение большого B-tree нецелесообразно.
Комментарии (0)
Войдите или зарегистрируйтесь, чтобы оставить комментарий
Загрузка комментариев…