Что такое пул соединений и какие настройки важны для продакшена (pool_size, max_overflow, pool_timeout)?

Что такое пул соединений и зачем он нужен

Создание нового TCP-соединения с PostgreSQL — дорогая операция (несколько десятков миллисекунд, TLS-хендшейк, аутентификация). Пул соединений держит набор уже открытых соединений и выдаёт их запросам по требованию. SQLAlchemy использует QueuePool по умолчанию.

Ключевые параметры

from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

engine = create_engine(
    "postgresql+psycopg2://user:pass@localhost:5432/mydb",
    # Базовое число постоянных соединений в пуле
    pool_size=10,
    # Дополнительные "временные" соединения поверх pool_size
    # Максимум активных соединений = pool_size + max_overflow
    max_overflow=20,
    # Секунд ждать свободное соединение, прежде чем бросить TimeoutError
    pool_timeout=30,
    # Пересоздавать соединение через N секунд (защита от таймаутов БД/NAT)
    pool_recycle=1800,
    # Проверять соединение SELECT 1 перед выдачей из пула
    pool_pre_ping=True,
    # Размер очереди ожидания (по умолчанию равен pool_size + max_overflow)
    # pool_use_lifo=True,  # LIFO: чаще используются "свежие" соединения (прогреты кешем)
)

Как выбрать значения для production

# Правило расчёта:
# max_connections (pg) >= (pool_size + max_overflow) * num_workers
#
# Пример: 4 gunicorn workers, PostgreSQL max_connections=100
# (10 + 10) * 4 = 80 <= 100 — ок
# Оставляем 20 для psql, monitoring, migrations

# Рекомендации по значениям:
# pool_size     = CPU workers * 2 (или = числу core приложения)
# max_overflow  = pool_size * 1-2  ("буфер" для пиков)
# pool_timeout  = 10-30 сек (зависит от SLA)
# pool_recycle  = 1800 (30 мин) — меньше idle_timeout вашей БД
# pool_pre_ping = True — всегда в production

Async Engine: те же параметры

from sqlalchemy.ext.asyncio import create_async_engine

async_engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/mydb",
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
    pool_recycle=1800,
    pool_pre_ping=True,
)

Мониторинг состояния пула

def get_pool_status(engine) -> dict:
    pool = engine.pool
    return {
        "pool_size": pool.size(),
        "checked_out": pool.checkedout(),  # активно используются
        "overflow": pool.overflow(),       # временных сверх pool_size
        "available": pool.checkedin(),     # свободных в пуле
    }

# Для Prometheus/Grafana — экспортируем как gauge-метрики

NullPool: когда пул не нужен

from sqlalchemy.pool import NullPool

# Serverless (AWS Lambda, Cloud Functions) — каждый invocation изолирован
# Alembic migrations — нет смысла держать соединения
# Тесты с изолированными транзакциями
engine = create_engine(DATABASE_URL, poolclass=NullPool)

PgBouncer: пул на уровне инфраструктуры

При многих инстансах приложения (K8s pods) SQLAlchemy-пул в каждом pod умножается на число инстансов. Решение — PgBouncer в transaction pooling mode: он объединяет соединения от всех pods и держит их в пуле на уровне инфраструктуры.

# При использовании PgBouncer в transaction mode
# отключить prepared statements (они не поддерживаются в transaction mode)
engine = create_engine(
    "postgresql+psycopg2://user:pass@pgbouncer:6432/mydb",
    pool_size=5,       # меньше — PgBouncer сам управляет пулом к PostgreSQL
    max_overflow=0,
    connect_args={"options": "-c statement_timeout=30000"},
    execution_options={"no_parameters": True},  # отключить server-side params
)

Подводные камни

• Превышение max_connections PostgreSQL: при горизонтальном масштабировании приложения (10 pods × pool_size=20 = 200 соединений) легко исчерпать лимит. Используйте PgBouncer или снижайте pool_size.
• Забытый pool_recycle: AWS RDS, GCP Cloud SQL и большинство managed-БД закрывают idle-соединения через 8 часов. Без pool_recycle приложение «падает» после ночного простоя с OperationalError: SSL connection has been closed unexpectedly.
• pool_timeout слишком большой: при pool_timeout=60 клиент получает HTTP 504 от nginx раньше, чем SQLAlchemy бросит TimeoutError. Устанавливайте pool_timeout < HTTP timeout.
• Fork-safety: при preforked workers (gunicorn) Engine нужно создавать после форка, иначе несколько процессов делят одни сокеты. Используйте @app.before_serving или создавайте Engine в воркере.
• LIFO vs FIFO: pool_use_lifo=True предпочитает недавно использованные соединения, которые скорее всего ещё «тёплые» (кеши PostgreSQL прогреты). Для высоконагруженных систем это даёт 5-15% прироста производительности.

• Описывать pool settings production только как термин и не показывать механизм на минимальном примере.
• Игнорировать ошибки, пустые данные, конкурентный доступ или границы транзакции.
• Не связывать поведение с официальным контрактом SQLAlchemy и реальной эксплуатацией.

• Объясняет pool settings production через последовательность действий, а не через набор ключевых слов.
• Приводит короткий кодовый пример или production-сценарий с ожидаемым поведением.
• Называет хотя бы один риск: производительность, безопасность, транзакции, память или сопровождение.
• Умеет обсудить отказ, наблюдаемость и rollback без изменения публичного контракта.