PostgreSQL под нагрузкой: индексы, которые реально помогают

Большинство разработчиков знают B-tree индексы. Этого достаточно для 80% задач. Но когда таблица растёт до десятков миллионов строк и появляются жёсткие SLA – нужно знать больше.

Partial Index: индексируем не всё

Partial index создаётся с условием WHERE – только строки, которые его удовлетворяют, попадают в индекс. Меньше размер, быстрее обновление, лучше cache hit ratio.

-- Индексируем только активные задачи (99% запросов) CREATE INDEX idx_tasks_active ON tasks (created_at, user_id) WHERE status = 'active'; -- Размер в 10-100x меньше полного индекса SELECT pg_size_pretty(pg_relation_size('idx_tasks_active'));

Covering Index: всё в индексе

INCLUDE позволяет добавить колонки в индекс без участия в поиске. Если запрос полностью покрывается индексом – Postgres не идёт в таблицу вообще (Index Only Scan).

-- Запрос: SELECT status, updated_at WHERE user_id = ? -- Без covering index: Index Scan + Heap Fetch -- С covering index: Index Only Scan CREATE INDEX idx_events_covering ON events (user_id) INCLUDE (status, updated_at);

BRIN для временных рядов

Block Range INdex – для колонок с естественной корреляцией с физическим порядком хранения. Временны́е метки в append-only таблицах – идеальный кейс. Размер индекса минимален (килобайты для таблиц в гигабайты), обновление почти бесплатное.

-- Для таблицы событий с 500M строк CREATE INDEX idx_events_brin ON events USING BRIN (created_at) WITH (pages_per_range = 128); -- B-tree на той же колонке: ~11GB -- BRIN: ~130KB

BRIN не подходит для точечных запросов – только для range scans по временны́м диапазонам. При запросах типа WHERE created_at BETWEEN – отлично.

GIN для full-text и массивов

Generalized Inverted Index – для полнотекстового поиска и колонок типа array/jsonb. Если у вас tags text[] и запросы WHERE tags @> '{python}' – без GIN это seq scan.

-- Full-text search CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('russian', title || ' ' || body)); -- Array containment CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

EXPLAIN ANALYZE: читаем план

Любой разговор об индексах должен начинаться с EXPLAIN (ANALYZE, BUFFERS). Смотрим на actual time, rows, buffers hit/read. Если видим Seq Scan на большой таблице – повод добавить индекс. Если видим Index Scan с большим числом rows filtered – возможно, partial index.

Не добавляйте индексы без EXPLAIN. Каждый индекс замедляет INSERT/UPDATE. Иногда seq scan быстрее index scan для маленьких таблиц.

---