Модуль 1. PostgreSQL под нагрузкой: индекс, план и пул

Цель модуля: поднять стенд и пройти первый полный цикл «симптом → гипотеза → инструмент → причина → починка → защита». Научиться читать план запроса и отличать медленный запрос от исчерпанного пула соединений.

Перед началом: PostgreSQL на уровне pg-clickhouse m2, EXPLAIN, индексы. Инструменты: Docker Compose v2, Grafana, psql, make. Трудоёмкость: ~2 ч (стенд 20 мин · разбор 50 мин · практика 50 мин).

🔥 Инцидент: «листинг заказов стал в десять раз медленнее»

09:12, дежурство в OrderFlow. Алерт ApiP99High: p99 маршрута /orders держится выше секунды. В Grafana картина складывается за минуту: RPS тот же, ошибок нет, но p99 листинга вырос с 0.12–0.2 с до 2–3 с, а панель пула показывает очередь db_pool_waiting_conns. Операторы жалуются, что список заказов «крутится».

Выкаток не было. Ночью проходила уборка: по отчёту pg_stat_user_indexes индекс idx_orders_user_created числился «неиспользуемым» (idx_scan = 0), и его удалили как мусор. Запросы при этом читают реплику — и именно там индекс был нужен каждый раз.

Через двадцать минут индекс вернули, p99 упал обратно. Но вопрос остался: почему «неиспользуемый» индекс на самом деле использовался, и почему при десятикратном росте времени запроса деградация выглядела как «упала БД»?

📖 1.1. Стенд за пять минут

OrderFlow — минимальная система, в которой есть все слои большого сервиса:

text
клиент → api :8088 ─┬─→ PostgreSQL master :5434   (запись)
                    ├─→ PostgreSQL replica :5435  (чтение, по умолчанию)
                    ├─→ PgBouncer :6432           (transaction pooling)
                    ├─→ Redis :6381               (cache-aside)
                    ├─→ Kafka :9094 → sink → ClickHouse :8124
                    │                └→ worker → UPDATE orders SET status='paid'
                    └─→ partner :8091             (внешний сервис)

Prometheus :9092 ← /metrics → Alertmanager :9093; Grafana :3001

Показать кодКопировать код

На схеме — вся система, которую вы будете ломать: api пишет в master и читает с реплики, кэш и PgBouncer стоят на пути запроса, поток событий идёт PG → Debezium → Kafka → sink → ClickHouse, воркер обновляет статус оплаты, а Prometheus собирает /metrics и отдаёт алерты в Alertmanager. Держите эту схему перед глазами: диагноз в каждом модуле начинается с вопроса «какой слой?».

bash
cd src/content/courses/arch-track/arch-polygon/polygon
make up        # первый запуск собирает образ api (~3–5 минут)
make seed      # проверяет готовность: 200k заказов, 200 пользователей

Ключевые маршруты api: /orders (GET список, POST создание), /orders/{id} (?direct=1 — читать с мастера, ?nocache=1 — мимо кэша), /hot?user_id= (дорогой отчёт), /risk/{id} (вызов партнёра), /stats (сверка PG и CH), /metrics, /healthz, /readyz. Нагрузка — профилями make load-reads, load-writes, load-hot, load-risk; все они запускают loadgen внутри compose-сети, чтобы не мешать вашему хосту.

Откройте Grafana (http://localhost:3001/d/polygon-overview) до начала работы: дашборд «Polygon: OrderFlow» — ваша карта на весь курс.

📖 1.2. План запроса: почему без индекса в 280 раз медленнее

Листинг заказов пользователя — обычный запрос с фильтром, сортировкой и лимитом:

sql
SELECT id, user_id, amount, status, created_at
  FROM orders
 WHERE user_id = $1
 ORDER BY created_at DESC
 LIMIT 20;

С индексом (user_id, created_at DESC) планировщик идёт по индексу и читает ровно 20 строк:

text
Limit  (cost=0.42..62.73 rows=20) (actual time=0.158..0.194 rows=20)
  ->  Index Scan using idx_orders_user_created on orders
        Index Cond: (user_id = 42)
Execution Time: 0.362 ms

Без индекса тот же запрос обязан прочитать всю таблицу и отсортировать выборку:

text
Limit  (cost=4232.68..4234.98 rows=20) (actual time=91.061..98.906 rows=20)
  ->  Gather Merge
        ->  Sort  (Sort Key: created_at DESC, top-N heapsort)
              ->  Parallel Seq Scan on orders
                    Filter: (user_id = 42)
                    Rows Removed by Filter: 102002
Execution Time: 100.600 ms

Разница — 280 раз на одном запросе (0.36 мс против 100 мс). При 20 параллельных клиентах она превращается в 35-кратное падение пропускной способности: ~14 000 запросов за 20 секунд с индексом против ~400 (и это уже с таймаутами) без него. Пользователь видит не «медленный запрос», а «сервис лёг».

Три вывода, которые стоит забрать из этого плана:

1. Индекс должен совпадать с формой запроса. (user_id, created_at DESC) закрывает и фильтр, и сортировку; после него LIMIT останавливает чтение. Индекс по одному user_id потребовал бы сортировки; индекс по created_at — чтения всей таблицы.
2. Rows Removed by Filter: 102002 — это цена ошибки: чтобы найти 1000 строк пользователя, PostgreSQL прочитал 200 тысяч.
3. pg_stat_user_indexes считается на конкретном узле. Чтения идут на реплику — там idx_scan растёт, а на мастере индекс действительно «не используется». Смотреть статистику нужно на том узле, куда идут запросы.

📖 1.3. Пул соединений: очередь, которая выглядит как медленная БД

Когда запрос замедлился в 280 раз, деградация усиливается очередью. Пул api ограничен (PG_POOL_MAX, по умолчанию 20 соединений на мастер и столько же на реплику). По закону Литтла:

text
параллельные запросы = RPS × задержка

При 0.36 мс и 20 соединениях потолок — десятки тысяч RPS. При 100 мс тот же пул держит ~200 RPS: клиенты встают в очередь, и их время ответа растёт быстрее, чем время самого запроса. На дашборде это видно как db_pool_waiting_conns > 0 при низком db_pool_acquired_conns. Если смотреть только на p99, легко «оптимизировать» пул, хотя причина — план запроса.

Отсюда правило диагностики: сначала план, потом пул. Очередь за соединением — усилитель проблемы, а не её причина.

Второй пул — PgBouncer (:6432, transaction pooling). Он нужен, когда приложений много и каждое держит свои соединения; в стенде api ходит в PostgreSQL напрямую, а PgBouncer доступен для экспериментов. Важная деталь: в transaction-режиме серверное соединение переиспользуется между клиентами, поэтому prepared statements, привязанные к сессии, требуют max_prepared_statements или отказа от кэша подготовленных выражений. Иначе after переезда на PgBouncer вы получите prepared statement "stmtcache_..." does not exist — классическую историю «работало локально».

Таймауты — третья линия обороны: context.WithTimeout(3s) в хендлере и statement_timeout на сервере не дают одному запросу занять соединение навсегда. Но таймаут — это не починка: он превращает зависание в ошибку, а причину всё равно надо найти.

📖 1.4. Регламент: как читать инцидент за пять шагов

1. Подтвердите симптом цифрой. Не «медленно», а «p99 /orders = 2.0 с при SLO 0.3 с, RPS не изменился». Grafana → панель латентности по маршрутам.
2. Локализуйте слой. API жив (/healthz быстрый)? Пул ждёт? Реплика отстаёт? Пайплайн молчит? Один взгляд на дашборд отсекает половину гипотез.
3. Сформулируйте гипотезу и проверьте её инструментом. «Индекса нет» → EXPLAIN (ANALYZE, BUFFERS); «пул мал» → db_pool_waiting_conns; «блокировки» → pg_stat_activity, pg_locks.
4. Почините причину, а не симптом. Вернуть индекс, а не поднять пул; убрать N+1, а не поставить таймаут.
5. Оставьте защиту. Проверка в CI/деплое, регресс-тест плана, алерт на симптом. Инцидент без защиты повторяется.

Полезные запросы на стенде:

sql
-- кто сейчас работает и чего ждёт
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS dur,
       left(query, 80) AS q
  FROM pg_stat_activity
 WHERE datname = 'orderflow' AND state <> 'idle'
 ORDER BY dur DESC;

-- размеры и попадания в кэш
SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE relname = 'orders';

-- план «как без индекса» (учебный эксперимент, не для прода)
SET enable_indexscan = off;
SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
RESET ALL;

📖 1.5. Компромиссы

• Индекс vs запись. Каждый индекс ускоряет чтение и замедляет INSERT, UPDATE, DELETE, занимает место и требует vacuum. Индексируем форму запроса, а не «все колонки на всякий случай».
• Пул больше vs БД под нагрузкой. Больший пул уменьшает очередь, но увеличивает параллелизм на PostgreSQL: больше конкуренции за CPU и блокировки. Пул — буфер, а не ускоритель.
• PgBouncer vs prepared statements. Transaction pooling экономит соединения ценой ограничений на сессионное состояние; проверяйте режим кэша выражений драйвера.
• Таймаут vs полнота. Короткий statement_timeout защищает систему, но убивает легитимные тяжёлые отчёты; для них — отдельный пул или реплика.

💻 Практика

Все команды — из каталога polygon/.

1. Базовая линия (10 мин). make up && make seed, откройте Grafana. Запустите make load-reads, зафиксируйте p99 /orders и db_pool_waiting_conns. Это ваша норма: p99 0.12–0.2 с и очередь 0.
2. Сломать (10 мин). make fail-1 — индекс удалён, 30 секунд чтения. Посмотрите на панель латентности: p99 вырос до 2–3 с. Затем в psql: \d orders (индекса нет), EXPLAIN (ANALYZE, BUFFERS) листинга — найдите Seq Scan и Rows Removed by Filter.
3. Проверить, что «сломано» (2 мин). make check-1 должен упасть с сообщением об отсутствующем индексе. Это ваш регресс-тест.
4. Починить (2 мин). make fix-1 && make check-1 — зелёный чек. Сравните план: Index Scan, 0.36 мс.
5. Пул (15 мин). Пересоздайте api с малым пулом: PG_POOL_MAX=4 docker compose up -d --force-recreate api, затем make load-reads и смотрите db_pool_waiting_conns. Верните make up (или PG_POOL_MAX=20 ... --force-recreate api). Объясните, почему p99 вырос, хотя план запроса не изменился.
6. PgBouncer (10 мин). Подключитесь к localhost:6432 тем же psql и выполните тот же план. Найдите в документации PgBouncer, что произойдёт с prepared statements в transaction-режиме.
7. Регламент (10 мин). Напишите пятистрочный разбор инцидента: симптом (цифра), слой, гипотеза, доказательство (план), защита. Именно в таком виде пишутся постмортемы.

🧩 Quiz "Проверь себя"

✅ Итоги модуля

• Поднять стенд make up и открыть дашборд Grafana.
• Объяснить разницу планов с индексом и без него на своих цифрах.
• Назвать, почему «неиспользуемый» индекс на мастере мог использоваться на реплике.
• Показать, как очередь пула усиливает деградацию, и почему пул — не причина.
• Пройти цикл fail-1 → check-1 (red) → fix-1 → check-1 (green).
• Написать пятистрочный разбор инцидента по регламенту.

🔗 Мостик к Модулю 2

Индекс вернул скорость, но у PostgreSQL есть второй источник расхождений — реплика. Она повторяет мастер с задержкой, и в этой задержке живёт пользователь, который оплатил заказ и не видит его в списке. В следующем модуле остановим replay, измерим лаг и научимся проектировать чтение так, чтобы read-your-writes не зависел от удачи.