PgBouncer в режиме транзакций и асинхронный SQLAlchemy: рабочая конфигурация, которой не хватает в документации¶

Конфигурация SQLAlchemy прекрасно работает напрямую с PostgreSQL. Затем перед БД ставят PgBouncer в режиме транзакций, ради которого обычно и нужен пулер, и прежние предположения перестают быть верными. Настройки сессии перетекают между запросами. Подготовленные запросы исчезают или конфликтуют. Схема, заданная при подключении, молча не применяется. Модульные тесты этого не видят: они обращаются к PostgreSQL напрямую. Измерим, что забирает transaction pooling, какие привычные исправления ещё нужны современному PgBouncer, какая настройка действительно ломает соединение и какую конфигурацию стоит собрать.

Все измерения выполнены с PostgreSQL 17 и PgBouncer 1.25.2 в контейнерах через скрипты статьи. Версии: SQLAlchemy 2.0.52, asyncpg 0.31.0, sqlalchemy-foundation-kit 0.3.0, Python 3.13.

Что забирает режим транзакций¶

В режиме session PgBouncer закрепляет серверное соединение за клиентским на весь срок его жизни, почти не помогая большому числу одновременно подключённых клиентов. В режиме transaction оно назначается лишь на одну транзакцию и возвращается в пул на COMMIT. Тысяча соединений приложения может делить двадцать серверных. Цена — всё состояние PostgreSQL на уровне сессии, потому что сессия больше не ваша:

SET вне транзакции меняет серверное соединение; следующий клиент наследует настройку.
Именованные подготовленные запросы живут в серверном соединении; следующая транзакция клиента может попасть в другое.
Advisory lock уровня сессии, LISTEN, временные таблицы и курсоры между транзакциями принадлежат соединению, которое может больше не достаться клиенту.
Стартовые параметры подключения PgBouncer передаёт только в пределах поддерживаемого им отслеживания.

Первый пункт легко показать новичку. Один клиент выполняет обычный SET вне транзакции и возвращает серверное соединение в пул размером один. Второй спрашивает:

client A ran SET search_path TO leaked; client B sees search_path = 'leaked'

Клиент B ничего не настраивал, но теперь читает и пишет в незнакомой схеме. Ошибка воспроизводится только у запросов, попавших в это соединение. SET LOCAL внутри транзакции заканчивается вместе с ней и безопасен; обычный SET через transaction pooler создаёт непредсказуемую область воздействия.

ИДЕЯ В СХЕМЕСерверное соединение закреплено за транзакцией

---
config:
  theme: default
  look: classic
  sequence:
    useMaxWidth: false
    wrap: true
    width: 140
    actorMargin: 36
    mirrorActors: false
---
sequenceDiagram
    accTitle: Серверное соединение закреплено за транзакцией
    accDescr: После COMMIT PgBouncer может выдать другое серверное соединение. Состояние сессии из предыдущей транзакции нельзя считать гарантией для следующей.
 participant S as SQLAlchemy
 participant P as PgBouncer
 participant D as PostgreSQL
 S->>P: BEGIN + query
 P->>D: Использовать серверное соединение A
 S->>P: COMMIT
 P->>D: COMMIT
 Note over P,D: Соединение A возвращается в пул
 S->>P: BEGIN + query
 P->>D: Может использовать соединение B
 Note over S,D: Не полагаться на прежний сессионный SET

После COMMIT PgBouncer может выдать другое серверное соединение. Состояние сессии из предыдущей транзакции нельзя считать гарантией для следующей.

Ошибка prepared statement и когда она исчезла¶

Знакомая по обсуждениям asyncpg с PgBouncer ошибка выглядит так:

asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_333__" does not exist

Asyncpg готовит запросы по имени и кеширует имена на соединение. Через transaction pooler следующая транзакция может попасть в серверное соединение, не видевшее этого имени. Привычные исправления — отключить кеш драйвера и давать уникальные имена, чтобы клиенты не конфликтовали. Вот двадцать клиентов, каждый дважды выполняет двадцать разных запросов со стандартными настройками драйвера:

plain SQLAlchemy + asyncpg, driver defaults (statement cache on)
  direct to PostgreSQL                                                   ok=800  errors=none
  PgBouncer transaction mode, defaults (max_prepared_statements=200)     ok=800  errors=none
  PgBouncer transaction mode, max_prepared_statements=0 (pre-1.22)       ok=484  errors={'DBAPIError': 316}
      first error: prepared statement "__asyncpg_stmt_333__" does not exist

Перечитайте среднюю строку. На современном PgBouncer без специальных клиентских настроек классическая ошибка не возникает. Поддержка отслеживания подготовленных запросов протокола появилась в 1.21, затем стала включаться по умолчанию в последующих версиях: пулер знает, где какой запрос подготовлен, и при необходимости готовит его заново. Третья строка — та же нагрузка с принудительно отключённой поддержкой, то есть условия старых руководств: около сорока процентов ошибок.

Итак, привычные советы верны лишь для части конфигураций. Если PgBouncer старый или max_prepared_statements = 0, нужно отдельно учитывать кеширование и уникальность имён. В актуальной конфигурации дополнительные настройки дают небольшой запас совместимости ценой обмена по сети. Но важнейшая настройка была другой.

Настройка, которая действительно ломает подключение¶

Она выглядит безобидно: стартовый параметр. Asyncpg позволяет передать server_settings при подключении. Естественно указать jit=off, поскольку JIT способен давать задержки на коротких запросах, или search_path=app, если таблицы в отдельной схеме. Оба приходят в PgBouncer как параметры старта. Незнакомый отслеживанию параметр он не просто пропускает: он отклоняет соединение:

sqlalchemy-foundation-kit 0.2.1, its "pgbouncer-safe" settings
  PgBouncer transaction mode, defaults     ok=0    errors={'ProtocolViolationError': 800}
      first error: unsupported startup parameter: jit

Ноль подключений. Так работала предыдущая версия моей библиотеки с настройками, которые её же документация требовала для PgBouncer. Я обнаружил это, построив таблицу измерений. Администраторский обход ignore_startup_parameters = jit,search_path разрешает подключение и делает ровно то, что обещает:

                                                        jit    search_path
  direct to PostgreSQL                                  off    app
  PgBouncer, ignore_startup_parameters=jit,search_path  on     "$user", public

Параметры игнорируются. JIT включён, схема стандартная, запросы идут в public. Библиотека приняла настройку, драйвер отправил, БД не применила — и нигде нет ошибки. Худшая конфигурация: работает в окружении без пулера.

Вывод: настройки сессии нельзя бездумно задавать клиентом через transaction pooler. Их нужно установить там, где создаётся сессия, например ALTER ROLE app SET search_path = app и ALTER ROLE app SET jit = off; либо настроить поддерживаемое отслеживание PgBouncer; либо применять первым запросом каждой транзакции через SET LOCAL. Последний вариант контролирует приложение. Так делает исправленная библиотека:

sqlalchemy-foundation-kit 0.3.0
  PgBouncer transaction mode, defaults     ok=800  errors=none
  jit_probe through PgBouncer              jit='on'  search_path='app'

jit больше не отправляется без явного запроса. db_schema применяется через set_config('search_path', ..., true) по событию engine в начале каждой транзакции: внутри уже закреплённого PgBouncer соединения и только до её конца. Проверка показывает нужную схему через пулер и JIT согласно серверной настройке.

Размеры пулов, когда перед пулом есть ещё пул¶

Два пула, два набора ограничений, которые нужно согласовать:

application     pool_size + max_overflow, per process     x  processes  =  client connections PgBouncer must accept
PgBouncer       max_client_conn                                          >= that number
PgBouncer       default_pool_size, per user/database pair                =  server connections PostgreSQL actually sees
PostgreSQL      max_connections                                          >  sum of every PgBouncer's pools, plus admins

Пул приложения ограничивает одновременные транзакции одного процесса. Десять соединений плюс двадцать overflow — частое, обычно слишком щедрое значение для async-сервиса: сотни запросов процесса большую часть времени ждут не БД. Пул PgBouncer ограничивает серверные соединения пользователя, за которые PostgreSQL платит памятью и конкуренцией блокировок. Польза в большом первом числе и небольшом втором. Она исчезает, если default_pool_size просто приравнять к сумме пулов приложений.

В эксперименте серверный пул из пяти соединений обслужил двадцать клиентов и восемьсот запросов без заметного ожидания.

Что наблюдать¶

Предвестник проблем — время ожидания соединения, а не только число занятых. Полностью занятый пул без очереди может быть настроен правильно; растущее ожидание требует смотреть время операций и распределение нагрузки. Библиотека предоставляет состояния пула, ожидание перед ним и время удержания полученного соединения, от которого очередь зависит:

postgres_db_pool_size                             gauge      what the pool is configured for
postgres_db_pool_checked_out                      gauge      connections in use right now
postgres_db_pool_overflow                         gauge      connections beyond pool_size, in use
postgres_db_connection_checkout_wait_seconds      histogram  how long a caller waited for a connection
postgres_db_connection_held_duration_seconds      histogram  how long it held the connection afterwards
postgres_db_connection_timeouts_total             counter    callers that never got one

Настраивайте тревоги по ожиданию и таймаутам, остальное выводите на графики. В статье о мониторинге пула измерены все шесть показателей при исчерпании пула. У PgBouncer SHOW POOLS даёт cl_waiting и maxwait: число клиентов в очереди к серверному соединению и возраст самого старого ожидания. Рост maxwait при ровном ожидании приложения указывает на очередь ниже; если растут оба, нужно исследовать и ёмкость PgBouncer, и длительность работы PostgreSQL.

Закрытие пулов при обновлении¶

Последняя ловушка — завершение. У заменяемого pod ещё могут выполняться транзакции; преждевременное закрытие используемых ресурсов способно сорвать запросы и оборвать соединения посреди транзакций. Порядок: прекратить приём новой работы, дождаться текущих транзакций, затем вызвать dispose engine с ограничением времени, чтобы зависшее освобождение не пережило бюджет pod. Менеджер session библиотеки ограничивает dispose таймаутом и не выпускает из него исключения; безопасный порядок описан в статье об остановке.

Конфигурация¶

Всё выше в виде реально создаваемого engine:

from pydantic import SecretStr
from sqlalchemy_foundation_kit import create_async_session_manager
from sqlalchemy_foundation_kit.contrib.settings import BasePostgresConfig, ConnectionSettings, PoolSettings

config = BasePostgresConfig(
    connection=ConnectionSettings(host="pgbouncer", port=6432, user="app", password=SecretStr("..."), database="app"),
    pool=PoolSettings(size=5, max_overflow=5),    # per process; PgBouncer's default_pool_size is the real limit
    application_name="orders",                    # the one startup parameter PgBouncer tracks and you want
    db_schema="app",                              # applied per transaction, not at connect
)
manager = create_async_session_manager(config)   # statement caches off, unique statement names, no jit, no search_path at startup

Измерения оставили три решения: нулевые кеши и уникальные имена как совместимость со старыми конфигурациями PgBouncer; схема внутри транзакции; имя приложения, которое стоит передавать при подключении, поскольку PgBouncer поддерживает его, а pg_stat_activity показывает. Не выдержал проверки jit как стартовый параметр. Хорошо, что об этом сообщила таблица эксперимента, а не развёртывание.

Это конфигурация sqlalchemy-foundation-kit. Версия 0.3.0 перестала отправлять два параметра, которые PgBouncer отклонял. Библиотека существовала до статьи; раздел её руководства о PgBouncer появился благодаря ей.