PgBouncer и prepared statements в режиме transaction pooling

Инструкции
20 ноя, 2025
Время на чтение: ~15 мин
Автор: AI (GPT 5)

Искусственный интеллект Fastfox

Данная статья была создана с использованием искусственного интеллекта (GTP-5). Пожалуйста, имейте в виду, что текст может содержать неточности или ошибки.

PgBouncer давно стал стандартом де-факто там, где PostgreSQL нужен под высокую конкурентную нагрузку с большим числом коротких запросов. Самый «экономный» режим его работы — pool_mode = transaction — способен кратно снизить количество серверных backends, но часто ломает привычные паттерны работы приложений с prepared statements. В этой статье разберёмся, почему так происходит, как это корректно лечить, какую конфигурацию выбрать, и как проверить всё руками через psql. На практике PgBouncer часто выносят на отдельный узел, например на облачный VDS, чтобы изолировать сетевой прокси от приложений.

Краткий разогрев: как устроены режимы pool_mode

PgBouncer — прокси, который держит пул соединений к PostgreSQL и выдаёт их клиентам по запросу. В зависимости от pool_mode он по-разному закрепляет серверное соединение за клиентом:

session — серверное соединение закреплено за клиентом на всю его сессию. Приложение может держать состояние: временные таблицы, курсоры, LISTEN/NOTIFY, серверные prepared statements. Экономия соединений минимальна.
transaction — закрепление действует только на время транзакции (BEGIN…COMMIT). Между транзакциями клиент может «переезжать» на другой серверный backend. Это сильно повышает утилизацию и позволяет обслуживать больше клиентов тем же количеством backends.
statement — закрепление только на время одного запроса. Самый жёсткий режим с максимальной экономией, но почти полностью убирающий сессионные возможности. Запрещены явные транзакции и многие команды.

Для большинства веб-приложений оптимальным компромиссом является transaction pooling. Но у него есть тонкий момент: взаимодействие с prepared statements.

Почему ломаются prepared statements в transaction pooling

Серверные prepared statements в PostgreSQL живут в контексте серверного сеанса (backend-процесса). Когда драйвер готовит запрос командой PREPARE (или через расширенный протокол с именованными statement/portal), он рассчитывает использовать сопоставленное имя позже. В pool_mode = session серверный сеанс не меняется — всё ок. В transaction после COMMIT PgBouncer может отдать вам другой backend, где такого имени нет.

Типичная ошибка при повторном выполнении: «prepared statement "S_1" does not exist» или «cached plan must not change result type». Это индикатор того, что драйвер кэшировал серверный план и попытался использовать его в новом сеансе.

Отсюда главный принцип: при transaction pooling нельзя полагаться на состояние, которое переживает границы BEGIN/COMMIT. К сожалению, многие драйверы из коробки включают кэширование или авто-prepare после нескольких одинаковых запросов. Для стабильной работы их надо настроить. Если вам нужна более базовая вводная по пулам, посмотрите также материал о видах пулов соединений в PgBouncer.

Диаграмма: transaction pooling и потеря именованных prepared statements между сессиями

Стратегии совместимости: что делать с prepared statements

1) Отключить серверные prepared statements в драйвере

Самый надёжный и часто самый простой путь — сказать драйверу не создавать именованные prepared statements, а выполнять запросы через простой протокол или использовать только безымянные statements. Конкретика по популярным драйверам:

Java (PgJDBC): добавить prepareThreshold=0 или preferQueryMode=simple. Первый отключает авто-prepare. Второй заставляет всегда использовать простой протокол.
.NET (Npgsql): Max Auto Prepare=0 отключает автоподготовку; при включённом мультиплексировании полезно оставлять только безымянные statements. При необходимости используйте Prefer Text=true.
Go (pgx): в конфиге соединения PreferSimpleProtocol=true. Это убирает именованные prepared statements и серверный кэш.
Python (psycopg2/psycopg): по умолчанию серверные prepared не используются, если явно не вызывать cursor.prepare. В ORM проверьте, что серверный prepare не включён.
Ruby (pg): избегайте долговечных conn.prepare с именами; используйте параметризованные запросы без явного PREPARE.
PHP (PDO_pgsql): включите эмуляцию на стороне PDO: PDO::ATTR_EMULATE_PREPARES = true.

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

2) Если подготовка нужна — используйте только безымянные statements

Безымянные prepared statements переопределяются на каждый запрос. Драйвер получает плюсы расширенного протокола (передача параметров) без сохранения состояния между транзакциями. Обычно достаточно отключить «кэш подготовленных запросов» или авто-prepare.

3) Избегайте явных PREPARE/EXECUTE/DEALLOCATE в приложении

Если код напрямую вызывает PREPARE и затем EXECUTE в разных транзакциях — это несовместимо с transaction pooling. Такой код переводим на параметризованные запросы без сохранения имени, либо используем отдельный пул с pool_mode = session для этого пользователя.

Конфигурация PgBouncer для transaction pooling

Ниже минимальный пример pgbouncer.ini, подходящий для большинства веб-нагрузок. Обратите внимание на pool_mode, server_reset_query и ignore_startup_parameters.

[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 100
reserve_pool_size = 20
server_idle_timeout = 600
query_wait_timeout = 120

; Сброс состояния при возврате соединения в пул
server_reset_query = DISCARD ALL
server_reset_query_always = 0

; Избегаем "ломающих" параметры, которые мешают реюзу бэкендов
ignore_startup_parameters = extra_float_digits

; Диагностика
log_connections = 1
log_disconnections = 1
server_lifetime = 0

pool_mode = transaction: закрепление на время транзакции, а не сессии.
server_reset_query: DISCARD ALL безопасен и убирает все сессионные следы. Альтернатива быстрее, но менее безопасна.
server_reset_query_always: обычно 0, иначе возрастут накладные расходы.
ignore_startup_parameters: минимум — extra_float_digits, избегайте игнорирования параметров, влияющих на семантику запросов.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

psql-практикум: воспроизводим проблему и проверяем решение

Предположим, PostgreSQL слушает на 5432, а PgBouncer — на 6432. Сначала убедимся, что pool_mode действительно transaction:

psql -h 127.0.0.1 -p 6432 -U app pgbouncer -c "SHOW HELP"
psql -h 127.0.0.1 -p 6432 -U app pgbouncer -c "SHOW CONFIG"

Посмотрим, что творится в пулах:

psql -h 127.0.0.1 -p 6432 -U app pgbouncer -c "SHOW POOLS"
psql -h 127.0.0.1 -p 6432 -U app pgbouncer -c "SHOW STATS"

Теперь подключимся к пользовательской БД через PgBouncer и явно создадим серверный prepared statement:

psql -h 127.0.0.1 -p 6432 -U app appdb

BEGIN;
PREPARE add1(int) AS SELECT $1 + 1;
EXECUTE add1(41);
COMMIT;

BEGIN;
EXECUTE add1(1);
COMMIT;

Второй EXECUTE с высокой вероятностью упадёт ошибкой «prepared statement "add1" does not exist». Причина: после первого COMMIT ваше клиентское соединение было привязано к другому серверному backend-у, где нет подготовленного имени. Повторите тест после отключения авто-prepare в драйвере — ошибка исчезнет.

Консоль psql: команды SHOW POOLS и SHOW STATS в админке PgBouncer

Ограничения режима transaction pooling, о которых важно помнить

Нельзя полагаться на сессионные фичи: временные таблицы между транзакциями, долгоживущие курсоры, LISTEN/NOTIFY на уровне сессии.
Межтранзакционное состояние храните в приложении или в БД, но не в сессии. Критичные случаи — выносите в отдельный пул с pool_mode = session.
server_reset_query = DISCARD ALL добавляет накладные расходы, но они компенсируются лучшим реюзом backends.
COPY с клиентской стороны требует закрепления соединения на время операции; в рамках транзакции это ок.

Диагностика в PgBouncer: быстрые команды

У PgBouncer есть встроенная админ-консоль (это отдельная «БД» pgbouncer). Полезные запросы:

SHOW POOLS — состав пулов и выделенные серверные соединения.
SHOW CLIENTS и SHOW SERVERS — список клиентов и серверов, состояния и ожидания.
SHOW STATS — агрегаты транзакций и ожиданий; подходит для алертинга.
SHOW CONFIG — активные значения конфигурации.

Подключение:

psql -h 127.0.0.1 -p 6432 -U admin pgbouncer

Если ищете альтернативы с похожей идеологией, посмотрите сравнение Odyssey и PgBouncer.

Как аккуратно мигрировать существующий проект на transaction pooling

Инвентаризация драйверов и ORM. Для каждого сервиса зафиксируйте стек: язык, драйвер, его версия и параметры. Ищем флаги, связанные с prepared statements и типом протокола.
Переключение драйверов. На стенде отключите авто-prepare и кэш именованных statements: prepareThreshold=0, PreferSimpleProtocol=true, Max Auto Prepare=0, PDO::ATTR_EMULATE_PREPARES = true — по ситуации.
Запуск PgBouncer в session. Убедитесь, что функционально всё работает через прокси: логин, транзакции, COPY, миграции.
Включите pool_mode = transaction для тестовой БД/схемы. Прогоните регрессию; включите нагрузочные тесты. Следите за ошибками «prepared statement does not exist» и планами запросов в PostgreSQL.
Промежуточная эксплуатация. В бою включите для части трафика. Отслеживайте метрики PgBouncer (SHOW STATS), ошибки приложений и латентность SQL.
Финиш. Переведите все совместимые сервисы. Оставшиеся, требующие сессионных фич — на отдельный пул с pool_mode = session.

Тонкости server_reset_query

server_reset_query запускается при возврате соединения в пул и обязан привести сеанс PostgreSQL к чистому состоянию:

DISCARD ALL — максимальная безопасность: очищает подготовленные операторы, временные объекты, параметры, подписки LISTEN, курсоры.
RESET ALL — быстрее, но не удаляет подготовленные операторы и временные объекты.
Комбинация: RESET ALL плюс явные DEALLOCATE ALL и DISCARD TEMP — компромисс с рисками пропуска артефактов.

Если нужна железобетонная совместимость, оставайтесь на DISCARD ALL и оптимизируйте размер транзакций, чтобы снизить частоту возврата соединений в пул.

Подводные камни драйверов и как их распознать

PgJDBC: при prepareThreshold > 0 возможны вспышки «prepared statement ... does not exist». Лечение: prepareThreshold=0 или preferQueryMode=simple.
Npgsql: авто-prepare по порогу использования. Лечение: Max Auto Prepare=0.
pgx/lib/pq (Go): не используйте долговечные DB.Prepare на пуле. Безопаснее — PreferSimpleProtocol=true или отказ от явного Prepare.
Node.js (pg): если явно задаёте имена statements — отключите это при работе через transaction.
PHP (PDO_pgsql): PDO::ATTR_EMULATE_PREPARES = true совместим с transaction pooling.

Производительность и экономия backends

Сильное снижение количества серверных процессов PostgreSQL при том же числе клиентских соединений.
Более ровная латентность при всплесках: клиенты выстраиваются в очереди внутри PgBouncer, а не создают сотни новых backends.
Накладные расходы на DISCARD ALL компенсируются реюзом backends; учитывайте это при выборе размера транзакций и default_pool_size.

Чек‑лист перед включением transaction pooling

Выделили сервисы, требующие сессионных фич — для них отдельный пул с pool_mode = session.
Отключили авто-prepare и кэш именованных statements: prepareThreshold=0, Max Auto Prepare=0, PreferSimpleProtocol=true, PDO::ATTR_EMULATE_PREPARES = true.
Оставили server_reset_query = DISCARD ALL как безопасный дефолт.
Проверили через psql админку PgBouncer: SHOW CONFIG, SHOW POOLS, SHOW STATS.
В алертинг добавили ожидания в пуле и таймауты (query_wait_timeout).

FAQ

Можно ли жить с prepared statements и transaction pooling одновременно?
Да, если драйвер использует только безымянные statements или не кэширует имена между транзакциями. Именованные PREPARE/EXECUTE между транзакциями несовместимы.

Поможет ли server_reset_query = DEALLOCATE ALL?
Это часть DISCARD ALL. Оставив только DEALLOCATE ALL, можно пропустить другие сессионные следы.

Почему в SHOW POOLS дробятся пулы для одного db/user?
Из-за разных startup‑параметров. Минимизируйте их, используйте ignore_startup_parameters = extra_float_digits, но не игнорируйте параметры, влияющие на семантику.

psql безопасен?
psql по умолчанию не делает авто-prepare и подходит для диагностики PgBouncer в transaction pooling.

Итоги

transaction pooling в PgBouncer — мощный рычаг экономии ресурсов, но требует дисциплины: не переносить сессионное состояние через транзакции и отключить авто-prepare в драйверах. С правильной конфигурацией (pool_mode = transaction, разумные размеры пулов, server_reset_query = DISCARD ALL) и настройками клиентов вы получите устойчивую систему, которая переживает всплески трафика без обвала PostgreSQL и без загадочных падений «prepared statement does not exist». Проверяйте себя быстро через psql и SHOW STATS в админке PgBouncer.

Поделиться статьей