Модуль: Базы данных · Уровень: Senior

TL;DR#

• Установка TCP+TLS соединения и форк backend-процесса в PostgreSQL — дорогая операция (десятки мс, мегабайты RAM на backend). Пул переиспользует физические соединения, амортизируя стоимость.
• PostgreSQL — process-per-connection: каждое соединение это отдельный ОС-процесс. max_connections ограничен сверху, и реальная пропускная способность падает задолго до лимита из-за конкуренции за CPU, lock manager и shared buffers.
• database/sql даёт встроенный пул: SetMaxOpenConns (жёсткий потолок), SetMaxIdleConns (сколько держать свободными), SetConnMaxLifetime (ротация), SetConnMaxIdleTime (закрытие простаивающих). Метрики — db.Stats().
• pgx имеет два режима: native API (pgx.Conn, pgxpool.Pool) с extended-протоколом и кэшем prepared statements, и совместимый database/sql драйвер (stdlib).
• pgxpool — production-grade пул pgx: MaxConns, MinConns, MaxConnLifetime, MaxConnIdleTime, HealthCheckPeriod, lifecycle-хуки.
• PgBouncer — внешний пулер. В transaction mode ломаются: серверные prepared statements, session-level advisory locks, SET, LISTEN/NOTIFY, temp tables, WITH HOLD cursors. Лечится DefaultQueryExecMode = simple protocol или statement_cache_capacity=0 (на новых PgBouncer >= 1.21 есть серверный prepared statement support).
• Размер пула считается, а не «ставится побольше». Базовая формула: connections ≈ ((core_count * 2) + effective_spindle_count). Больше соединений почти всегда хуже из-за context switching.
• Утечки соединений — главный production-инцидент: незакрытые Rows, забытый Close() у транзакции, отсутствие context с таймаутом. Пул исчерпывается, запросы виснут на ожидании свободного соединения.

Теория#

Зачем вообще пул: цена соединения в PostgreSQL#

PostgreSQL использует модель process-per-connection. Каждое новое клиентское соединение приводит к тому, что главный процесс postmaster делает fork(), создавая отдельный backend-процесс. Это влечёт:

1. Стоимость установки (latency). TCP-handshake, опционально TLS-handshake, аутентификация (SCRAM-SHA-256 — несколько round-trip’ов), затем форк процесса и инициализация его памяти. На реальной сети это легко 5–50 мс. Для запроса, который сам выполняется 1 мс, это катастрофа.

2. Стоимость памяти. Каждый backend имеет собственный work_mem, кэши, каталоговый кэш (relcache/catcache), prepared statements. Идле-backend «весит» порядка нескольких МБ резидентной памяти; под нагрузкой с большими work_mem — кратно больше.

3. Стоимость на стороне ядра БД. Lock manager, snapshot management (GetSnapshotData исторически O(N) по числу активных backend’ов — заметно улучшено в PG 14), процессы вроде autovacuum конкурируют за CPU. Чем больше backend’ов, тем дороже служебная работа.

4. max_connections. Жёсткий потолок (по умолчанию 100). Превышение — клиент получает FATAL: sorry, too many clients already. Поднятие max_connections не бесплатно: резервируется память под структуры синхронизации (semaphores, lock table) пропорционально лимиту.

Пул решает это, переиспользуя уже установленные физические соединения между логическими операциями приложения. Соединение открывается один раз и обслуживает тысячи запросов.

Ключевая мысль для senior: пул нужен не «чтобы было быстрее», а чтобы (а) убрать latency установки из горячего пути и (б) ограничить число одновременных backend’ов величиной, которую БД реально переваривает без деградации.

Пул в database/sql#

*sql.DB — это не одно соединение, а пул. Частая ошибка джунов: открывать sql.Open на каждый запрос. sql.Open создаёт пул и не открывает физических соединений сразу (ленивая инициализация при первом запросе или Ping).

db, err := sql.Open("pgx", dsn) // pgx через stdlib-драйвер
if err != nil {
    return err
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

if err := db.PingContext(ctx); err != nil { // здесь реально откроется соединение
    return err
}

Параметры:

Тонкости:

• MaxIdleConns > MaxOpenConns бессмысленно — drvier сам подрежет idle до open.
• Если MaxIdleConns < MaxOpenConns, под пиком соединения постоянно открываются/закрываются («churn») — anti-pattern. Частая рекомендация: MaxIdleConns == MaxOpenConns.
• ConnMaxLifetime не закрывает соединение мгновенно посреди запроса — проверка делается при возврате в пул.
• Когда все соединения заняты, новый запрос блокируется до освобождения или отмены через context. Это не ошибка — это backpressure.

Метрики db.Stats() (тип sql.DBStats):

s := db.Stats()
log.Printf("open=%d inUse=%d idle=%d wait=%d waitDur=%s maxIdleClosed=%d maxLifeClosed=%d",
    s.OpenConnections, s.InUse, s.Idle,
    s.WaitCount, s.WaitDuration,
    s.MaxIdleClosed, s.MaxLifetimeClosed)

Эти метрики — первое, что смотрит senior при разборе «база тормозит»: высокий WaitCount/WaitDuration означает, что узкое место — размер пула или медленные запросы, держащие соединения.

pgx и pgxpool#

pgx (jackc/pgx) — самый используемый Go-драйвер PostgreSQL. Два способа использования:

1. Через database/sql (github.com/jackc/pgx/v5/stdlib): pgx как драйвер за стандартным интерфейсом. Получаете встроенный пул database/sql, но теряете часть нативных возможностей (батчи, CopyFrom, богатые типы pgx, тонкий контроль протокола).

2. Native API: pgx.Conn (одно соединение) или pgxpool.Pool (пул). Полный доступ к фичам pgx.

Чем native pgx отличается от database/sql:

• Использует extended query protocol по умолчанию (раздельные Parse/Bind/Execute), а не текстовую подстановку. Параметры передаются типизированно, защита от SQL-инъекций на уровне протокола.
• Богатая система типов: pgtype (массивы, jsonb, numeric, inet, ranges, composite types, hstore) — то, что database/sql отдаёт как []byte.
• Нативные Batch (pipelining), CopyFrom (быстрая массовая загрузка через COPY protocol), LISTEN/NOTIFY.
• Автоматический кэш prepared statements на каждом соединении.

Режимы выполнения запросов (QueryExecMode):

Simple protocol важен для совместимости с PgBouncer в transaction mode (см. ниже).

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

import (
    "context"
    "time"
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

func newPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
    cfg, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, err
    }

    cfg.MaxConns = 25                          // верхний предел соединений
    cfg.MinConns = 5                           // минимум держим тёплыми
    cfg.MaxConnLifetime = 30 * time.Minute     // полная ротация соединения
    cfg.MaxConnLifetimeJitter = 5 * time.Minute // разброс, чтобы не закрывать все разом
    cfg.MaxConnIdleTime = 5 * time.Minute      // закрытие простаивающих сверх MinConns
    cfg.HealthCheckPeriod = 1 * time.Minute    // период фоновой проверки/поддержки MinConns

    // Тонкая настройка соединения
    cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeCacheStatement

    // Lifecycle-хуки
    cfg.BeforeAcquire = func(ctx context.Context, c *pgx.Conn) bool {
        return true // false — выбросить соединение из пула перед выдачей
    }
    cfg.AfterRelease = func(c *pgx.Conn) bool {
        return true // false — не возвращать в пул, закрыть
    }
    cfg.BeforeClose = func(c *pgx.Conn) {
        // метрики / логирование
    }

    return pgxpool.NewWithConfig(ctx, cfg)
}

Использование:

// Пул сам берёт и возвращает соединение для одиночных операций
var n int
err := pool.QueryRow(ctx, "SELECT count(*) FROM users WHERE active = $1", true).Scan(&n)

// Если нужно несколько операций на одном соединении (например, SET, advisory lock):
conn, err := pool.Acquire(ctx)
if err != nil { return err }
defer conn.Release() // КРИТИЧНО: вернуть соединение в пул
_, err = conn.Exec(ctx, "SET LOCAL statement_timeout = '5s'")

MaxConnLifetimeJitter — фича, которой нет в database/sql: предотвращает «thundering herd» одновременного переподключения всех соединений (важно за балансировщиком/при failover).

Prepared statement cache. В режиме CacheStatement pgx при первом выполнении запроса делает PREPARE на соединении и переиспользует его. Кэш — per-connection (LRU). Это даёт:

• экономию на повторном парсинге/планировании на стороне сервера;
• но: prepared statement привязан к конкретному backend, поэтому не переживает transaction-pooling в PgBouncer.

PgBouncer: внешний пулер#

Даже с пулом в приложении возникает проблема: при множестве инстансов приложения (10 подов × 25 соединений = 250) суммарное число соединений к БД превышает разумное. PgBouncer — лёгкий внешний пулер, который мультиплексирует множество клиентских соединений на небольшой набор серверных.

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

transaction mode — самый популярный: позволяет сотням клиентов делить десяток серверных соединений, потому что между транзакциями соединение свободно.

Что ломается в transaction mode (потому что клиент не «владеет» одним серверным backend’ом постоянно):

1. Серверные prepared statements. PREPARE на одном backend, EXECUTE может попасть на другой → ошибка prepared statement "..." does not exist. Это самая частая боль pgx + PgBouncer.
2. Session-level advisory locks (pg_advisory_lock) — лок привязан к сессии backend’а, который вы потеряете после COMMIT. Используйте транзакционные advisory locks (pg_advisory_xact_lock).
3. SET / SET SESSION — настройки сессии (search_path, statement_timeout, timezone) теряются. Используйте SET LOCAL внутри транзакции или options в DSN.
4. LISTEN/NOTIFY — LISTEN это session-state; не работает через transaction pooling. Для notify-канала держите выделенное session-mode соединение (отдельный порт/пул).
5. Temp tables (CREATE TEMP TABLE) — живут в сессии backend’а, исчезают/недоступны на другом.
6. WITH HOLD курсоры, незакрытые курсоры между транзакциями.
7. WITH HOLD/unnamed portals, DEALLOCATE, любая логика, полагающаяся на постоянство backend’а.

Как с этим жить (pgx + PgBouncer transaction mode):

Вариант А — отключить серверные prepared statements в pgx:

cfg, _ := pgxpool.ParseConfig(dsn)
// Самый надёжный способ для PgBouncer transaction mode (старые версии):
cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
// Альтернатива — extended-протокол без серверного кэша имён:
// cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeExec

В DSN это же выражается параметром:

postgres://user:pass@pgbouncer:6432/db?default_query_exec_mode=simple_protocol

(в старом pgx v4 был флаг prefer_simple_protocol=true; в v5 — default_query_exec_mode).

Вариант Б — для PgBouncer >= 1.21 появилась поддержка prepared statements в transaction mode (max_prepared_statements > 0 в конфиге PgBouncer). Тогда можно оставить QueryExecModeCacheStatement. Это предпочтительный современный путь, т.к. сохраняет выигрыш от prepared statements.

Замечание про statement_cache: в старом pgx (lib/pq стиль) фигурировал statement_cache_mode/statement_cache_capacity. В pgx v5 эквивалент — выбор QueryExecMode. «Выключить кэш» = QueryExecModeExec/QueryExecModeSimpleProtocol.

PgBouncer (.ini) для transaction mode:

[databases]
mydb = host=postgres port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000      ; сколько клиентов принимаем
default_pool_size = 20      ; серверных соединений на пару (user,db)
reserve_pool_size = 5
server_idle_timeout = 600
max_prepared_statements = 200  ; >=1.21, чтобы PS работали в transaction mode

Расчёт размера пула#

Главный контринтуитивный факт: больше соединений ≠ больше throughput. За пределами некоторой точки добавление соединений снижает производительность из-за context switching, конкуренции за блокировки и cache thrashing. CPU может одновременно реально выполнять работу только для числа потоков ≈ числу ядер.

Формула из документации PgBouncer / wiki HikariCP (эмпирическая база):

connections = ((core_count * 2) + effective_spindle_count)

• core_count — физические ядра (не hyperthread’ы).
• effective_spindle_count — грубо число дисков, способных обслуживать одновременный I/O (для SSD/облака — оценивается отдельно, часто берут небольшое число или возможность параллельного I/O).

Для сервера с 8 ядрами и SSD это даёт ориентир ~20–30 соединений — и это часто оптимум даже для высоконагруженного сервиса. Маленький пул, обрабатывающий запросы быстро, обгоняет большой пул, тонущий в контеншене.

Распределение по инстансам. Если 10 подов приложения, а суммарно к БД должно идти ≤ 25 активных соединений, то либо MaxConns ≈ 2-3 на под (мало, churn), либо ставите PgBouncer и каждый под держит свои соединения к PgBouncer, а тот мультиплексирует на 25 серверных. Поэтому в Kubernetes-окружениях PgBouncer почти обязателен.

Под нагрузкой:

• MinConns (pgx) / достаточный MaxIdleConns — чтобы не платить latency установки на каждом всплеске.
• MaxConnLifetime с jitter — чтобы соединения ротировались и не «застревали» на упавшей реплике после failover.
• statement_timeout / lock_timeout на стороне БД — чтобы один зависший запрос не держал соединение пула вечно.

Утечки соединений и context cancellation#

Утечка соединения = соединение взято из пула и не возвращено. Пул исчерпывается, новые запросы виснут на ожидании. Источники:

// ❌ Утечка: Rows не закрыты — соединение держится до GC (а его может и не быть)
rows, _ := db.QueryContext(ctx, "SELECT ...")
for rows.Next() { /* ... return из середины без rows.Close() */ }

// ✅ Всегда defer rows.Close() и проверка rows.Err()
rows, err := db.QueryContext(ctx, "SELECT ...")
if err != nil { return err }
defer rows.Close()
for rows.Next() { /* scan */ }
return rows.Err()

// ❌ Утечка транзакции: нет Rollback при ошибке/панике
tx, _ := db.BeginTx(ctx, nil)
// ... если return до Commit без Rollback — соединение занято

// ✅ defer rollback (после Commit он no-op / даёт ErrTxDone — игнорируем)
tx, err := db.BeginTx(ctx, nil)
if err != nil { return err }
defer tx.Rollback()
// ... do work ...
return tx.Commit()

// ❌ pgxpool: забыли Release
conn, _ := pool.Acquire(ctx)
// ... return без conn.Release()

// ✅
conn, err := pool.Acquire(ctx)
if err != nil { return err }
defer conn.Release()

Context cancellation — критичная связка с пулом:

• Всегда передавайте context с дедлайном/таймаутом в запросы. Отмена контекста прерывает ожидание свободного соединения и отменяет выполняющийся запрос (драйвер шлёт cancel-request на сервер).
• Без таймаута: медленный/зависший запрос держит соединение, под нагрузкой это каскадом исчерпывает пул → весь сервис стоит.
• Нюанс: при отмене контекста во время активного запроса драйвер должен «починить» соединение (или закрыть его), что само по себе стоит ресурсов — слишком агрессивные таймауты при медленной БД приводят к churn соединений.

ctx, cancel := context.WithTimeout(r.Context(), 3*time.Second)
defer cancel()
row := pool.QueryRow(ctx, "SELECT ...")

Подводные камни / gotchas#

• sql.Open на каждый запрос. Это создаёт новый пул каждый раз. *sql.DB — долгоживущий синглтон на приложение.
• MaxOpenConns = 0 (дефолт). Без лимита приложение под нагрузкой выест max_connections и положит БД для всех. Всегда ставьте лимит.
• MaxIdleConns по умолчанию = 2. При MaxOpenConns=50 это вызывает постоянный churn под нагрузкой. Выравнивайте idle с open.
• prepared statements + PgBouncer transaction mode. Самая частая прод-ошибка с pgx: prepared statement "lrupsc_..." does not exist. Лечение — simple protocol или PgBouncer ≥ 1.21 с max_prepared_statements.
• SET search_path через PgBouncer transaction mode молча «теряется» между транзакциями. Используйте SET LOCAL или options=-c search_path=... в DSN.
• pg_advisory_lock (session-level) в transaction pooling — лок не освобождается ожидаемо / не виден. Берите pg_advisory_xact_lock.
• Незакрытые Rows / отсутствие defer Close() — медленная утечка, проявляется только под нагрузкой. То же для pgxpool.Acquire без Release().
• Отсутствие context с таймаутом — зависший запрос держит соединение неограниченно. Обязательно statement_timeout на стороне БД как страховка.
• ConnMaxLifetime = 0 за балансировщиком/прокси. Соединения «протухают», вы получаете connection reset после простоя. Ставьте разумный lifetime.
• Большой пул «для запаса». 200 соединений к 8-ядерной БД медленнее, чем 25. Контеншен растёт нелинейно.
• Слишком много инстансов × локальный пул без PgBouncer → суммарно тысячи соединений к БД.
• MinConns без HealthCheckPeriod — pgxpool не будет активно поддерживать минимум; health-check досоздаёт и проверяет соединения.
• db.Stats() игнорируют. Без экспорта WaitCount/WaitDuration в метрики невозможно понять, что пул — узкое место.

Вопросы на собеседовании#

В: Почему пул соединений в PostgreSQL критичнее, чем, скажем, в MySQL/потоковых СУБД? О: Потому что PostgreSQL использует process-per-connection: каждое соединение — отдельный ОС-процесс, созданный через fork() postmaster’ом. Это дорого по памяти (несколько МБ на backend + work_mem), по latency установки (handshake + аутентификация + форк), и нагружает ядро (lock manager, снапшоты). Пул амортизирует это и ограничивает число одновременных backend’ов.

В: Что делают SetMaxIdleConns и SetConnMaxLifetime и какие значения вы ставите? О: SetMaxIdleConns — сколько свободных соединений держать готовыми; обычно равняю с MaxOpenConns, чтобы избежать churn под нагрузкой. SetConnMaxLifetime — максимальный возраст соединения, после чего оно закрывается при возврате в пул; ставлю 15–30 мин для ротации и устойчивости к failover за балансировщиком. Дополнительно SetConnMaxIdleTime, чтобы освобождать ресурсы в тихие периоды.

В: По каким метрикам вы поймёте, что пул — узкое место? О: Из db.Stats(): растущие WaitCount и WaitDuration означают, что запросы ждут свободное соединение. Если OpenConnections постоянно упирается в MaxOpenConnections — пул мал или запросы держат соединения слишком долго (медленные запросы/утечки). Высокий MaxIdleClosed — churn, надо поднять idle.

В: В чём разница между native pgx и использованием pgx через database/sql? О: Native pgx (pgxpool.Pool) даёт extended-протокол по умолчанию, богатую систему типов pgtype (jsonb, массивы, numeric, ranges), батчи/pipelining, CopyFrom, LISTEN/NOTIFY, кэш prepared statements и lifecycle-хуки. Через database/sql (драйвер stdlib) вы получаете стандартный интерфейс и его пул, но теряете часть нативных возможностей и тонкий контроль над протоколом.

В: Что ломается при использовании prepared statements за PgBouncer в transaction mode и почему? О: В transaction mode серверное соединение возвращается в пул после каждой транзакции, поэтому клиент не привязан к одному backend’у. Серверный prepared statement живёт на конкретном backend; EXECUTE может попасть на другой → prepared statement does not exist. Решения: переключить pgx на simple protocol (DefaultQueryExecMode = QueryExecModeSimpleProtocol) или QueryExecModeExec; либо использовать PgBouncer ≥ 1.21 с max_prepared_statements > 0, где пулер сам управляет PS.

В: Что ещё, кроме prepared statements, ломается в transaction pooling и как обходите? О: Session-level advisory locks (pg_advisory_lock) → беру pg_advisory_xact_lock. SET/search_path теряется между транзакциями → SET LOCAL или options в DSN. LISTEN/NOTIFY не работает → выделенное session-mode соединение. Temp tables и WITH HOLD курсоры — тоже session-state, недоступны на другом backend.

В: Как вы рассчитываете размер пула? Почему не «поставить побольше»? О: Ориентир — эмпирическая формула ((core_count*2) + effective_spindle_count), для 8-ядерной БД с SSD это ~20–30. Больше соединений увеличивает context switching, конкуренцию за блокировки и cache thrashing — throughput падает. CPU реально параллелит работу примерно на число ядер; «лишние» соединения только создают очередь внутри БД вместо очереди в пуле, где ей и место.

В: Как утечка соединений выглядит в проде и как её предотвратить? О: Симптом: запросы начинают виснуть на ожидании соединения, WaitCount/WaitDuration взлетают, при этом БД не загружена. Причины: незакрытые Rows, забытый tx.Rollback()/conn.Release(), отсутствие таймаута у контекста. Предотвращение: всегда defer rows.Close(), defer tx.Rollback(), defer conn.Release(); контекст с дедлайном на каждый запрос; statement_timeout/lock_timeout на стороне БД как страховка.

В: Зачем MaxConnLifetimeJitter в pgxpool? О: Чтобы избежать «thundering herd»: без jitter все соединения, открытые примерно одновременно, истекут одновременно и одновременно переподключатся, создав всплеск нагрузки на БД и downtime-окно (особенно болезненно после failover за балансировщиком). Jitter размазывает ротацию во времени.

На что копают на senior+#

• Process-per-connection и его следствия: почему высокий max_connections сам по себе вреден (память под lock table/semaphores, стоимость снапшотов до PG 14, autovacuum контеншен). Знание улучшений GetSnapshotData в PG 14.
• Архитектура многоуровневого пулинга: app pool (pgxpool) → PgBouncer (transaction) → PostgreSQL; как считать суммарное число серверных соединений при N инстансах в Kubernetes; почему локальные пулы плохо масштабируются без внешнего пулера.
• Глубокое понимание PgBouncer modes и полного списка session-state, который ломается в transaction mode, с конкретными обходами (xact advisory locks, SET LOCAL, выделенный LISTEN-канал, серверный PS-support в 1.21+).
• Extended vs simple protocol на уровне wire-протокола: Parse/Bind/Describe/Execute, что значит unnamed statement, как pgx именует prepared statements (lrupsc_*), почему simple protocol совместим с PgBouncer но теряет типизацию/PS-кэш.
• Поведение пула при отмене контекста: что происходит с соединением, когда context отменяется во время активного запроса (cancel request на сервер, возможное закрытие/«починка» соединения), баланс между агрессивными таймаутами и churn.
• Tuning под конкретную нагрузку: связь размера пула, statement_timeout, work_mem (помните: work_mem × число одновременных сортировок/хэшей × соединений = потенциальный OOM), и почему ограничение пула косвенно ограничивает пиковое потребление памяти БД.
• Наблюдаемость: экспорт db.Stats() / pool.Stat() в Prometheus, алерты на WaitDuration, AcquireWaitTime (pgxpool), корреляция с p99 latency запросов.
• Failover и health-checks: как MaxConnLifetime + jitter + HealthCheckPeriod помогают пулу «отпустить» соединения к упавшей primary/реплике, взаимодействие с target_session_attrs и read/write splitting.