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

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

Понимаете ли вы, что пулов здесь два, и как режим PgBouncer ограничивает то, что может делать приложение. От DevOps ждут, что вы посчитаете соединения и объясните ошибки вроде prepared statement ... already exists. Следом спрашивают, какой режим выбрать и куда направлять миграции.

Как отвечать

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

Режимы PgBouncer:

• session: серверное соединение закреплено за клиентом до отключения. Совместим со всем, но при постоянном пуле приложения почти ничего не экономит;
• transaction: соединение выдаётся на транзакцию. Самый полезный режим, но ломает всё сессионное: SET без LOCAL, LISTEN, сессионные advisory-блокировки, временные таблицы;
• statement: соединение на один запрос, многозапросные транзакции запрещены. Для ORM не подходит.

Со стороны SQLAlchemy:

• пул: у QueuePool по умолчанию pool_size=5 и max_overflow=10. С PgBouncer ставят NullPool или маленький пул. Реплики × воркеры × (pool_size + max_overflow) должно влезать в max_client_conn;
• prepared statements: asyncpg и psycopg 3 готовят выражения на сервере, а в transaction-режиме следующий запрос уходит на другое соединение. Для psycopg 3 ставят prepare_threshold=None, для asyncpg отключают кэш и задают уникальные имена через prepared_statement_name_func. В новых PgBouncer есть max_prepared_statements;
• pool_pre_ping=True: отсекает соединения, закрытые по таймауту.

Миграции Alembic и долгие DDL пускают напрямую в PostgreSQL. Пулы смотрят в админ-консоли: SHOW POOLS (cl_waiting, maxwait) и SHOW STATS.

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

Путают режимы и говорят, что session не поддерживает транзакции. Ограничения как раз у transaction и statement.

Задают search_path или statement_timeout в событии connect. В transaction-режиме настройка остаётся на случайном серверном соединении.

Держат сессию SQLAlchemy открытой весь HTTP-запрос с внешними вызовами. Серверное соединение простаивает, клиенты копятся в cl_waiting.