Полное руководство по оптимизации настроек PostgreSQL для максимальной производительности

-- Проверка текущей конфигурации
SELECT name, setting, unit, short_desc 
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size');

-- Статистика использования буферов
SELECT * FROM pg_stat_bgwriter;

-- Кэш попаданий
SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  (sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read) + 0.0001)) * 100 as hit_ratio
FROM pg_statio_user_tables;

-- Активные соединения и ожидания
SELECT 
  datname,
  usename,
  state,
  wait_event_type,
  wait_event,
  query 
FROM pg_stat_activity 
WHERE state = 'active';

# Рекомендуемые значения:
# Для сервера с 8-32 ГБ RAM: 25% от общей памяти
# Для сервера с >32 ГБ RAM: 8-16 ГБ

# Пример для сервера с 16 ГБ RAM:
shared_buffers = 4GB  # 25% от 16 ГБ

# Для production-сервера с 64 ГБ RAM:
shared_buffers = 8GB  # 12.5%

# Пример для сервера с 16 ГБ RAM и 100 соединений:
work_mem = 32MB  # (16GB - 4GB) / (100 * 2) ≈ 60MB, но берем меньше для безопасности
maintenance_work_mem = 512MB

# Оптимальные настройки для production:
checkpoint_completion_target = 0.9
wal_buffers = 16MB
max_wal_size = 4GB
min_wal_size = 2GB

# Не делай так! Это антипаттерн:
max_connections = 1000  # Слишком много для большинства систем

# Вместо этого используй:
max_connections = 100
# И добавь пул соединений (PgBouncer или pgpool)

# Для систем с 8+ ядрами:
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# postgresql.conf
# ПАМЯТЬ
shared_buffers = 8GB                 # 25% от 32GB
work_mem = 32MB                     # (32GB - 8GB) / (200 * 2) ≈ 60MB
maintenance_work_mem = 1GB
effective_cache_size = 24GB         # 75% от RAM

# WAL И CHECKPOINTS
wal_buffers = 16MB
max_wal_size = 4GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min          # Чаще для OLTP, реже для OLAP

# СОЕДИНЕНИЯ И ПАРАЛЛЕЛИЗМ
max_connections = 200
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# ВРЕМЕННЫЕ ФАЙЛЫ
temp_buffers = 32MB

# ПЛАНИРОВЩИК
default_statistics_target = 100
random_page_cost = 1.1              # Для SSD
effective_io_concurrency = 200      # Для SSD/NVMe

# ЛОГИРОВАНИЕ
log_min_duration_statement = 1000   # Логировать медленные запросы >1с

-- Эффективность shared_buffers
SELECT 
  blks_hit * 100 / (blks_hit + blks_read) AS cache_hit_ratio
FROM pg_stat_database 
WHERE datname = current_database();

-- Проверка нагрузки на checkpoint
SELECT 
  checkpoints_timed,
  checkpoints_req,
  buffers_checkpoint,
  buffers_clean
FROM pg_stat_bgwriter;

-- Медленные запросы (требует включенного log_min_duration_statement)
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;