Налаштування PgBouncer для веб-застосунку: пулінг з'єднань

Проблема: сотні з'єднань до 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. Вибір режиму пулінгу критичний:

Для сучасних фреймворків ми використовуємо 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.
• Документацію з архітектури та годину гарантійної підтримки.
• Навчання команди роботі з моніторингом.

Процес роботи

1. Аналітика — знімаємо метрики з'єднань, вивчаємо поточну конфігурацію.
2. Проєктування — розраховуємо pool_size, max_client_conn, обираємо топологію (single/HA).
3. Реалізація — розгортаємо PgBouncer, правимо код застосунку.
4. Тест — навантажувальне тестування за допомогою pgbench або k6.
5. Деплой — підключаємо моніторинг, налаштовуємо алерти.
6. Пост-реліз — спостерігаємо 48 годин, коригуємо пул при необхідності.

[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. Точне значення підбирається навантажувальним тестуванням.

Порівняння конфігурацій для різних навантажень

Строки та гарантії

Встановлення та налаштування PgBouncer для існуючого застосунку займає від півдня до 1 дня. Зв'яжіться з нами — оцінимо ваш проект за годину. Замовте налаштування під ключ і отримайте безкоштовну консультацію з архітектури. Ми сертифіковані інженери PostgreSQL з комерційним досвідом. Даємо гарантію на коректну роботу пулу протягом 30 днів після впровадження.

PgBouncer

Послуги бекенд-розробки: production-grade надійність

На production-сервері о 3:14 ночі черга Laravel Jobs перестала оброблятися — 40 000 необроблених завдань у Redis. Причина: worker упав через memory leak у статичній змінній Eloquent observer, supervisor не перезапустив через misconfigured stopwaitsecs. Ми розбирали такий інцидент на проекті з 500 RPS: діагностика 4 години, фікс — 20 хвилин. Щоб ви не втрачали гроші, пропонуємо послуги бекенд-розробки з акцентом на production-grade надійність — 10+ років досвіду, 50+ проектів, 5 років на ринку. Оцінимо ваш проект за 2 дні.

Які проблеми вирішуємо

N+1 запити: головний вбивця швидкості

N+1 — найпоширеніша причина повільних сторінок у Laravel-додатках. Стандартна історія: сторінка працювала нормально на dev з 10 записами, на production з 10 000 — 8-секундне завантаження.

Laravel Debugbar у dev-оточенні показує кількість запитів. Більше 20 — сигнал для audit.

Model::preventLazyLoading(! app()->isProduction());

Telescope для профілювання: логує всі запити, jobs, mail, notifications з деталізацією. Після впровадження eager loading час завантаження сторінки падає з 8 с до 0.3 с — у 27 разів.

Memory leak у статичних змінних

У Laravel Octane або Swoole додаток тримається в пам’яті між запитами. Статичні змінні не скидаються — призводять до неконтрольованого росту пам’яті. Використовуємо defer-функції та контейнерні біндинги для коректного скидання стану.

Неправильний connection pool

Rails, Laravel, Django відкривають нове з'єднання PostgreSQL на кожен PHP/Python процес. 100 воркерів — 100 з'єднань. PostgreSQL деградує від 200+ активних з'єднань через overhead на управління.

PgBouncer у transaction pooling: 1000 воркерів → 20–50 реальних з'єднань. Це знижує latency на 40% та зменшує витрати на хостинг на 30% — при середній вартості хостингу $2,000/міс економить $600/міс. GIN-індекс для JSONB до 100 разів швидший за B-tree при пошуку.

Як Octane справляється з високим навантаженням?

Laravel Octane (RoadRunner або Swoole) прибирає overhead bootstrap на кожен HTTP-запит. Приріст: 3–8x на синтетичних бенчмарках, 2–4x на реальних додатках. Важливо: не зберігати стан у статичних змінних — застосовуємо це на проектах >1000 RPS.

Як PostgreSQL допомагає уникнути повільних запитів?

Використовуємо composite indexes для WHERE + ORDER BY, partial indexes для фільтрів з високою селективністю, GIN-індекси для JSONB та full-text search. to_tsvector + GIN замість LIKE '%query%' — запобігає seq scan навіть на мільйонах записів. Аналізуємо плани через EXPLAIN ANALYZE та pg_stat_statements.

Як обрати стек для вашого проекту?

Node.js виправданий для realtime: Laravel публікує події в Redis Pub/Sub, Node.js підписується та транслює клієнтам. Go — для goroutines (10k з'єднань на сервер — норма), але розробка повільніша, ніж Laravel.

Чому Redis критичний для продуктивності?

Redis виконує кілька ролей:

Redis Cluster для горизонтального масштабування, Sentinel для автоматичного failover. Замовте консультацію щодо оптимізації Redis для вашого проекту.

Що входить в роботу під ключ

• Архітектурне проектування (документація API, схема БД, діаграма сервісів)
• Реалізація за узгодженим ТЗ з code review
• Налаштування CI/CD (GitHub Actions, Docker), моніторингу (Sentry, Grafana), алертингу
• Навантажувальне тестування (k6, wrk) зі звітом
• Передача вихідних кодів, доступів, інструкція з деплою
• Навчання команди замовника (2–3 сесії)
• Гарантійна підтримка 1 місяць після здачі

Орієнтири по термінах

Вартість розраховується індивідуально після аналізу вимог до навантаження, інтеграцій та бізнес-логіки. Зв'яжіться з нами для безкоштовного аудиту вашого поточного backend — отримайте план оптимізації за 2 дні. Замовте консультацію та дізнайтеся, як знизити витрати на інфраструктуру на 30% без втрати продуктивності.