Всем привет, меня зовут Сергей Прощаев, и в этой статье я расскажу про индексы в PostgreSQL. Точнее, про то, как из блага они превращаются в проклятие, когда их неправильно выбирают или создают «на всякий случай».

Много лет занимаюсь архитектурой высоконагруженных систем. Руковожу направлением Java‑разработки в FinTech и регулярно сталкиваюсь с тем, что даже опытные команды попадают в ловушку индексов. Казалось бы, что тут сложного? Добавил индекс по полю, по которому фильтруешь — и всё летает. Но в реальности всё иначе.

На одном проекте команда потратила три спринта, пытаясь выяснить, почему простой запрос на выборку пользователей выполняется 12 секунд. Индексы были, планировщик их использовал, но нагрузка на базу росла. А потом выяснилось, что эти индексы... мешали. Они занимали половину памяти, вызывали блокировки при записи и просто не подходили под тип данных и характер запросов.

Сегодня мы разберём, как не попасть в такую ситуацию. Я покажу, как PostgreSQL выбирает индексы, почему иногда игнорирует их, какие типы индексов подходят для разных задач, и как строить индексы в highload‑среде, чтобы они не стали узким местом.

Индексы есть, а запросы всё равно тормозят?

Начну с частого сценария. Вы смотрите на запрос:

SELECT * FROM users WHERE last_login > '2025-01-01';

Поле last_login заиндексировано. Вы выполняете EXPLAIN ANALYZE и видите:

Index Scan using idx_last_login on users (cost=0.42..15872.31 rows=345123)

Вроде бы индекс используется, но запрос выполняется за секунды. Почему?

  • Первая причина — низкая селективность. Если по условию выбирается 30–40% строк таблицы, PostgreSQL может посчитать, что дешевле сделать последовательное сканирование (Seq Scan), чем прыгать по индексу и потом читать таблицу. Но здесь индекс всё же использовался, а тормоза остались.

  • Вторая причина — размер индекса. Если индекс не помещается в память, PostgreSQL начинает читать его с диска, а это резко увеличивает latency. На одном проекте с таблицей в 50 млн строк индекс на text‑поле занимал 8 ГБ, а буферный кэш был всего 4 ГБ. В итоге каждый запрос вызывал чтение с диска.

  • Третья причина — проблемы с планировщиком. Он опирается на статистику. Если статистика устарела (например, после массовой вставки не вызвали ANALYZE), он может недооценить количество строк и выбрать неоптимальный план.

Личный опыт: я всегда начинаю с EXPLAIN (ANALYZE, BUFFERS). Смотрю на «buffers: shared hit» и «buffers: read». Если много «read» — индекс не помещается в кэш, либо данные сильно разбросаны.

Как PostgreSQL выбирает индекс — и почему он может его игнорировать

Планировщик оценивает стоимость выполнения. Есть два основных фактора:

  1. Селективность — сколько строк вернёт условие.

  2. Стоимость доступа — для Index Scan нужно сначала прочитать индекс (обычно несколько блоков), а потом ��о указателям читать таблицу. Если таблица большая, а условие не очень селективное, дешевле пройтись по всей таблице один раз.

Индекс может игнорироваться, если:

  • Статистика устарела. Решение: ANALYZE.

  • Тип условия не совместим с индексом. Например, для LIKE '%text' обычный B‑Tree не подойдёт — нужен триграммный индекс.

  • Запрос использует функцию. Если у вас WHERE lower(email) = 'test@mail.com', а индекс по email, он не будет использован. Нужен функциональный индекс.

Давайте рассмотрим рисунок 1.

Рис. 1. Как планировщик PostgreSQL выбирает между индексом и последовательным сканированием
Рис. 1. Как планировщик PostgreSQL выбирает между индексом и последовательным сканированием

На схеме показано, как PostgreSQL принимает решение использовать индекс или нет. Основной фактор — селективность условия. Даже если индекс выбран, его эффективность зависит от того, помещается ли он в кэш. Внизу перечислены внешние факторы, которые влияют на оценку стоимости: свежесть статистики, размер индекса и параметры планировщика.

Типы индексов: когда B‑Tree, а когда GIN или BRIN

Мы привыкли, что индекс — это B‑Tree. Но PostgreSQL предлагает целый арсенал. Давайте пройдёмся по основным типам, я приведу примеры, где они реально спасают.

B‑Tree — универсальный солдат

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

  • равенство (=)

  • диапазоны (>, <, BETWEEN)

  • ORDER BY

  • уникальность

Если вы не знаете, какой индекс поставить — ставьте B‑Tree. Это default. Но он неэффективен для полнотекстового поиска, jsonb, массивов и геоданных.

Hash — только для равенства

Теоретически быстрее B‑Tree при точном сравнении, но на практике B‑Tree часто не уступает. Hash‑индексы полезны, если нужно очень много операций = и нет необходимости в диапазонном поиске. Раньше они были не WAL‑логируемыми, сейчас это исправили, но я редко их использую.

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

GIN (Generalized Inverted Index) — король для:

  • полнотекстового поиска (tsvector)

  • jsonb

  • массивов

  • ltree

Пример: у нас есть таблица products с колонкой tags text[]. Запрос WHERE tags @> ARRAY['electronics'] на B‑Tree будет работать медленно, а GIN — молниеносно.

GiST — для геоданных и не только

GiST (Generalized Search Tree) используется для:

  • геоданных (PostGIS)

  • полнотекстового поиска (альтернатива GIN)

  • диапазонов (например, для поиска пересекающихся интервалов)

GiST медленнее на запись, чем GIN, но поддерживает более сложные операторы, например &&для геометрии.

BRIN — для огромных таблиц с естественным порядком

BRIN (Block Range INdex) хранит метаинформацию о блоках. Идеален для очень больших таблиц, где данные физически упорядочены по времени. Пример: таблица логов, где мы часто фильтруем по created_at. B‑Tree на 100 млрд строк занял бы сотни гигабайт, а BRIN — десятки мегабайт. Его структура и размер представлены схематично на рисунке 2.

На одном проекте мы хранили события из IoT‑устройств (миллиарды записей). Запросы по временным диапазонам стали выполняться мгновенно после перехода с B‑Tree на BRIN.

Рис. 2 Структура и размер BRIN
Рис. 2 Структура и размер BRIN

B���Tree строит полноценное дерево с отдельной записью для каждой строки, что приводит к значительному разрастанию (см. рис. 3). BRIN напротив, обобщает информацию на уровне блоков (обычно 128 страниц), поэтому занимает на порядки меньше места. За счёт этого BRIN идеально подходит для огромных таблиц с физически упорядоченными данными, особенно когда запросы выбирают диапазоны значений.

Рис. 3 Структура и размер B-Tree
Рис. 3 Структура и размер B‑Tree

Highload и индексы: партиционирование и шардирование

Когда таблица вырастает до десятков терабайт, даже идеальный индекс может не спасти. Здесь на помощь приходят партиционирование и шардирование.

Партиционирование (декларативное)

В PostgreSQL 10+ есть декларативное партиционирование. Оно помогает:

  • Локализовать индексы — каждая партиция имеет свои индексы, которые меньше по размеру и лучше помещаются в память.

  • Упростить удаление старых данных — DROP PARTITION мгновенно удаляет гигабайты данных без блокировок.

  • Улучшить производительность запросов — если условие фильтрации включает ключ партиционирования, планировщик обращается только к нужным партициям (partition pruning).

Пример: мы партиционируем таблицу orders по полю created_at по месяцам. Запросы за последний месяц ходят только в одну партицию, индекс по user_id внутри этой партиции — 500 МБ вместо 20 ГБ. Кэш заполняется актуальными данными.

Шардирование

Когда партиционирования недостато��но (например, вы упёрлись в производительность одной ноды), применяется шардирование. Здесь индексы строятся на каждом шарде независимо.

Важный момент: нельзя создавать глобальные уникальные индексы в классическом шардировании. Если вам нужна уникальность по полю, приходится использовать составной ключ с идентификатором шарда или применять дополнительные сервисы.

Как не «перегрузить» систему лишними индексами

Ошибка новичков: создать индексы на все поля, которые хоть раз встречаются в WHERE. Это приводит к:

  • замедлению INSERT/UPDATE (каждая операция должна обновить все индексы);

  • раздуванию базы;

  • проблемам с памятью.

Правильный подход:

  1. Сначала измеряйте нагрузку. Используйте pg_stat_user_indexes — сколько раз индекс использовался, сколько сканирований.

  2. Удаляйте неиспользуемые индексы. На одном проекте мы нашли 15 индексов, которые ни разу не использовались за две недели, и спокойно их убрали.

  3. Следите за размером индексов. Если индекс больше таблицы — это повод задуматься.

  4. Используйте составные индексы. Вместо двух отдельных индексов на (a) и (b) можно создать один (a, b), если часто идёт фильтрация по обоим полям.

  5. Для редких операций рассмотрите partial index: CREATE INDEX ... WHERE status = 'active'. Это уменьшит размер индекса и ускорит запросы, работающие только с активными записями.

Реальная история: как мы перестали гадать и начали использовать BRIN

Расскажу кейс, который случился пару лет назад. Мы разрабатывали систему сбора метрик от тысяч серверов. Каждые 10 секунд каждый сервер присылал срез показателей. В сутки получалось ~100 млн записей.

Таблица metrics содержала поля: server_id, timestamp, cpu, memory, disk_io. Типичный запрос: получить средние показатели за последний час для конкретного сервера.

Мы поставили B‑Tree на (server_id, timestamp). Индекс разросся до 40 ГБ, память была забита. Запросы стали тормозить, потому что индекс не помещался в кэш, а дисковые чтения убивали производительность.

Попробовали партиционировать по timestamp (по дням). Это помогло с удалением старых данных, но индексы в каждой партиции всё равно были тяжёлыми.

Тогда я вспомнил про BRIN. Данные физически упорядочены по времени, так как вставляются в хронологическом порядке. Индекс на timestamp в формате BRIN занял всего 120 МБ на всю таблицу, а запросы по временному диапазону стали отрабатывать за миллисекунды, потому что BRIN пропускал блоки, не попадающие в диапазон.

После этого мы пересмотрели подход к индексам во всех больших таблицах и теперь всегда анализируем, подходит ли BRIN. Это простой пример того, как знание типов индексов и особенностей физического хранения может кардинально изменить архитектуру.

Заключение: индексы — это инструмент, а не панацея

Системный аналитик или архитектор, который работает с PostgreSQL, должен мыслить не в терминах «добавить индекс», а в терминах «какой тип индекса и почему». Это требует понимания:

  • планировщика и статистики;

  • типов данных и операторов;

  • физического устройства таблиц и индексов;

  • нефункциональных требований: нагрузка, доступность, скорость записи.

Хороший индекс экономит команде часы простоя, а плохой — создаёт иллюзию защищённости до первой аварии.

Если вы хотите научиться системно подходить к проектированию высоконагруженных систем, включая работу с индексами, партиционированием и шардированием, приглашаю вас на курс  «Высоконагруженные системы: архитектура и масштабирование» в OTUS. Вы узнаете, как реальные архитекторы проектируют базы данных, выдерживающие миллионы запросов в секунду.

➦ Пройдите вступительный тест, чтобы узнать, подойдет ли вам программа курса.

А чтобы узнать больше о формате обучения и задать вопросы экспертам, приходите на бесплатные уроки:

  • 30 марта в 20:00. «Индексы в PostgreSQL». Записаться

  • 20 апреля в 20:00. «Балансировка и геораспределение: как создать быстрое и надёжное приложение для всего мира». Записаться