Настройка производительности PostgreSQL: ключевые параметры

PostgreSQL из коробки настроен консервативно — для совместимости с минимальными ресурсами. Для продакшена нужна ручная настройка. Разберём ключевые параметры.

Архитектура памяти PostgreSQL

Запрос → shared_buffers (кеш страниц)
         ↓ если нет → OS page cache → диск

Сортировка, хеши → work_mem
Maintenance → maintenance_work_mem

shared_buffers: главный кеш

# postgresql.conf
shared_buffers = 256MB  # по умолчанию (мало!)

# Рекомендуется: 25% RAM для выделенного сервера
shared_buffers = 4GB    # для 16GB RAM
shared_buffers = 8GB    # для 32GB RAM

PostgreSQL также использует OS page cache. Общее правило: shared_buffers = 25%, OS cache заберёт ещё 50-75% RAM.

-- Проверить hit rate (должен быть > 99%)
SELECT
    sum(blks_hit) AS hits,
    sum(blks_read) AS reads,
    ROUND(sum(blks_hit)::numeric / (sum(blks_hit) + sum(blks_read)) * 100, 2) AS hit_rate
FROM pg_stat_database;

work_mem: память для сортировки и хешей

work_mem = 4MB  # по умолчанию (слишком мало для аналитики)

# Рекомендации:
work_mem = 64MB   # для OLTP с умеренными запросами
work_mem = 256MB  # для аналитических запросов (осторожно!)

Важно: work_mem выделяется на каждую операцию в запросе. Сложный запрос с несколькими сортировками × 100 подключений = огромное потребление RAM.

-- Временно увеличить для одного запроса
SET work_mem = '512MB';
SELECT ... ORDER BY ...;
RESET work_mem;

Как определить нужное значение:

-- Сколько раз сортировка уходила на диск?
SELECT sort_spills FROM pg_stat_statements WHERE query LIKE '%ORDER BY%';

-- Через EXPLAIN: если "external merge Disk" → work_mem мало
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table ORDER BY field;

max_connections: не ставьте слишком большое

max_connections = 100  # по умолчанию

# Каждое соединение ~5-10MB RAM
# 500 соединений = 2.5-5GB только на процессы
max_connections = 200  # для прямых соединений

Для высоких нагрузок используйте PgBouncer (connection pooler) вместо увеличения max_connections:

# С PgBouncer:
max_connections = 50   # только воркеры, не приложение
# PgBouncer: 1000+ клиентов → 50 реальных соединений

WAL и checkpoint настройки

# Размер WAL (журнала транзакций)
wal_buffers = 64MB  # рекомендуется (дефолт: 1/32 shared_buffers)

# Checkpoint: запись dirty pages на диск
checkpoint_completion_target = 0.9  # растянуть checkpoint (меньше пиков)
max_wal_size = 4GB   # максимальный размер WAL (дефолт: 1GB)
min_wal_size = 1GB   # минимальный (дефолт: 80MB)

-- Частые checkpoints → диск нагружен
SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;
-- checkpoints_req > 0 часто → увеличьте max_wal_size

Autovacuum: автоматическая очистка

# Насколько агрессивно vacuum?
autovacuum_vacuum_scale_factor = 0.2      # дефолт: 20% мертвых строк → vacuum
autovacuum_vacuum_scale_factor = 0.05     # для больших таблиц → 5%

autovacuum_analyze_scale_factor = 0.1    # дефолт: 10% → analyze
autovacuum_analyze_scale_factor = 0.02   # для больших таблиц

# Ресурсы vacuum
autovacuum_vacuum_cost_delay = 2ms        # дефолт: 2ms (пауза между чтением)
autovacuum_max_workers = 3                # дефолт: 3 воркера

-- Таблицы с большим bloat
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 1) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC
LIMIT 20;

-- Ручной vacuum если autovacuum не справляется
VACUUM (ANALYZE, VERBOSE) large_table;

effective_cache_size: подсказка планировщику

# Это НЕ выделяет память — только подсказывает планировщику
effective_cache_size = 12GB  # 75% RAM для выделенного сервера

С большим значением планировщик чаще выбирает Index Scan вместо Seq Scan.

Параллельные запросы

max_parallel_workers_per_gather = 2  # дефолт: 2
max_parallel_workers = 8             # дефолт: 8
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0

-- Проверить параллельный план
EXPLAIN SELECT COUNT(*) FROM large_table;
-- Должен показать "Gather" узел

-- Принудительно запустить с параллелизмом
SET max_parallel_workers_per_gather = 4;

Логирование медленных запросов

log_min_duration_statement = 1000  # логировать запросы > 1 секунды
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_lock_waits = on
log_temp_files = 0  # логировать использование temp файлов

Диагностика: что нагружает базу прямо сейчас

-- Текущие активные запросы
SELECT
    pid,
    usename,
    application_name,
    state,
    ROUND(EXTRACT(EPOCH FROM (NOW() - query_start))) AS seconds,
    query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 seconds'
ORDER BY seconds DESC;

-- Ожидающие блокировок
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity WHERE wait_event IS NOT NULL AND state = 'active';

-- Самые нагруженные таблицы
SELECT relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;
-- Много seq_scan при большом n_live_tup → нужен индекс

Быстрый старт: рекомендации для 16GB RAM

# postgresql.conf
shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
max_connections = 100  # + PgBouncer для приложения
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
log_min_duration_statement = 1000
log_lock_waits = on

Или используйте PGTune — автоматическая конфигурация по параметрам сервера.

Итог: топ-5 параметров

После изменения shared_buffers нужен перезапуск. Остальные параметры применяются через pg_reload_conf() или SELECT pg_reload_conf().