Налаштування PgBouncer (connection pooling) для веб-додатку

PgBouncer налаштування: коли пул з'єднань рятує застосунок

Застосунок відкриває з'єднання до Postgres на кожен запит і тримає його до кінця обробки. Поки користувачів десятки, це непомітно.

На піковому навантаженні кількість одночасних з'єднань упирається в max_connections, і далі починається каскад: нові запити отримують помилку підключення, черга на звільнений слот зростає, час відповіді сягає секунд.

Найгірше те, що збій приходить саме тоді, коли навантаження максимальне, — у момент розпродажу, звітної кампанії або ранкового сплеску.

Кожне з'єднання в Postgres — це окремий процес із власною пам'яттю. Тримати сотні таких процесів дорого: база витрачає ресурси на перемикання між ними замість обробки запитів, а частина з'єднань простоює в очікуванні.

Збільшення ліміту — глухий шлях: наступний пік знову упреться в стелю, тільки тепер відмова буде глибшою.

PgBouncer вирішує це інакше. Застосунок підключається до пулу, а не до бази напряму, і пул перевикористовує невеликий набір реальних з'єднань між сотнями клієнтів.

У режимі transaction з'єднання повертається в пул одразу після завершення транзакції, тому кілька десятків бекендів обслуговують тисячі одночасних запитів.

Налаштування зводиться до трьох речей: вибрати режим пулу, задати default_pool_size і max_client_conn, налаштувати таймаути простою та коректно розвести службові підключення, яким потрібні сесійні стани.

Правильно налаштований пул знімає помилки підключення при піках, вирівнює затримки та знижує навантаження на базу без переписування коду застосунку. Розбираємо, як це закривається на практиці.

Що дає правильно налаштований пул з'єднань

Пул з'єднань — це не «опція для оптимізації», а розділювач між застосунком, який тримає навантаження, і застосунком, який падає на піку.

Коли кожен запит відкриває нове з'єднання до бази, час відгуку зростає нелінійно: на 50 одночасних користувачах ще терпимо, на 300 — черга на підключення та таймаути. Нижче — що саме змінюється після налаштування PgBouncer, з прив'язкою до метрик, які видно в моніторингу.

• Час відгуку перестає залежати від кількості користувачів. З'єднання перевикористовуються з готового пулу, і застосунок не витрачає час на встановлення нового підключення та автентифікацію на кожен запит. На графіку відгук тримається рівною лінією там, де раніше йшло ступінчасте зростання.
• Відмови підключення зникають як клас. Обмеження на кількість з'єднань з боку бази більше не впирається в стелю: зайві запити чекають у черзі пулу частки секунди замість помилки. Помилки виду «too many connections» зникають з логів застосунку.
• Пікове навантаження гаситься без зростання ресурсів. Сплеск трафіку розбирається чергою пулу, а не лавиною нових підключень до бази. Сервер бази перестає йти в перевантаження по пам'яті та процесору на кожному піку.
• Стабільність під навантаженням стає передбачуваною. Поведінка застосунку при зростанні трафіку описується заздалегідь: відомо, скільки з'єднань тримаємо, скільки чекаємо, що відбувається при переповненні. Це прибирає «плаваючі» відмови, які важко відтворити.
• Продуктивність застосунку зростає без переписування коду. Пул налаштовується на рівні інфраструктури, логіка застосунку не змінюється. Ефект видно одразу після перемикання трафіку на пул.
• Ресурси сервера витрачаються рівніше. База тримає фіксовану кількість робочих процесів замість сотень простоюючих з'єднань — звільнена пам'ять іде на кеш і обробку запитів.

Кожен пункт перевіряється за метриками: час відгуку на перцентилях, кількість помилок підключення, завантаження бази в піку. Якщо хоча б один показник не зрушив — налаштування неповне, і ми це бачимо на етапі приймання, а не на проді.

Режими роботи пулу: порівняння підходів

Режим пулінгу визначає, на якому рівні PgBouncer повертає серверне з'єднання назад у пул. Від цього залежить і пропускна здатність під навантаженням, і сумісність із функціями PostgreSQL: підготовленими запитами, SET, advisory-блокуваннями, курсорами. Нижче — робочі варіанти, між якими реально обирають під час налаштування.

Для транзакційного режиму критично виставити server_reset_query у DISCARD ALL і вимкнути кеш підготовлених запитів на боці драйвера або увімкнути їхню підтримку в самому PgBouncer.

Інакше застосунок отримає помилки «prepared statement already exists» під навантаженням — класичний збій під час переходу із сесійного режиму.

Змішані сценарії вирішують розділенням пулів: окремий інстанс PgBouncer для транзакційного трафіку й окремий — для аналітики та фонових задач.

Це знімає конфлікт обмежень і дозволяє незалежно налаштовувати pool_mode, max_client_conn і default_pool_size під кожен профіль навантаження.

Як ми впроваджуємо пул з'єднань: етапи робіт

Впровадження пулу з'єднань починається не з встановлення PgBouncer, а з аудиту того, як застосунок працює з базою зараз.

Дивимося активні з'єднання під навантаженням, поведінку драйвера та ORM, тривалість транзакцій, наявність «сплячих» сесій, які тримають з'єднання відкритим годинами.

Часто вже на цьому етапі видно, що частина з'єднань витрачається марно — наприклад, фонові завдання тримають окремий пул, не перетинаючись із вебтрафіком.

Далі обираємо режим пулу та розкладаємо навантаження за групами. Транзакційний режим підходить більшості вебзастосунків, але вимагає, щоб код не покладався на стан сесії між запитами — перевіряємо це окремо, бо підготовлені вирази та тимчасові таблиці поводяться інакше.

Якщо застосунок використовує їх активно, залишаємо сесійний режим для конкретної групи й не ламаємо логіку заради економії з'єднань.

1. Аудит з'єднань. Знімаємо профіль навантаження: пікове число одночасних сесій, розподіл у часі, частка простоюючих з'єднань, найдовші транзакції.
2. Проєктування пулу. Визначаємо число груп, ліміти на кожну, режим роботи та правила маршрутизації застосунку до потрібної групи.
3. Налаштування PgBouncer. Задаємо розміри пулів, таймаути простою та утримання, ліміти на клієнта, параметри перепідключення до бази.
4. Правка конфігурації застосунку. Переводимо драйвер на порт пулу, прибираємо власний пул там, де він дублює зовнішній, інакше сенс налаштування втрачається.
5. Перевірка під навантаженням. Прогоняємо профіль, близький до бойового: дивимося, що число з'єднань до бази тримається в заданих межах, а час відповіді не зростає на піках.
6. Передача документації. Віддаємо конфігурацію, схему груп, регламент зміни лімітів та інструкцію з діагностики, щоб команда могла супроводжувати пул без нас.

Окремо перевіряємо сценарії відмови: перезапуск PgBouncer, недоступність бази, сплеск трафіку вище розрахункового. Конфігурація має переживати це без ручного втручання — з'єднання відновлюються самі, а застосунок отримує зрозумілу помилку замість завислого запиту.

Що замовник отримує на руки після налаштування

Налаштовуємо пул так, щоб його можна було експлуатувати без автора робіт: конфігурація, схема підключень і регламенти залишаються в репозиторії проєкту, а не в листуванні. Нижче — що саме передаємо.

• Конфігурація пула — робочі pgbouncer.ini і userlist.txt (паролі в хешованому вигляді) під режим transaction або session, з обґрунтуванням вибору. Параметри max_client_conn, default_pool_size, reserve_pool_size і таймаути виставлені під профіль навантаження застосунку, а не «за замовчуванням».
• Схема підключень — діаграма: застосунок → пул → PostgreSQL, із зазначенням, які сервіси йдуть через PgBouncer, а які (міграції, аналітика, LISTEN/NOTIFY) — напряму. Окремо відзначені точки відмови та поведінка при недоступності пула.
• Політики доступу — які ролі та бази видно через пул, обмеження за адресами, окремий користувач для моніторингу. Правила узгоджені з поточною моделлю прав у базі, щоб пул не став обхідним шляхом.
• Моніторинг і метрики — експорт метрик пула (зайнятість, очікування, відмови) у вашу систему спостереження, дашборд із ключовими показниками та пороги для оповіщень.
• Регламенти експлуатації — що робити при вичерпанні пула, як безпечно перезавантажити конфігурацію без розриву з'єднань, як відкотитися на пряме підключення, якщо пул деградував.
• Документація — стислий опис архітектури, інструкція зі зміни параметрів і чек-ліст перевірки після релізу. Розрахована на чергового інженера, а не на того, хто впроваджував.
• План перевірки — сценарії навантаження, якими підтверджували результат, і зафіксовані значення до і після: число з'єднань до бази, час відгуку під піком.

Усе це передаємо у ваш репозиторій і систему документації, з розбором на дзвінку. Опишіть задачу — повернемося з оцінкою та планом.

Кейс: як пул з'єднань прибрав відмови при піковому навантаженні

Клієнт — сервіс з особистим кабінетом і оплатами — приходив з класичною картиною: вдень застосунок тримав відгук у нормі, але в години пік, коли збігалися розсилка й наплив користувачів, частина запитів падала з помилкою підключення до бази.

Логи показували, що процес застосунку відкривав з'єднання на кожен запит і тримав його до кінця транзакції, а база впиралася в ліміт одночасних підключень.

Кожне нове з'єднання — це окремий процес на боці бази, і при стрибку їхня кількість зростала швидше, ніж база встигала їх обслуговувати.

PgBouncer поставили між застосунком і базою в режимі транзакційного пулу: з'єднання повертається в пул одразу після завершення транзакції, а не висить до закриття сесії.

Кількість реальних підключень до бази зафіксували на рівні, який вона тримає спокійно, — решту застосунок отримує з пулу миттєво. Окремо налаштували таймаути простою й ліміт на клієнта, щоб одне «залипле» з'єднання не виїдало пул.

Метрики до і після

Важливий ефект — передбачуваність: база перестала бути вузьким місцем, і пікове навантаження тепер тримає пул, а не запас за ресурсами сервера.

Якщо у вас відгук просідає саме в години пік і в логах видно відмови підключення — опишіть задачу, повернемося з оцінкою та планом налаштування під ваш профіль навантаження.

Чим транзакційний режим відрізняється від сесійного

Сесійний режим закріплює серверне з'єднання за клієнтом на весь час сесії: поки застосунок тримає підключення, він монопольно займає один бекенд PostgreSQL.

Це єдиний режим, де працюють усі можливості сервера — підготовлені вирази на рівні сесії, тимчасові таблиці, SET і LISTEN/NOTIFY, advisory-блокування, курсори поза транзакцією.

Плата — масштабування: кожне з'єднання застосунку потребує окремого процесу в базі, і за сотень одночасних користувачів PostgreSQL упирається в пам'ять і перемикання контексту задовго до вичерпання процесора.

Транзакційний режим віддає серверне з'єднання клієнту лише на час транзакції й повертає його в пул одразу після COMMIT або ROLLBACK. Один бекенд обслуговує десятки клієнтів послідовно, тому пул на сотні застосунків обходиться парою десятків реальних з'єднань до бази.

Це режим за замовчуванням для вебзастосунків: типовий запит живе мілісекунди, а пауза між запитами одного користувача може тривати секунди — тримати під неї з'єднання марнотратно.

Обмеження транзакційного режиму випливають із того, що клієнт не володіє бекендом між транзакціями. Стан, прив'язаний до сесії, втрачається: підготовлені вирази, тимчасові таблиці, зміни search_path і параметрів через SET, сесійні advisory-блокування.

Курсори з WITH HOLD і LISTEN/NOTIFY теж не переносяться. Обхідні шляхи — SET LOCAL усередині транзакції замість SET, іменовані підготовлені вирази з явним керуванням, винесення фонових задач на окремий пул у сесійному режимі.

Вибір режиму — це вибір між сумісністю та щільністю. Якщо застосунок використовує сесійний стан або працює через драйвер із неявними підготовленими виразами, починають із сесійного режиму й поступово переводять гарячі шляхи на транзакційний.

Змішана схема нормальна: два пули на різні порти, транзакційний для вебтрафіку, сесійний для міграцій, аналітики та фонових обробників. Обмеження за кількістю з'єднань задається на боці пула, а не застосунку, — інакше сенс пулінгу втрачається.

Технічні сумніви перед впровадженням пулу

Перед увімкненням пулу майже кожна команда проходить один і той самий список сумнівів: «чи не зламає це ORM», «чи не поїдуть транзакції», «чи пройдуть міграції», «що буде з підготовленими запитами». Нижче — розбір за пунктами, без загальних слів.

Транзакції та блокування не втратять цілісність?

У transaction pooling всі оператори однієї транзакції зобов'язані йти через одне з'єднання — пул це гарантує і не віддає його іншим клієнтам до COMMIT або ROLLBACK.

Проблема виникає з SET, тимчасовими таблицями та advisory-блокуваннями рівня сесії: вони не переживають повернення з'єднання. Такі місця виносимо в session pooling точково або переписуємо на транзакційний рівень блокування.

Міграції та фонові задачі — окремий пул?

Міграції, pg_dump, аналітичні вивантаження та тривалі фонові задачі тримають з'єднання хвилинами. Якщо пустити їх через спільний пул, вони виберуть весь ліміт і покладуть веб-трафік.

Тому під них піднімаємо окремий пул з малою кількістю з'єднань і окремим користувачем — веб-застосунок і службові процеси перестають конкурувати за один і той самий набір.

Як відкотитися, якщо щось піде не так?

Пул — це проксі між застосунком і базою, схему даних він не чіпає. Відкат — перемикання рядка підключення назад на пряму адресу бази; займає хвилини і не потребує міграцій.

Тому впровадження безпечно робити поетапно: спочатку один сервіс, замір затримок і кількості з'єднань, потім решта.

Усі чотири побоювання закриваються на етапі проєктування — до того, як пул потрапить у робочий контур.

Обговоримо ваше завдання і повернемося з оцінкою

Щоб повернутися з оцінкою та планом робіт, нам потрібен мінімум вхідних даних: поточна конфігурація PostgreSQL і PgBouncer (режим пулу, ліміти з'єднань, таймаути), характер навантаження — скільки одночасних користувачів, чи є довгі транзакції та важкі звіти, як застосунок утримує з'єднання (пул на боці застосунку чи нове з'єднання на запит). Цього достатньо, щоб зрозуміти вузьке місце і запропонувати режим пулу без здогадок.

Далі працюємо за кроками:

• розбираємо поточні метрики: кількість активних і простоюючих з'єднань, пікові черги, відмови за таймаутом;
• визначаємо цільовий режим пулу та ліміти під профіль навантаження, а не «за замовчуванням»;
• перевіряємо сумісність із драйвером і ORM: prepared statements, транзакції, сесії;
• готуємо конфігурацію та схему перемикання без простою — через другий екземпляр і переведення трафіку;
• проганяємо навантажувальний тест на копії бойової бази і знімаємо метрики до/після;
• викочуємо на прод із планом відкату та спостережуваністю за з'єднаннями і затримками;
• фіксуємо підсумкові параметри і передаємо документацію вашій команді.

Що потрібно від вас для старту: доступ до конфігурації та метрик, вікно на тестовий прогін і контакт інженера, який знає, як застосунок відкриває з'єднання. Решта — наша зона: конфігурація, схема перемикання, тести і відкат.

Опишіть завдання — повернемося з оцінкою, планом робіт і зрозумілими наступними кроками.