GIN, GiST, BRIN и специальные индексы

Цели урока

• Создавать GIN индексы для полнотекстового поиска и JSONB
• Применять GiST для геозапросов и диапазонных типов
• Использовать BRIN для append-only таблиц с временными рядами
• Оптимизировать индексы через partial и expression подходы

B-Tree покрывает 80% задач. Но полнотекстовый поиск, геозапросы, гигантские временные ряды требуют специализированных структур. GIN, GiST и BRIN встроены в PostgreSQL - никаких дополнительных сервисов.

• **Airbnb:** GiST для поиска жилья в географическом радиусе
• **Вместо Elasticsearch:** GIN + tsvector для полнотекстового поиска прямо в PostgreSQL
• **IoT/Metrics:** BRIN на timestamp для таблиц в сотни гигабайт
• **SaaS:** partial index на active records сокращает индекс в 10-100 раз

GIN для полнотекстового поиска

Нужно найти все статьи, где встречаются слова «машинное обучение». B-Tree не поможет - он сравнивает строки целиком, не слова внутри. **GIN (Generalized Inverted Index)** работает как индекс книги: слово → список страниц где оно встречается. Поиск «машинное» даёт список документов, поиск «обучение» даёт другой список - GIN возвращает пересечение.

GIN также используется для **jsonb поиска** и массивов. Операторы `@>` (содержит), `?` (ключ существует), `&&` (пересечение массивов) - все поддерживаются GIN индексом.

**GIN vs B-Tree для текста:** B-Tree может найти строку по LIKE 'prefix%', но не по '%суффикс' и не по словам внутри. GIN индексирует каждый токен отдельно - поиск по любому слову одинаково быстрый.

GiST для геоданных

Найти все кафе в радиусе 2 км от точки (55.75, 37.62). B-Tree хранит числа в линейном порядке - он может отфильтровать по latitude или по longitude, но не по расстоянию. **GiST (Generalized Search Tree)** - расширяемый фреймворк для пространственных структур. PostGIS строит R-Tree поверх GiST для геозапросов.

GiST также используется для **диапазонных типов** (range types). Оператор `&&` для ranges проверяет пересечение периодов и использует GiST индекс - полезно для бронирований без конфликтов.

BRIN для временных рядов

Таблица `metrics` с 10 миллиардами строк телеметрии. B-Tree индекс на `timestamp` занял бы 200GB. **BRIN (Block Range Index)** хранит только min/max значений для каждого диапазона физических страниц - весь индекс занимает ~50MB. Работает потому что данные вставляются хронологически: новые строки на новых страницах.

**Когда BRIN не работает:** если данные вставляются не в порядке timestamp, min/max диапазонов перекрываются - планировщик не может исключить страницы. BRIN эффективен только при высокой **корреляции** между порядком вставки и значением колонки.

Partial Indexes

Таблица `orders` - 100 миллионов строк. 99% имеют статус 'completed', запросы по ним не нужны. Реально нужен быстрый поиск по 1% - статус 'pending'. **Partial Index** индексирует только строки, удовлетворяющие условию WHERE. Индекс в 100 раз меньше - обновления быстрее, кеш эффективнее.

Partial index решает и проблему **NULL в уникальных индексах**. По стандарту SQL, NULL != NULL, поэтому UNIQUE разрешает несколько NULL. Partial index `WHERE col IS NOT NULL` гарантирует уникальность только для не-NULL значений.

Expression Indexes

Запрос `WHERE LOWER(email) = 'user@example.com'` не использует обычный индекс на `email` - функция LOWER меняет значение, B-Tree не знает результата заранее. **Expression Index** индексирует результат выражения или функции, а не само поле.

**Важное требование:** выражение в запросе должно совпадать **точно** с выражением в индексе. `LOWER(email)` и `LOWER(TRIM(email))` - это разные индексы. PostgreSQL сравнивает деревья выражений.

**Expression index** - это специальный случай функционального индекса. Можно индексировать любое IMMUTABLE выражение: арифметику, string функции, JSON операторы, пользовательские функции. Главное - выражение в WHERE должно совпадать с выражением в CREATE INDEX.

Специализированные индексы

• GIN: инвертированный индекс для токенов - полнотекст (@@), jsonb (@>), массивы (&&)
• GiST: расширяемый фреймворк - геоданные (PostGIS R-Tree), range types, KNN (<->)
• BRIN: min/max по диапазонам страниц - для хронологических данных, в тысячи раз меньше B-Tree
• Partial index: WHERE в CREATE INDEX - индексирует только нужные строки
• Expression index: индексирует LOWER(col), jsonb->>'key' - запрос должен совпадать точно

Связанные темы

Специализированные индексы дополняют B-Tree там, где структура данных не линейна.

• B-Tree индексы — Базовый тип, отправная точка
• Query Optimization — Как планировщик выбирает тип индекса
• Полнотекстовый поиск — Глубже в tsvector, tsquery, ранжирование

Вопросы для размышления

• Когда стоит выбрать PostgreSQL + GIN вместо отдельного Elasticsearch?
• Как определить что таблица подходит для BRIN без запуска запросов?
• Можно ли комбинировать partial + expression + GIN в одном индексе?

Связанные уроки

• ds-09-trees-intro