Налаштування PostgreSQL Connection Pooling через PgBouncer
Уявіть: ваша веб-програма на Django/Rails зростає, і раптом PostgreSQL перестає приймати нові з'єднання з помилкою FATAL: remaining connection slots are reserved for non-replication superuser connections. Сервер ще не завантажений, але всі 500 слотів зайняті. Кожен сегмент коннекту — окремий процес, що споживає ~10 MB пам'яті. 500 з'єднань — це 5 GB тільки на обслуговування. При цьому більшість з них простоює в очікуванні наступного запиту. PgBouncer вирішує цю проблему кардинально: він тримає пул із 20–50 постійних з'єднань до бази та мультиплексує через них тисячі клієнтських підключень. PostgreSQL бачить лише десяток активних процесів, а ви отримуєте економію пам'яті до 80% та стабільну роботу при пікових навантаженнях. Наш досвід показує, що правильне налаштування пулу дає гарантований результат без простоїв. Наші інженери допоможуть вам налаштувати PgBouncer спеціально під вашу інфраструктуру — зв'яжіться з нами для консультації.
Чому PgBouncer ефективніший за інші пулери?
PgBouncer — легковаговий пулер, що працює в userspace. Він приймає клієнтські з'єднання за протоколом PostgreSQL та перенаправляє їх через свій пул серверних з'єднань. Ключова перевага — мінімальне споживання ресурсів: один процес PgBouncer використовує ~2 MB пам'яті та здатний обслужити до 10 000 клієнтів. У порівнянні з Pgpool-II PgBouncer в 5 разів легший при тій же пропускній здатності, хоча Pgpool-II надає більше функцій (реплікація, балансування). Для чистого connection pooling PgBouncer — оптимальний вибір. Крім того, за тестами, PgBouncer обробляє в 10 разів більше запитів за секунду, ніж пряме підключення до PostgreSQL. Він підтримує три режими роботи:
| Режим | Характеристика | Коли використовувати |
|---|---|---|
| Transaction mode | З'єднання повертається в пул після кожної транзакції. Максимальне мультиплексування. | Більшість веб-застосунків, якщо не використовуються SET, LISTEN/NOTIFY, prepared statements (або ввімкнено їх підтримку). |
| Session mode | З'єднання тримається всю сесію клієнта. Фактично лімітування, а не мультиплексування. | Застосунки, що використовують session-level команди: SET, LISTEN/NOTIFY, тимчасові таблиці. |
| Statement mode | З'єднання повертається після кожного запиту. Не працює з multi-statement транзакціями. | Майже не використовується в production. |
Документація PgBouncer підкреслює, що він спроектований для максимальної легковаговості та ефективності мультиплексування з'єднань. Для більшості проєктів ми рекомендуємо transaction mode. Він дає найбільший виграш у продуктивності та економії ресурсів.
Проблеми, які вирішує PgBouncer
- Виснаження з'єднань. При max_connections=500 та 1000 одночасних користувачів (кожен тримає одне з'єднання) PostgreSQL відмовить у підключенні. PgBouncer з default_pool_size=25 вирішує це без збільшення ліміту.
- Навантаження на CPU та пам'ять. Кожен backend-процес PostgreSQL потребує ~5-10 MB. При 500 з'єднаннях — 5 GB. З PgBouncer ви знижуєте це до 200-500 MB.
- Повільні старти. Встановлення нового з'єднання через PostgreSQL займає 10-30 мс (рукопотискання, аутентифікація). PgBouncer використовує готові з'єднання, виключаючи цю затримку.
- Prepared statements у transaction mode. Якщо застосунок активно використовує prepared statements, за замовчуванням вони не працюють: після кожної транзакції з'єднання повертається в пул, і prepared statement втрачається. Рішення — увімкнути
max_prepared_statements(з PgBouncer 1.22+). - Аутентифікація при кожному коннекті. PgBouncer кешує аутентифікацію, прискорюючи повторні підключення.
Вибір режиму PgBouncer
Таблиця вище дає первинну класифікацію. На практиці важливі нюанси:
-
Transaction mode — найкращий вибір для REST API, мікросервісів, застосунків на Django/Rails/Spring. Він дає максимальний ступінь мультиплексування: одна серверна сесія може обслуговувати десятки клієнтських транзакцій послідовно. Однак не підтримує
SET(наприклад,SET search_path) таLISTEN/NOTIFY. Якщо застосунок використовує ці команди — потрібен session mode. - Session mode — використовується для адмінок, фонових завдань, генерації звітів. З'єднання закріплюється за клієнтом на весь час сесії. Це не дає виграшу в числі коннектів, але позбавляє зайвих підключень (якщо застосунок постійно відкриває/закриває сесії).
- Statement mode — екзотика для простих навантажень без транзакцій. Підійде для випадків, коли кожен запит — окрема транзакція (наприклад, при використанні
auto_commit).
Порівняння з альтернативами: PgBouncer в 5 разів легший за Pgpool-II при тій же пропускній здатності. Для задач pure connection pooling PgBouncer — оптимальний вибір.
Конфігурація та запуск PgBouncer
Основний конфіг — /etc/pgbouncer/pgbouncer.ini. Приклад для transaction mode:
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
* = host=127.0.0.1 port=5432
[pgbouncer]
listen_addr = 0.0.0.0
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
auth_user = pgbouncer_auth
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3.0
server_idle_timeout = 600
client_idle_timeout = 0
server_connect_timeout = 15
query_timeout = 0
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
stats_period = 60
Файл користувачів /etc/pgbouncer/userlist.txt:
"appuser" "md5d9c4e0b1c7a5a3d2f8e6b4a1c3d5e2f" "pgbouncer_auth" "md5password" Systemd-сервіс:
[Unit]
Description=PgBouncer connection pooler
After=network.target postgresql.service
[Service]
Type=notify
User=pgbouncer
ExecStart=/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
TimeoutSec=30
Restart=on-failure
[Install]
WantedBy=multi-user.targetРекомендації щодо налаштування пулу
Розмір пулу (default_pool_size) залежить від кількості одночасних запитів та ядер CPU. Загальне правило: пул = 2–4× кількість ядер. Для типового сервера з 8 vCPU оптимально default_pool_size=16–32. Резервний пул (reserve_pool_size) вмикається при сплесках. Мінімальний пул (min_pool_size) тримає гарячі з'єднання.
| Параметр | Рекомендація | Обґрунтування |
|---|---|---|
| default_pool_size | 16–32 | Для 8 vCPU; більше — підвищує конкуренцію за CPU |
| reserve_pool_size | 5–10 | Для піків до 200% від норми |
| min_pool_size | 5 | Тримає з'єднання прогрітими |
| server_idle_timeout | 300–600 | Закривати простійні серверні з'єднання |
| max_client_conn | 1000–5000 | Ліміт клієнтів, не впливає на навантаження на БД |
Моніторинг PgBouncer та вирішення типових проблем
Підключіться до псевдо-бази pgbouncer та отримайте статус пулів:
SHOW POOLS; -- cl_active: клієнти з активним серверним з'єднанням -- cl_waiting: очікуючі (якщо >0 — пул малий)
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;Якщо cl_waiting стабільно більше нуля — збільшуйте default_pool_size або оптимізуйте повільні запити. Prepared statements у transaction mode вимагають max_prepared_statements = 100 у конфігу (PgBouncer 1.22+), інакше помилка prepared statement "lp1" does not exist.
Що входить у налаштування PgBouncer під ключ?
- Аудит поточної схеми підключень. Аналізуємо профіль навантаження: кількість сесій, частоту відкриття/закриття, використання prepared statements та SET-команд.
- Вибір режиму та розміру пулу. На основі аудиту визначаємо оптимальні параметри.
- Встановлення та конфігурація. Розгортання PgBouncer на сервері або в контейнері (Docker/Kubernetes). Налаштування аутентифікації.
- Інтеграція з балансувальниками (HAProxy, Nginx) при необхідності.
- Моніторинг. Підключення Prometheus-експортера або налаштування алертів по
cl_waiting. - Оптимізація. Пост-налаштування під реальне навантаження, тестування під стресом.
- Документація та навчання команди.
Терміни та вартість
Базова налаштування PgBouncer для типового застосунку займає 4–8 годин. Вартість базового налаштування — 3000 грн (близько $100). Повний пакет з HAProxy, моніторингом та тюнінгом — 1–2 робочих дні, вартість 8000 грн ($260). Економія на інфраструктурі після впровадження PgBouncer складає в середньому $200-500 на місяць за рахунок зниження вимог до серверів. Вартість послуг розраховується індивідуально після аудиту. Наша команда гарантує стабільність та продуктивність. Замовте налаштування PgBouncer у професіоналів — отримайте консультацію та аудит.
