pgBouncer

Для чего нужен пулер соединений

Количество соединений, которое поддерживает сервер БД PostgreSQL, ограничено. Поэтому нормальная логика работы клиентского приложения с БД предполагает, что приложение:

1. Устанавливает соединение с сервером БД.

2. Отправляет SQL-запрос и получает результат.

3. Закрывает соединение, освобождая его для следующих клиентов.

Проблема в том, что PostgreSQL выделяет отдельный процесс на каждое установленное соединение. Постоянное открытие и закрытие множества соединений означает запуск и остановку множества процессов, что создает большие накладные расходы ресурсов сервера и приводит к снижению производительности СУБД.

Чтобы решить эту проблему, в DBaaS Postgres для всех инстансов установлен менеджер соединений PgBouncer (пулер). Логика его работы следующая:

1. Пулер устанавливает с сервером БД фиксированное количество соединений (пул), которые держит открытыми все время. В DBaaS Postgres размер пула по умолчанию (default_pool_size pgbouncer) составляет 200 соединений.

2. Клиентское приложение подключается не напрямую к БД, а к пулеру. Максимальное количество поддерживаемых соединений между приложением и пулером составляет 6000.

3. Пулер коммутирует клиентское соединение на сервер, используя открытое соединение из пула.

4. Когда приложение закрывает соединение с пулером, соединение между пулером и сервером возвращается в пул и может быть переиспользовано следующим клиентским соединением.

Режимы работы PgBouncer

Менеджер соединений PgBouncer в DBaaS Postgres работает в двух режимах:

• Сессионный — в этом режиме клиентское соединение устанавливается при первом запросе к базе данных и поддерживается до тех пор, пока клиент не разорвет сессию. Такой режим поддерживается всеми клиентами PostgreSQL, но является менее производительным, чем транзакционный режим. PgBouncer в сессионном режиме доступен на порту 5432.

• Транзакционный — в этом режиме клиентское соединение устанавливается при первом запросе к базе данных и поддерживается до завершения транзакции. Транзакционный режим обеспечивает высокую производительность и позволяет максимально эффективно нагрузить СУБД. PgBouncer в транзакционном режиме доступен на порту 6432.

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

• временных таблиц (temporary tables);

• курсоров (cursors);

• рекомендательных блокировок (advisory locks), которые существуют дольше одной транзакции;

• подготовленных операторов (prepared statements).

Experiment

Preparation

Having following docker-compose file:

services:
  postgres:
    image: postgres
    environment:
      POSTGRES_PASSWORD: mysecretpassword
    ports:
      - "5437:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

  pgbouncer:
    image: edoburu/pgbouncer
    depends_on:
      - postgres
    ports:
      - "6432:6432"
    volumes:
      - ./pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini
      - ./userlist.txt:/etc/pgbouncer/userlist.txt

volumes:
  postgres_data:

# pgbouncer.ini file content
[databases]
maindb = host=postgres port=5432 dbname=postgres

[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 = 100
default_pool_size = 20

# userlist.txt
"pgbouncer" "pgbouncer"
"postgres" "mysecretpassword"

# prepare for benchmark
pgbench -i -h 127.0.0.1 -p 5437 -U postgres -d postgres

This creates the test tables (pgbench_branches, pgbench_accounts, etc.) and populates them with sample data.

Without pgBouncer

# start benchmark
pgbench -j 4 -c 50 -t 1000 -h 127.0.0.1 -p 5437 -U postgres -d postgres  

• -c 50: Use 10 concurrent clients.

• -j 4: Use 4 threads.

result may look like

pgbench (17.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 4
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 50000/50000
number of failed transactions: 0 (0.000%)
latency average = 83.820 ms
initial connection time = 1491.121 ms
tps = 596.515427 (without initial connection time)

With pgBouncer

# start benchmark
pgbench -j 4 -c 50 -t 1000 -h 127.0.0.1 -p 6432 -U postgres -d maindb

-d maindb (this comes from pgbouncer.ini where I define an alias maindb which leads to actual postgres database in postgres container)

pgbench (17.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 4
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 50000/50000
number of failed transactions: 0 (0.000%)
latency average = 93.732 ms
initial connection time = 76.382 ms
tps = 533.434535 (without initial connection time)

Session vs Transaction modes

Scenario 1: pgBouncer in session Mode

📌 Behavior

• A single PostgreSQL session is assigned to a client connection for its entire lifetime.

• All transactions from the same client use the same backend PID.

• Session-scoped features (like temporary tables, GUCs, SET LOCAL, etc.) persist across transactions.

✅ Use Cases

• Applications that rely on:

  • Temporary tables

  • Session variables (SET LOCAL app.user_id = '123')

  • Listeners (LISTEN / NOTIFY)

  • Extensions like pg_trgm with session-local settings

🧠 Spring Boot + Hibernate Behavior

• One EntityManager may reuse the same connection (session) across multiple @Transactional methods.

• Session state (e.g., SET LOCAL) persists across transactions.

• Temporary tables created in one transaction are visible in subsequent transactions.

⚠️ Drawbacks

• Less scalable: Each client connection holds a backend connection.

• Risk of connection leaks if clients don’t close connections.

---

🧪 Scenario 2: pgBouncer in transaction Mode

📌 Behavior

• A backend connection is assigned only for the duration of a single transaction.

• After COMMIT or ROLLBACK, the connection is returned to the pool and can be reused by another client.

• Session-scoped features do not persist across transactions.

✅ Use Cases

• Stateless applications

• High concurrency with many short transactions

• Applications that don’t rely on session state

🧠 Spring Boot + Hibernate Behavior

• Each @Transactional method may get a different backend PID.

• Session variables (SET LOCAL) are lost between transactions.

• Temporary tables are not visible across transactions (since they’re session-scoped).

• Hibernate and Spring can still work fine, as long as they don’t rely on session state.

⚠️ Drawbacks

• Incompatible with:

  • Temporary tables

  • SET LOCAL variables

  • LISTEN / NOTIFY

  • Extensions that rely on session state