Диагностика здоровья PostgreSQL: 10 SQL-запросов для поиска мертвых строк, тяжелых запросов и блокировок

Диагностика здоровья PostgreSQL: 10 SQL-запросов для поиска мертвых строк, тяжелых запросов и блокировок

В процессе эксплуатации PostgreSQL штатный мониторинг на уровне операционной системы — загрузка процессорных ядер, использование оперативной памяти и объем свободных дисковых операций (IOPS) — часто показывает идеальные графики. Однако пользователи при этом сталкиваются с неожиданными задержками при выполнении привычных операций. Причина кроется в специфике внутреннего устройства СУБД, где деградация производительности накапливается незаметно для внешних утилит.

Для своевременного обнаружения узких мест инженеру по базам данных и бэкенд-разработчику необходимо регулярно анализировать системные представления (System Statistics Views). В данной карточке собраны 10 ключевых SQL-запросов, позволяющих диагностировать накопление мертвых строк, неэффективные индексы, зависшие транзакции и цепочки блокировок до того, как они приведут к падению продакшена.

1. Анализ накопления мертвых строк (Dead Tuples)

PostgreSQL использует механизм многоверсионности (MVCC). При выполнении операций UPDATE и DELETE старые версии строк не удаляются физически, а помечаются как неактуальные (dead tuples). Их очисткой занимается фоновый процесс autovacuum. Если он не успевает за потоком изменений, таблица раздувается (bloat), а скорость чтения резко падает.

SELECT 
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_tuple_ratio,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_tuple_ratio DESC
LIMIT 10;

Интерпретация: Доля мертвых строк более 20% в крупной таблице — четкий сигнал к проверке настроек autovacuum или анализу длительных транзакций.

Предупреждение: Не используйте VACUUM FULL в рабочее время! На время работы он эксклюзивно блокирует таблицу (ACCESS EXCLUSIVE LOCK). Для очистки без блокировки записи применяйте утилиту pg_repack, требующую запаса дискового пространства и наличия уникального ключа.

2. Поиск самых тяжелых запросов через pg_stat_statements

Модуль pg_stat_statements агрегирует статистику выполнения всех входящих запросов. Чтобы включить его, потребуется добавить имя расширения в параметр shared_preload_libraries файла postgresql.conf, перезапустить службу СУБД и выполнить команду CREATE EXTENSION IF NOT EXISTS pg_stat_statements;.

SELECT 
    calls,
    ROUND(total_exec_time::numeric / 1000, 2) AS total_time_sec,
    ROUND(mean_exec_time::numeric, 2) AS mean_time_ms,
    ROUND((total_exec_time / SUM(total_exec_time) OVER ()) * 100, 2) AS percentage_time,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Интерпретация: Запросы следует ранжировать по суммарному времени (total_exec_time), а не по среднему (mean_exec_time). Часто выполняемый легкий запрос, вызываемый 100 000 раз в минуту, создает куда большую нагрузку на CPU и накопители, чем один тяжелый отчет, запускаемый раз в день.

3. Обнаружение избыточных последовательных сканирований (Seq Scan)

Последовательное чтение таблицы (seq_scan) заставляет СУБД считывать все блоки с диска. Если таблица содержит миллионы записей, отсутствие нужного индекса приводит к катастрофическому падению производительности.

SELECT 
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND pg_relation_size(relid) > 10 * 1024 * 1024
ORDER BY seq_tup_read DESC
LIMIT 10;

Интерпретация: Если колонка seq_tup_read исчисляется миллиардами прочитанных строк при малом числе idx_scan, вам необходимо изучить планы запросов к этой таблице с помощью EXPLAIN (ANALYZE, BUFFERS) и добавить недостающие индексы.

4. Выявление неиспользуемых индексов

Каждый созданный индекс ускоряет чтение, но замедляет операции INSERT, UPDATE и DELETE, а также занимает место в оперативной памяти и на диске.

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
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 
  AND indisunique = FALSE 
  AND indisprimary = FALSE
ORDER BY pg_relation_size(indexrelid) DESC;

Интерпретация: Индексы с idx_scan = 0 — кандидаты на удаление. Однако перед выполнением DROP INDEX CONCURRENTLY убедитесь, что статистика не сбрасывалась недавно и данный индекс не используется в редких ежемесячных регламентных задачах или на репликах.

5. Поиск сессий в состоянии idle in transaction

Сессия, открывшая транзакцию и зависшая в ожидании действия от клиента (idle in transaction), удерживает старый снимок данных (xmin horizon). Это блокирует работу autovacuum, запрещая ему очищать мертвые строки во всем кластере.

SELECT 
    pid,
    usename,
    client_addr,
    state,
    NOW() - state_change AS idle_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_duration DESC;

Интерпретация: Для предотвращения подобных проблем на уровне конфигурации рекомендуется задавать параметр idle_in_transaction_session_timeout = '60s'.

6. Дерево блокировок и взаимные блокировки (Deadlocks)

Когда один процесс ждет освобождения ресурса, захваченного другим процессом, возникает цепочка блокировок.

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

7. Коэффициент попадания в кэш (Cache Hit Ratio)

Высокая производительность PostgreSQL зависит от того, насколько эффективно данные читаются из кэша оперативной памяти (shared_buffers), а не с медленного диска.

SELECT 
    ROUND(sum(blks_hit) / NULLIF(sum(blks_hit + blks_read), 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database;

Интерпретация: В здоровых OLTP-системах показатель cache_hit_ratio должен составлять не менее 99%. Если значение опускается ниже 95%, необходимо либо увеличить shared_buffers, либо оптимизировать запросы, считывающие лишние данные.

Резюме правил безопасности при диагностике

1. Всегда проверяйте планы запросов через EXPLAIN на тестовом контуре или с ограничением времени выполнения.
2. Не проводите удаление индексов или массовую пересборку таблиц в часы пиковой нагрузки.
3. Сохраняйте историю метрик во времени, так как единичный снимок статистики не дает полного представления о динамике системы.