Что такое индекс и как он ускоряет запросы

Дашборд команды «фильтр по status» выполняется за 50 мс на 100k строк в staging. В продакшне с 50M строк — 8 секунд. Схема корректна. Запрос корректен. Одна DDL-строка — CREATE INDEX CONCURRENTLY ON orders(status) — возвращает 20 мс. Эта строка — весь смысл урока.

Что такое индекс

Индекс — это отдельная структура данных (обычно B-tree), которая хранит отсортированную копию значений одного или нескольких столбцов плюс указатели на фактические строки. Когда запрос фильтрует по индексированному столбцу, Postgres проходит дерево за O(log n) шагов вместо сканирования каждой строки.

Метафора книжного индекса точна: предметный указатель книги — это отсортированный список терминов с номерами страниц. Чтобы найти «MVCC», открываешь указатель (один поиск O(log n)), читаешь номер страницы и переходишь туда. Без указателя — сканируешь каждую страницу. Индекс базы данных идентичен: копия значений столбца, организованная для быстрого поиска по одному конкретному вопросу.

Цена: индексы облагают налогом каждую запись

Каждый индекс — это отдельная структура на диске, которая должна оставаться синхронизированной с таблицей. Каждый INSERT, UPDATE или DELETE, затрагивающий индексированный столбец, также должен обновить индекс — что стоит CPU и I/O. Таблица с десятью неиспользуемыми индексами при write-heavy нагрузке тратит на запись в 10 раз больше, чем необходимо.

Типичный overhead на запись одного индекса: 5–50 мкс на операцию записи. При 10 000 inserts/сек с десятью индексами это дополнительные 0,5–5 мс overhead в секунду — измеримо, и часто узкое место при избыточной индексации.

Размер индекса на диске: примерно bytes_per_key × row_count × 1.3. Для BIGINT-ключа (8 байт) и таблицы из 100M строк получается ~1 GB индекс. Каждый дополнительный индекс умножает это.

Конкретный сценарий

Запрос: SELECT * FROM orders WHERE user_id = 42 на таблице с 10M строк. Без индекса: sequential scan, ~2 секунды. После CREATE INDEX ON orders(user_id): index lookup плюс heap fetch, ~3 миллисекунды. Тот же запрос, ускорение в 600 раз, одна DDL-строка.

Postgres поставляется с шестью типами индексов. B-tree — дефолт и покрывает 95% продакшн-использования: равенство, диапазон, ORDER BY, LIKE 'abc%'. Остальные пять (GIN, GiST, BRIN, Hash, Bloom) служат для специфических форм данных и рассматриваются в уроке 04.

Продакшн-правило: всегда использовать CONCURRENTLY

CREATE INDEX берёт блокировку ACCESS EXCLUSIVE — все чтения и записи заблокированы на всё время построения. На таблице с активным трафиком даже 5-минутная блокировка — это инцидент.

CREATE INDEX CONCURRENTLY использует многофазное построение без эксклюзивной блокировки. Он сканирует таблицу дважды и ждёт in-flight транзакций между фазами. Он в 2–3 раза медленнее и не может быть обёрнут в транзакцию, но не создаёт видимого воздействия на трафик приложения.

Если построение CONCURRENTLY завершается неудачей (например, уникальный индекс находит дубликат или процесс убивается), индекс остаётся в состоянии INVALID (indisvalid = false в pg_indexes). Планировщик рассматривает его как несуществующий. Исправление: DROP INDEX CONCURRENTLY и повторная попытка.