Как строить индексы в PostgreSQL

Что проверяют

Умеете ли вы выбрать индекс под конкретный запрос, а не «на всякий случай», и знаете ли, как безопасно добавить его в живую базу. Для Node.js-разработчика следом почти всегда идёт вопрос, как оформить это миграцией и не заблокировать таблицу на проде.

Как отвечать

Индекс строится от запроса: смотрите EXPLAIN (ANALYZE, BUFFERS) и ищете Seq Scan по большой таблице с фильтром. Дальше типы:

• B-tree: по умолчанию, равенство, диапазоны, ORDER BY. В составном индексе сначала колонки с равенством, потом с диапазоном;
• частичный: WHERE deleted_at IS NULL, меньше размер и дешевле обслуживание;
• по выражению: lower(email), иначе запрос с функцией индекс не использует;
• покрывающий: INCLUDE (...) даёт Index Only Scan без чтения таблицы;
• GIN: jsonb, массивы, полнотекстовый поиск, а с расширением pg_trgm ещё и LIKE '%текст%';
• BRIN: огромные таблицы, где значения идут в физическом порядке, например журнал событий по времени;
• GiST: геоданные, диапазоны, ограничения исключения.

Про прод: обычный CREATE INDEX блокирует запись в таблицу на всё время построения, поэтому используют CREATE INDEX CONCURRENTLY. Он не работает внутри транзакции, а Knex и node-pg-migrate по умолчанию оборачивают миграцию в транзакцию, это отключают для конкретного файла. Если построение упало, остаётся невалидный индекс: его удаляют и строят заново.

Неиспользуемые индексы находят через pg_stat_user_indexes, у них idx_scan остаётся нулевым.

Где спотыкаются

Объявляют индекс в Prisma или TypeORM и не смотрят, какой SQL уйдёт в миграцию. На большой таблице такая миграция надолго блокирует запись.

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

Пишут EXPLAIN без ANALYZE и делают выводы по оценкам планировщика, а не по реальному времени.