PgBouncer на VDS: пулы соединений PostgreSQL, конфиг, лимиты и метрики

PgBouncer — лёгкий прокси для PostgreSQL, который даёт то, чего не хватает большинству приложений на VDS под нагрузкой: стабильные пулы соединений. Он снижает потребление памяти на стороне PostgreSQL, сглаживает всплески коннектов, уменьшает время установления соединения и помогает держать сервис отзывчивым. В статье разбираю практическую установку, конфиг, ключевые лимиты (включая max_client_conn и default_pool_size), выбор pool_mode, таймауты, а также метрики и сигналы, по которым видно реальную производительность.

Зачем PgBouncer на VDS и как он работает

Каждое клиентское соединение к PostgreSQL — это отдельный серверный процесс. На небольших VDS это быстро упирается в оперативную память и контекстные переключения. Если веб-приложение или пул воркеров открывает сотни соединений, даже «простой» пик трафика может привести к задержкам и ошибкам «too many clients». PgBouncer решает это через мультиплексирование: десятки и сотни клиентских коннектов снаружи складываются в ограниченный пул относительно небольшого числа серверных соединений к PostgreSQL. Клиенты могут ожидать в очереди, а сервер держит аккуратный, контролируемый уровень конкуренции.

Важно понимать, что PgBouncer не «ускоряет» сами запросы — он оптимизирует фазу установления соединений и их утилизацию. Главная выгода в экономии памяти, уменьшении накладных расходов на коннекты и предотвращении лавинообразных переподключений под нагрузкой. Результат — более предсказуемая производительность.

Выбор режима pool_mode

Критический параметр — pool_mode. От него зависит семантика работы с серверными соединениями:

• session — серверное соединение закрепляется за клиентом на всю сессию. Максимальная совместимость (работают сессионные параметры, session-level prepared statements), но наименее эффективное использование пула.
• transaction — серверное соединение выдаётся только на время транзакции. Золотая середина для большинства OLTP-нагрузок: хорошая утилизация пула и предсказуемое поведение. Важный нюанс: session-level prepared statements и сессионные GUC могут быть несовместимы.
• statement — закрепление на время одного запроса. Максимальная агрегация, но много ограничений. Используйте только если точно понимаете последствия (нет многозапросных транзакций, риски с prepared statements).

Практическое правило: начинайте с transaction. Если приложение активно использует сессионные особенности (например, долгоживущие подготовленные выражения), переходите на session или перепроверьте настройки и код.

Установка PgBouncer

Debian/Ubuntu

sudo apt update
sudo apt install pgbouncer

RHEL/CentOS/AlmaLinux/Rocky

sudo dnf install pgbouncer

После установки проверьте версию и путь до конфигов (обычно /etc/pgbouncer), чтобы понимать, где искать pgbouncer.ini и userlist.txt. Полезно заранее оценить, сколько соединений реально потянет ваш инстанс — про подбор ресурсов есть разбор в материале как выбрать план VDS по CPU и RAM.

Базовый pgbouncer.ini с пояснениями

[databases]
app = host=127.0.0.1 port=5432 dbname=app_db auth_user=pgbouncer

[pgbouncer]
; Где слушать клиентов
listen_addr = 127.0.0.1
listen_port = 6432

; Аутентификация
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Либо централизованно из БД:
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename = $1

; Основные режимы и лимиты
pool_mode = transaction
max_client_conn = 500
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 20
reserve_pool_timeout = 5

; Таймауты и поведение
server_login_retry = 15
server_idle_timeout = 600
server_lifetime = 3600
query_wait_timeout = 120
client_login_timeout = 60
client_idle_timeout = 0

; Безопасность и совместимость
ignore_startup_parameters = extra_float_digits

; Админ и статистика
admin_users = pgbouncer
stats_users = pgbouncer
log_connections = 1
log_disconnections = 1
stats_period = 60

; Сброс состояния между транзакциями (важно для transaction-пула)
server_reset_query = DISCARD ALL

Что здесь важно. listen_addr лучше оставить на 127.0.0.1, а доступ снаружи решать через прокси или файрвол. auth_type используйте scram-sha-256, если на сервере PostgreSQL пароли в SCRAM; иначе подойдёт md5. Параметры default_pool_size, min_pool_size, reserve_pool_size задают поведение пула, а max_client_conn определяет, сколько клиентских сессий PgBouncer готов удерживать одновременно (часть может ждать в очереди).

Пользователи и пароли: userlist.txt и auth_query

Есть два пути аутентификации:

• Локальный файл userlist.txt — содержит пары имя/хеш. Подходит, когда набор пользователей небольшой и редко меняется.
• Через SQL с auth_query — PgBouncer будет спрашивать пароли в самой БД. Удобно для централизованного управления.

Пример userlist.txt (хеш условный):

"pgbouncer" "SCRAM-SHA-256$<long-hash>"
"app_user" "SCRAM-SHA-256$<long-hash>"

Если используете auth_query, убедитесь, что роль pgbouncer существует в PostgreSQL и имеет право читать пароли (обычно системная таблица pg_shadow доступна только суперпользователю; практичнее держать пароль в файле). Компромисс: маленькая «операционная» группа логинов — в userlist.txt, остальное — через БД.

Лимиты и sizing пула

max_client_conn

Это верхний предел клиентских подключений к PgBouncer. Его можно ставить заметно выше, чем max_connections PostgreSQL, но не бездумно: каждый клиентский слот занимает память у PgBouncer и при достижении лимита новые подключения будут отвергаться. Типичный ориентир для приложения со 100–200 веб-воркерами — от 2× до 3× запаса к количеству воркеров (например, 500).

default_pool_size, min_pool_size, reserve_pool_size

default_pool_size — сколько серверных соединений к PostgreSQL выделяется на связку «база+пользователь». Это ключ к экономии памяти. Для OLTP на VDS 2–8 CPU разумно начинать с 20–60, корректируя по метрикам. min_pool_size держит «тёплые» коннекты, чтобы не тратить время на логин под нагрузкой. reserve_pool_size помогает пережить кратковременные всплески: когда все стандартные коннекты заняты, выдаются резервные (обычно 10–30% от default_pool_size).

Если у вас несколько приложений/ролей, помните, что пул формируется по комбинации база+пользователь, и суммарная конкуренция будет выше. Иногда полезно завести отдельного пользователя для фоновых задач и дать ему меньший default_pool_size через секции БД.

Как совместить с max_connections PostgreSQL

PgBouncer не отменяет лимиты на стороне PostgreSQL. Если суммарный пул (по всем базам и пользователям) превышает max_connections, сервер будет отказывать в логине. План простой: оценить бюджет памяти и выставить max_connections на уровне, который реально тянет VDS, а default_pool_size в PgBouncer — чуть ниже, оставив запас для репликации, бэкапов и обслуживания. Тонкости серверной настройки PostgreSQL и автосборщика мусора разборно описаны в статье тюнинг autovacuum и индексирования.

Грубая оценка памяти: один серверный процесс PostgreSQL часто «съедает» от нескольких до десятков мегабайт в пике (учитывая рабочие буферы, локальные аллокации и расширение под нагрузкой). За счёт пула держите активных процессов ровно столько, сколько нужно для целевой конкурентности.

Таймауты и стабильность

• query_wait_timeout — сколько клиент может ждать свободный серверный коннект в очереди. Если очередь длинная и часто выжидается до таймаута — признак перегруза пула.
• server_idle_timeout — через сколько простаивающее серверное соединение закрывать. Полезно, чтобы экономить ресурсы ночами, но не ставьте слишком мало, иначе потеряете выгоду «тёплых» коннектов.
• server_lifetime — мягкая ротация серверных соединений для борьбы с дрейфом памяти и «залипшими» состояниями.
• client_login_timeout — ограничивает фазу логина клиента в PgBouncer, помогает снять зависшие подключения.
• client_idle_timeout — отключает клиентов, ничего не делающих слишком долго (0 — не отключать).

Отдельно про server_reset_query: в transaction-пуле это критично, чтобы обнулять любые следы состояния после транзакции. Обычно достаточно DISCARD ALL. Если приложение полагается на сеансовые GUC-параметры, будьте аккуратны.

Пер-база и пер-пользовательные настройки

В секции [databases] можно переопределять параметры для конкретной базы. Например, для административной базы оставить session-пул и минимальные лимиты, а для основной рабочей — transaction и увеличенный default_pool_size. Это гибкий способ разнести фоновые джобы и пользовательский трафик.

[databases]
admin = host=127.0.0.1 port=5432 dbname=postgres pool_mode=session default_pool_size=5
app   = host=127.0.0.1 port=5432 dbname=app_db   pool_mode=transaction default_pool_size=50 reserve_pool_size=20

Системный сервис и запуск

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer

Если приложение на той же машине, укажите в его строке подключения порт PgBouncer (например, 6432) и хост 127.0.0.1. На стороне PostgreSQL убедитесь, что локальные подключения разрешены правилами pg_hba.conf.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

Метрики и диагностика: что смотреть каждый день

PgBouncer предоставляет оперативную статистику через свою служебную базу pgbouncer. Подключаемся клиентом psql:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

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

• SHOW POOLS; — состояние пулов по связкам база+пользователь. Смотрите на cl_active/cl_waiting, sv_active/sv_idle.
• SHOW STATS; — агрегаты: количество запросов, байты, времена. В сочетании с stats_period даёт скорость.
• SHOW CLIENTS; и SHOW SERVERS; — конкретные подключения, флаги, ожидание.
• SHOW CONFIG; — текущий конфиг с учётом переопределений.

Ключевые «сигналы тревоги» для мониторинга и алертов:

• Очередь: cl_waiting > 0 заметное время или растёт — признак, что default_pool_size мал или запросы слишком долгие.
• Дефицит свободных серверных коннектов: sv_idle почти всегда 0 — вы на пределе конкурентности.
• Средние и перцентили времени запроса в SHOW STATS растут при том же трафике — подозрение на I/O, блокировки или нехватку CPU.
• Частые ошибки логина и server_login_retry — проверьте pg_hba.conf, доступность PostgreSQL и лимиты max_connections.

Для экспортов метрик используйте системные агенты и экспортеры, умеющие читать статистику PgBouncer и PostgreSQL. В алертах фиксируйте пороги по очереди, нулевому sv_idle, ошибкам логина и времени ответов.

Тонкости prepared statements и transaction-пул

Частая проблема при pool_mode=transaction — ошибки вида «prepared statement already exists» или «prepared statement does not exist». Это следствие того, что сеансовые prepared statements не переживают переключение серверных коннектов между транзакциями. Что делать:

• Отключить сессионные prepared statements в приложении или переводить их в режим «на транзакцию/запрос».
• Переключиться на pool_mode=session для конкретной базы/пользователя, где это критично.
• Убедиться, что server_reset_query = DISCARD ALL и приложение не полагается на сеансовое состояние между транзакциями.

Практические профили настроек

Небольшой VDS (2 CPU, 4–8 ГБ RAM), веб-приложение 100 воркеров

• pool_mode: transaction
• max_client_conn: 300–400
• default_pool_size: 30–50
• min_pool_size: 10–20
• reserve_pool_size: 10–15
• server_idle_timeout: 600
• server_lifetime: 1800–3600
• query_wait_timeout: 60–120

Средний VDS (4–8 CPU, 16–32 ГБ RAM), микросервисы с 300–500 воркерами

• pool_mode: transaction
• max_client_conn: 800–1200
• default_pool_size: 60–120
• min_pool_size: 20–40
• reserve_pool_size: 20–40
• server_idle_timeout: 600–900
• server_lifetime: 3600–7200
• query_wait_timeout: 60–120

Это ориентиры, а не догма. Настраивайте по метрикам: цель — чтобы очередь была нулевой или кратковременной, а PostgreSQL сохранял запас по CPU и I/O.

Обновление конфига и безопасный reload

PgBouncer позволяет перезагружать конфиг без остановки:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "RELOAD;"

Либо послать процессу сигнал HUP через systemd:

sudo systemctl reload pgbouncer

Для «тихой паузы» при переключениях используйте команды PAUSE и RESUME в админ-консоли. Это помогает выпустить конфигурацию без лишних ошибок в приложении.

Типовые ошибки и быстрая диагностика

• FATAL: too many clients already в PgBouncer — упёрлись в max_client_conn. Увеличьте лимит, но сначала проверьте, почему клиенты копятся: залипшие соединения, долгие запросы, всплески трафика.
• sorry, too many clients на стороне PostgreSQL — суммарный пул превышает max_connections. Уменьшите default_pool_size или увеличьте max_connections с учётом памяти.
• no pg_hba.conf entry — неправильные правила доступа PostgreSQL. Проверьте хост/пользователь/метод аутентификации.
• prepared statement already exists — конфликт с transaction-пулом. См. раздел о prepared statements.
• частые server_login_retry — проблемы с сетью или PostgreSQL нестабилен. Проверьте логи, systemd-journald, ресурсы VDS.

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

• Держите listen_addr на 127.0.0.1, если PgBouncer и приложение на одном хосте.
• Если нужно слушать снаружи, ограничьте доступ фаерволом и используйте шифрование. PgBouncer умеет TLS к клиентам и к серверу (настройте cert/key/ciphers при необходимости).
• Разделяйте роли: отдельный пользователь для админа (admin_users) и для метрик (stats_users).
• Не складывайте все конфигурации в один пул. Разносите шумные фоновые задачи на отдельные связки база+пользователь с меньшими лимитами.

Как понять, что настроено хорошо

• В обычное время cl_waiting = 0, sv_idle не ноль, есть запас.
• При пиках очередь появляется, но быстро схлопывается, ошибки таймаута редки.
• Средние времена запросов и транзакций стабильны, CPU и I/O VDS не на пределе.
• Логи PgBouncer чистые, без постоянных server_login_retry и отказов в аутентификации.

Короткий чек-лист внедрения

1. Установить PgBouncer и включить сервис.
2. Сконфигурировать [databases] и [pgbouncer], выбрать pool_mode.
3. Настроить аутентификацию: userlist.txt или auth_query.
4. Выставить лимиты: max_client_conn, default_pool_size, резервы и таймауты.
5. Переключить приложение на порт PgBouncer.
6. Включить мониторинг: SHOW POOLS, SHOW STATS, алерты на очередь и ошибки.
7. Недельная калибровка по метрикам: аккуратно подправить размеры пула и таймауты.

Итоги

PgBouncer — обязательный элемент для большинства инсталляций PostgreSQL на VDS, где активных клиентов больше десятков. Грамотный подбор pool_mode, внимательное ограничение default_pool_size и обоснованный max_client_conn позволяют держать базу в устойчивом состоянии, экономить память и сохранять производительность под всплесками. Добавьте мониторинг метрик и дисциплину релизов (reload без даунтайма) — и вы получите предсказуемую систему, в которой нагрузочные сценарии не будут превращаться в ночные приключения.