Настройка 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 режимі
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 режимі. Оновлюємо 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, тощо
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), настройку моніторингу та тести під навантаженням.