Как настраивал PostgreSQL для высокой нагрузки

В DevOps-контексте настройка PostgreSQL — это не только правка postgresql.conf, но и обеспечение отказоустойчивости, мониторинга и автоматического восстановления. Моя типичная конфигурация для сервиса под нагрузкой:

1. Архитектура: Разворачивал Patroni для управления кластером с автоматическим фейловером. Хранилище — либо сетевой SSD (в облаке), либо локальные диски с репликацией.

2. Ключевые параметры тюнинга (postgresql.conf):

# Память (для сервера с 16 ГБ ОЗУ)
shared_buffers = 4GB                 # 25% от RAM
effective_cache_size = 12GB          # 75% от RAM
work_mem = 32MB                      # Для сложных сортировок
maintenance_work_mem = 1GB
max_connections = 200

# Write-Ahead Log (WAL) - для надежности и производительности
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9   # Растягиваем checkpoint

# Запросы и планирование
random_page_cost = 1.1               # Для SSD
effective_io_concurrency = 200

3. Мониторинг и обслуживание:

• Устанавливал pg_stat_statements для анализа медленных запросов.
• Настраивал pg_cron для регулярных задач (например, очистка старых данных).
• Интегрировал в Prometheus через экспортер postgres_exporter, отслеживая ключевые метрики: количество подключений, репликационный лаг, рост таблиц.

4. Резервное копирование: Использовал pgbackrest для инкрементальных бэкапов с дедупликацией и потоковой передачей WAL. Бэкапы хранились в S3-совместимом хранилище с политикой ротации.

5. Доступ (pg_hba.conf): Ограничивал доступ только IP-адресами прикладных серверов и сервисов мониторинга, отключая доверенную (trust) аутентификацию.