Типы индексов: GIN, GiST, BRIN, Hash, Bloom и HOT-обновления

Команда добавляет CREATE INDEX ON events(payload) на JSONB-столбец и наблюдает, как время запроса меняется с 4 секунд… на 4 секунды. B-tree не может индексировать внутри JSONB. GIN может. Знание того, какой тип индекса соответствует какой форме данных, — это разница между рабочим индексом и потраченным часом и гигабайтом.

Почему B-tree недостаточно

B-tree индексирует одно значение на строку и отвечает на запросы equality, range и sort по этому значению. Когда строка содержит несколько значений (JSONB-ключи, элементы массива, текстовые лексемы) или когда отношение «меньше чем» не определено (геометрические фигуры, IP-диапазоны), B-tree не имеет полезного порядка сортировки. Другие типы индексов заполняют эти пробелы.

GIN: inverted index для composite-типов

Generalized Inverted Index. Где B-tree хранит одно значение на строку, GIN хранит много значений на строку — каждый ключ в JSONB-документе, каждый элемент в массиве, каждый лексем в tsvector. Внутри GIN — B-tree indexed values, каждое указывает на posting list (или posting tree) TID-ов, содержащих это значение.

Use cases:

• JSONB containment: WHERE payload @> '{"event_type":"login"}'
• Array overlap: WHERE tags && ARRAY['premium','active']
• Full-text search: WHERE to_tsvector('english', body) @@ to_tsquery('postgres')

GIN-индексы обычно в 2-5x больше индексируемых данных столбца. На high-write JSONB-столбцах каждая вставка строки обновляет каждый indexed ключ — fastupdate режим откладывает эти обновления в background-процесс. Продакшн-дисциплина: измерь GIN write-cost после добавления на write-heavy столбец; при спайке latency вставки — рассмотри fastupdate или expression-индексы на известных горячих JSONB-полях.

GiST: generic search tree

Для данных без глобального порядка сортировки: геопространственные фигуры, IP-диапазоны, timestamp-диапазоны. GiST расширяем — каждый non-leaf узел хранит «предикат» (например, bounding box), обобщающий своё поддерево. Запросы отсекают поддеревья, проверяя предикат перед спуском.

Большинство инженеров встречают GiST через PostGIS:

CREATE INDEX ON locations USING GIST (geom);
-- включает: WHERE ST_DWithin(geom, ST_MakePoint(-74, 40.7), 1000)

Range-типы (int4range, tsrange) также используют GiST по умолчанию. Nearest-neighbour запросы (ORDER BY geom <-> point LIMIT 10) требуют GiST.

BRIN: крошечный индекс для огромных упорядоченных таблиц

Block Range INdex. Хранит min и max значения per block range (дефолт: 128 страниц, ~1 MB heap). Для ответа на запрос Postgres читает BRIN-метаданные, находит candidate ranges и сканирует только эти блоки. BRIN приближённый — сужает heap-scan, но не устраняет его.

BRIN полезен только когда данные физически упорядочены по indexed-столбцу. Классический случай: time-series таблица со вставками в порядке timestamp.

-- 10 миллиардов строк, 8 TB таблица
CREATE INDEX ON events USING BRIN (created_at);
-- Результат: ~несколько MB индекс; range-запрос читает только нужные 128-страничные блоки

Для случайно распределённых данных BRIN не даёт ускорения — min-max каждого block range охватывает весь диапазон.

Hash-индексы

Только equality; нет range-поддержки; нет ORDER BY. До Postgres 10 Hash-индексы не логировались в WAL и не переживали сбои — избегай в старых инсталляциях. После Postgres 10 — стабильны. Узкий use case: equality-lookup на очень широких ключах. На практике B-tree почти всегда предпочтительнее (поддерживает range и ORDER BY). Дефолт B-tree.

Bloom-индексы для multi-column equality

Расширение bloom — вероятностный индекс. Bloom на (a, b, c, d, e) ускоряет любой запрос с фильтром по любому подмножеству столбцов — без leading-column rule. Цена: approximate — каждый матч — кандидат, требующий re-проверки в heap (false positives возможны). Use case: таблицы с много столбцами, где запросы непредсказуемо комбинируют разные наборы фильтров, что делает невозможным построить 2^N composite B-tree.

HOT-обновления и fillfactor

При UPDATE Postgres создаёт новую tuple-версию (MVCC) и должен обновить каждый индекс, ссылающийся на эту строку — даже индексы с неизменёнными столбцами. Это write amplification: одно логическое обновление разворачивается в N обновлений индексов.

HOT (Heap-Only Tuple) устраняет обновления индексов при выполнении обоих условий:

1. Ни один indexed-столбец не изменился в этом UPDATE.
2. Новый tuple помещается на ту же heap-страницу, что и старый (есть свободное место).

Когда HOT срабатывает, меняется только heap-страница — записи индекса по-прежнему указывают на старый TID, который цепочкой ведёт к новой версии. Результат: резко сниженный write-overhead на update-heavy workloads.

Fillfactor управляет заполненностью каждой страницы при вставке (дефолт: 100% для таблиц, 90% для B-tree). Установка fillfactor в 70-80% на update-heavy таблице оставляет место на страницах для HOT:

ALTER TABLE orders SET (fillfactor = 70);

Цена: начальное хранилище больше — меньший fillfactor означает больше страниц. Продакшн-команды измеряют частоту обновлений и настраивают fillfactor на горячих таблицах — часто упускаемый рычаг.

Write-cost accounting

Каждый индекс добавляет ~5-50 мкс на запись на каждый затронутый индекс. Для таблицы с 10 индексами каждый INSERT стоит 50-500 мкс в overhead на индексы сверх heap write. GIN-индексы на JSONB-столбцах с многими ключами могут стоить 50-200 мкс на запись — больше любого B-tree.

Senior-команды моделируют index budget для каждой таблицы: при заданном write-throughput target, сколько индексов может себе позволить таблица? Инструменты: pg_stat_statements для baseline latency, pgbench для синтетических write-бенчмарков под новые индексы.

Размер индекса и shared_buffers

Postgres кеширует горячие страницы в shared_buffers (типично 25% RAM сервера). Большие индексы конкурируют с heap-страницами за cache-место. 5 GB GIN-индекс на JSONB-столбце может вытеснить горячие heap-страницы, превращая запросы, ранее быстрые из cache, в disk-bound. Симптом: pg_stat_database.blks_hit / blks_read падает после добавления нового индекса. Mitigation: partial-индексы, expression-индексы на known-hot полях, удаление неиспользуемых.

Почему ANALYZE важнее самого индекса

Выбор планировщика — использовать индекс или нет — зависит от статистики таблицы (pg_statistic): распределение значений, количество строк, корреляции. Устаревшая статистика вынуждает планировщика недо- или переоценивать количество строк, соответствующих фильтру, что приводит к неверному выбору плана.

Продакшн-дисциплина:

• Запускай ANALYZE после bulk-insert, больших UPDATE или schema-изменений.
• Снижай autovacuum_analyze_scale_factor для таблиц со skewed данными (дефолт 0.2 = 20% изменений — установи 0.02 для горячих OLTP-таблиц).
• Используй CREATE STATISTICS для коррелированных столбцов (например, country, city).

«Неправильный выбор индекса» — часто в действительности «устаревшая статистика».