Что такое connection pooling в PostgreSQL?

Что такое connection pooling

Каждое соединение с PostgreSQL — это отдельный процесс на стороне сервера (fork от postmaster). Установка соединения требует:

• TCP-handshake
• аутентификации
• выделения памяти (~5–10 MB на соединение)
• инициализации backend-процесса

При высокой нагрузке создание сотен соединений в секунду становится узким местом. Connection pooling решает эту проблему, поддерживая пул «тёплых» соединений и переиспользуя их между клиентами.

Режимы работы пулера

Session mode

Соединение закреплено за клиентом на всё время сессии. Фактически не даёт выигрыша по числу соединений — просто проксирует трафик. Используется для совместимости с приложениями, хранящими состояние в сессии (временные таблицы, SET переменные).

Transaction mode

Соединение выдаётся клиенту только на время транзакции, затем возвращается в пул. Наиболее распространённый режим — даёт максимальный выигрыш. Ограничение: нельзя использовать SET, advisory locks, LISTEN/NOTIFY, подготовленные запросы (prepared statements) без специальных настроек.

Statement mode

Соединение занято только на время одного SQL-выражения. Самый агрессивный режим, запрещает многошаговые транзакции. На практике используется редко.

Популярные реализации

PgBouncer — наиболее распространённый легковесный пулер. Работает как прокси между приложением и PostgreSQL.

Pgpool-II — более функциональный инструмент: пулинг + репликация + балансировка нагрузки + кеширование запросов.

Встроенный пулинг в драйверах (pg, node-postgres) — пул на уровне приложения, проще в настройке, но менее эффективен при нескольких инстансах приложения.

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

; pgbouncer.ini
[pgbouncer]
pool_mode = transaction          ; режим пулинга
max_client_conn = 1000           ; максимум клиентских соединений
default_pool_size = 20           ; соединений к PostgreSQL на базу/пользователя
min_pool_size = 5                ; минимум постоянных соединений
reserve_pool_size = 5            ; резерв при пиковой нагрузке
reserve_pool_timeout = 3         ; секунд ожидания резервного соединения
server_idle_timeout = 600        ; закрыть idle-соединение через N секунд
client_idle_timeout = 0          ; 0 = не закрывать клиентов по таймауту

Пример подключения через пулер (Node.js)

import { Pool } from 'pg';

// Подключаемся к PgBouncer, а не напрямую к PostgreSQL
const pool = new Pool({
  host: 'pgbouncer-host',
  port: 6432,           // стандартный порт PgBouncer
  database: 'mydb',
  user: 'app_user',
  password: process.env.DB_PASSWORD,
  max: 10,              // пул на уровне приложения поверх PgBouncer
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  // При transaction mode нельзя использовать prepared statements
  // node-postgres отправляет их автоматически — отключаем
});

// Правильное использование пула: всегда освобождать соединение
async function getUserById(id: number) {
  const client = await pool.connect();
  try {
    const result = await client.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return result.rows[0];
  } finally {
    client.release(); // возвращаем соединение в пул
  }
}

На что обратить внимание на production

• max_connections в PostgreSQL должен быть чуть больше default_pool_size × количество_баз, а не числа клиентов
• В transaction mode prepared statements требуют prepare_threshold = 0 на клиенте или использования ? вместо $1
• Мониторинг: SHOW POOLS;, SHOW STATS; в консоли PgBouncer
• При Kubernetes-деплое PgBouncer часто разворачивают как sidecar-контейнер рядом с каждым подом приложения