PgBouncer: лекарство с побочными эффектами. Часть 1 - обзор

Про PgBouncer обычно вспоминают в двух ситуациях. Либо PostgreSQL уже начал ругаться на too many connections, а приложение завопило PSQLException: FATAL: sorry, too many clients already. Либо кто-то заранее посмотрел на количество pod’ов, умножил его на размер Hikari pool и тихо отложил кофе.

На первый взгляд PgBouncer элегантно решает проблему: поставили маленький прокси перед PostgreSQL, направили туда приложения, получили меньше настоящих соединений к базе. Но PgBouncer тем и опасен, что долго работает «прозрачно». А потом в один день выясняется, что временная таблица пропала, LISTEN не слушает, миграция оставила странную ошибку prepared statement cache, а SET search_path повел себя не как на локальной базе.

Это первая обзорная часть: зачем PgBouncer нужен, почему он не заменяет connection pool внутри приложения, чем отличаются session, transaction и statement pooling, где ломается session state и что сейчас происходит с prepared statements.

Во второй части разберем эксплуатацию: стартовый конфиг, sizing, таймауты, мониторинг, production-топологии и failover.

Пример использования PgBouncer можно посмотреть в этом демо примере.

Что делает PgBouncer

PostgreSQL обслуживает обычные клиентские соединения отдельными процессами. У соединения с базой есть память, session state, кэши, prepared statements, параметры сессии, возможные временные объекты и открытая транзакция.

Если у нас один монолит и пул с 10 соединениями, жизнь спокойная. Если у нас 40 экземпляров сервиса с таким же пулом, получается уже до 400 соединений. Даже если большинство из них почти ничего не делает, PostgreSQL все равно должен их держать: память, backend-процессы, планировщик ОС, служебные проверки, контекстные переключения.

Официальная документация PgBouncer описывает его как слой, через который клиент подключается так же, как к PostgreSQL. С точки зрения приложения это обычный PostgreSQL port. Внутри PgBouncer принимает клиентские соединения и держит собственный ограниченный набор серверных соединений к базе.

В результате появляются два разных типа соединений:

• client connection - соединение приложения с PgBouncer;
• server connection - настоящее соединение PgBouncer с PostgreSQL.

Другими словами, HikariCP в условном Java-приложении управляет client connections к PgBouncer. PgBouncer управляет server connections к PostgreSQL. Они стоят на разных этажах и решают разные задачи.

Почему PgBouncer не заменяет connection pool внутри сервиса

Иногда PgBouncer воспринимают как внешний HikariCP: раз есть пул перед базой, можно убрать пул в приложении или сделать его очень большим. Обычно это плохой план.

Pool внутри сервиса отвечает за локальную дисциплину:

• ограничивает количество потоков или virtual threads, которые одновременно полезут в базу;
• переиспользует уже открытые соединения;
• не тратит время бизнес-запроса на новое подключение;
• проверяет живость соединений;
• интегрируется с транзакциями фреймворка и ORM;
• дает метрики на уровне конкретного сервиса.

PgBouncer отвечает за другое:

• принимает много клиентских соединений от разных сервисов;
• не дает приложениям открыть слишком много реальных процессов в PostgreSQL;
• переиспользует настоящие соединения к базе между клиентами;
• перераспределяет ограниченный пул server connections между приложениями;
• ставит клиентов в очередь, если все server connections заняты.

Если убрать локальный пул и ходить в PgBouncer «на каждый запрос новым connection», мы снова заплатим за создание JDBC-объекта, аутентификацию, TLS/сетевые расходы и лишние ошибки при всплесках. PostgreSQL будет защищен, но приложение хуже проконтролирует собственную конкуренцию.

Если оставить локальный пул, но поставить maximum-pool-size в сотни «потому что PgBouncer все равно ограничит», мы просто перенесем очередь из приложения в PgBouncer. Сервис будет держать много занятых worker threads или virtual threads, а пользователи увидят задержку уже после того, как запрос добрался до базы.

Размер локального пула подбирается от профиля конкретного сервиса. Размер PgBouncer pool — от возможностей PostgreSQL и общей нагрузки всех клиентов.

Режимы pooling

У PgBouncer три основных режима. В документации они описаны как разные уровни “brutality”, и эта шутка в документации довольно точно передает инженерный смысл.

Session pooling

session - самый спокойный режим и дефолт PgBouncer.

Клиент подключился к PgBouncer, PgBouncer выдал ему server connection к PostgreSQL и держит эту пару до конца клиентской сессии. Когда клиент отключается, server connection возвращается в пул.

Плюсы:

• почти все PostgreSQL-фичи работают как при прямом подключении;
• можно использовать LISTEN, session-level advisory locks, временные таблицы, session variables;
• меньше сюрпризов для ORM и старого кода.

Минус очевидный: если приложение держит постоянные соединения, то экономия слабая. Десять соединений в одном экземпляре приложения займут десять server connections в PgBouncer на все время жизни сервиса.

session полезен, когда нужно снизить стоимость переподключений или обслужить короткоживущих клиентов, но нельзя ломать session state.

Transaction pooling

transaction - самый популярный режим, ради которого и используют PgBouncer.

Server connection выдается клиенту только на время транзакции. Транзакция закончилась — соединение возвращается в пул, а следующая транзакция этого же клиента может попасть уже на другое соединение с PostgreSQL.

Это дает главную экономию: приложение может держать много client connections к PgBouncer, но настоящих PostgreSQL соединений будет заметно меньше. Особенно хорошо это работает, если сервисы делают короткие транзакции.

Цена — потеря стабильной привязки клиента к одной PostgreSQL-сессии. Если приложение работает классическими короткими запросами без session state, вы можете этого не почувствовать. Более сложные ситуации требуют отдельной проверки. В официальной таблице совместимости это сформулировано так: transaction pooling специально разрывает ожидание клиента, что за ним закреплен один server connection. Использовать его можно, только если приложение не опирается на session state. Но не стоит пугаться, в современных микросервисах эти ситуации встречаются крайне редко.

Statement pooling

statement - самый агрессивный режим. Server connection возвращается в пул после каждого statement. Многошаговые транзакции запрещены, потому что иначе PgBouncer сломал бы саму семантику транзакции.

Для обычного backend это почти никогда не то, что нужно. Такой режим можно рассматривать для очень специфичной нагрузки: много коротких независимых запросов в autocommit и полная уверенность, что приложению не нужны транзакции из нескольких SQL-команд.

Если сомневаетесь между transaction и statement, почти наверняка вам нужен transaction.

Что ломается в transaction pooling

Окей, вероятнее всего, если вы задумались над PgBouncer, то вам нужен transaction pooling. Но какие именно ограничения в нем есть?

Главный принцип простой: все, что живет дольше одной транзакции и привязано к PostgreSQL-сессии, под transaction pooling становится подозрительным.

Официальная таблица совместимости PgBouncer отдельно отмечает несколько важных случаев:

Это не произвольные ограничения именно PgBouncer. Это следствие того, что после завершения транзакции следующий statement клиента может попасть на другую PostgreSQL-сессию.

SET, search_path и переменные

В transaction pooling нельзя полагаться на обычный SET something = ..., если это session-level изменение.

Плохая идея:

set search_path to surveys_schema;
select *
from surveys;

Первый statement мог выполниться на одном server connection, второй — на другом. Даже если сегодня повезло, контракт уже сломан.

Варианты лучше:

• задавать search_path на роли или базе в PostgreSQL;
• использовать SET LOCAL внутри транзакции;
• указывать схему явно;
• для PgBouncer использовать connect_query, если это подходит вашей модели;
• для параметров, которые PostgreSQL умеет репортить клиенту, смотреть в сторону track_extra_parameters.

Пример настройки PgBouncer с помощью connect_query:

jooq_demo_db = host=postgres port=5432 dbname=jooq_demo_db user=test password=test \
  pool_mode=transaction pool_size=20 reserve_pool_size=5 \
  connect_query='SET search_path TO surveys_schema'

Это лучше, чем выполнять SET search_path из приложения в начале каждой «сессии», которой в transaction pooling фактически нет. Но и здесь надо понимать границы: connect_query выполняется при создании server connection, а не перед каждым клиентским запросом.

Временные таблицы

Временные таблицы живут в PostgreSQL-сессии. Поэтому обычный сценарий «создал temp table, потом в другой транзакции дочитал из нее данные» не совместим с transaction pooling.

Совместимый вариант — временная таблица, которая целиком живет внутри одной транзакции и умирает на COMMIT:

begin;

create temporary table tmp_ids
(
    id uuid
) on commit drop;

insert into tmp_ids
values ('018f0000-0000-7000-8000-000000000001');

select *
from orders
where id in (select id from tmp_ids);

commit;

Этот фрагмент нормальный для transaction pooling: ON COMMIT DROP попадает в совместимую зону, потому что таблица умирает вместе с транзакцией.

Плохой вариант — создать temp table и ожидать, что она переживет несколько транзакций клиента:

create temporary table tmp_ids(id uuid) on commit preserve rows;

insert into tmp_ids
values ('018f0000-0000-7000-8000-000000000001');

commit;

select *
from orders
where id in (select id from tmp_ids);

После COMMIT следующий запрос может попасть на другой server connection, где такой временной таблицы нет. Поэтому ON COMMIT PRESERVE ROWS и ON COMMIT DELETE ROWS в официальной таблице совместимости помечены как несовместимые с transaction pooling.

Для прикладного кода это хороший повод спросить себя: а точно ли здесь нужна временная таблица? Часто ее можно заменить CTE, VALUES, массивом параметров, JSON-to-recordset или нормальной staging-таблицей с ключом batch/job.

LISTEN / NOTIFY

NOTIFY можно выполнить через transaction pooling. LISTEN - уже нельзя.

PostgreSQL сам описывает LISTEN как регистрацию текущей session на notification channel. Регистрация очищается, когда сессия заканчивается. PgBouncer в transaction mode не дает приложению стабильную server session, значит подписчик будет сломан концептуально.

Если приложению нужен LISTEN/NOTIFY, делайте отдельный канал с уровнем session pooling или прямым подключением к PostgreSQL. Для Java это особенно важно: pgJDBC получает notifications через PGConnection, то есть listener и так обычно живет на отдельном выделенном соединении.

Advisory locks

Session-level advisory locks через transaction pooling использовать нельзя. Они привязаны к сессии, а сессия после транзакции больше не принадлежит клиенту.

Transaction-level advisory locks (pg_advisory_xact_lock) обычно укладываются в модель transaction pooling, потому что их жизненный цикл совпадает с транзакцией. Но это не повод превращать PgBouncer в распределенный lock manager. Если lock держится долго, он занимает server connection и уменьшает полезную емкость пула.

Если хочется отдельно освежить механику advisory locks, есть хороший разбор на Хабре: «Advisory Locks в PostgreSQL». Для PgBouncer там особенно важна разница между session-level и transaction-level блокировками.

DDL и миграции

DDL лучше не гонять через transaction pool. Не потому, что PgBouncer вообще не умеет DDL, а потому что миграции любят ровно те вещи, которые плохо дружат с агрессивным pooling:

• долгие транзакции;
• advisory locks;
• смена схемы под работающими prepared statements;
• команды, которым нужен особый transaction mode;
• расширения и session state;
• отдельные прямые проверки metadata.

Практичный паттерн такой:

spring:
  datasource:
    url: jdbc:postgresql://pgbouncer-app:6432/app_db
  flyway:
    url: jdbc:postgresql://postgres:5432/app_db

Runtime-трафик идет через PgBouncer. Flyway, Liquibase, CREATE EXTENSION, CREATE INDEX CONCURRENTLY и прочие DDL-задачи идут напрямую в PostgreSQL.

Prepared statements: старый страх и новая реальность

Самая частая страшилка про PgBouncer: «transaction pooling несовместим с prepared statements». Она пугала много лет, но сейчас ее надо произносить аккуратнее.

Есть два разных уровня:

1. SQL-level prepared statements: PREPARE, EXECUTE, DEALLOCATE.
2. Protocol-level prepared statements: то, что используют драйверы через PostgreSQL extended query protocol.

Первый вариант по-прежнему не работает нормально в transaction pooling. Если вы выполняете:

prepare find_survey(uuid) as
    select *
    from surveys
    where id = $1;

execute find_survey('018f0000-0000-7000-8000-000000000001');

то PREPARE может случиться на одном server connection, а EXECUTE - на другом. PgBouncer не переписывает SQL-level PREPARE/EXECUTE/DEALLOCATE, он отправляет их в PostgreSQL как есть.

Второй вариант интереснее. Начиная с PgBouncer 1.21.0, появился tracking protocol-level named prepared statements. В свежем PgBouncer max_prepared_statements уже по умолчанию равен 200, а не 0.

Официальная документация описывает механизм примерно так:

• клиент готовит statement под своим именем;
• PgBouncer хранит соответствие клиентского имени и внутреннего имени;
• одинаковый SQL получает общий internal name вида PGBOUNCER_123;
• если текущий server connection еще не знает этот statement, PgBouncer подготовит его перед выполнением;
• при max_prepared_statements = 0 эта поддержка выключена.

Это важная поправка для Java. Все популярные ORM вроде Hibernate, Spring Data JDBC, jOOQ и другие по умолчанию используют JDBC PreparedStatement, когда им надо передавать bind values.

pgJDBC, в свою очередь, использует extended protocol, а после нескольких выполнений может перейти к server-side prepared statement. В документации pgJDBC дефолтный prepareThreshold равен 5. Отключается это параметром prepareThreshold=0.

То есть современные варианты такие:

# Вариант 1: используем поддержку PgBouncer 1.21+
spring.datasource.url=jdbc:postgresql://pgbouncer:6432/app_db

# Вариант 2: отключаем server-side prepared statements в pgJDBC
spring.datasource.url=jdbc:postgresql://pgbouncer:6432/app_db?prepareThreshold=0

Первый вариант обычно лучше для классического Java backend, если PgBouncer свежий и max_prepared_statements включен. Prepared statements уменьшают parse/plan overhead и не заставляют драйвер каждый раз слать полный SQL.

Второй вариант полезен как диагностика или временное решение, если:

• PgBouncer старый;
• используется не PgBouncer, а совместимый пулер с другими ограничениями;
• приложение ловит ошибки prepared statement cache после DDL;
• есть библиотека, которая странно именует statements или смешивает типы параметров.

У поддержки prepared statements есть цена. PgBouncer должен разбирать и переписывать protocol messages, хранить query cache, держать prepared statements на server connections. На больших нагрузках это добавляет CPU и память. Зато один подготовленный план запроса может переиспользоваться между разными клиентами, если SQL совпадает.

Что дальше

На уровне обзора картина такая:

• PgBouncer нужен, чтобы ограничить количество настоящих соединений к PostgreSQL.
• Он не заменяет connection pool внутри приложения.
• Самый полезный режим для backend-сервисов - transaction.
• transaction pooling требует дисциплины вокруг session state.
• Prepared statements больше не надо автоматически выключать, но надо понимать разницу между SQL-level и protocol-level prepared statements.
• Миграции и DDL лучше вести прямым подключением к PostgreSQL.

Во второй части разберем, как это эксплуатировать: с какого конфига стартовать, как выбирать размеры пулов, какие таймауты согласовать с HikariCP, какие метрики смотреть и где PgBouncer лучше ставить в production.