Проблема: сотні з'єднань до PostgreSQL
При розробці високонавантажених веб-застосунків на PostgreSQL одна з найчастіших проблем — витік з'єднань. Кожен додатковий процес PostgreSQL потребує до 10 МБ пам'яті, що при 500 підключеннях дає 5 ГБ оверхеду. Типова картина: 100 інстансів Django/Laravel, кожен тримає 5 з'єднань — разом 500. База задихається, а адмін платить за зайву пам'ять. Ми понад 7 років налаштовуємо PostgreSQL-інфраструктуру для проектів з навантаженням до 10 000 RPS. PgBouncer вирішує цю проблему, мультиплексуючи клієнтські з'єднання в невеликий пул реальних з'єднань до PostgreSQL. Економія пам'яті сягає 25 разів: з 5 ГБ до 200 МБ.
Як працює PgBouncer і який режим обрати
PgBouncer виступає проксі-шаром між застосунком і базою даних. Клієнтські з'єднання (їх можуть бути тисячі) підключаються до PgBouncer, а він передає запити через пул постійних з'єднань до PostgreSQL. Вибір режиму пулінгу критичний:
| Режим | Опис | Коли використовувати | Обмеження |
|---|---|---|---|
| Session pooling | З'єднання PG зайняте весь час сесії | Старі застосунки з короткими сесіями | Економія мінімальна при довгих сесіях |
| Transaction pooling | З'єднання повертається після транзакції | Рекомендується для веб-застосунків | Несумісний з prepared statements до версії 1.21, SET поза транзакцією, advisory locks |
| Statement pooling | Після кожного запиту | Майже ніколи не потрібен | Сильно обмежує SQL |
Для сучасних фреймворків ми використовуємо transaction pooling. Він забезпечує максимальну утилізацію з'єднань: після кожної транзакції канал повертається в пул і готовий обслужити інший запит. Така схема в 5 разів ефективніша за прямі підключення при однаковій пропускній здатності.
Чому transaction pooling оптимальний для веб-застосунків
Веб-застосунки працюють у парадигмі «запит-відповідь»: кожен обробник виконує одну-дві короткі транзакції. Transaction pooling дозволяє 100 клієнтським підключенням ділити 20 реальних з'єднань до PG. Втрат продуктивності немає, а виграш у пам'яті — в 5 разів. Єдине «але» — prepared statements на протокольному рівні. До версії PgBouncer 1.21 вони не працювали в transaction mode. Рішення: відключити їх у драйвері (наприклад, prepare_threshold=None у SQLAlchemy) або оновити PgBouncer.
Що входить у налаштування PgBouncer під ключ
Впровадження PgBouncer включає повний цикл робіт:
- Встановлення та конфігурацію pgbouncer.ini під ваше навантаження.
- Налаштування аутентифікації (SCRAM-SHA-256 або auth_query).
- Адаптацію драйвера застосунку (відключення prepared statements, якщо потрібно).
- Розгортання моніторингу через Prometheus + Grafana.
- Документацію з архітектури та годину гарантійної підтримки.
- Навчання команди роботі з моніторингом.
Процес роботи
- Аналітика — знімаємо метрики з'єднань, вивчаємо поточну конфігурацію.
- Проєктування — розраховуємо pool_size, max_client_conn, обираємо топологію (single/HA).
- Реалізація — розгортаємо PgBouncer, правимо код застосунку.
- Тест — навантажувальне тестування за допомогою pgbench або k6.
- Деплой — підключаємо моніторинг, налаштовуємо алерти.
- Пост-реліз — спостерігаємо 48 годин, коригуємо пул при необхідності.
Приклад конфігурації pgbouncer.ini
[databases] mydb = host=127.0.0.1 port=5432 dbname=mydb user=app password=secret [pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 1000 default_pool_size = 20 min_pool_size = 5 reserve_pool_size = 5 reserve_pool_timeout = 3 server_connect_timeout = 15 server_login_retry = 15 query_timeout = 0 query_wait_timeout = 120 client_idle_timeout = 0 server_lifetime = 3600 server_idle_timeout = 600 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 stats_period = 60 admin_users = pgbouncer_admin stats_users = pgbouncer_stats Моніторинг та алерти
Підключаємося до псевдо-бази pgbouncer: SHOW POOLS; — ключовий показник cl_waiting. Якщо він постійно >0, збільшуємо default_pool_size. Для Prometheus використовуємо pgbouncer-exporter — він віддає метрики: активні з'єднання, час очікування, кількість помилок. Графана візуалізує тренди.
Як вибрати розмір пулу
Розмір пулу залежить від кількості ядер CPU та характеру запитів. Емпіричне правило: default_pool_size = 2 * (кількість ядер) + 1 для IO-bound задач. Для CPU-bound задач — не більше кількості ядер. Приклад: на сервері з 8 ядрами та IO-bound навантаженням ставимо 17 з'єднань у пулі. Якщо запити важкі (аналітичні), зменшуємо до 8. Точне значення підбирається навантажувальним тестуванням.
Порівняння конфігурацій для різних навантажень
| Тип навантаження | default_pool_size | max_client_conn | Рекомендація |
|---|---|---|---|
| Низька (до 100 RPS) | 10 | 200 | Один PgBouncer на сервері застосунку |
| Середня (до 1000 RPS) | 20 | 500 | Виділений сервер з PgBouncer |
| Висока (понад 1000 RPS) | 50 | 1000 | Кластер PgBouncer з HAProxy |
Строки та гарантії
Встановлення та налаштування PgBouncer для існуючого застосунку займає від півдня до 1 дня. Зв'яжіться з нами — оцінимо ваш проект за годину. Замовте налаштування під ключ і отримайте безкоштовну консультацію з архітектури. Ми сертифіковані інженери PostgreSQL з комерційним досвідом. Даємо гарантію на коректну роботу пулу протягом 30 днів після впровадження.