Пулы соединений и масштабирование чтения

Пул соединений переиспользует ограниченный набор подключений к PostgreSQL вместо того, чтобы открывать новое на каждый запрос.

Пул соединений (connection pool) — посредник, который держит небольшой набор постоянных соединений с базой и раздаёт их приложению по очереди, скрывая дороговизну установки нового подключения.

В PostgreSQL каждое клиентское соединение — это отдельный процесс операционной системы на сервере, со своей памятью и накладными расходами. Сотня соединений — сотня процессов; тысяча — тысяча, и сервер начинает захлёбываться от переключений контекста и расхода памяти, даже если реальной работы немного. Поэтому веб-приложение, открывающее новое соединение на каждый HTTP-запрос, быстро упирается в потолок. Решение — пул: фиксированный набор соединений, который обслуживает всех.

Этот урок продолжает тему масштабирования: репликация дала нам реплики для чтения, а пулинг и грамотная маршрутизация запросов позволяют выжать из них максимум, не утопив primary в соединениях.

Зачем это на практике

Без пула типична картина: приложение настроено на max_connections = 500, под нагрузкой все 500 заняты, сервер тратит память и CPU на процессы, которые большую часть времени простаивают между запросами. С пулом тех же пользователей обслуживают, скажем, 20–40 реальных соединений к базе, а остальные ждут своей очереди миллисекунды. Выигрыш:

Меньше процессов на сервере БД. PostgreSQL стабильно работает с десятками активных соединений, а не сотнями простаивающих.
Нет цены установки соединения на каждый запрос. Аутентификация, форк процесса, инициализация — всё это происходит один раз, а не тысячи раз в секунду.
Защита от перегрузки. Пул ограничивает число одновременных запросов к базе, сглаживая всплески.

Почему соединения дорогие именно в PostgreSQL

В отличие от СУБД с моделью «поток на соединение», PostgreSQL форкает на каждое подключение полноценный процесс backend. У процесса своя память под кэш каталога, подготовленные планы, буферы. Установка соединения включает TCP-рукопожатие, аутентификацию (например, scram-sha-256) и инициализацию backend-процесса — это миллисекунды, которые на тысячах подключений в секунду превращаются в заметную нагрузку. А каждое простаивающее, но открытое соединение всё равно держит процесс и память. Отсюда правило: держать соединений ровно столько, сколько реально нужно для параллельной работы, а не «про запас».

PgBouncer: лёгкий пулер

PgBouncer — самый распространённый внешний пул для PostgreSQL: крошечный демон, который встаёт между приложением и базой. Приложение подключается к PgBouncer (как будто к обычному PostgreSQL), а он мультиплексирует множество клиентских соединений на небольшой набор реальных серверных.

; pgbouncer.ini
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
listen_port = 6432
pool_mode = transaction         ; режим пулинга (см. ниже)
max_client_conn = 1000          ; сколько клиентов могут подключиться к pgbouncer
default_pool_size = 20          ; реальных соединений к базе на одну БД/пользователя

Здесь 1000 клиентских соединений обслуживаются всего 20 серверными — приложение «думает», что у него тысяча подключений, а PostgreSQL видит двадцать. Запускается PgBouncer как обычный сервис:

# запустить pgbouncer с конфигом
pgbouncer -d /etc/pgbouncer/pgbouncer.ini

# приложение теперь ходит на порт 6432 вместо 5432
psql -h 127.0.0.1 -p 6432 -U app_user shop

Режимы пулинга

Главная настройка PgBouncer — pool_mode: когда серверное соединение возвращается в пул для повторного использования.

Режим	Соединение занято на время	Особенности
`session`	всей клиентской сессии	безопаснее всего, но экономия минимальна
`transaction`	одной транзакции	лучший баланс — рекомендуется для веб-приложений
`statement`	одного запроса	максимальная экономия, запрещает многозапросные транзакции

Transaction-режим — золотая середина: серверное соединение закрепляется за клиентом только на время транзакции и сразу после COMMIT возвращается в пул. Это даёт огромную экономию, но накладывает ограничение: нельзя полагаться на состояние, привязанное к соединению между транзакциями. В частности, не работают сессионные конструкции — серверные подготовленные операторы (PREPARE), SET на уровне сессии, LISTEN/NOTIFY, временные таблицы, привязанные к сессии: следующая транзакция может уйти на другое серверное соединение.

Масштабирование чтения через read replicas

Пул экономит соединения, но не увеличивает суммарную мощность чтения — для этого нужны реплики для чтения (read replicas) из предыдущего урока. Идея проста: записи (INSERT/UPDATE/DELETE) идут на primary, а чтения (SELECT) распределяются по нескольким hot standby. Добавили реплику — добавили мощности чтения.

Приложение
   |-- запись (INSERT/UPDATE/DELETE) --> PRIMARY
   |
   |-- чтение (SELECT) --> PgBouncer --> REPLICA 1
                                    \--> REPLICA 2

Маршрутизацию «запись на primary, чтение на реплику» обычно реализуют на уровне приложения (две настройки подключения) или через прокси. Важный нюанс — лаг репликации: реплика отстаёт от primary на доли секунды, поэтому сразу после записи на primary чтение с реплики может вернуть ещё старые данные. Для сценариев «записал и тут же показал пользователю» такое чтение направляют на primary, остальное — на реплики.

Как это работает под капотом

PgBouncer — однопоточный процесс на событийном цикле (epoll/kqueue): он не форкает процесс на клиента, а обрабатывает тысячи сокетов в одном потоке, поэтому сам почти ничего не стоит. Когда клиент шлёт запрос, PgBouncer берёт из пула свободное серверное соединение, проксирует через него байты протокола PostgreSQL и в нужный момент (конец транзакции в transaction-режиме) возвращает соединение в пул. Поскольку PgBouncer говорит на родном протоколе PostgreSQL, приложению он неотличим от настоящего сервера — менять код не нужно, только адрес и порт.

Именно из-за проксирования протокола transaction-режим конфликтует с сессионным состоянием: PgBouncer не отслеживает, что вы сделали SET или PREPARE, и спокойно отдаст соединение другому клиенту, у которого этого состояния не будет. Драйверы умеют это обходить (например, отключают серверный prepared-statement-кэш в transaction-режиме), но об ограничении нужно знать.

Частые ошибки

Раздувать max_connections вместо пула. Поднять лимит до 1000 «чтобы хватило» — прямой путь к перегрузке сервера простаивающими процессами. Правильно — небольшой max_connections плюс пул.
Серверные prepared statements в transaction-режиме. Подготовленный на одном соединении оператор не найдётся на другом — типичная ошибка «prepared statement does not exist» под пулером.
Сессионные SET и LISTEN/NOTIFY за transaction-пулом. Они привязаны к соединению и теряются при возврате в пул; для них нужен session-режим или прямое подключение.
Игнорировать лаг при чтении с реплики. «Записал на primary — прочитал с реплики» сразу после записи может вернуть устаревшие данные; такие чтения направляйте на primary.
Слишком большой default_pool_size. Смысл пула — ограничить число серверных соединений; если поставить его равным числу клиентов, пул перестаёт защищать базу.

Итоги

В PostgreSQL каждое соединение — отдельный процесс с памятью, поэтому сотни простаивающих подключений перегружают сервер.
Пул соединений (PgBouncer) мультиплексирует множество клиентов на небольшой набор серверных соединений.
Режимы пулинга: session (безопасно), transaction (оптимально для веба), statement (максимум экономии с ограничениями).
Transaction-режим несовместим с сессионным состоянием — серверными prepared statements, SET, LISTEN/NOTIFY, сессионными временными таблицами.
Масштабирование чтения — это read replicas: запись на primary, SELECT на реплики, с поправкой на лаг репликации.