Типичные сбои в продакшне и аудит индексов

Семь типичных причин сбоев

1. Отсутствующий индекс на FK-столбце

Внешние ключи не индексируются автоматически. Когда вы добавляете REFERENCES posts(id), Postgres создаёт индекс на posts(id) (referenced-столбец), но не на comments(post_id) (referencing-столбец). ON DELETE CASCADE на родительской таблице выполняет полный скан дочерней таблицы для поиска каскадируемых строк.

-- Опасный паттерн:
ALTER TABLE comments ADD CONSTRAINT fk_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE;
-- Нет индекса на comments(post_id) — DELETE FROM posts теперь O(n) по comments

-- Исправление:
CREATE INDEX CONCURRENTLY idx_comments_post_id ON comments(post_id);

Правило: индексируйте каждый FK-столбец — если только не можете доказать, что родительская таблица никогда не удаляется и ни один запрос не фильтрует дочернюю по FK.

2. Неявное приведение типов отключает индекс

-- orders.user_id имеет тип BIGINT; индекс на orders(user_id) существует
SELECT * FROM orders WHERE user_id = '42';  -- '42' — TEXT

Postgres применяет неявное приведение TEXT → BIGINT, но для этого ему нужно применить cast к каждой строке — он не может обойти B-tree (cast применяется к столбцу, не к константе). Результат: Seq Scan. Диагностика: EXPLAIN ANALYZE запроса. Исправление: используйте типизированные параметры ($1::BIGINT или корректный тип в query builder).

Другие распространённые ловушки: created_at::date = '2024-01-01' отключает индекс на created_at; используйте created_at >= '2024-01-01' AND created_at < '2024-01-02'.

3. Устаревшая статистика приводит к неверному плану

Модель стоимости планировщика зависит от данных pg_statistic — распределения значений, количества строк, корреляций. После массовой вставки, большого удаления или изменения схемы autovacuum планирует ANALYZE, но по умолчанию только после изменения 20% строк. До запуска ANALYZE у планировщика неверные оценки.

Симптом: EXPLAIN показывает rows=50, а фактически было rows=500 000 — промах в 10 000 раз. Планировщик выбрал nested-loop с индексом, который катастрофичен при таком количестве строк.

Исправление: запускайте ANALYZE table_name после массовых операций с данными. Для таблиц с сильно скошенными данными (например, 90% заказов принадлежат 5 воркспейсам) используйте CREATE STATISTICS (ndistinct) ON workspace_id, status FROM orders, чтобы дать планировщику лучшую многостолбцовую статистику.

4. Bloat индекса замедляет сканирование

Индексы накапливают мёртвые записи от UPDATE и DELETE. Postgres 14+ выполняет bottom-up удаление в индексах (агрессивная очистка горячих листовых страниц без ожидания vacuum), но у таблиц с частыми UPDATE может накапливаться значительный bloat.

Симптом: размер индекса в 5–10 раз превышает ожидаемый для данного числа строк; запросы медленные несмотря на использование индекса.

-- Пересоздать индекс без блокировки чтения/записи
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

REINDEX CONCURRENTLY (Postgres 12+) строит новый индекс в фоне, затем атомарно подменяет старый. Продолжительность аналогична первоначальному CREATE INDEX CONCURRENTLY.

5. Неверный порядок столбцов в composite-индексе

Composite-индекс (status, workspace_id), где большинство запросов фильтруют только по workspace_id, не ускоряет эти запросы. Планировщик падает обратно на Seq Scan.

Это нарушение правила leading-column, рассмотренного в уроке 02. В продакшне оно часто появляется, когда индекс проектировался для одного запроса, а позже добавился второй горячий запрос с другим паттерном фильтрации.

Исправление: добавьте второй индекс с корректным ведущим столбцом или переосмыслите composite, если исходный паттерн запросов менее частый.

6. JSONB GIN — cardinality bomb

JSONB-столбец, где документы содержат миллионы уникальных ключей, создаёт GIN-индекс с миллионами различных записей в posting-листах. Такой индекс вытесняет горячие страницы кучи из shared_buffers и имеет огромную стоимость записи (обновление posting-листа для каждого уникального ключа на каждый INSERT).

Симптом: размер GIN-индекса в 10–50 раз превышает размер столбца; коэффициент попаданий в кэш shared_buffers падает; задержки INSERT растут после добавления GIN.

Исправление: используйте expression B-tree индексы на известных горячих полях вместо GIN на всём столбце:

-- Вместо: CREATE INDEX ON events USING GIN (payload)
-- Используйте:
CREATE INDEX ON events ((payload->>'event_type'));
CREATE INDEX ON events ((payload->>'user_id'));

Это создаёт небольшие B-tree индексы только на двух известных горячих полях вместо монолитного GIN.

7. «Индекс есть — но всё равно медленно»

Самый коварный случай: нужный индекс существует, но план всё равно медленный, потому что:

• Индекс не покрывающий (heap-обращения доминируют при масштабе).
• Столбец ORDER BY не входит в индекс (полная сортировка после index scan).
• Ведущий столбец индекса не соответствует наиболее селективному фильтру.

Диагностика — всегда EXPLAIN (ANALYZE, BUFFERS) на точном запросе с реалистичными параметрами, а не на искусственном тестовом запросе и не на staging с в 100 раз меньшим числом строк.

Квартальный плейбук аудита индексов

Если семь сбоев выше описывают проблемы, на которые ты реагируешь, то этот плейбук описывает проблемы, которые ты предотвращаешь. Запускайте его ежеквартально на каждой продакшн-базе Postgres. Обычно это занимает 1–2 часа и возвращает 10–30% экономии хранилища плюс измеримое улучшение write-throughput.

Шаг 1: найти неиспользуемые индексы

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Предостережения: (a) pg_stat_reset() происходит при рестарте — если сервер недавно перезапускался, счётчик сканирований вводит в заблуждение; используйте систему мониторинга, отслеживающую значения во времени. (b) Некоторые индексы используются только периодическими batch-задачами — сверьтесь с pg_stat_statements за более длительный период. (c) Unique-индексы на constraint-столбцах могут показывать idx_scan = 0, но всё равно проверяют constraint при каждом INSERT.

Шаг 2: найти раздутые индексы

-- Требует расширение pgstattuple
SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS total_size,
  round(100 * (approx_free_space + dead_tuple_len)::numeric
    / GREATEST(1, pg_relation_size(indexrelid)), 2) AS bloat_pct
FROM (
  SELECT indexrelid, (pgstattuple_approx(indexrelid::regclass)).*
  FROM pg_stat_user_indexes
) AS s
ORDER BY bloat_pct DESC;

Индексы с bloat_pct выше 30% — кандидаты для REINDEX CONCURRENTLY. Планируйте в часы низкой нагрузки.

Шаг 3: найти отсутствующие индексы

Возьмите top-20 запросов из pg_stat_statements по total_exec_time. Для каждого запустите EXPLAIN (ANALYZE, BUFFERS) с реалистичными параметрами. Ищите Seq Scan на таблицах более 10k строк, где фильтр селективный (возвращается малая доля строк). Каждый такой случай — кандидат на создание индекса.

Шаг 4: найти избыточные индексы

Два индекса избыточны, если ключевые столбцы одного являются префиксом другого. idx_on_a и idx_on_a_b — idx_on_a избыточен, если composite idx_on_a_b всегда используется вместо него. Удалите более короткий.

-- Найти потенциальные дубликаты (требует ручной проверки)
SELECT
  i.indexrelid::regclass AS index_name,
  array_to_string(array_agg(a.attname ORDER BY x.n), ', ') AS columns
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
CROSS JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS x(attnum, n)
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = x.attnum
GROUP BY i.indexrelid, i.indrelid
ORDER BY i.indrelid, columns;

Стратегия индексирования по окружениям

Разные окружения требуют разного подхода:

Development: минимальные индексы — только PK и обязательные unique constraints. Цель — быстрая итерация схемы, а не производительность запросов. Добавление всех продакшн-индексов замедляет миграции и скрывает архитектурные проблемы (не стоит опираться на индексы для компенсации плохой схемы).

Staging: воспроизводите продакшн-индексы при нагрузочном тестировании или бенчмарках; пропускайте при чисто функциональном тестировании. Это помогает поймать «работает в dev, медленно на staging» до продакшна.

Production: полный продуманный набор индексов со всеми инструментами из этого раздела — covering composite, partial indexes, INCLUDE, регулярный аудит. Добавление индексов в продакшн проходит code review вместе с запросом, для которого он создаётся.

Реплики / аналитические копии: могут иметь дополнительные OLAP-специфичные индексы (широкие composites, BRIN на диапазонах дат, GIN для полнотекстового поиска), которые были бы слишком дорогими для поддержки на OLTP-primary. При использовании логической репликации в аналитический Postgres — добавляйте такие индексы только на реплику, не на primary.

Стратегическая позиция

Самый распространённый постмортем на продакшн-масштабе: «отсутствующий индекс на этом горячем запросе». Второй по частоте: «слишком много индексов замедляют запись». Обе проблемы имеют одну причину — индексы не рассматривались как часть дизайна.

Зрелые команды:

• Добавляют индекс в ту же миграцию, что и фичу (запрос + индекс в одном PR).
• Проверяют индексы в code review вместе с SQL.
• Владеют стратегией индексирования на уровне платформы, а не на уровне команды.
• Проводят аудит ежеквартально.

Цена дисциплины: один пункт чеклиста на каждый PR с SQL. Цена её отсутствия: ночная тревога, когда дашборд клиента тормозит из-за FK, добавленного на прошлой неделе без индекса.

slow_query: SELECT id, total_cents FROM orders WHERE workspace_id = 42 AND status = 'pending' ORDER BY created_at DESC LIMIT 50;
execution_time: 4280 ms
rows_returned: 50

EXPLAIN ANALYZE:
Limit  (cost=320..380 rows=50 width=24) (actual time=4271..4280 rows=50 loops=1)
->  Sort  (cost=320..18420 rows=180000 width=24) (actual time=4270..4275 rows=50 loops=1)
      Sort Key: created_at DESC
      Sort Method: top-N heapsort  Memory: 32kB
      ->  Index Scan using idx_orders_workspace_status on orders
            (cost=0.42..18000 rows=180000 width=24) (actual time=0.02..3800 rows=178240 loops=1)
            Index Cond: ((workspace_id = 42) AND (status = 'pending'::text))

Определения индексов:
idx_orders_workspace_status: btree (workspace_id, status)
Размер idx_orders_workspace_status: 1.2 GB
Размер таблицы: 18 GB, строк: 80M

Статистика:
n_dead_tup: 12.4M (15% от общего числа)
last_autovacuum: 14 дней назад
last_analyze: 30 дней назад