Индексы PostgreSQL для SaaS: как выбирать между B-tree, GIN и GiST, исходя из реальной формы запросов — фильтры, поиск, JSONB и массивы.

Индекс меняет способ, которым PostgreSQL находит строки. Без индекса база часто вынуждена прочитать большую часть таблицы (seq scan), а затем откинуть большую часть данных. С подходящим индексом она может сразу прыгнуть к подходящим строкам (index lookup) и забрать только то, что нужно.
Вы замечаете это рано в SaaS, потому что повседневные экраны сильно нагружают запросами. Один клик может запустить несколько чтений: страницу списка, подсчёт общего количества, пару карточек на дашборде и поле поиска. Когда таблица растёт от тысяч до миллионов строк, тот же запрос, который раньше казался мгновенным, начинает притормаживать.
Типичный пример — страница Orders, отфильтрованная по статусу и дате, отсортированная по новизне, с пагинацией. Если PostgreSQL приходится сканировать всю таблицу orders, чтобы найти оплаченные заказы за последние 30 дней, каждый загруз страницы делает лишнюю работу. Хороший индекс превращает это в быстрый прыжок к нужному фрагменту данных.
Индексы не бесплатны. Каждый индекс даёт более быстрые чтения для конкретных запросов, но замедляет записи (INSERT/UPDATE/DELETE должны обновлять индексы) и занимает место на диске (и увеличивает нагрузку на кэш). Поэтому начинать нужно с реальных шаблонов запросов, а не с типов индексов.
Простое правило, которое избавляет от лишней работы: добавляйте индекс только тогда, когда можете указать конкретный, частый запрос, который он ускорит. Если вы строите экраны с чат‑помощником вроде Koder.ai, полезно захватить SQL, который стоит за вашими страницами списков и дашбордами, и использовать его как список пожеланий по индексам.
Большая часть путаницы с индексами исчезает, когда вы перестаёте думать о возможностях (JSON, поиск, массивы) и начинаете думать о форме запроса: что делает WHERE, и как вы хотите получить упорядоченные результаты?
Используйте B-tree, когда ваш запрос выглядит как обычные сравнения и вам важен порядок сортировки. Это рабочая лошадка для равенств, диапазонов и джоинов.
Примеры форм запроса: фильтрация по tenant_id = ?, status = 'active', created_at >= ?, соединение users.id = orders.user_id или показ «сначала новые» с ORDER BY created_at DESC.
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).
Практический способ выбора:
Индексы ускоряют чтение, но портят время записи и занимают диск. В SaaS эта компромиссная зависимость особенно важна для горячих таблиц: events, sessions, activity_logs.
Большинство экранов списков в SaaS имеют одинаковую форму: граница арендатора, пара фильтров и предсказуемая сортировка. B-tree — стандартный выбор здесь, и он обычно самый дешёвый в обслуживании.
Обычная картина: WHERE tenant_id = ? плюс фильтры вроде status = ?, user_id = ?, и временной диапазон created_at >= ?. Для составных B-tree индексах ставьте сначала колонки для равенств (=), затем колонку, по которой вы сортируете.
Правила, которые хорошо работают в большинстве приложений:
tenant_id, если каждый запрос привязан к арендатору.= следующими (часто status, user_id).ORDER BY последней (обычно created_at или id).INCLUDE, чтобы покрывать страницы списка, не расширяя ключ индекса.Реалистичный пример: страница 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 удобен, когда приложению нужны гибкие дополнительные поля: feature flags, атрибуты пользователя или настройки по арендатору. Загвоздка в том, что разные операторы JSONB ведут себя по‑разному, поэтому лучший индекс зависит от того, как вы запрашиваете данные.
Две формы особенно важны:
@>.-> / ->> (часто сравнивают с =).Если вы часто фильтруете с @>, 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 может быть меньше и быстрее, но поддерживает меньше типов операторов.
Если 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).
Массивы удобны: их легко хранить и читать. 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) обычно проще для валидации, запросов и эволюции.
Для поисковых полей чаще всего встречаются два паттерна: полнотекстовый поиск (найти записи по теме) и нечёткое совпадение (опечатки, частичные имена, ILIKE). Правильный индекс — разница между «мгновенно» и «таймаутом при 10k пользователей».
Используйте полнотекстовый поиск, когда пользователи вводят настоящие слова и вы хотите ранжировать результаты по релевантности — например, поиск по теме и описанию в тикетах. Обычно создают 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), вы заплатите размером индекса и стоимостью записи.
Используйте триграммы, когда пользователи ищут имена, email или короткие фразы и вам нужны частичные совпадения или устойчивость к опечаткам. Триграммы помогают с ILIKE '%term%' и операторами схожести. GIN обычно быстрее для «совпадает или нет» запросов; GiST может лучше подойти, если вам также важно ранжирование по схожести.
Правила на заметку:
tsvector для релевантного текстового поиска.Подводные камни:
ILIKE '%abc') заставляют сканировать таблицу.Если вы быстро запускаете экран поиска, относитесь к индексу как к части фичи: UX поиска и выбор индекса должны проектироваться вместе.
Начинайте с точного запроса, который выполняет ваше приложение, а не с догадок. «Медленный экран» обычно — это один SQL с конкретным WHERE и ORDER BY. Скопируйте его из логов, вывода ORM или того механизма, который вы уже используете.
Рабочий процесс, который реально работает в приложениях:
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. Они в создании индекса, который кажется правильным, но не соответствует тому, как приложение запрашивает таблицу.
Ошибки, которые чаще всего вредят:
tenant_id и created_at, но индекс начинается с created_at, планировщик может его пропустить.status, is_active или boolean часто мало что даёт — он совпадает со многими строками. Скомбинируйте его с селективной колонкой (tenant_id или created_at) или не создавайте.ANALYZE давно не запускался, планировщик может выбрать плохой план даже при наличии подходящего индекса.Конкретный пример: экран Invoices фильтрует по tenant_id и status, затем сортирует по created_at DESC. Индекс только на status едва поможет. Лучше составной индекс, который начинается с tenant_id, затем status, затем created_at — это одно изменение часто эффективнее трёх отдельных индексов.
Относитесь к каждому индексу как к затратному ресурсу. Он должен окупаться в реальных запросах, а не только в теории.
Изменения индексов просто добавить, но тяжело откатить, если они увеличили стоимость записи или заблокировали загруженную таблицу. Перед мёржем относитесь к ним как к небольшому релизу.
Сначала решите, что вы оптимизируете. Возьмите два коротких рейтинга из логов или мониторинга: самые частые запросы и запросы с наибольшей задержкой. Для каждого запишите точную форму: колонки фильтрации, порядок сортировки, джоины и используемые операторы (=, range, IN, ILIKE, JSONB операторы, array contains). Это уберёт догадки и поможет выбрать тип индекса.
Чек‑лист перед отправкой в прод:
После добавления индекса проверьте, помог ли он в реальном плане. Запустите EXPLAIN (ANALYZE, BUFFERS) для точного запроса и сравните до/после. Затем наблюдайте за поведением в проде в течение дня:
Если вы строите с Koder.ai, храните сгенерированный SQL для пары медленных экранов рядом с изменением, чтобы индекс действительно соответствовал тому, что выполняет приложение.
Представьте админ‑экран: список 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;
Небольшой, но продуманный набор индексов для этого экрана:
(tenant_id, status, last_active_at DESC).tsvector с GIN‑индексом.GIN (metadata), если вы часто делаете @>; или expression‑B-tree вроде (metadata->> 'plan'), если вы фильтруете по одному ключу.Смешанные потребности — нормальное дело. Если одна страница делает фильтры + поиск + JSON, не пытайтесь впихнуть всё в один огромный индекс. Держите B-tree для сортировки/пагинации и добавьте один специализированный индекс (чаще всего GIN) для самой тяжёлой части.
Следующие шаги: выберите один медленный экран, запишите его 2–3 формы запросов и проверьте каждый индекс по назначению (фильтр, сортировка, поиск, JSON). Если индекс явно не соответствует реальному запросу — исключите его из плана. Если вы быстро итеративно развиваете проект с Koder.ai, проводите такие ревью по мере добавления новых экранов, чтобы избежать раздутия индексов на ранней стадии смены схемы.
Индекс позволяет PostgreSQL находить подходящие строки, не читая большую часть таблицы. Для типичных экранов SaaS — списков, дашбордов и поиска — правильный индекс может превратить медленный последовательный обход в быстрый поиск, который масштабируется по мере роста таблицы.
Начинайте с B-tree для большинства запросов приложения: он подходит для фильтров с =, диапазонов, соединений и ORDER BY. Если запрос в основном про проверку вхождения (JSONB, массивы) или полнотекстовый поиск — смотрите в сторону GIN; GiST применяют для пересечения/близости и специальных задач.
Ставьте колонки, которые вы сравниваете с = первыми, а колонку, по которой вы сортируете, — последней. Такой порядок позволяет планировщику эффективно ходить по индексу и избегать дополнительной сортировки.
Если каждый запрос фильтруется по tenant_id, то помещать tenant_id первым помогает сгруппировать данные каждого арендатора в индексе. Это обычно снижает количество обращений к индексу и таблице для повседневных списков.
INCLUDE позволяет добавить дополнительные колонки, чтобы поддержать чтение только из индекса для страниц списка, не расширяя ключ индекса. Полезно, когда вы фильтруете и сортируете по нескольким колонкам, но на экране показываете ещё пару полей.
Частичный индекс лучше, когда вам важен только поднабор строк, например «не удалённые» или «только активные». Он меньше по размеру и дешевле в обслуживании, что важно для горячих таблиц с большим количеством вставок и обновлений.
Используйте GIN для JSONB, когда вы часто делаете фильтрацию через containment, например metadata @> '{"plan":"pro"}'. Если вы чаще фильтруете по одному конкретному ключу, выраженный индекс B-tree на (metadata ->> 'plan') обычно меньше и быстрее.
GIN отлично подходит, когда ваш основной вопрос — «содержит ли массив значение X?» с операторами @> или &&. Но если вам нужно метаданные по элементу, частые правки или аналитику по меткам, таблица связи (join table) обычно проще в поддержке и эволюции.
Для полнотекстового поиска храните tsvector (часто как сгенерированную колонку) и индексируйте его GIN, затем ищите с помощью @@ для релевантности. Для нечёткого совпадения и устойчивости к опечаткам используйте триграммы (trigrams), обычно с GIN или GiST в зависимости от потребностей в ранжировании.
Скопируйте точный SQL, который выполняет ваше приложение, и запустите EXPLAIN (ANALYZE, BUFFERS), чтобы понять, где тратится время: сканирование, сортировка или тяжёлые фильтры. Добавьте минимальный индекс, который соответствует операторам и порядку сортировки, затем снова прогоните EXPLAIN, чтобы подтвердить эффект.