PgBouncer для PostgreSQL: пул соединений, режимы работы и настройка под нагрузку

Что решает PgBouncer и когда он нужен​

PostgreSQL создаёт отдельный процесс на каждое клиентское соединение. Каждый процесс потребляет память (минимум 5–10 МБ на старте, больше при активной работе), а при превышении max_connections сервер просто отказывает в подключении. Типичные симптомы перегрузки:

• Ошибки FATAL: sorry, too many clients already в логах приложений.
  
• Рост времени отклика при увеличении числа одновременных подключений.
  
• Высокий CPU на создание/уничтожение процессов при коротких транзакциях.
  

PgBouncer — легковесный прокси, который принимает клиентские соединения и мультиплексирует их на ограниченное число реальных подключений к PostgreSQL. Сам он потребляет единицы мегабайт и способен обслуживать тысячи клиентов.

Три режима пулинга​

Режим определяется директивой pool_mode в секции [pgbouncer] или переопределяется для конкретной базы в секции [databases].

session​

Серверное соединение удерживается за клиентом на всё время его сессии. После отключения клиента серверное соединение возвращается в пул.

• Плюсы: полная совместимость с любым SQL, включая prepared statements, SET, advisory locks.
  
• Минусы: минимальная экономия соединений — пул помогает только при коротких сессиях.
  
• Когда использовать: приложения с длинными сессиями и сложным состоянием, миграция без изменения кода.
  

transaction (рекомендуемый по умолчанию)​

Серверное соединение выделяется на время транзакции и возвращается в пул сразу после COMMIT или ROLLBACK. Между транзакциями клиент «сидит» на внутреннем соединении PgBouncer без серверного ресурса.

• Плюсы: максимальная переиспользуемость при коротких транзакциях.
  
• Минусы: SET, PREPARE, advisory locks, LISTEN/NOTIFY не сохраняются между транзакциями — состояние сбрасывается при возврате соединения в пул.
  
• Когда использовать: веб-приложения, микросервисы, любой сценарий с короткими транзакциями.
  

statement​

Серверное соединение выделяется на один SQL-запрос. После завершения запроса соединение сразу возвращается в пул.

• Плюсы: экстремальная экономия.
  
• Минусы: многооператорные транзакции невозможны — каждый запрос выполняется в отдельной транзакции.
  
• Когда использовать: только для простых read-only запросов без транзакционной логики.
  

Установка и минимальная конфигурация​

Установка​

## Debian/Ubuntu
sudo apt install pgbouncer

## RHEL/CentOS/Rocky
sudo dnf install pgbouncer

Структура pgbouncer.ini​

Основной файл конфигурации обычно расположен в /etc/pgbouncer/pgbouncer.ini.

[databases]
; Формат: логическое_имя = подключение к реальному серверу
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
log_connections = 0
log_disconnections = 0

Файл аутентификации​

userlist.txt содержит пары «пользователь» — «пароль» (или хеш):

"app_user" "md5a1b2c3d4e5f6..."
"readonly" "md5f6e5d4c3b2a1..."

Хеш можно получить из pg_shadow:

SELECT usename, passwd FROM pg_shadow WHERE usename = 'app_user';

Ключевые параметры и их влияние​

Параметр	Назначение	Типичное значение
max_client_conn	Максимум клиентских подключений к PgBouncer	1000–10000
default_pool_size	Серверных соединений на одну пару пользователь/база	20–50
reserve_pool_size	Дополнительных соединений при пиковой нагрузке	5–10
reserve_pool_timeout	Секунд ожидания перед выдачей резервного соединения	3–5
max_db_connections	Жёсткий лимит серверных соединений на базу (глобально)	≤ max_connections PostgreSQL
server_idle_timeout	Закрытие неиспользуемых серверных соединений	300–600
query_wait_timeout	Максимальное время ожидания свободного соединения из пула	10–30
server_lifetime	Максимальное время жизни серверного соединения (защита от утечек)	3600
idle_transaction_timeout	Закрытие клиентских сессий в состоянии idle in transaction	300–600

Как подобрать default_pool_size​

Формула грубой оценки:

default_pool_size ≈ max_connections_postgresql / число_баз_в_пуле × 0.8

Если PostgreSQL настроен на max_connections = 200 и PgBouncer обслуживает одну базу, разумно выставить default_pool_size = 150–160, оставив запас для суперпользователя и служебных подключений. При нескольких базах лимит делится между ними.

Настройка PostgreSQL для работы с PgBouncer​

На стороне PostgreSQL нужно убедиться, что:

1. listen_addresses включает адрес, с которого подключается PgBouncer.
   
2. В pg_hba.conf разрешён доступ для пользователя PgBouncer (обычно md5 или scram-sha-256).
   
3. max_connections не завышен без необходимости — PgBouncer сам ограничивает реальное число подключений.
   

Пример строки в pg_hba.conf:

host    mydb    app_user    127.0.0.1/32    md5

Мониторинг через SHOW-команды​

PgBouncer предоставляет встроенную консоль. Подключение:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

Полезные команды:

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW CONFIG;

Интерпретация SHOW POOLS​

Вывод содержит колонки cl_active, cl_waiting, sv_active, sv_idle:

• cl_active — клиенты, ожидающие ответа сервера.
  
• cl_waiting — клиенты, ждущие свободного серверного соединения. Если значение стабильно > 0, пул мал.
  
• sv_active — серверные соединения, обрабатывающие запрос.
  
• sv_idle — серверные соединения в пуле, готовые к выдаче.
  

Если cl_waiting растёт, увеличивайте default_pool_size или reserve_pool_size.

Типичные ошибки и их последствия​

Prepared statements в transaction mode​

При pool_mode = transaction prepared statements (PREPARE/EXECUTE) создаются на одном серверном соединении, а следующий EXECUTE может уйти на другое. Результат — ошибка prepared statement "xxx" does not exist.

Решение: использовать pool_mode = session для приложений с prepared statements, либо переключиться на расширенный протокол на уровне драйвера (например, prepareThreshold=0 в JDBC или prepared_statements=false в некоторых ORM).

SET и session-level переменные​

SET search_path, SET timezone, SET application_name сбрасываются при возврате соединения в пул. Если приложение полагается на них, нужно либо выставлять их в начале каждой транзакции, либо использовать session mode.

LISTEN/NOTIFY​

Работает только в session mode. В transaction mode подписка теряется после завершения транзакции.

Завышенный max_client_conn без лимитов на сервере​

Если max_client_conn = 10000, а default_pool_size = 25, PgBouncer примет 10 000 клиентов, но одновременно обслуживать будет только 25. Остальные будут ждать в очереди. Это нормально для пиковых всплесков, но при постоянной перегрузке query_wait_timeout начнёт отбрасывать запросы.

Безопасность​

• Не выставляйте listen_addr = 0.0.0.0 без файрвола. PgBouncer не имеет встроенного механизма ограничения по IP.
  
• Используйте auth_type = scram-sha-256 (PostgreSQL 14+), если поддерживается клиентскими драйверами.
  
• Файл userlist.txt должен иметь права 0600 и владельца pgbouncer.
  
• Для TLS настройте client_tls_sslmode, client_tls_cert_file, client_tls_key_file в секции [pgbouncer].
  

Перезагрузка конфигурации без остановки​

## SIGHUP — перечитать конфиг без разрыва существующих соединений
kill -HUP $(cat /var/run/pgbouncer/pgbouncer.pid)

## Или через консоль:
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "RELOAD;"

RELOAD перечитывает pgbouncer.ini и userlist.txt. Существующие соединения не разрываются, новые параметры применяются к новым подключениям.

Проверка результата​

После настройки убедитесь:

1. Приложение подключается через порт 6432 и получает ответы.
   
2. SHOW POOLS; показывает cl_waiting = 0 в штатном режиме.
   
3. На стороне PostgreSQL SELECT count(*) FROM pg_stat_activity; не превышает default_pool_size + reserve_pool_size.
   
4. При пиковой нагрузке нет ошибок query_wait_timeout в логах приложений.
   
5. Prepared statements и session-переменные работают корректно (или вы осознанно выбрали transaction mode и убрали зависимости от них).
   

Источники​

• Ubuntu Server documentation
  
• Docker Engine | Docker Docs