# Пул з'єднань ## Що це таке Пул з'єднань — це набір відкритих з'єднань із базою даних, які застосунок тримає й позичає знову, замість того щоб відкривати нове з'єднання для кожної дії та закривати його після неї. Його ще називають пулом підключень. Код бере з'єднання з пулу, виконує на ньому свої інструкції та повертає його, і наступний, кому воно потрібне, отримує те саме з'єднання, уже відкрите. Документація SQLAlchemy описує пулінг як стандартний прийом, що тримає довгоживучі з'єднання в пам'яті для ефективного повторного використання. Та сама сторінка дає пулу ще одну роль: він керує тим, скільки з'єднань застосунок може використовувати одночасно, тож раптовий сплеск трафіку чекає на вільне з'єднання, а не відкриває нові. Пул може жити у двох місцях. Усередині застосунку він є частиною бібліотеки для роботи з базою: кожен `Engine` у SQLAlchemy має власний пул, а Django може тримати по одному в кожному процесі для PostgreSQL. Поза застосунком це окрема програма, що стоїть між клієнтами й сервером бази даних. PgBouncer — саме такий пулер для PostgreSQL: застосунки під'єднуються до нього так, ніби це база даних, а він позичає їм серверні з'єднання з власних пулів. Ці два варіанти можна поєднувати, і в довіднику Django про бази даних є розділ про роботу за транзакційним пулером на кшталт PgBouncer. Чому з'єднання варто зберігати, видно на боці сервера. PostgreSQL дає кожному клієнтові окремий процес: головний процес, postmaster, слухає вхідні з'єднання й запускає новий серверний процес на кожен запит на з'єднання, і кожен клієнт під'єднаний рівно до одного з цих процесів. Отже, відкрити з'єднання означає запустити процес на сервері бази даних, а пул платить цю ціну один раз і далі використовує результат повторно. ## Чому це важливо Перша причина — ціна під'єднання. Документація Django каже про це прямо: постійне з'єднання позбавляє від витрат на встановлення нового в кожному HTTP-запиті. Проте типове налаштування робить навпаки. `CONN_MAX_AGE` дорівнює 0, і з'єднання закривається наприкінці кожного запиту; додатне число секунд тримає його відкритим для повторного використання, а `None` — без обмеження. Це найпростіша форма повторного використання, одне з'єднання на потік, яке живе довше за запит, а пул — її загальна форма. Друга причина — межа на сервері. Типове значення `max_connections` у PostgreSQL зазвичай становить 100, може бути меншим, якщо налаштування ядра цього не витримують, і змінюється лише під час запуску сервера. Три місця з них типово зарезервовано для суперкористувачів. Підняти це число теж не безкоштовно: PostgreSQL визначає розмір деяких ресурсів, зокрема спільної пам'яті, безпосередньо з `max_connections`. Без пулу кожен робочий процес тримає власне з'єднання, і документація Django пояснює наслідок: оскільки кожен потік має своє з'єднання, база даних мусить приймати щонайменше стільки одночасних з'єднань, скільки є робочих потоків. Пул перетворює це на число, яке команда обирає сама й може скласти. Третя причина — гігієна. Пул — це єдине місце, через яке кожне з'єднання проходить між двома позичальниками, тож саме тут його прибирають і перевіряють. Коли з'єднання повертається, пул SQLAlchemy викликає на ньому `rollback()`, і це звільняє весь транзакційний стан разом із блокуваннями, тож наступний позичальник не успадкує недороблену транзакцію. PgBouncer надсилає свій `server_reset_query`, типово `DISCARD ALL`, коли звільняє серверне з'єднання й перш ніж його отримає інший клієнт. А ще пул може перевірити з'єднання, перш ніж його позичити, і це важливо для з'єднань, які довго простоювали. ## Як це виглядає на практиці У SQLAlchemy 2.1 кожен `Engine` типово отримує `QueuePool`, окрім бази даних SQLite у пам'яті. Його `pool_size` дорівнює 5, а `max_overflow` — 10, тож один engine може мати до 15 з'єднань одночасно, а виклик, який застає їх усі зайнятими, чекає `pool_timeout`, типово 30 секунд, і лише тоді здається. Ці параметри передають як іменовані аргументи до `create_engine()`: ```python from sqlalchemy import create_engine # Щонайбільше десять з'єднань на цей engine: усі відкриті, понад них жодного. engine = create_engine( "postgresql+psycopg2://me@localhost/mydb", pool_size=10, max_overflow=0, pool_timeout=10, # чекати на вільне з'єднання десять секунд, далі здатися pool_pre_ping=True, # перевіряти кожне з'єднання під час видачі pool_recycle=3600, # замінювати з'єднання, старші за годину ) ``` Із `pool_pre_ping` пул під час видачі перевіряє, що з'єднання ще придатне, через власний ping драйвера або інструкцію, рівнозначну `SELECT 1`. `pool_recycle` призначений для серверів, які закривають з'єднання, що певний час не використовувалися: зі значенням 3600 кожне з'єднання, відкрите понад годину, замінюється під час наступної видачі. Django 6.1 має власний пул для PostgreSQL. Він працює лише з драйвером psycopg, потребує пакета `psycopg[pool]`, а вмикають його через `"OPTIONS": {"pool": True}` у налаштуваннях бази даних або словником параметрів для `ConnectionPool` із psycopg. Пул повторно використовує з'єднання між потоками, і кожен процес має окремий пул для кожного псевдоніма бази даних. `CONN_MAX_AGE` має лишатися 0, коли пул увімкнено; час життя з'єднання в пулі натомість задає параметр пулу `max_lifetime`. Для ASGI документація Django радить вимкнути постійні з'єднання й користуватися цим вбудованим пулом. Параметр PgBouncer `pool_mode` визначає, коли серверне з'єднання може перейти до іншого клієнта. У режимі `session`, типовому, клієнт тримає з'єднання, доки не від'єднається. У режимі `transaction` клієнт має серверне з'єднання лише поки триває транзакція, тож кілька десятків серверних з'єднань можуть обслуговувати значно більше клієнтів. Режим `statement` звільняє з'єднання після кожного запиту й не дозволяє транзакцій із кількох інструкцій. Кожен пул вміщує до `default_pool_size` серверних з'єднань на пару користувача й бази даних, типово 20, а PgBouncer приймає до `max_client_conn` клієнтських з'єднань, типово 100. Власні нотатки компанії ставлять пул на ранній крок. [Оптимізуйте базу, перш ніж її шардувати](/uk/notes/optimise-before-you-shard/) пропонує порахувати активні сесії в `pg_stat_activity` і порівняти з кількістю ядер CPU: коли активних у багато разів більше, ніж ядер, сервер витрачає час на перемикання між ними, і пул з'єднань виправить це раніше, ніж будь-яке залізо. [Продати останній квиток рівно один раз](/uk/notes/selling-limited-stock-without-overselling/) застосовує ту саму думку до флеш-розпродажу: сто тисяч покупців мають перетворитися на кілька десятків з'єднань через PgBouncer, а не на тисячі серверних процесів, що чекають на одне блокування. ## Де виникають проблеми **Пули разом просять більше з'єднань, ніж дозволяє сервер.** Кожен пул рахує лише власні з'єднання. За типових значень SQLAlchemy один engine може дійти до 15, тож дванадцять процесів можуть попросити 180 у сервера, де `max_connections` зазвичай типово дорівнює 100, а три з них зарезервовано для суперкористувачів; до того ж Django має окремий пул для кожного псевдоніма бази даних у кожному процесі. Складіть максимум кожного пулу, що дивиться на цю базу даних, по всіх процесах усіх застосунків і всіх запланованих завдань, і тримайте суму нижче за `max_connections` мінус зарезервовані місця. Якщо не вміщається, поставте попереду PgBouncer і дайте йому тримати кількість серверних з'єднань. **З'єднання вмирає, поки чекає в пулі.** З'єднання, яке простоювало, можуть закрити з іншого боку так, що пул про це не знає, і наступний позичальник тоді падає на першій же інструкції. Увімкніть `pool_pre_ping` у SQLAlchemy або `CONN_HEALTH_CHECKS` у Django, яке перевіряє повторно використане з'єднання раз на запит, і задайте `pool_recycle` або `max_lifetime` у psycopg меншим за час, після якого сервер закриває невикористовувані з'єднання. Жоден із цих засобів не рятує з'єднання, що обривається посеред транзакції: документація SQLAlchemy прямо каже, що pre-ping цього випадку не обробляє, тож застосунок мусить сам повторити роботу. **Робочий процес, створений через fork, успадковує з'єднання батьківського.** Вебсервер або обробник завдань, що створює свій engine, а потім запускає робочі процеси через fork, залишає кожному дочірньому процесу ті самі з'єднання з пулу, що й у батьківського. Документація SQLAlchemy називає критично важливим не ділити з'єднання з пулу з процесом, створеним через fork, бо обидва процеси тоді говоритимуть через одне й те саме мережеве з'єднання. Дотримуйтеся рекомендованого підходу й викликайте `engine.dispose(close=False)` в ініціалізаторі кожного дочірнього процесу, щоб він починав із порожнього пулу й не чіпав з'єднань батьківського. Альтернативи — `NullPool`, який відкриває й закриває з'єднання для кожного використання, або виклик `engine.dispose()` перед fork. **Транзакційний пулінг ламає стан сесії.** У режимі `transaction` у PgBouncer дві транзакції одного клієнта можуть виконуватися на різних серверних з'єднаннях, тож усе, що клієнт налаштовує для своєї сесії, не обов'язково буде на місці в його наступній транзакції. Перелік можливостей PgBouncer позначає `SET`, `LISTEN`, курсори, оголошені з `WITH HOLD`, `PREPARE` на рівні SQL і рекомендаційні блокування на рівні сесії як ніколи не підтримувані в цьому режимі й відверто визнає, що режим за своєю природою порушує деякі припущення клієнтів щодо сервера. Тримайте стан сесії поза кодом, що йде через транзакційний пулер, а клієнтів, яким він потрібен, спрямовуйте до пулу в режимі `session`. У Django для бази даних за транзакційним пулером встановіть `DISABLE_SERVER_SIDE_CURSORS` у `True` або виконуйте ці запити всередині `atomic()`. ## Що почитати далі - [Connection Pooling](https://docs.sqlalchemy.org/en/21/core/pooling.html) — `QueuePool` і його типові значення, обробка розірваних з'єднань, скидання під час повернення та пули після fork у SQLAlchemy 2.1. - [PgBouncer features](https://www.pgbouncer.org/features.html) — режими session, transaction і statement та можливості PostgreSQL, які підтримує кожен із них. - [PgBouncer configuration](https://www.pgbouncer.org/config.html) — `pool_mode`, `default_pool_size`, `max_client_conn` і тайм-аути з їхніми типовими значеннями. - [How Connections Are Established](https://www.postgresql.org/docs/current/connect-estab.html) — модель PostgreSQL з окремим процесом на кожного користувача і процес postmaster. - [Connections and Authentication](https://www.postgresql.org/docs/current/runtime-config-connection.html) — `max_connections` і місця для з'єднань, зарезервовані для суперкористувачів та інших ролей. - [Databases](https://docs.djangoproject.com/en/stable/ref/databases/) — постійні з'єднання, пул для PostgreSQL і транзакційний пулінг у довіднику Django.