Стратегия индексов: что, когда, какой ценой

Спроектируй стратегию индексирования для {{tables}} в {{db}}.

Базовая аксиома: каждый индекс — это вторая копия данных. Ускоряет read, замедляет write, ест диск и RAM. Цель — минимальный набор индексов, покрывающий реальные запросы. «На всякий случай» — анти-паттерн.

1. Что индексировать

Кандидаты: колонки в WHERE, JOIN ON, ORDER BY, GROUP BY.

Не индексируй:

• Low-cardinality booleans (is_active, deleted) — full scan быстрее, чем index scan + lookup
• Колонки которые никогда не появляются в WHERE — индекс мёртвый груз
• Маленькие таблицы (< 1000 строк) — full scan быстрее любого индекса

Cardinality матрица:

2. Single column vs composite

Single column — простой случай:

CREATE INDEX ON orders (user_id);
-- ускоряет: WHERE user_id = $1

Composite — когда WHERE содержит несколько условий вместе:

CREATE INDEX ON orders (user_id, created_at DESC);
-- ускоряет:
--   WHERE user_id = $1
--   WHERE user_id = $1 AND created_at > $2
--   WHERE user_id = $1 ORDER BY created_at DESC
-- НЕ ускоряет:
--   WHERE created_at > $2   ← левая колонка нужна

Правило левого префикса: composite index (A, B, C) работает для WHERE A, WHERE A AND B, WHERE A AND B AND C. Не работает для WHERE B, WHERE C, WHERE B AND C.

Порядок колонок:

1. Equality columns первыми (WHERE x = ?)
2. Range columns в конце (WHERE y > ?)
3. Внутри равных по типу — высокая cardinality первой

Пример query: WHERE tenant_id = ? AND status = 'active' AND created_at > ? Правильный индекс: (tenant_id, status, created_at) — equality, equality, range.

3. Covering indexes (INCLUDE)

Если запрос читает только колонки из индекса — БД может не идти в heap. Index-only scan.

-- Запрос
SELECT id, status FROM orders WHERE user_id = $1;

-- Без covering: index scan + heap lookup для каждой строки
CREATE INDEX ON orders (user_id);

-- Covering: всё из индекса
CREATE INDEX ON orders (user_id) INCLUDE (id, status);
-- или
CREATE INDEX ON orders (user_id, status);

INCLUDE (Postgres 11+) — добавляет колонки в leaf nodes без участия в key:

• Не влияет на сортировку/WHERE matching
• Не делает индекс UNIQUE
• Index растёт по размеру → write cost

Когда covering окупается: hot query, читает 2-3 маленькие колонки, heap lookup составляет > 30% времени.

4. Partial indexes — индекс только части таблицы

Когда запросы всегда фильтруют по предикату:

-- 99% queries по active заказам, 1% по архивным
CREATE INDEX ON orders (created_at)
  WHERE status = 'active';

-- Размер индекса в 100 раз меньше full
-- ускоряет: WHERE status = 'active' AND created_at > $1

Классические кейсы:

• WHERE deleted_at IS NULL — индекс только не-удалённых
• WHERE status = 'pending' — индекс только активных задач (queue)
• WHERE archived = false

Условие в WHERE индекса должно совпадать с предикатом запроса — иначе planner не использует.

5. Functional / expression indexes

Когда WHERE содержит выражение:

-- Запрос: WHERE LOWER(email) = $1
CREATE INDEX ON users (LOWER(email));

-- Запрос: WHERE date(created_at) = '2025-05-17'
CREATE INDEX ON events (date(created_at));

-- Запрос: WHERE (payload->>'event_type') = 'login'
CREATE INDEX ON logs ((payload->>'event_type'));

Иначе planner делает full scan — не понимает что LOWER(email) соответствует индексу на email.

6. Цена записи

Каждый INSERT/UPDATE/DELETE обновляет все индексы таблицы.

Таблица users, 6 индексов:
  INSERT user → 1 heap write + 6 index updates = 7 writes
  UPDATE last_login → 1 heap update + 1 index update (если last_login индексирован) = 2 writes

Эмпирика:

• 1-3 индекса: пренебрежимо для большинства workload
• 5-10: заметно на write-heavy таблицах
• 15+: проблема, особенно на bulk INSERT (5x-10x slowdown)

Тяжелее всего: UPDATE колонки, которая в индексе (особенно composite) → старый ключ delete, новый insert. Для hot updates выбирай партиции / эту колонку выноси.

7. Аудит unused indexes

Postgres: pg_stat_user_indexes показывает usage:

SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan AS scans,
  idx_tup_read AS tuples_read
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Кандидаты на удаление:

• idx_scan = 0 после нескольких недель (НЕ дней — могут быть редкие отчёты раз в месяц)
• Размер большой, scan'ов мало
• Дубликат другого индекса (composite (a,b) делает (a) лишним)

Перед удалением:

• pg_stat_reset() мог обнулить статы — проверь stats_reset в pg_stat_database
• На primary smотри, не на read replica — у них своя статистика
• Подожди полный business cycle (квартал)

Удаление: DROP INDEX CONCURRENTLY idx_name; — не блокирует таблицу.

8. Дубликаты и накладки

CREATE INDEX ON orders (user_id);                    -- (1)
CREATE INDEX ON orders (user_id, created_at);        -- (2)
-- (1) избыточен — (2) покрывает все запросы (1)

Кроме случая UNIQUE — single column UNIQUE и composite — разные констрейнты.

Postgres скрипт для поиска дублей:

-- См. pgexperts/pgx_scripts или duplicate_indexes.sql из wiki
SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS size,
       (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2
FROM (...)
GROUP BY key HAVING count(*) > 1;

9. Типы индексов (Postgres)

GIN для JSONB:

CREATE INDEX ON events USING GIN (payload);
-- ускоряет: WHERE payload @> '{"event_type": "login"}'

BRIN — крошечный (KB vs GB), но работает только если данные на диске уже отсортированы:

CREATE INDEX ON metrics USING BRIN (created_at);
-- идеально для append-only time-series

10. Создание без downtime

CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

CONCURRENTLY:

• Не блокирует writes
• Медленнее в 2-3 раза
• Может фейлиться (constraint violation, etc.) → INVALID индекс, дропни и повтори
• НЕ работает в транзакции

Проверка:

SELECT indexname FROM pg_indexes WHERE schemaname = 'public';
\d+ orders   -- покажет все индексы

11. Что отдать на выходе

• Список индексов с обоснованием (какой query ускоряет)
• Композиция: какие composite, в каком порядке колонки, почему
• Partial conditions где применимо
• Audit: какие unused, к удалению
• Write impact estimate (X-кратное замедление INSERT)
• Migration plan (CONCURRENTLY)

Anti-patterns

• ❌ Индекс на каждую колонку «на всякий случай» — INSERT медленнее в 5x, 80% индексов не используются
• ❌ Composite с range-колонкой не в конце ((created_at, user_id)) — index scan превращается в range scan по большому участку
• ❌ Single-column индексы рядом с covering composite — дубликат, выбрасываешь зря место
• ❌ Индекс на boolean без partial — Postgres почти всегда выберет full scan, индекс мёртвый
• ❌ WHERE LOWER(email) = ? без functional index на LOWER(email) — full scan несмотря на индекс (email)
• ❌ DROP INDEX без CONCURRENTLY на горячей таблице — блокировка writes на минуты
• ❌ CREATE INDEX (без CONCURRENTLY) в продакшене — full table lock на bigtable
• ❌ Удаление «unused» индекса после 3 дней наблюдения — пропустил месячный отчёт
• ❌ Партиционированная таблица без global index — UNIQUE (email) не уникален между партициями
• ❌ Индекс на колонку которую UPDATE'ишь в каждом запросе (last_seen_at) — каждый UPDATE = двойной write

В конце

• Список индексов: имя, колонки, тип (B-tree/GIN/partial), какой query ускоряет
• Порядок колонок в composite (equality → range, high → low cardinality)
• Covering candidates (INCLUDE) с обоснованием
• Unused indexes к удалению (с метриками)
• Write cost estimate
• Migration script (CREATE/DROP CONCURRENTLY)
• Monitoring план (pg_stat_user_indexes weekly review)