К содержимому
Войти

Node.js Road Map

🌲 Индексы

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

🧠 Как это устроено

Что даёт индекс

Таблица orders занимает 534 МБ. Запрос where user_id = 4242 без индекса читает все 5 млн строк, чтобы найти 28 нужных: Parallel Seq Scan за 86 мс, и это при таблице, целиком лежащей в памяти. Индекс на user_id собрался за 1,2 секунды, занял 37 МБ, и тот же запрос выполнился за 0,3 мс

Разница в 300 раз выглядит не страшно, пока запрос один. Когда такой запрос приходит тысячу раз в секунду, без индекса база занята только им

B-дерево

B-tree — индекс по умолчанию: create index без using строит именно его. Это сбалансированное дерево из страниц по 8 КБ. В листьях лежат значения ключа в отсортированном порядке и ссылки на строки таблицы, листья связаны между собой в список. Верхние уровни хранят границы: «ключи до 1000 налево, остальные направо»

Поиск идёт от корня к листу, на каждом уровне выбирая одну страницу. Дерево низкое: у первичного ключа на 6,5 млн строк всего три уровня. Значит, чтобы найти одну строку, база читает три страницы индекса и одну страницу таблицы

Из устройства следует, что B-tree умеет:

  • равенство и диапазоны: =, <, >, between, in
  • is null и is not null
  • order by по ключу без сортировки, в обе стороны
  • like 'abc%', когда шаблон начинается с константы

И чего не умеет: like '%abc', поиск по элементу массива или ключу внутри jsonb. Для этого есть другие типы

ТипДля чего
B-treeпочти всё: равенство, диапазоны, сортировка, уникальность
Hashтолько =, на практике его редко выбирают вместо B-tree
GINзначения из многих частей: массивы, jsonb, полнотекстовый поиск, триграммы
GiST, SP-GiSTгеометрия, диапазоны, поиск ближайших
BRINогромные таблицы, где значения идут по порядку записи, например время в логах

Составной индекс и порядок полей

Индекс на (user_id, created_at) отсортирован сначала по user_id, а внутри одного пользователя по created_at. Как телефонная книга: по фамилии найти легко, по имени без фамилии — только перелистав всю книгу

Правило из документации Postgres: диапазон чтения индекса сужают равенства на первые поля и одно неравенство на первое поле без равенства. Условия на поля правее проверяются внутри индекса, но читать меньше не заставляют

ЗапросЧто вышло
where user_id = 4242 order by created_at desc limit 10Index Scan Backward, без сортировки, 0,1 мс
where user_id between 4000 and 4100 and created_at > now() - interval '1 day'индекс используется, 0,2 мс
where created_at > now() - interval '1 hour'Parallel Seq Scan, 178 мс

Первая строка — главный повод для составного индекса: «последние заказы пользователя» отдаются прямо из индекса в нужном порядке. Третья — то, о чём спрашивают на собеседовании: без условия на первое поле индекс не помог

В PostgreSQL 18 (вышел в сентябре 2025) у B-tree появился skip scan: база перебирает значения первого поля и для каждого ищет по второму. Документация оговаривает, что это выгодно, только когда разных значений первого поля мало. У нас 200 тысяч пользователей, и в такой ситуации, по той же документации, планировщик почти всегда предпочтёт прочитать таблицу. Так что правило порядка полей осталось: первым ставят поле, по которому ищут равенством во всех запросах

Покрывающий индекс

Обычный индексный поиск находит ссылку и идёт за строкой в таблицу. Если все нужные запросу столбцы есть в индексе, Postgres делает Index Only Scan и в таблицу не ходит. Чтобы добавить столбец только для чтения, без участия в сортировке, есть include:

create index orders_user_created_incl
  on orders (user_id, created_at) include (amount);

explain (analyze, costs off, timing off)
select created_at, amount from orders where user_id = 4242;
-- Index Only Scan using orders_user_created_incl on orders (actual rows=28 loops=1)
--   Index Cond: (user_id = 4242)
--   Heap Fetches: 0

Есть подвох. В индексе нет информации о видимости строки для транзакции, поэтому Postgres смотрит в карту видимости: если страница таблицы помечена как «все строки видны всем», в неё можно не ходить. После обновления 25 тысяч строк у тех же 28 заказов Heap Fetches выросло до 56, база снова читала таблицу. После vacuum вернулось к 0. На таблице, которую постоянно пишут, покрывающий индекс выигрывает мало

И он тяжелее: с include (amount) индекс занял 194 МБ вместо 151 МБ

Частичный индекс

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

create index orders_new on orders (created_at) where status = 'new';

select id from orders where status = 'new' order by created_at limit 20;
-- Index Scan using orders_new, 0,06 мс

Такой индекс занял 80 КБ. Обычный индекс на status весит 33 МБ. Частичный индекс работает, только если условие запроса совпадает с условием индекса или следует из него: для status = 'paid' он бесполезен

Индекс по выражению и GIN

Индекс на email не помогает запросу where lower(email) = 'user42@mail.ru': в индексе лежит email, а не lower(email), и база читает таблицу. Решение — индекс по тому же выражению: create index on orders (lower(email)). После него план стал Index Scan. Выражение в запросе должно совпасть с индексом буквально

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

create index orders_tags on orders using gin (tags);
select count(*) from orders where tags @> array['vip'];
-- 500 строк: 0,6 мс с индексом, 169 мс без него

Тот же индекс на теге gift, который есть у 100 тысяч заказов, проиграл чтению таблицы: 310 мс против 200. Эти 2% строк разбросаны по 61 тысяче страниц из 68, и база прочитала почти всю таблицу, только в случайном порядке

Цена индекса

Каждый индекс обновляется при каждой вставке. 500 тысяч строк в пустую таблицу:

ИндексовВремя вставкиРазмер таблицы с индексами
00,3 с45 МБ
21,9 с98 МБ
75,5 с193 МБ

С обновлением тоньше. Если меняется столбец, которого нет ни в одном индексе, и на странице есть свободное место, Postgres делает HOT-обновление: новая версия строки ложится на ту же страницу, индексы не трогаются. В таблице с fillfactor = 90 обновление неиндексированного столбца у 5000 строк дало 5000 HOT-обновлений. Обновление индексированного столбца у других 5000 строк не дало ни одного. Лишний индекс на часто меняющемся поле ломает этот механизм

🛠️ На практике

Индекс на живой таблице создают так:

create index concurrently orders_user_created on orders (user_id, created_at);

Обычный create index блокирует запись в таблицу на всё время построения: чтение идёт, а вставки и обновления ждут. На большой таблице построение идёт минуты, и всё это время запись стоит. concurrently не блокирует запись, но читает таблицу дважды, ждёт завершения текущих транзакций и не работает внутри транзакции. Последнее важно для миграций: многие инструменты оборачивают каждую миграцию в транзакцию, и такой шаг придётся вынести отдельно

Если построение упало, например на нарушении уникальности, остаётся индекс с пометкой INVALID. Запросы его не используют, а вставки продолжают обновлять. Его удаляют и строят заново или делают reindex index concurrently

SQL · Почему индекс не работает: три запроса из жизни

-- 1. Функция над столбцом: индекс на created_at не используется
select id from orders where created_at::date = '2026-11-01';
-- так используется
select id from orders where created_at >= '2026-11-01' and created_at < '2026-11-02';

-- 2. LIKE по префиксу в базе с локалью en_US.utf8: обычный индекс не годится
create index orders_email_pattern on orders (email text_pattern_ops);
select id from orders where email like 'User4242%';
-- Index Cond: ((email ~>=~ 'User4242'::text) AND (email ~<~ 'User4243'::text))

-- 3. LIKE с % в начале: B-tree не поможет никогда, нужен pg_trgm
create extension pg_trgm;
create index orders_email_trgm on orders using gin (email gin_trgm_ops);
select id from orders where email like '%4242@Mail.ru';

Триграммный индекс на той же колонке занял 162 МБ против 54 МБ у обычного B-tree, так что его заводят под конкретный поиск, а не на всякий случай

⚠️ Где ошибаются

Индекс на каждый столбец. Запросы по нескольким полям это ускоряет плохо: два одиночных индекса Postgres умеет объединить через BitmapAnd, но это дороже одного составного и не даёт готового порядка для сортировки. Зато каждая вставка обновляет все индексы, и HOT-обновления перестают работать

Индекс на поле с тремя-четырьмя значениями. Индекс на status для status = 'paid' бесполезен: оплачена половина заказов, и Postgres читает таблицу целиком, даже когда индекс есть. Для редкого значения лучше частичный индекс, он в сотни раз меньше

Составной индекс в неправильном порядке. Индекс (created_at, user_id) не отдаст последние заказы пользователя без сортировки. Первым ставят поле, по которому ищут равенством, вторым то, по которому сортируют или ищут диапазон

create index без concurrently в миграции. На маленькой таблице незаметно, на большой запись встаёт на минуты, а сервис отваливается по таймаутам

Индекс по выражению без analyze. Статистика по lower(email) собирается при следующем analyze. До этого планировщик оценивает число строк наугад и может выбрать не тот план

🎯 Вопросы с собеседований

Что такое B-дерево и почему поиск по нему быстрый?

Сбалансированное дерево из страниц, где в листьях лежат отсортированные ключи и ссылки на строки, а на верхних уровнях границы диапазонов. Поиск идёт от корня к листу, на каждом уровне читается одна страница. Дерево низкое: на миллионы строк хватает трёх-четырёх уровней, поэтому одна строка находится за несколько чтений страниц вместо полного перебора таблицы

Важен ли порядок полей в составном индексе?

Да. Индекс на (a, b) отсортирован по a, а внутри по b, поэтому он ускоряет запросы с условием на a или на a и b, а запрос только по b обычно идёт мимо. В PostgreSQL 18 появился skip scan, который иногда спасает такой запрос, но выгоден он, только когда разных значений a мало. Первым ставят поле с равенством, затем поле для диапазона или сортировки

Индекс есть, а запрос его не использует. Почему?

Обычные причины: функция или приведение типа над столбцом (lower(email), created_at::date), like с процентом в начале, условие не на первое поле составного индекса. Ещё бывает, что запрос выбирает большую долю таблицы, и последовательное чтение дешевле. Или статистика устарела, и планировщик неверно оценил число строк. Проверяют через explain analyze

Чем покрывающий индекс отличается от обычного?

В нём есть все столбцы, которые нужны запросу, поэтому Postgres делает Index Only Scan и не читает таблицу. Неключевые столбцы добавляют через include. Выигрыш есть, только если большая часть страниц таблицы помечена в карте видимости, а это зависит от vacuum: на часто обновляемой таблице база всё равно ходит в таблицу проверять видимость

Что такое частичный индекс и когда он нужен?

Индекс с условием where, в него попадают только подходящие строки. Нужен, когда запросы всегда ищут по редкому значению: необработанные заказы, активные сессии. Он меньше и быстрее обновляется. Работает, только если условие запроса совпадает с условием индекса или следует из него

Почему не стоит создавать индекс на каждый столбец?

Каждый индекс обновляется при вставке и при изменении его столбцов, занимает место и память. Индексированные столбцы мешают HOT-обновлениям, и обновление начинает трогать все индексы. А пользы мало: запросу по нескольким полям нужен один составной индекс, а не набор одиночных

Когда нужен GIN, а когда B-tree?

B-tree работает со значением целиком: равенство, диапазоны, сортировка. GIN раскладывает значение на части и для каждой хранит список строк, поэтому он нужен для поиска элемента в массиве, ключа в jsonb, слова в тексте или подстроки через pg_trgm. GIN дороже обновлять и он не умеет сортировать

Как добавить индекс на большую таблицу в проде?

Через create index concurrently. Он не блокирует запись, но идёт дольше, читает таблицу дважды и не работает внутри транзакции, поэтому в миграции его выносят в отдельный шаг без транзакции. Если построение упало, остаётся невалидный индекс: его удаляют и строят заново

📚 Что почитать