Оглавление

• Зачем бизнесу пул соединений и где теряются деньги
• Как работает пул: в приложении и через PgBouncer
• Быстрый старт: PgBouncer + PostgreSQL в docker-compose
• Настройка в приложении: Django и SQLAlchemy (sync/async)
  • Django
  • SQLAlchemy (синхронный драйвер psycopg2)
  • SQLAlchemy (async + asyncpg)
• Как посчитать лимиты и не получить “узкое горлышко”
• Таймауты и защита от зависаний
• Наблюдаемость и метрики: что смотреть каждый день
• Типичные грабли (и как их обойти)
• Кейс-оценка выгоды: минус 70% соединений, минус 30–40% CPU
• Чек‑лист внедрения

Зачем бизнесу пул соединений и где теряются деньги

PostgreSQL создаёт отдельный серверный процесс под каждое клиентское соединение. Каждый такой процесс потребляет память (обычно от 5 до 15 МБ), CPU на переключение контекста и участвует в планировании задач. Когда у вас десятки сервисов, фоновые воркеры, задачи и веб‑процессы, суммарное число соединений легко уходит за сотни. В итоге:

• растёт задержка на запросы и время отклика API;
• база упирается в “Too many connections” в самый неподходящий момент;
• из‑за “висячих” транзакций блокируются VACUUM/автоочистка и растёт таблица;
• облачные счета за CPU/RAM идут вверх, хотя бизнес‑нагрузка не изменилась.

Пул соединений решает сразу три задачи:

1. ограничивает число реальных серверных соединений к PostgreSQL;
2. переиспользует соединения между запросами и транзакциями;
3. стабилизирует задержки при пиках (шипах входящих запросов).

На практике грамотный пул уменьшает число подключений в 5–10 раз и снижает CPU на 30–40% на узлах базы. Это прямые деньги и меньше инцидентов.

Как работает пул: в приложении и через PgBouncer

Есть два уровня пула:

• Пул в приложении/ORM. Например, SQLAlchemy имеет встроенный пул, Django переиспользует соединения в рамках процесса. Это помогает, но не защищает от всплесков при большом числе процессов/воркеров и не даёт кросс‑процессного пула.

• Внешний прокси‑пул — PgBouncer. Он держит ограниченное число серверных соединений к PostgreSQL и распределяет их между большим числом клиентских подключений от приложений.

PgBouncer поддерживает три режима:

• session — одно серверное соединение закрепляется на всё время клиентской сессии;
• transaction — закрепление только на время транзакции (наиболее универсален для веб‑приложений);
• statement — на время одного запроса (самый строгий, но совместим не со всеми паттернами).

Для веб‑API и фоновых задач чаще всего подходит transaction, он даёт лучший баланс между совместимостью и экономией ресурсов.

Быстрый старт: PgBouncer + PostgreSQL в docker-compose

Ниже — минимальная, но рабочая сборка для локального стенда и тестовой среды.

yaml
version: "3.9"
services:
  postgres:
    image: postgres:16
    container_name: pg
    environment:
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: app_pass
      POSTGRES_DB: app
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app_user -d app"]
      interval: 5s
      timeout: 3s
      retries: 10
    volumes:
      - pg_data:/var/lib/postgresql/data

  pgbouncer:
    image: edoburu/pgbouncer:1.23
    container_name: pgb
    depends_on:
      postgres:
        condition: service_healthy
    ports:
      - "6432:6432"
    volumes:
      - ./pgbouncer/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro
      - ./pgbouncer/userlist.txt:/etc/pgbouncer/userlist.txt:ro

volumes:
  pg_data:

Конфигурация PgBouncer:

ini
; ./pgbouncer/pgbouncer.ini
[databases]
app = host=pg port=5432 dbname=app user=app_user password=app_pass

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
;
; Аутентификация. Для локального/тестового стенда оставим plain.
; В проде используйте md5/scram и TLS.
auth_type = plain
auth_file = /etc/pgbouncer/userlist.txt

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

; Ограничения и размеры пулов
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 5

; Таймауты и гигиена соединений
server_idle_timeout = 600
server_lifetime = 3600
query_timeout = 600
idle_transaction_timeout = 60

; Сброс состояния после транзакции
server_reset_query = DISCARD ALL

; Админ-доступ для SHOW команд
admin_users = app_user

log_connections = 1
log_disconnections = 1
stats_period = 60

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

ini
; ./pgbouncer/userlist.txt
"app_user" "app_pass"

Подключаемся:

bash
# Прямо к PgBouncer (порт 6432)
psql "host=127.0.0.1 port=6432 dbname=app user=app_user password=app_pass"

# Консоль PgBouncer (служебная БД "pgbouncer")
psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=app_user password=app_pass" -c "SHOW POOLS;"

Настройка в приложении: Django и SQLAlchemy (sync/async)

Django

Django не содержит полноценного пула, но умеет переиспользовать соединение в процессе. С PgBouncer в режиме transaction используйте такие настройки:

python
# settings.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "app",
        "USER": "app_user",
        "PASSWORD": "app_pass",
        "HOST": "127.0.0.1",
        "PORT": "6432",
        # Закрывать соединение после запроса — PgBouncer сам управляет пулом
        "CONN_MAX_AGE": 0,
        "OPTIONS": {
            "connect_timeout": 5,
            # Лимит на длительность запроса (в мс)
            "options": "-c statement_timeout=5000",
        },
    }
}

# Если используете потоковую выборку .iterator(), отключите серверные курсоры
DATABASES["default"]["DISABLE_SERVER_SIDE_CURSORS"] = True

Совет:

• Длинные транзакции в Django (select_for_update, массовые операции) оборачивайте в явно короткие блоки transaction.atomic().
• Не держите транзакции открытыми на время сетевых вызовов к внешним сервисам.

SQLAlchemy (синхронный драйвер psycopg2)

python
from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+psycopg2://app_user:app_pass@127.0.0.1:6432/app",
    pool_size=5,           # на процесс
    max_overflow=0,        # не раздувать пул
    pool_pre_ping=True,    # проверка соединений перед выдачей
    pool_recycle=1800,     # пересоздавать раз в 30 минут
    connect_args={
        "connect_timeout": 5,
        "options": "-c statement_timeout=5000",
    },
)

with engine.begin() as conn:
    conn.execute(text("CREATE TABLE IF NOT EXISTS t(id serial PRIMARY KEY, v text)"))
    conn.execute(text("INSERT INTO t(v) VALUES (:v)"), {"v": "hello"})
    rows = conn.execute(text("SELECT * FROM t"))
    print(rows.all())

SQLAlchemy (async + asyncpg)

В режиме transaction у PgBouncer нежелательны подготовленные запросы (prepared statements), а в asyncpg они включены по умолчанию. Их стоит отключить:

python
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text

async def main():
    engine = create_async_engine(
        "postgresql+asyncpg://app_user:app_pass@127.0.0.1:6432/app",
        pool_size=5,
        max_overflow=0,
        pool_pre_ping=True,
        connect_args={
            # отключаем кэш подготовленных запросов
            "statement_cache_size": 0,
            "timeout": 5,
        },
    )
    async with engine.begin() as conn:
        await conn.execute(text("SELECT 1"))
    await engine.dispose()

asyncio.run(main())

Как посчитать лимиты и не получить “узкое горлышко”

1. Посчитайте конкурентность на уровне процессов приложения. Пример: 4 сервиса, каждый по 10 воркеров, каждый воркер делает до 5 параллельных обращений к БД → максимум 200 одновременных запросов.

2. Задайте default_pool_size в PgBouncer с запасом 20–30% от этого числа. Например, 260–280. Если вы делите по БД/пользователю, настраивайте по секции [databases] точечно.

3. max_client_conn должен быть больше потенциальных внешних клиентов (все процессы приложений) — например, 1000.

4. На стороне PostgreSQL установите разумный max_connections, например 200–300. Помните: каждый бэкенд — это память. Слишком большое значение съест RAM и ухудшит планирование задач.

5. Оставьте резерв под служебные подключения (админ, бэкапы): reserve_pool_size и пара десятков соединений в max_connections.

Таймауты и защита от зависаний

• На стороне PostgreSQL:
  • idle_in_transaction_session_timeout: убивает “висячие” транзакции.
  • statement_timeout: ограничивает время запросов.

Пример:

sql
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET statement_timeout = '60s';
SELECT pg_reload_conf();

• В PgBouncer:

  • idle_transaction_timeout, query_timeout, server_connect_timeout, server_lifetime.

• На клиенте (ORM/драйвер):

  • connect_timeout и statement_timeout через options.

Все три уровня нужны, чтобы гарантировать ограничение “плохих” случаев и быстро освобождать ресурсы.

Наблюдаемость и метрики: что смотреть каждый день

Проверка пулов в PgBouncer:

sql
-- Подключение к БД pgbouncer
SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

Полезные метрики:

• cl_active / cl_waiting — активные и ожидающие клиенты;
• sv_active / sv_idle — занятые и свободные серверные соединения;
• avg_query_count, avg_query_time — средняя нагрузка и задержка.

На стороне PostgreSQL:

sql
SELECT state, count(*) FROM pg_stat_activity GROUP BY 1;
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
GROUP BY 1, 2
ORDER BY count DESC;

Также стоит собирать:

• число подключений в динамике;
• долю запросов, упёршихся в таймаут;
• длину очереди в PgBouncer (cl_waiting);
• длинные транзакции и блокировки (pg_locks).

Экспорт метрик: pgbouncer‑exporter для Prometheus, готовые дашборды Grafana.

Типичные грабли (и как их обойти)

• LISTEN/NOTIFY. В режиме transaction сессия не фиксирована, поэтому такие сценарии ненадёжны. Используйте session режим для отдельного соединения слушателя или подключайтесь к PostgreSQL в обход PgBouncer для канала уведомлений.

• Временные таблицы и переменные сессии. Они “живут” в рамках сессии, а не транзакции. Избегайте или переходите на session режим для конкретного кода.

• Подготовленные запросы (prepared statements). В transaction режиме они не работают, потому что следующая транзакция может уйти на другой серверный бэкенд. Отключайте кэш подготовленных запросов в драйвере (см. параметр statement_cache_size=0 для asyncpg; для JDBC — prepareThreshold=0; для некоторых ORM — соответствующие флаги).

• Сессионные advisory‑lock’и. В transaction режиме используйте транзакционные варианты (pg_advisory_xact_lock) вместо сессионных.

• Длинные транзакции. Они блокируют автоочистку и удерживают версии строк. Дробите батчи, коммитьте чаще, не делайте сетевые вызовы внутри транзакции.

• Отмена запросов. В некоторых версиях и конфигурациях отмена (cancel) из клиента может не попасть в нужный бэкенд. Проверяйте, что параметры сервиса и PgBouncer согласованы, и тестируйте.

Кейс-оценка выгоды: минус 70% соединений, минус 30–40% CPU

Компания с 5 микросервисами и пиком 1500 RPS держала ~800 соединений к PostgreSQL. Всплески приводили к “Too many connections”, API временами “замерзало”. После внедрения PgBouncer (transaction), пересчёта пулов в приложениях и настройки таймаутов:

• серверных соединений стало 120–150 (–80%);
• CPU на узле БД снизился на 35%;
• p95 задержка запросов уменьшилась с 90 до 45 мс;
• инциденты по блокировкам и “висячим” транзакциям — практически исчезли.

Инфраструктурные расходы сократились за счёт отказа от ещё одного реплики и уменьшения размера инстанса БД.

Чек‑лист внедрения

1. Замерьте текущее число соединений и задержки (pg_stat_activity, метрики драйвера).
2. Разверните PgBouncer рядом с БД или на отдельном узле.
3. Включите режим transaction, задайте default_pool_size и max_client_conn.
4. Настройте приложения: ограничьте пул в процессах, отключите prepared statements там, где нужно, выставьте таймауты.
5. На стороне PostgreSQL включите idle_in_transaction_session_timeout и statement_timeout.
6. Проведите нагрузочное тестирование: проверьте cl_waiting, sv_active, время отклика.
7. Подготовьте дашборды и алёрты (очередь в PgBouncer, запросы с таймаутом, длинные транзакции).
8. Постепенно переключите трафик, наблюдайте, подберите стабильные значения пулов.
9. Документируйте ограничения (LISTEN/NOTIFY, временные таблицы, подготовленные запросы) для команд разработки.

Итог: пул соединений и PgBouncer — это недорогой и быстро внедряемый способ стабилизировать работу БД, ускорить API и снизить затраты. Главное — осознанно выбрать режим пула, выставить лимиты и таймауты и не забыть про наблюдаемость.