Профилирование и мониторинг PostgreSQL

ОБЯЗАТЕЛЬНО

Инженеру

Раздел 8.11, шаг 11 из 12. Дальше — Ansible и Terraform.

---

Что мониторить

Категория	Примеры метрик
Доступность	pg_up, replication lag seconds
Производительность	TPS, latency p95, cache hit ratio
Ресурсы	connections, locks, temp files, bloat
WAL / checkpoint	checkpoint frequency, wal generation rate
Autovacuum	dead tuples, last autovacuum age

Золотые сигналы для приложения с БД — latency запросов, error rate, saturation (connections), replication lag.

---

pg_stat_activity — «кто сейчас»

SELECT pid, usename, datname, state, wait_event_type, wait_event,
       now() - query_start AS duration, left(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

wait_event — где backend ждёт (IO, Lock, Client, …). Долгие idle in transaction — кандидаты на kill и fix в коде.

SELECT pg_cancel_backend(12345);  -- мягко
SELECT pg_terminate_backend(12345); -- жёстко

---

pg_stat_statements — топ запросов

Расширение (часто через shared_preload_libraries):

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

CREATE EXTENSION pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 100)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Сброс статистики после деплоя — pg_stat_statements_reset().

---

pg_stat_user_tables / indexes

SELECT relname,
       seq_scan, idx_scan,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

seq_scan >> idx_scan на большой таблице — сигнал к индексам или statistics.

---

auto_explain

Логирует план медленных запросов автоматически:

shared_preload_libraries = 'pg_stat_statements, auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on

Альтернатива — sampling в приложении (OpenTelemetry) + EXPLAIN в staging.

---

pgBadger — отчёт из логов

pgBadger парсит postgresql-*.log, строит HTML с топ запросами, checkpoint, locks.

pgbadger /var/log/postgresql/postgresql-16-main.log -o report.html

Требует log_line_prefix с %t, %p, %u, %d и log_min_duration_statement или log_statement.

---

Prometheus + Grafana

postgres_exporter exposes metrics:

# scrape config фрагмент
- job_name: postgres
  static_configs:
    - targets: ['postgres-exporter:9187']

Дашборды — ID 9628 (PostgreSQL Database), алерты:

• pg_replication_lag > 30s
• pg_stat_activity_count{state="active"} > 80% max_connections
• rate(pg_stat_bgwriter_buffers_backend[5m]) spike — checkpoint pressure

Стек observability DevOps — 8.04/19, Prometheus, Практикум Prometheus и Grafana, Практикум Zabbix.

---

Профилирование нагрузки

Инструмент	Назначение
pgbench	Synthetic TPS benchmark
EXPLAIN (ANALYZE, BUFFERS)	Один запрос
perf / eBPF	CPU на уровне OS (редко для DBA)
strace	Debug IO syscall (осторожно в prod)

pgbench -i -s 100 testdb
pgbench -c 20 -j 4 -T 60 testdb

---

Checklist алертов production

1. Replication lag > порог (30s–5min по SLA).
2. Disk > 85% на WAL/data.
3. max_connections близко к лимиту.
4. Dead tuples ratio > 20% на hot table без autovacuum.
5. age(datfrozenxid) > 200M.
6. Failed backup job (Wal-G / operator).

---

Практика

1. Включите pg_stat_statements, найдите top-3 по total_exec_time, оптимизируйте один запрос.
2. Сгенерируйте нагрузку pgbench, постройте Grafana dashboard cache hit + TPS.
3. Запустите pgBadger на log с auto_explain.

---

Связанные материалы

• postgresql.conf
• Бэкапы
• VACUUM и bloat

---