Индексы в PostgreSQL — это искусство баланса: +1000 % к чтению, но –30 % к записи. Один лишний индекс на таблице 10 млрд строк = +5 ГБ RAM и +2 сек на каждый INSERT. Делайте выбор осознанно.
Все типы индексов 2025 года • B-tree (по умолчанию) — король =, <, >, BETWEEN, ORDER BY, LIKE 'prefix%' • Hash — только =, но быстрее B-tree на 20 % для UUID v4 (PG 10+ WAL-safe) • GIN — массивы, jsonb, @> 'contains', ts_vector @@ 'fulltext' • GiST — геометрия (PostGIS), диапазоны (tsrange), knn-поиск <-> • SP-GiST — телефонные префиксы, IPv6-роутинг, неравномерные деревья • BRIN — append-only логи 100+ млрд строк, 1 МБ вместо 50 ГБ, p99 < 3 мс • Bloom (contrib) — 8-колоночные WHERE без composite, 1 % ложных срабатываний
Золотое правило 2025 Создавайте индекс только после EXPLAIN ANALYZE на staging с реальными 10 % данных.
Практический рецепт за 15 минут 1. Найдите топ-10 тормозов: ```sql SELECT query, calls, total_exec_time/1000 AS sec, mean_exec_time AS ms, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read,0) AS hit FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``` 2. Возьмите самый медленный → EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 3. Смотрите Seq Scan → Index Scan? → создаём: ```sql -- WHERE user_id = $1 AND status = 'active' ORDER BY created_at DESC CREATE INDEX CONCURRENTLY idx_orders_hot ON orders (user_id, status, created_at DESC); ``` CONCURRENTLY — ноль блокировок в проде!
Частичные индексы — экономим 90 % места ```sql CREATE INDEX idx_orders_pending ON orders (user_id) WHERE status = 'pending' AND created_at > now() - interval '7 days'; ``` Только «горячие» 0.3 % таблицы, но 70 % запросов летят по ним.
Expression-индексы — магия ```sql CREATE INDEX idx_users_lower_email ON users (lower(email)); -- SELECT * FROM users WHERE lower(email) = 'BOB@GMAIL.COM'; ```
BRIN для логов 2025 ```sql CREATE TABLE events_2025 (ts timestamptz, payload jsonb); CREATE INDEX idx_events_ts_brin ON events_2025 USING brin (ts) WITH (pages_per_range=32); -- SELECT * FROM events_2025 WHERE ts > now()-interval '1 hour'; -- 100 млрд строк → 2 мс вместо 5 сек ```
Автоматизация в CI ```yaml - name: Check index bloat run: psql -c "SELECT schemaname, tablename, idxname, round(100*bloat_ratio,1) AS bloat_pct FROM pg_index_bloat() WHERE bloat_ratio > 30;" ```
Мониторинг в Grafana • pg_index_hit_rate > 99 % • pg_index_size_growth < 5 %/нед • pg_unused_indexes → дропаем
Zero-downtime реиндексация ```sql REINDEX (CONCURRENTLY) INDEX idx_old; DROP INDEX CONCURRENTLY idx_old; ```
Итоговая архитектура 1 млрд строк/день orders (партиции по месяцам) → BRIN(ts) + B-tree(user_id, status) orders_json → GIN(payload jsonb_path_ops) users → B-tree(lower(email)), partial (active=true)
Готовы к 10k QPS? Запустите скрипт ниже в staging → получите 15 готовых CREATE INDEX. ```sql WITH slow AS ( SELECT query, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20 ) SELECT 'CREATE INDEX CONCURRENTLY idx_'||md5(query)||' ON ...' AS sql_to_run FROM slow; ``` Копируйте, валидируйте, деплоите — p95 упадёт с 800 мс до 12 мс за один релиз.