PostgreSQL: типы индексов — B-tree, Hash, GIN, GiST, BRIN, SP-GiST

Когда говорят «индекс» — обычно подразумевают B-tree. Но PostgreSQL поддерживает шесть разных типов, и на специфических задачах правильный выбор сокращает запрос с минут до миллисекунд. Эта статья — про то, какой брать в каком сценарии.

1. Шесть типов

Тип	Когда брать	Что внутри
B-tree	По умолчанию: `=`, `<`, `>`, `BETWEEN`, `LIKE 'x%'`, `ORDER BY`	Сбалансированное дерево
Hash	Только `=` для крупных значений; почти всегда уступает B-tree	Хэш-таблица
GIN	Полнотекстовый поиск, JSONB, массивы, hstore	Inverted index
GiST	Геометрия (`PostGIS`), range-типы, kNN-поиск, EXCLUDE	Сбалансированное дерево по обобщённой метрике
BRIN	Очень большие таблицы с естественной упорядоченностью (временные ряды)	Block range — индекс по диапазонам страниц
SP-GiST	Неравномерные данные (фрактальные деревья, IP-префиксы)	Space-partitioned tree

2. B-tree — дефолт

PG-I-020 CREATE INDEX без указания типа = B-tree. Это правильный выбор в 80–90% случаев.

CREATE INDEX ix_orders_created_at ON orders (created_at);   -- по умолчанию B-tree
CREATE INDEX ix_orders_created_at ON orders USING btree (created_at);  -- то же самое явно

B-tree поддерживает:

Все операторы сравнения: =, <, <=, >, >=, BETWEEN, IN.
LIKE 'prefix%' — префиксный поиск (но не LIKE '%suffix').
IS NULL / IS NOT NULL.
ORDER BY (включая DESC через index scan backward).
Уникальность (UNIQUE INDEX) и PRIMARY KEY.
Composite-индексы (см. Composite-индексы).

Для абсолютного большинства запросов на типичной CRUD/доменной задаче B-tree — единственный нужный тип.

3. Hash — почти никогда

PG-I-021 Hash-индекс почти всегда хуже B-tree. Не берите без явной причины.

CREATE INDEX ix_account_email_hash ON account USING hash (email);

Теоретически hash быстрее B-tree на чистом = для очень длинных значений (когда сравнение строк дороже, чем хэш). На практике:

B-tree поддерживает не только =, но и сортировку, диапазоны, IS NULL.
Размер hash и B-tree сравнимы.
Производительность практически одинаковая.
До PG10 hash вообще не журналировался в WAL — был непригоден для прода.

Берите только при очень специфических нагрузочных тестах, которые показали выигрыш.

4. GIN — для составных значений

PG-I-022 GIN — единственный нормальный индекс для JSONB, массивов, полнотекстового поиска.

-- JSONB
CREATE INDEX ix_event_payload ON event_log USING gin (payload jsonb_path_ops);

-- Массивы
CREATE INDEX ix_article_tags ON article USING gin (tags);

-- Полнотекстовый поиск
CREATE INDEX ix_post_search ON post USING gin (to_tsvector('russian', body));

Особенности:

Inverted index — для каждого «токена» (ключа JSONB / элемента массива / слова текста) хранится список строк, где он встречается.
Запись медленнее, чем B-tree — на каждое обновление надо разнести изменения по многим строкам индекса.
fastupdate — буфер pending-list ускоряет вставки за счёт замедления чтения, пока буфер не сольётся в основной индекс. По умолчанию включён.
Чтение быстрое — найти все строки с ключом «X» в JSONB за 1 lookup.

Подробнее по операторам в JSONB.

5. GiST — обобщённый индекс для нестандартных данных

PG-I-023 GiST — для range-типов, геометрии (PostGIS), полнотекста, kNN-поиска.

-- range-типы (см. /pg/arrays-ranges/)
CREATE EXTENSION btree_gist;
CREATE TABLE booking (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

-- геометрия
CREATE INDEX ix_shop_location ON shop USING gist (location);   -- PostGIS
SELECT * FROM shop ORDER BY location <-> ST_Point(37.6, 55.7) LIMIT 10;

-- полнотекстовый — GIN обычно лучше, но GiST даёт ranking «на лету»
CREATE INDEX ix_post_search_gist ON post USING gist (to_tsvector('russian', body));

Особенности:

Сбалансированное дерево по обобщённой метрике — каждый узел описывает «что-то общее» для своих потомков.
Поддерживает kNN — ORDER BY x <-> point находит ближайших соседей через индекс.
EXCLUDE constraint — гарантирует непересечение range, geometry, etc.
Чтение медленнее GIN на типичных задачах JSONB/array, но GiST универсальнее.

Правило выбора между GIN и GiST для текста / массивов / JSONB:

GIN — быстрее на чтение, медленнее на запись, больший размер. Берём, когда таблица читается чаще, чем пишется.
GiST — быстрее на запись, медленнее на чтение, меньший размер. Берём при интенсивных вставках.

6. BRIN — для огромных таблиц с упорядоченностью

PG-I-024 BRIN оправдан на таблицах от десятков миллионов строк, где данные физически упорядочены по индексируемой колонке.

Типичный сценарий — таблица событий / логов с timestamptz-колонкой, в которую только дописывают (append-only):

CREATE INDEX ix_event_log_at_brin ON event_log USING brin (occurred_at);

Особенности:

Block range index — хранит min/max значения для каждого диапазона страниц (по умолчанию 128 страниц).
Размер крошечный — десятки KB на таблицу в гигабайты, против гигабайтов у B-tree.
Запросы по диапазону работают быстро: «дай события за последний час» — BRIN сразу отбросит блоки, где max < now()-interval '1 hour'.
Точечный поиск (= ?) — не ускоряет.
Эффективен только при физической упорядоченности. Если строки вставляются не по возрастанию occurred_at, BRIN деградирует — таблица периодически нуждается в CLUSTER по этой колонке.

Подходит для:

Логи и события с автоматическим возрастающим timestamp.
Метрики (timestamp + значение).
Архивные таблицы с партиционированием по датам.

Не подходит для:

Обычных доменных таблиц с обновлениями.
Поиска по = в большой таблице (B-tree или GIN).

7. SP-GiST — редко

Space-partitioned GiST для неравномерно распределённых данных: префиксные деревья (IP-адреса, URL-пути), фрактальные структуры. Берётся, когда профайлер показывает, что обычный B-tree / GiST даёт плохую балансировку.

CREATE INDEX ix_request_url ON request USING spgist (url);
CREATE INDEX ix_visit_ip ON visit USING spgist (ip);   -- inet

В типичном бэкенде встречается крайне редко.

8. Сравнительная таблица: какой тип под какую задачу

Задача	Индекс
Поиск по `=` / `<` / `>` / `BETWEEN` обычной колонки	B-tree
Сортировка `ORDER BY`	B-tree
`LIKE 'prefix%'`	B-tree
`LIKE '%substring%'`	GIN с `pg_trgm`
Foreign key	B-tree
`UNIQUE` constraint	B-tree
Поиск по полю JSONB (`@>`, `?`)	GIN
Поиск элемента в массиве	GIN
Полнотекстовый поиск	GIN (если читаем чаще, чем пишем) или GiST
Геометрия (PostGIS), kNN	GiST
Range-типы (`tstzrange`, `int4range`)	GiST
`EXCLUDE` для непересечений	GiST + `btree_gist`
Огромная append-only таблица с timestamp	BRIN
IP-адреса с поиском по префиксу	SP-GiST
Точечный `=` по очень длинной строке	B-tree (Hash почти никогда не лучше)

9. `pg_trgm` — отдельно про `LIKE '%X%'`

PG-I-025 Для LIKE '%substring%' нужен GIN-индекс с расширением pg_trgm.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- ускоряет:
SELECT * FROM customer WHERE full_name ILIKE '%иван%';
SELECT * FROM customer WHERE similarity(full_name, 'иванв') > 0.4;

pg_trgm разбивает строку на триграммы (тройки символов), индекс находит строки с пересечением триграмм. Полезно для нечёткого поиска, когда полнотекстовый избыточен.

10. Partial index — фильтр на индекс

PG-I-026 Если индекс по факту нужен только для подмножества строк — WHERE-условие в индексе.

-- индексируем только активные заказы (90% таблицы — завершённые, не нужны)
CREATE INDEX ix_orders_active_customer
    ON orders (customer_id)
    WHERE status IN ('NEW', 'PAID', 'SHIPPED');

-- запрос должен включать то же условие, чтобы планировщик использовал индекс:
SELECT * FROM orders
WHERE customer_id = ?
  AND status IN ('NEW', 'PAID', 'SHIPPED');

Размер индекса — в разы меньше. Запись быстрее (индекс не обновляется на «не подходящих» строках).

Применимо к любому типу индекса (B-tree, GIN, GiST).

Чек-лист на ревью

[ ] B-tree по умолчанию для скаляров, FK, PK, ORDER BY.
[ ] JSONB / массивы — GIN.
[ ] Range / геометрия — GiST.
[ ] Append-only таблицы > 50M строк с timestamp — кандидат на BRIN.
[ ] LIKE '%X%' — GIN с pg_trgm, не B-tree.
[ ] Если индекс нужен только для подмножества — partial с WHERE.
[ ] Hash — не использовать без явных нагрузочных оснований.
[ ] Для полнотекста думать GIN vs GiST по соотношению чтение/запись.

Связанные

Composite-индексы — порядок полей и левый префикс.
Как выбрать индекс и считать селективность.
JSONB — GIN-операторы для jsonb.
Массивы и range-типы — GiST для range и EXCLUDE.

1. Шесть типов

2. B-tree — дефолт

3. Hash — почти никогда

4. GIN — для составных значений

5. GiST — обобщённый индекс для нестандартных данных

6. BRIN — для огромных таблиц с упорядоченностью

7. SP-GiST — редко

8. Сравнительная таблица: какой тип под какую задачу

9. pg_trgm — отдельно про LIKE '%X%'

10. Partial index — фильтр на индекс

Чек-лист на ревью

Связанные

9. `pg_trgm` — отдельно про `LIKE '%X%'`