Поиск узких мест в PostgreSQL

Поиск узких мест в PostgreSQL

Методология диагностики

1. Сбор симптомов

Вопросы для старта:

• Что именно медленно? (конкретный запрос, операция, весь БД)
• Когда началось? (после деплоя, роста данных, изменения нагрузки)
• Как часто? (постоянно или пиками)
• Сколько пользователей/запросов?

2. Базовые проверки

-- Нагрузка CPU/Memory на сервере
-- Диск I/O: iostat, iotop
-- Сеть: iftop, netstat

-- Активность в БД
SELECT count(*) FROM pg_stat_activity;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Блокировки
SELECT * FROM pg_locks WHERE granted = false;

-- Долгие транзакции
SELECT pid, now() - xact_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'active' AND now() - xact_start > interval '5 minutes';

3. Анализ запросов

Топ медленных

SELECT query, mean_time, calls, total_time 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 20;

С высоким I/O

SELECT query, shared_blks_read, shared_blks_hit 
FROM pg_stat_statements 
ORDER BY shared_blks_read DESC 
LIMIT 20;

Без индексов

-- Запросы Seq Scan на больших таблицах
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM large_table WHERE created_at > '2026-01-01';

4. Анализ планов

Красные флаги в EXPLAIN:

• Seq Scan на таблице > 100K строк с фильтром
• Nested Loop с большим количеством строк во внешнем цикле
• Sort с «Disk: yes» — сортировка на диске
• Hash Join с «Disk: yes» — хеш на диске
• Много loops в Nested Loop

5. Системные bottlenecks

CPU

• Признаки: 100% CPU, много активных запросов
• Причины: сложные запросы, отсутствие индексов, функции в WHERE
• Решение: оптимизация запросов, индексы, партиционирование

Memory

• Признаки: swap usage, OOM killer
• Причины: слишком много connections, большой work_mem
• Решение: pgBouncer, уменьшение max_connections, настройка work_mem

Disk I/O

• Признаки: высокий await, %util в iostat
• Причины: много записи (WAL), checkpoint, vacuum
• Решение: SSD, настройка checkpoint, отдельные диски для WAL

Locks

• Признаки: запросы висят в состоянии «waiting»
• Причины: долгие транзакции, DDL во время нагрузки
• Решение: короткие транзакции, избегайте DDL в пик

6. Специфичные проблемы PostgreSQL

Autovacuum

-- Проблемы autovacuum
SELECT schemaname, relname, n_dead_tup, last_autovacuum 
FROM pg_stat_user_tables 
WHERE n_dead_tup > 1000;

-- Настройки
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;

Симптомы: раздувание таблиц, медленные запросы, высокий I/O

Checkpoint

-- Частые checkpoint
SELECT * FROM pg_stat_bgwriter;

Симптомы: spikes I/O, высокий %util диска

Cache hit ratio

SELECT 
    sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

Норма: > 99%. Ниже — мало RAM или плохой план запросов.

7. Инструменты

• pg_stat_statements — статистика запросов
• pg_stat_activity — текущие подключения
• EXPLAIN ANALYZE — планы выполнения
• pgBadger — анализ логов
• pg_top — top для PostgreSQL
• PoWA — Performance Observability

8. Чек-лист быстрого поиска

1. Что медленно? — конкретный запрос или всё
2. Есть блокировки? — pg_locks
3. Долгие транзакции? — pg_stat_activity
4. Топ запросов — pg_stat_statements
5. Планы — EXPLAIN ANALYZE
6. Системные метрики — CPU, RAM, Disk I/O
7. Autovacuum — pg_stat_user_tables
8. Cache hit — pg_statio_user_tables

9. Типовые сценарии

Сценарий 1: «Всё тормозит»

• Проверьте CPU и RAM
• Много активных подключений? → pgBouncer
• Высокий Disk I/O? → checkpoint, WAL

Сценарий 2: «Один запрос тормозит»

• EXPLAIN ANALYZE
• Есть индекс? → создайте
• Много строк? → партиционирование

Сценарий 3: «Периодические spikes»

• Логи: checkpoint, autovacuum
• Расписание задач
• Пики нагрузки

Действия после поиска

1. Быстрые победы — добавить индекс, увеличить work_mem
2. Средние — оптимизировать запрос, настроить autovacuum
3. Сложные — партиционирование, изменение архитектуры
4. Мониторинг — настроить алёрты на найденные метрики