Типы индексов в PostgreSQL — B-tree, GIN, GiST, BRIN

Опирается на правила: PG-I-020 … PG-I-026 из PostgreSQL Style Guide → раздел Типы индексов.

Важно знать

• B-tree — дефолт в 80-90% случаев. Все операторы сравнения, LIKE 'prefix%', ORDER BY.
• Hash почти всегда хуже B-tree — не берите без явной причины.
• GIN — для JSONB, массивов, full-text search. Медленная запись, быстрое чтение.
• GiST — range-типы, PostGIS, kNN, EXCLUDE. Медленнее GIN на чтение, быстрее на запись.
• BRIN — огромные append-only таблицы (логи, метрики). Размер KB на гигабайты.
• SP-GiST — редко. IP-префиксы, неравномерные данные.
• pg_trgm для LIKE '%substring%'.
• Partial index — WHERE-условие, размер в разы меньше.

PostgreSQL — это не «индекс = B-tree». На специфических задачах правильный выбор сокращает запрос с минут до миллисекунд. UCP формулирует таблицу выбора.

Шесть типов

Тип	Когда	Что внутри
B-tree	=, <, >, BETWEEN, LIKE 'x%', ORDER BY	сбалансированное дерево
Hash	только = для крупных значений; редко	хэш-таблица
GIN	JSONB, массивы, full-text	inverted index
GiST	геометрия, range, kNN, EXCLUDE	дерево по обобщённой метрике
BRIN	append-only с упорядоченностью	block range
SP-GiST	IP-префиксы, фрактальные	space-partitioned tree

B-tree — дефолт

PG-I-020:

CREATE INDEX ix_orders_created_at ON orders (created_at);   -- default
CREATE INDEX ix_orders_created_at ON orders USING btree (created_at);   -- то же явно

Поддерживает:

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

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

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

PG-I-021:

CREATE INDEX ix_account_email_hash ON account USING hash (email);

Теоретически hash быстрее B-tree на чистом = для длинных значений. На практике:

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

Берите только при специфических benchmark-доказательствах.

GIN — JSONB, массивы, full-text

PG-I-022:

-- 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);

-- Full-text
CREATE INDEX ix_post_search ON post USING gin (to_tsvector('russian', body));

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

GiST — обобщённый

PG-I-023:

-- Range
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 &&)
);

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

-- Full-text (GIN обычно быстрее, GiST даёт ranking «на лету»)
CREATE INDEX ix_post_search_gist ON post USING gist (to_tsvector('russian', body));

• Поддерживает kNN — ORDER BY x <-> point через индекс.
• EXCLUDE constraint — гарантирует непересечение.
• Чтение медленнее GIN на типичных JSONB/array, но универсальнее.

GIN vs GiST

• GIN: быстрее чтение, медленнее запись, больше размер. Read-heavy таблицы.
• GiST: быстрее запись, медленнее чтение, меньше размер. Write-heavy таблицы.

BRIN — для огромных таблиц

PG-I-024: append-only с естественной упорядоченностью.

CREATE INDEX ix_event_log_at_brin ON event_log USING brin (occurred_at);

• Block range — min/max для каждого диапазона страниц (default 128).
• Размер крошечный — десятки KB на таблицу в гигабайтах.
• Range queries быстрые: «события за последний час» — BRIN отбросит блоки, где max < now()-1h.
• Точечный = — не ускоряет.
• Только при физической упорядоченности. Если строки вставляются не по возрастанию occurred_at — BRIN деградирует, нужен периодический CLUSTER.

Подходит: логи и события с автоматически возрастающим timestamp, метрики, архивные партиции по датам.

Не подходит: обычные таблицы с обновлениями, точечный поиск.

SP-GiST — редко

Space-partitioned для неравномерных данных:

CREATE INDEX ix_request_url ON request USING spgist (url);   -- префиксное дерево
CREATE INDEX ix_visit_ip ON visit USING spgist (ip);

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

Сравнительная таблица

Задача	Индекс
=/</>/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
Full-text (read-heavy)	GIN
Full-text (write-heavy)	GiST
Геометрия PostGIS, kNN	GiST
Range-типы	GiST
EXCLUDE для непересечений	GiST + btree_gist
Append-only с timestamp	BRIN
IP-префиксы	SP-GiST

pg_trgm для LIKE '%X%'

PG-I-025:

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 разбивает строку на триграммы (тройки символов). Индекс находит строки с пересечением. Полезно для нечёткого поиска.

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');

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

Что запрещено

Антипаттерн	Правило	Что взамен
Hash без benchmark-доказательства	PG-I-021	B-tree
B-tree на JSONB-поле	PG-I-022	GIN
B-tree на массиве	PG-I-022	GIN
B-tree на full-text	PG-I-022	GIN или GiST
LIKE '%X%' без pg_trgm	PG-I-025	GIN + gin_trgm_ops
BRIN на таблице с обновлениями	PG-I-024	B-tree
Полный индекс там, где 90% строк не нужны	PG-I-026	partial с WHERE
Default GIN там, где нужен jsonb_path_ops	PG-I-022	jsonb_path_ops (меньше, только @>)

Куда дальше

• PG → Типы индексов — нормативные формулировки.
• Composite-индексы и левый префикс — порядок колонок.
• Как выбрать индекс и считать селективность — критерии.
• EXPLAIN ANALYZE — проверить, что индекс работает.
• JSONB — GIN детали.
• Массивы и range — GIN и GiST для range.
• Полнотекстовый поиск — GIN на tsvector.
• Расширения — pg_trgm, btree_gist.