Индексы PostgreSQL для SaaS: B-tree vs GIN vs GiST

Какую проблему решают индексы на реальных экранах SaaS

Индекс меняет способ, которым PostgreSQL находит строки. Без индекса база часто вынуждена прочитать большую часть таблицы (seq scan), а затем откинуть большую часть данных. С подходящим индексом она может сразу прыгнуть к подходящим строкам (index lookup) и забрать только то, что нужно.

Вы замечаете это рано в SaaS, потому что повседневные экраны сильно нагружают запросами. Один клик может запустить несколько чтений: страницу списка, подсчёт общего количества, пару карточек на дашборде и поле поиска. Когда таблица растёт от тысяч до миллионов строк, тот же запрос, который раньше казался мгновенным, начинает притормаживать.

Типичный пример — страница Orders, отфильтрованная по статусу и дате, отсортированная по новизне, с пагинацией. Если PostgreSQL приходится сканировать всю таблицу orders, чтобы найти оплаченные заказы за последние 30 дней, каждый загруз страницы делает лишнюю работу. Хороший индекс превращает это в быстрый прыжок к нужному фрагменту данных.

Индексы не бесплатны. Каждый индекс даёт более быстрые чтения для конкретных запросов, но замедляет записи (INSERT/UPDATE/DELETE должны обновлять индексы) и занимает место на диске (и увеличивает нагрузку на кэш). Поэтому начинать нужно с реальных шаблонов запросов, а не с типов индексов.

Простое правило, которое избавляет от лишней работы: добавляйте индекс только тогда, когда можете указать конкретный, частый запрос, который он ускорит. Если вы строите экраны с чат‑помощником вроде Koder.ai, полезно захватить SQL, который стоит за вашими страницами списков и дашбордами, и использовать его как список пожеланий по индексам.

B-tree vs GIN vs GiST простыми словами

Большая часть путаницы с индексами исчезает, когда вы перестаёте думать о возможностях (JSON, поиск, массивы) и начинаете думать о форме запроса: что делает WHERE, и как вы хотите получить упорядоченные результаты?

B-tree: быстро для сортировки и сравнений

Используйте B-tree, когда ваш запрос выглядит как обычные сравнения и вам важен порядок сортировки. Это рабочая лошадка для равенств, диапазонов и джоинов.

Примеры форм запроса: фильтрация по tenant_id = ?, status = 'active', created_at >= ?, соединение users.id = orders.user_id или показ «сначала новые» с ORDER BY created_at DESC.

GIN и GiST: когда в строке много искомых значений

GIN (Generalized Inverted Index) подходит, когда одна колонка содержит много элементов, и вы спрашиваете «содержит ли она X?». Это часто встречается с JSONB ключами, элементами массивов и векторами полнотекста.

Примеры форм: metadata @> {'plan':'pro'} на JSONB, tags @> ARRAY['urgent'], или to_tsvector(body) @@ plainto_tsquery('reset password').

GiST (Generalized Search Tree) лучше для вопросов о расстоянии или перекрытии, где значения ведут себя как диапазоны или фигуры. Часто используется для range‑типов, геометрии и некоторых «ближайших совпадений». Примеры: перекрывающиеся временные окна с range‑колонками, поиски по схожести (например, триграммы), или пространственные запросы (PostGIS).

Практический способ выбора:

• Если вы фильтруете или сортируете по обычным колонкам — начните с B-tree.
• Если вы проверяете вхождение или членство — смотрите GIN.
• Если вы спрашиваете «насколько близко» или «перекрывается ли» — GiST часто подходит.
• Если запрос редкий или таблица маленькая — новый индекс может быть не нужен.
• Если вы не можете описать форму запроса, замерьте сначала (EXPLAIN) перед добавлением индекса.

Индексы ускоряют чтение, но портят время записи и занимают диск. В SaaS эта компромиссная зависимость особенно важна для горячих таблиц: events, sessions, activity_logs.

Шаблоны B-tree для фильтров, сортировки и пагинации

Большинство экранов списков в SaaS имеют одинаковую форму: граница арендатора, пара фильтров и предсказуемая сортировка. B-tree — стандартный выбор здесь, и он обычно самый дешёвый в обслуживании.

Обычная картина: WHERE tenant_id = ? плюс фильтры вроде status = ?, user_id = ?, и временной диапазон created_at >= ?. Для составных B-tree индексах ставьте сначала колонки для равенств (=), затем колонку, по которой вы сортируете.

Правила, которые хорошо работают в большинстве приложений:

• Начинайте с tenant_id, если каждый запрос привязан к арендатору.
• Ставьте фильтры с = следующими (часто status, user_id).
• Ставьте колонку из ORDER BY последней (обычно created_at или id).
• Используйте INCLUDE, чтобы покрывать страницы списка, не расширяя ключ индекса.
• Предпочитайте seek‑пагинацию вместо offset при глубоких страницах.

Реалистичный пример: страница Tickets, показывающая сначала самые новые, отфильтрованная по статусу.

-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);

Этот индекс покрывает и фильтр, и сортировку, так что Postgres может избежать сортировки большого набора результатов. INCLUDE (title) помогает странице списка коснуться меньшего числа страниц таблицы, сохраняя ключи индекса узкими.

Для временных диапазонов тот же подход применим:

SELECT id, created_at
FROM events
WHERE tenant_id = $1
  AND created_at >= $2
  AND created_at <  $3
ORDER BY created_at DESC
LIMIT 100;

CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);

Пагинация — где многие SaaS тормозят. Offset‑пагинация (OFFSET 50000) заставляет базу проходить большое число строк. Seek‑пагинация остаётся быстрой, используя последний увиденный ключ сортировки:

SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
  AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;

С правильным B-tree индексом это остаётся быстрым даже при росте таблицы.

Индексирование с учётом арендатора, чтобы не переиндексировать всё подряд

Большинство SaaS — мультиарендные: каждый запрос ограничен одним tenant. Если ваши индексы не включают tenant_id, Postgres всё ещё может быстро найти строки, но часто он просматривает гораздо больше записей в индексе, чем нужно. Индексы, учитывающие tenant, группируют данные каждого арендатора в индексе, так что обычные экраны остаются быстрыми и предсказуемыми.

Простое правило: ставьте tenant_id первым в индексе, если запрос всегда фильтруется по арендаторам. Затем добавляйте колонку, по которой вы чаще всего фильтруете или сортируете.

Высокоэффективные, но скучные индексы обычно выглядят так:

• (tenant_id, created_at) для списков недавних элементов и курсорной пагинации
• (tenant_id, status) для фильтров по статусу (Open, Paid, Failed)
• (tenant_id, user_id) для экранов «элементы этого пользователя»
• (tenant_id, updated_at) для admin‑просмотров «недавно изменённые»
• (tenant_id, external_id) для lookup‑запросов из вебхуков или импортов

Переиндексация (over-indexing) случается, когда вы создаёте индекс для каждой слегка отличающейся страницы. Прежде чем добавить ещё один, проверьте, покрывает ли существующий составной индекс нужные вам левые колонки. Например, если у вас есть (tenant_id, created_at), вам обычно не нужен (tenant_id, created_at, id), если вы действительно не фильтруете по id после этих колонок.

Частичные индексы сокращают размер и стоимость записи, когда большинство строк не релевантно. Хороши для soft‑delete и «только активные» данных, например индексировать только где deleted_at IS NULL или только где status = 'active'.

Каждый дополнительный индекс делает записи дороже. Вставки обновляют каждый индекс, а обновления могут менять несколько индексов, даже если меняется одна колонка. Если ваше приложение поглощает много событий (включая приложения, быстро собранные с помощью Koder.ai), держите индексы сфокусированными на паре форм запросов, которые пользователи реально используют каждый день.

Индексирование JSONB: GIN и целевые expression‑индексы

JSONB удобен, когда приложению нужны гибкие дополнительные поля: feature flags, атрибуты пользователя или настройки по арендатору. Загвоздка в том, что разные операторы JSONB ведут себя по‑разному, поэтому лучший индекс зависит от того, как вы запрашиваете данные.

Две формы особенно важны:

• Containment: «содержит ли этот JSON эти пары ключ‑значение?» с помощью @>.
• Извлечение пути: «какое значение у этого поля?» через -> / ->> (часто сравнивают с =).

Когда GIN — правильный выбор

Если вы часто фильтруете с @>, GIN‑индекс на JSONB колонке обычно оправдан.

-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata @> '{"region":"eu","plan":"pro"}';

-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);

Если структура JSON предсказуема и вы в основном используете @> по ключам верхнего уровня, jsonb_path_ops может быть меньше и быстрее, но поддерживает меньше типов операторов.

Когда лучше expression‑индекс

Если UI постоянно фильтрует по одному полю (например, plan), извлечение этого поля и его индексирование часто быстрее и дешевле, чем широкий GIN.

SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata->> 'plan' = 'pro';

CREATE INDEX accounts_plan_expr
ON accounts ((metadata->> 'plan'));

Практическое правило: держите JSONB для гибких, редко фильтруемых атрибутов, но продвигайте стабильные, часто используемые поля (plan, status, created_at) в реальные колонки. Если вы итеративно развиваете приложение, часто это простое изменение схемы, когда вы видите, какие фильтры появляются на каждой странице.

Пример: если вы храните { "tags": ["beta","finance"], "region": "us" } в JSONB, используйте GIN при фильтрации по наборам атрибутов (@>), и добавляйте expression‑индексы для ключей, которые определяют большинство представлений (plan, region).

Индексирование массивов: где GIN эффективен

Массивы удобны: их легко хранить и читать. users.roles text[] или projects.labels text[] хорошо работают, когда вы чаще спрашиваете одно: содержит ли строка значение? Именно здесь GIN помогает.

GIN — основной выбор для запросов про членство в массивах. Он разбивает массив на элементы и строит быстрый поиск по строкам, содержащим эти элементы.

Формы запросов, где это полезно:

• Содержит значение или набор: @> (array contains)
• Пересечение с набором: && (array shares any items)
• Иногда: = ANY(...), но @> часто предсказуемее

Типичный пример фильтрации пользователей по роли:

-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @> ARRAY['admin'];

CREATE INDEX users_roles_gin ON users USING GIN (roles);

И фильтрация проектов по набору меток (должен включать обе метки):

SELECT id, name
FROM projects
WHERE labels @> ARRAY['billing', 'urgent'];

CREATE INDEX projects_labels_gin ON projects USING GIN (labels);

Где люди удивляются: некоторые паттерны не используют индекс так, как вы ожидаете. Если вы превращаете массив в строку (array_to_string(labels, ',')) и затем ищете LIKE, GIN не поможет. Если нужны «начинается с» или нечеткие совпадения внутри меток — это уже про текстовый поиск, а не членство в массиве.

Массивы также сложно поддерживать, когда они превращаются в мини‑базу: частые обновления, метаданные на элемент (кто добавил метку, когда, почему) или аналитика по меткам. В таких случаях таблица‑связка вроде project_labels(project_id, label) обычно проще для валидации, запросов и эволюции.

Индексация поиска: полнотекст и нечёткие совпадения (GIN и GiST)

Для поисковых полей чаще всего встречаются два паттерна: полнотекстовый поиск (найти записи по теме) и нечёткое совпадение (опечатки, частичные имена, ILIKE). Правильный индекс — разница между «мгновенно» и «таймаутом при 10k пользователей».

Полнотекстовый поиск: tsvector + GIN

Используйте полнотекстовый поиск, когда пользователи вводят настоящие слова и вы хотите ранжировать результаты по релевантности — например, поиск по теме и описанию в тикетах. Обычно создают tsvector (часто в сгенерированной колонке) и индексируют его GIN. Поиск выполняют через @@ и tsquery.

-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
  to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;

CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);

-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');

-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);

SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';

Что хранить в векторе: только те поля, которые вы реально ищете. Если включить всё подряд (notes, internal logs), вы заплатите размером индекса и стоимостью записи.

Нечёткие совпадения: триграммы с GIN или GiST

Используйте триграммы, когда пользователи ищут имена, email или короткие фразы и вам нужны частичные совпадения или устойчивость к опечаткам. Триграммы помогают с ILIKE '%term%' и операторами схожести. GIN обычно быстрее для «совпадает или нет» запросов; GiST может лучше подойти, если вам также важно ранжирование по схожести.

Правила на заметку:

• Используйте GIN + tsvector для релевантного текстового поиска.
• Используйте триграммы для ILIKE и устойчивого к опечаткам поиска по именам.

Подводные камни:

• Ведущие wildcard‑ы без триграмм (ILIKE '%abc') заставляют сканировать таблицу.
• Очень короткие поисковые термы (1–2 символа) плохо работают с триграммами.
• Стоп‑слова и стемминг могут удивлять пользователей в полнотекстовых результатах, поэтому выбирайте конфигурацию, соответствующую языку продукта.

Если вы быстро запускаете экран поиска, относитесь к индексу как к части фичи: UX поиска и выбор индекса должны проектироваться вместе.

Шаг за шагом: от медленного запроса к правильному индексу

Начинайте с точного запроса, который выполняет ваше приложение, а не с догадок. «Медленный экран» обычно — это один SQL с конкретным WHERE и ORDER BY. Скопируйте его из логов, вывода ORM или того механизма, который вы уже используете.

Рабочий процесс, который реально работает в приложениях:

• Захватите полный SQL, включая WHERE, ORDER BY и LIMIT.
• Запустите EXPLAIN (ANALYZE, BUFFERS) для этого же запроса.
• Сосредоточьтесь на операторах, которые делают работу (=, >=, LIKE, @>, @@), а не только на именах колонок.
• Добавьте минимальный индекс, который совпадает с этими операторами.
• Снова прогоните EXPLAIN (ANALYZE, BUFFERS) с реалистичным объёмом данных.

Конкретный пример. Страница Customers фильтруется по tenant и status, сортируется по новизне и странично выводится:

SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;

Если EXPLAIN показывает seq scan и сортировку, B-tree индекс, который совпадает с фильтром и сортировкой, обычно решает проблему:

CREATE INDEX ON customers (tenant_id, status, created_at DESC);

Если медленно работает фильтрация JSONB вроде metadata @> '{"plan":"pro"}', это указывает на GIN. Если это полнотекст как to_tsvector(...) @@ plainto_tsquery(...), это тоже GIN. Если это «ближайшее совпадение» или операторы перекрытия — смотрите в сторону GiST.

После добавления индекса измерьте компромисс. Проверьте размер индекса, время вставок и обновлений, и помогает ли он основным медленным запросам или только одному краевому случаю. В быстро меняющихся проектах (включая те, что строятся на Koder.ai) такая проверка помогает не накапливать неиспользуемые индексы.

Типичные ошибки индексации, которые тратят время и деньги

Большинство проблем с индексами не в выборе B-tree vs GIN vs GiST. Они в создании индекса, который кажется правильным, но не соответствует тому, как приложение запрашивает таблицу.

Ошибки, которые чаще всего вредят:

• Индексы, которые никогда не используются. Запрос использует другой оператор, чем поддерживает индекс, или составной индекс имеет неправильный порядок колонок. Если WHERE начинается с tenant_id и created_at, но индекс начинается с created_at, планировщик может его пропустить.
• Индексация колонок с низкой кардинальностью в одиночку. Одиночный индекс на status, is_active или boolean часто мало что даёт — он совпадает со многими строками. Скомбинируйте его с селективной колонкой (tenant_id или created_at) или не создавайте.
• Перекрывающиеся индексы, которые раздувают размер и замедляют записи. Похожие индексы на одной таблице могут удваивать хранилище и замедлять вставки/обновления.
• Проблемы с пагинацией. Индекс, созданный только под OFFSET, — частая ловушка. Для keyset‑пагинации нужен индекс, который совпадает с сортировкой и фильтром «последний видимый ключ».
• Актуальность статистики и мусор в таблице. Если autovacuum не справляется или ANALYZE давно не запускался, планировщик может выбрать плохой план даже при наличии подходящего индекса.

Конкретный пример: экран Invoices фильтрует по tenant_id и status, затем сортирует по created_at DESC. Индекс только на status едва поможет. Лучше составной индекс, который начинается с tenant_id, затем status, затем created_at — это одно изменение часто эффективнее трёх отдельных индексов.

Относитесь к каждому индексу как к затратному ресурсу. Он должен окупаться в реальных запросах, а не только в теории.

Быстрый чек‑лист перед выпуском изменений индексов

Изменения индексов просто добавить, но тяжело откатить, если они увеличили стоимость записи или заблокировали загруженную таблицу. Перед мёржем относитесь к ним как к небольшому релизу.

Сначала решите, что вы оптимизируете. Возьмите два коротких рейтинга из логов или мониторинга: самые частые запросы и запросы с наибольшей задержкой. Для каждого запишите точную форму: колонки фильтрации, порядок сортировки, джоины и используемые операторы (=, range, IN, ILIKE, JSONB операторы, array contains). Это уберёт догадки и поможет выбрать тип индекса.

Чек‑лист перед отправкой в прод:

• Подтвердите, что запросы там, где нужно, ограничены tenant.
• Сопоставьте индекс с оператором: B-tree для равенств/диапазонов/сортировки, GIN для членства (массивы, JSONB, полнотекст), GiST для перекрытий/расстояния.
• Предпочитайте один составной индекс, который покрывает общий фильтр + сортировку, а не несколько одно‑колоночных.
• Держите индекс узким: включайте только колонки, которые запрос действительно использует.
• Спланируйте развёртывание: будет ли создание индекса блокировать записи, нужно ли запускать в непиковое время?

После добавления индекса проверьте, помог ли он в реальном плане. Запустите EXPLAIN (ANALYZE, BUFFERS) для точного запроса и сравните до/после. Затем наблюдайте за поведением в проде в течение дня:

• Уменьшилась ли задержка чтения для целевых экранов?
• Увеличилось ли время записи (inserts/updates)?
• Вырос ли CPU или диск из‑за нового индекса?
• Используется ли индекс вообще или он мёртвый груз?

Если вы строите с Koder.ai, храните сгенерированный SQL для пары медленных экранов рядом с изменением, чтобы индекс действительно соответствовал тому, что выполняет приложение.

Пример: индексирование типичного рабочего процесса SaaS + следующие шаги

Представьте админ‑экран: список Users с привязкой к tenant, несколькими фильтрами, сортировкой по last_active и полем поиска. Здесь индексы перестают быть теорией и начинают реально экономить время.

Три формы запросов, которые вы обычно увидите:

-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;

-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
  AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;

-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
  AND metadata @> '{"plan":"pro"}'::jsonb;

Небольшой, но продуманный набор индексов для этого экрана:

• B-tree составной для списка: (tenant_id, status, last_active_at DESC).
• GIN для поиска: сгенерированная колонка tsvector с GIN‑индексом.
• Индексация JSONB по использованию: GIN (metadata), если вы часто делаете @>; или expression‑B-tree вроде (metadata->> 'plan'), если вы фильтруете по одному ключу.

Смешанные потребности — нормальное дело. Если одна страница делает фильтры + поиск + JSON, не пытайтесь впихнуть всё в один огромный индекс. Держите B-tree для сортировки/пагинации и добавьте один специализированный индекс (чаще всего GIN) для самой тяжёлой части.

Следующие шаги: выберите один медленный экран, запишите его 2–3 формы запросов и проверьте каждый индекс по назначению (фильтр, сортировка, поиск, JSON). Если индекс явно не соответствует реальному запросу — исключите его из плана. Если вы быстро итеративно развиваете проект с Koder.ai, проводите такие ревью по мере добавления новых экранов, чтобы избежать раздутия индексов на ранней стадии смены схемы.