PostgreSQL: архитектура и инструменты разработчика

PostgreSQL: архитектура и инструменты разработчика

Главная идея

Как это выглядит на практике

1. Подключение через psql: выполни psql -U postgres -d mydb. После подключения используй мета-команды: \dt выводит все таблицы схемы, \d users показывает структуру таблицы users со всеми столбцами, типами и индексами, \timing включает вывод времени каждого запроса.
2. Настройка psql для комфортной работы: добавь в ~/.psqlrc строки \set PROMPT1 '%[%033[1m%]%/%[%033[0m%]> ' и \pset pager off чтобы отключить постраничный вывод. Опция \x auto включает расширенный режим вывода для широких таблиц.
3. Найди все активные запросы через pg_stat_activity: SELECT pid, usename, state, wait_event_type, wait_event, left(query, 80) AS query FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start. Это первый шаг при диагностике любого замедления.
4. Убей зависший запрос: SELECT pg_cancel_backend(pid) для мягкой отмены (как Ctrl+C) или SELECT pg_terminate_backend(pid) для принудительного завершения. Всегда предпочитай cancel — terminate завершает соединение полностью.
5. Проверь статистику по таблицам: SELECT relname, seq_scan, idx_scan, n_dead_tup, n_live_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10. Таблицы с большим n_dead_tup и редким autovacuum — кандидаты на ручной VACUUM ANALYZE.
6. Включи и используй pg_stat_statements: добавь shared_preload_libraries = 'pg_stat_statements' в postgresql.conf, перезапусти PostgreSQL, выполни CREATE EXTENSION pg_stat_statements. После этого SELECT query, calls, total_exec_time/calls AS avg_ms, rows/calls AS avg_rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10 показывает самые дорогие запросы.
7. Сброс статистики: SELECT pg_stat_statements_reset() очищает накопленную статистику. Удобно делать в начале нагрузочного теста, чтобы видеть только профиль нового кода.
8. Проверь использование памяти: SELECT sum(pg_database_size(datname))/1024/1024 AS total_db_mb FROM pg_database показывает размер всех БД. SELECT pg_size_pretty(pg_total_relation_size('orders')) показывает полный размер таблицы включая индексы.
9. Мониторинг блокировок: SELECT bl.pid AS blocked_pid, ka.query AS blocking_query, a.query AS blocked_query FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid WHERE NOT bl.granted. Этот запрос показывает заблокированные процессы и запросы, которые их блокируют.
10. Настрой autovacuum для активных таблиц через ALTER TABLE: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005). Для больших таблиц с частыми UPDATE снижай scale_factor — autovacuum будет запускаться чаще и не даст накапливаться мёртвым туплам.
11. Используй \watch для периодического выполнения: SELECT count(*) FROM pg_stat_activity WHERE state = 'active'; \watch 2 выполняет этот запрос каждые 2 секунды. Удобно для наблюдения за нагрузкой в реальном времени.
12. Установи PgBouncer: в pgbouncer.ini задай pool_mode = transaction, max_client_conn = 1000, default_pool_size = 20. Это позволяет 1000 клиентских соединений поддерживать всего 20 реальных соединений к PostgreSQL.

Примеры кода

Мониторинг активных запросов и блокировок

-- Все активные (не idle) соединения
SELECT
  pid,
  usename,
  application_name,
  state,
  wait_event_type,
  wait_event,
  now() - query_start AS duration,
  left(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY duration DESC NULLS LAST;

Топ медленных запросов через pg_stat_statements

SELECT
  round(total_exec_time::numeric / calls, 2)  AS avg_ms,
  calls,
  round(total_exec_time::numeric, 2)           AS total_ms,
  rows / calls                                  AS avg_rows,
  left(query, 120)                              AS query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY total_exec_time DESC
LIMIT 15;

Таблицы с накопившимися мёртвыми туплами

SELECT
  schemaname,
  relname AS table_name,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

Размеры таблиц и индексов

SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid))  AS total_size,
  pg_size_pretty(pg_relation_size(relid))        AS table_size,
  pg_size_pretty(pg_indexes_size(relid))         AS indexes_size,
  pg_size_pretty(
    pg_total_relation_size(relid) - pg_relation_size(relid)
  )                                              AS bloat_estimate
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Cache hit ratio — эффективность shared buffers

SELECT
  relname,
  heap_blks_read   AS disk_reads,
  heap_blks_hit    AS cache_hits,
  round(
    heap_blks_hit * 100.0 /
    NULLIF(heap_blks_hit + heap_blks_read, 0), 2
  ) AS cache_hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 10;

Что происходит под капотом

• Postmaster — главный процесс PostgreSQL. При новом TCP-соединении postmaster вызывает fork(), создавая дочерний backend-процесс, который полностью обслуживает это соединение. Каждый backend имеет свой стек (~5MB), локальные переменные и указатель в shared buffers. Это архитектурное решение даёт изоляцию, но ограничивает масштабирование при тысячах соединений.
• Shared buffers — общий кеш страниц данных, доступный всем backend-процессам через общую память (shared memory). Дефолт 128MB катастрофически мал для продакшена — рекомендуется 25% RAM. При cache miss PostgreSQL читает 8KB-страницу с диска в shared buffers, откуда её читают все последующие запросы.
• WAL (Write-Ahead Log) — журнал изменений. Каждое изменение данных сначала записывается в WAL, и только потом в shared buffers (dirty page). WAL writer периодически сбрасывает WAL-буфер на диск. Checkpoint-процесс раз в несколько минут (max_wal_size) синхронизирует все dirty pages с диска. WAL — основа ACID durability и физической репликации.
• Autovacuum daemon периодически сканирует pg_stat_user_tables и запускает VACUUM для таблиц, где n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup. По умолчанию scale_factor = 0.2, т.е. VACUUM запускается при накоплении 20% мёртвых туплов. Для больших таблиц (10M+ строк) это слишком поздно — нужно снижать scale_factor до 0.01–0.05.
• pg_stat_statements — расширение, которое перехватывает каждый SQL-запрос, нормализует его (заменяет литералы на $1, $2), и обновляет счётчики в общей памяти. Нормализация позволяет группировать SELECT * FROM orders WHERE id = 1 и SELECT * FROM orders WHERE id = 999 как один тип запроса. Требует shared_preload_libraries = 'pg_stat_statements' и перезапуска.
• Background writer (bgwriter) — процесс, который проактивно записывает dirty pages из shared buffers на диск, чтобы checkpoint не создавал резкого всплеска I/O. bgwriter_lru_maxpages и bgwriter_delay — параметры настройки.
• Процесс stats collector собирает статистику (счётчики таблиц, запросов, I/O) через UDP-сокеты от каждого backend и записывает в shared memory. Именно он питает все pg_stat_* представления.
• Logical replication worker и WAL sender — процессы для репликации. WAL sender читает WAL и отправляет изменения на standby-серверы. Logical replication worker применяет изменения на подписчиков при публикации/подписке.
• Walwriter flush на каждый COMMIT по умолчанию (synchronous_commit = on): PostgreSQL ждёт подтверждения записи WAL на диск перед ответом клиенту. Это гарантирует durability, но добавляет latency. synchronous_commit = off даёт ~10x больший throughput для некритичных данных ценой потери последних ~1-3 секунд при crash.
• PgBouncer в transaction pooling mode: соединение из пула выдаётся backend-процессу только на время транзакции и немедленно возвращается в пул после COMMIT/ROLLBACK. Это позволяет 1000 приложений работать через 20-50 реальных PostgreSQL-процессов. Несовместимо с SET LOCAL, временными таблицами, LISTEN/NOTIFY — для этих случаев используй session pooling mode.

Термины урока

Мини-разбор реальной ситуации

SaaS-платформа на Rails с PostgreSQL на AWS RDS: приложение деплоилось с 50 Puma-воркерами, каждый с пулом из 5 соединений = 250 соединений к PostgreSQL. max_connections = 200 — при пиковой нагрузке новые запросы получали 'FATAL: sorry, too many clients already'. Команда увеличила max_connections до 500, потребление памяти возросло на 3GB, PostgreSQL начал деградировать из-за context switching. Правильное решение: PgBouncer в transaction mode с pool_size=20. Реальных соединений к PostgreSQL стало 20, все 250 воркеров обслуживаются через пул. Потребление памяти уменьшилось на 2.5GB, latency p99 снизилась с 800ms до 90ms. Дополнительно включили pg_stat_statements и нашли три запроса, которые суммарно занимали 70% всего времени PostgreSQL — добавление составных индексов убрало ещё 60% нагрузки.