Настройка PgBouncer (connection pooling) для веб-приложения

Настройка PgBouncer (connection pooling) для веб-приложения

PostgreSQL не умеет эффективно держать тысячи одновременных соединений. Каждое соединение — это отдельный процесс с ~5–10 МБ памяти. 500 соединений = до 5 ГБ только на overhead. PgBouncer решает это, мультиплексируя соединения приложения в небольшой пул реальных соединений к PostgreSQL.

Режимы пулинга

Session pooling — соединение PostgreSQL выделяется клиенту на всё время его сессии. Минимальные ограничения на SQL, но экономия минимальная — если приложение держит соединения открытыми.

Transaction pooling — соединение PostgreSQL возвращается в пул после каждой транзакции. Максимальная эффективность. Ограничения: нельзя использовать prepared statements, SET вне транзакции, advisory locks, LISTEN/NOTIFY.

Statement pooling — после каждого запроса. Почти никогда не нужен.

Для веб-приложений: transaction pooling.

Установка

# Ubuntu/Debian
apt-get install pgbouncer

# или Docker
docker run -d \
    -e DATABASE_URL="postgresql://app:pass@postgres:5432/mydb" \
    -e POOL_MODE=transaction \
    -e MAX_CLIENT_CONN=1000 \
    -e DEFAULT_POOL_SIZE=20 \
    -p 5432:5432 \
    edoburu/pgbouncer

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

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb user=app password=secret

; Или через DSN:
; mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

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

; Максимум клиентских соединений (app → pgbouncer)
max_client_conn = 1000

; Размер пула на базу+пользователь (pgbouncer → postgres)
default_pool_size = 20

; Минимум соединений в пуле (держим "тёплыми")
min_pool_size = 5

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

; Таймауты
server_connect_timeout = 15
server_login_retry = 15
query_timeout = 0           ; 0 = без ограничения (ограничивать на уровне PG)
query_wait_timeout = 120    ; ждать свободное соединение из пула
client_idle_timeout = 0

; Закрывать серверное соединение после N транзакций (предотвращает memory bloat в PG)
server_lifetime = 3600
server_idle_timeout = 600

; Логирование
log_connections = 0         ; в production выключаем, иначе flooding
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60

; Admin interface
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

userlist.txt

"app" "SCRAM-SHA-256$4096:...hash..."
"pgbouncer_admin" "md5hashhere"

Хеши получаем из PostgreSQL:

SELECT usename, passwd FROM pg_shadow WHERE usename = 'app';

Или используем auth_query — PgBouncer сам запрашивает пароль у PostgreSQL:

; pgbouncer.ini
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

-- В PostgreSQL
CREATE ROLE pgbouncer_auth WITH LOGIN PASSWORD 'authpass';
GRANT SELECT ON pg_shadow TO pgbouncer_auth;

Prepared statements в transaction mode

Transaction pooling несовместим с prepared statements на уровне протокола. Решения:

1. Отключить в ORM/драйвере:

# SQLAlchemy
engine = create_engine(dsn, connect_args={"prepare_threshold": None})

# asyncpg
conn = await asyncpg.connect(dsn, statement_cache_size=0)

// GORM + pgx
db, err := gorm.Open(postgres.New(postgres.Config{
    DSN: dsn,
    PreferSimpleProtocol: true,  // отключает extended query protocol
}), cfg)

// node-postgres
const pool = new Pool({ max: 10, statement_timeout: 30000 });
// pg по умолчанию не кеширует prepared statements в Pool

2. PgBouncer 1.21+ поддерживает protocol-level prepared statements в transaction mode. Обновляем PgBouncer и не меняем приложение.

Мониторинг

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

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

SHOW POOLS;
-- Видим: database, user, cl_active, cl_waiting, sv_active, sv_idle, sv_used, maxwait

SHOW CLIENTS;
SHOW SERVERS;
SHOW STATS;
-- total_query_count, total_query_time, avg_query_time, etc.

SHOW CONFIG;
RELOAD;  -- перечитать конфиг без рестарта

cl_waiting > 0 продолжительное время — пул слишком мал, увеличить default_pool_size. sv_idle близко к default_pool_size — пул избыточен, уменьшить.

Метрики для Prometheus

pip install prometheus-pgbouncer-exporter

# docker-compose.yml
pgbouncer-exporter:
  image: spreaker/prometheus-pgbouncer-exporter
  environment:
    PGBOUNCER_HOST: pgbouncer
    PGBOUNCER_PORT: 6432
    PGBOUNCER_USER: pgbouncer_stats
    PGBOUNCER_PASSWORD: statspass
  ports:
    - "9127:9127"

Ключевые метрики: pgbouncer_pools_cl_waiting, pgbouncer_pools_sv_active, pgbouncer_stats_avg_query_time.

Топология для production

App pods (100 инстансов)
    ↓ 5 соединений на pod
PgBouncer (2 инстанса, HAProxy перед ними)
    ↓ 20 соединений к primary, 10 к replica
PostgreSQL Primary + Replica

500 соединений от приложения → 30 реальных соединений к PostgreSQL. Экономия памяти: ~2.3 ГБ.

Сроки

Установка и настройка PgBouncer для существующего приложения: полдня–1 день. Включает конфигурацию, адаптацию драйвера приложения (отключение prepared statements), настройку мониторинга и тесты под нагрузкой.