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

DELETE FROM posts WHERE id = 42 с ON DELETE CASCADE выполняется 7 минут и всё это время удерживает row-level блокировки. Причина: comments(post_id) не проиндексирован. Postgres делает полный скан 200 млн строк комментариев, чтобы найти строки для удаления. Один отсутствующий индекс на внешнем ключе вызвал инцидент в продакшне. Этот урок каталогизирует типичные сбои и плейбук, который позволяет найти их до того, как они найдут вас.

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

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 дней назад