PgBouncer: пул соединений для PostgreSQL

PostgreSQL создаёт отдельный процесс для каждого соединения. При 1000+ одновременных клиентов это убивает производительность. PgBouncer — легковесный пул соединений, который решает эту проблему.

Зачем нужен пул соединений

Без PgBouncer:
1000 клиентов → 1000 процессов PostgreSQL
Каждый процесс: ~5-10 МБ RAM → 5-10 ГБ только на соединения
Context switching: деградация производительности

С PgBouncer:
1000 клиентов → PgBouncer → 20-50 реальных соединений с PostgreSQL
RAM: минимальная, производительность: высокая

PgBouncer принимает соединения от приложений и мультиплексирует их в меньшее количество реальных соединений с PostgreSQL.

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

Session pooling (по умолчанию)

Соединение с PostgreSQL выдаётся клиенту на всё время сессии.

pool_mode = session

Когда: приложения используют временные таблицы, prepared statements, SET LOCAL. Минимальные изменения в приложении.

Минус: не даёт большой выгоды при длинных сессиях.

Transaction pooling (рекомендуется)

Соединение выдаётся только на время транзакции. После COMMIT/ROLLBACK возвращается в пул.

pool_mode = transaction

Когда: большинство современных приложений (Django, Rails, Node.js).

Минус: нельзя использовать SET, временные таблицы, LISTEN/NOTIFY без специальной настройки.

Statement pooling

Соединение выдаётся только на один запрос. Транзакции невозможны.

pool_mode = statement

Редко используется — ограничения слишком жёсткие.

Конфигурация pgbouncer.ini

[databases]
# Имя_БД = параметры подключения
myapp = host=localhost port=5432 dbname=myapp

; Все базы через шаблон
* = host=localhost port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Режим пула
pool_mode = transaction

; Размер пула на одну пару (база, пользователь)
default_pool_size = 25

; Максимум клиентских соединений
max_client_conn = 1000

; Резервные соединения для суперпользователей
reserve_pool_size = 5
reserve_pool_timeout = 3

; Логирование
log_connections = 0
log_disconnections = 0
log_stats = 1
stats_period = 60

Файл пользователей

# /etc/pgbouncer/userlist.txt
"myapp_user" "md5хэш_пароля"
"readonly_user" "md5хэш_пароля"

# Сгенерировать MD5-хэш
echo -n "пароль_пользователь" | md5sum
# Формат: md5 + md5(password + username)
echo -n "passwordmyapp_user" | md5sum | awk '{print "md5"$1}'

Docker Compose

services:
  pgbouncer:
    image: bitnami/pgbouncer:latest
    environment:
      POSTGRESQL_HOST: postgres
      POSTGRESQL_PORT: 5432
      POSTGRESQL_DATABASE: myapp
      POSTGRESQL_USERNAME: myapp_user
      POSTGRESQL_PASSWORD: secret
      PGBOUNCER_POOL_MODE: transaction
      PGBOUNCER_DEFAULT_POOL_SIZE: 25
      PGBOUNCER_MAX_CLIENT_CONN: 500
    ports:
      - "6432:6432"
    depends_on:
      - postgres

  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myapp_user
      POSTGRES_PASSWORD: secret

Подключение приложения

# Django settings.py — указываем PgBouncer вместо PostgreSQL
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'pgbouncer',  # ← PgBouncer
        'PORT': '6432',       # ← порт PgBouncer
        'NAME': 'myapp',
        'USER': 'myapp_user',
        'PASSWORD': 'secret',
        'OPTIONS': {
            # Отключить prepared statements (не работают в transaction mode)
            'options': '-c statement_timeout=30000'
        },
        'CONN_MAX_AGE': 0,  # Django не должен кешировать соединения
    }
}

// Node.js (pg)
const pool = new Pool({
  host: 'pgbouncer',
  port: 6432,
  database: 'myapp',
  user: 'myapp_user',
  password: 'secret',
  // PgBouncer сам управляет пулом
  max: 10,  // небольшой пул на уровне приложения
});

Мониторинг

PgBouncer имеет встроенную консоль мониторинга:

# Подключиться к специальной БД pgbouncer
psql -h localhost -p 6432 -U myapp_user pgbouncer

-- Статистика по пулам
SHOW POOLS;
-- database | user       | cl_active | cl_waiting | sv_active | sv_idle | sv_used
-- myapp    | myapp_user | 45        | 0          | 25        | 0       | 3

-- Общая статистика
SHOW STATS;

-- Текущие клиенты
SHOW CLIENTS;

-- Текущие серверные соединения
SHOW SERVERS;

-- Конфигурация
SHOW CONFIG;

Ключевые метрики:

• cl_waiting > 0 — клиенты ждут соединения, увеличьте default_pool_size
• sv_idle — простаивающие серверные соединения (норма)
• avg_wait_time — среднее время ожидания (должно быть < 5 мс)

Ограничения transaction mode

Что не работает в transaction pooling:

-- Временные таблицы: не работают между транзакциями
CREATE TEMP TABLE tmp ...;  -- ❌ исчезнет после транзакции

-- SET: потеряется между транзакциями
SET search_path = 'myschema';  -- ❌

-- LISTEN/NOTIFY: требует постоянного соединения  ❌

-- Prepared statements: нужна специальная настройка
PREPARE myplan AS ...;  -- ❌ без pgbouncer.ini: max_prepared_statements

Решения:

; Разрешить prepared statements (PgBouncer 1.21+)
max_prepared_statements = 100

; Или использовать session pool для специфичных подключений
[databases]
myapp_session = host=localhost dbname=myapp pool_mode=session

Настройка max_client_conn

max_client_conn — сколько клиентов может подключиться к PgBouncer
default_pool_size — сколько реальных соединений с PostgreSQL

Формула:
PostgreSQL max_connections = (количество баз × pool_size) + несколько для мониторинга

Пример:
1 база, pool_size=25, 3 пользователя → нужно ~75 соединений в PostgreSQL
max_connections = 100 (с запасом)
max_client_conn = 2000 (сколько приложений подключается)

Итог

PgBouncer — стандартный компонент продакшн-инфраструктуры с PostgreSQL. Добавьте его если у вас > 50 одновременных соединений или заметна деградация при росте нагрузки.