Как взаимодействует SQLAlchemy с PgBouncer?

Что проверяют

Понимаете ли вы, что пулов становится два и что режим PgBouncer меняет гарантии, на которые рассчитывают драйвер и ORM. Следом обычно спрашивают, почему после перехода на transaction mode посыпались ошибки про prepared statements.

Как отвечать

Сначала режимы PgBouncer:

• session (по умолчанию): серверное соединение закреплено за клиентом до отключения. Работает как голый PostgreSQL, но экономит мало;
• transaction: соединение выдаётся на транзакцию и возвращается в пул после COMMIT или ROLLBACK. Самый полезный режим, но теряется состояние сессии: SET, сессионные advisory-блокировки, LISTEN, временные таблицы, prepared statements;
• statement: соединение на один запрос, многозапросные транзакции запрещены, для ORM не годится.

Дальше связка с SQLAlchemy:

• двойной пул: у create_engine по умолчанию QueuePool (pool_size=5, max_overflow=10) на каждый процесс. За PgBouncer часто ставят poolclass=NullPool или маленький пул: дорогие соединения к базе уже держит PgBouncer;
• prepared statements: asyncpg готовит запросы на сервере и кэширует их, в transaction mode это даёт ошибки prepared statement ... already exists или does not exist. Решения: отключить кэш (prepared_statement_cache_size=0 у диалекта, statement_cache_size=0 у asyncpg), задать уникальные имена через prepared_statement_name_func либо включить max_prepared_statements в PgBouncer 1.21 и новее;
• обрывы: PgBouncer закрывает простаивающие соединения по server_idle_timeout и client_idle_timeout, поэтому нужен pool_pre_ping=True или pool_recycle.

Где спотыкаются

Держат Session открытой весь HTTP-запрос вместе с вызовами внешних API. В transaction mode серверное соединение занято всё это время, и пул PgBouncer кончается под нагрузкой.

Настраивают SET search_path или statement_timeout в событии connect. В transaction mode следующая транзакция может попасть на другое серверное соединение без этих настроек.

Советуют transaction mode без оговорок. Для долгих миграций, сессионных advisory-блокировок и LISTEN нужно прямое подключение к PostgreSQL или отдельный пул в session mode.