Connection pooling в PostgreSQL — HikariCP, PgBouncer и размер пула

← назад к разделу

Когда приложение открывает соединение с PostgreSQL, база не просто принимает TCP-пакет — она запускает отдельный процесс операционной системы. Пока соединение простаивает, своей памяти такой процесс ест немного: замер на PostgreSQL 17 показал, что пятнадцать лишних соединений добавили базе около 21 МБ — примерно 1,4 МБ на соединение. В top цифра выглядит куда страшнее, но там к каждому процессу приписаны общие страницы shared_buffers, одни и те же на всех, и суммировать их по процессам нельзя.

Настоящий счёт начинается, когда соединение работает. Каждая сортировка и каждый хеш внутри запроса может взять до work_mem (по умолчанию 4 МБ) — причём не на запрос, а на узел плана: у запроса с двумя сортировками и хеш-соединением таких порций три. Двести одновременных запросов умножают это на двести. Плюс переключение контекста между сотнями процессов. Вот почему 200 соединений — заметная нагрузка даже на мощном сервере.

Пул соединений решает эту проблему: он держит фиксированное число открытых соединений и раздаёт их запросам по мере надобности.

Почему «больше соединений» не значит «быстрее»

Интуитивно кажется: больше соединений — больше параллельной работы — выше пропускная способность. На практике это не так.

PostgreSQL обрабатывает запросы параллельно, но узкое место — не количество соединений, а количество ядер процессора. Переключение между сотнями процессов стоит дорого, а конкуренция за блокировки растёт квадратично с числом одновременных работников.

В документации HikariCP — самого распространённого пула в мире Java — приводят формулу, пришедшую из вики самого PostgreSQL:

connections = (число ядер × 2) + effective_spindle_count

Второе слагаемое переводят как «число дисковых устройств», и это неточность, которая портит весь расчёт. В оригинале стоит effective_spindle_count — число шпинделей вращающихся дисков: идея была в том, что пока один процесс ждёт, когда головка доедет до нужной дорожки, другой может считать со второго диска. У SSD никаких шпинделей нет, и та же вики HikariCP отдельно оговаривает, что на SSD формула не проверялась и размер надо подбирать замером.

Для сервера с 4 ядрами и обычным массивом из 8 дисков получится:

connections = (4 × 2) + 8 = 16

На SSD формула даёт только нижнюю границу — ядра × 2, то есть 8, — а дальше размер ищут нагрузочным тестом.

На практике рабочий диапазон — 10–20 соединений на инстанс приложения. Пул из 20 соединений на 8-ядерном сервере даёт больший итоговый пропуск, чем пул из 100.

Бюджет max_connections

PostgreSQL имеет параметр max_connections (по умолчанию 100) — это общий лимит на все соединения со всеми базами. Если у вас 10 инстансов приложения по 20 соединений, итого 200 — уже выше дефолта. Нужно либо увеличить max_connections до 300–500, либо поставить PgBouncer.

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

Четыре параметра важны вне зависимости от языка и библиотеки, и у каждого своя беда, которую он предотвращает: разогрев пула под нагрузкой, зависший на минуты запрос, соединение, которое сервер уже закрыл, и утечка соединений из кода.

max = min-idle (держать пул всегда полным)

Если установить минимум 5 и максимум 20, пул будет подниматься с 5 до 20 в момент всплеска нагрузки. Эти несколько секунд «разогрева» добавляют задержку как раз тогда, когда нагрузка уже высокая. Проще: держать постоянное число соединений равным максимуму.

В HikariCP это к тому же поведение по умолчанию — незаданный minimumIdle равен maximumPoolSize. Прописывают его не чтобы что-то изменить, а чтобы намерение было видно в конфиге. И раз минимум сравнялся с максимумом, пул стал фиксированным: закрывать простаивающие соединения больше некому, и idle-timeout перестаёт действовать вовсе. Это не поломка, а следствие — просто не ждите от него ничего, пока минимум не опустят ниже максимума.

connection-timeout: 3s (лучше упасть быстро)

Если все соединения заняты, запрос встаёт в очередь. 3 секунды ожидания — хороший порог: если пул не смог выдать соединение за это время, что-то идёт не так. Быстрый отказ лучше, чем тихое зависание.

max-lifetime: 30 мин (обновлять соединения)

Соединение со временем накапливает состояние на стороне сервера: кешированные запросы, изменённые параметры сессии. Через 30 минут пул закроет соединение и откроет новое. Это значение должно быть меньше таймаутов в балансировщике нагрузки — иначе пул будет держать соединения, которые балансировщик уже считает мёртвыми.

leak-detection-threshold: 60s (обнаруживать утечки)

Если соединение не вернулось в пул за минуту, пул запишет в лог трассировку стека того потока, который это соединение взял (это делает именно HikariCP, не JDBC-драйвер — драйвер про пул ничего не знает). Это признак одной из трёх вещей: забыли закрыть соединение, внутри транзакции вызывается медленный HTTP-запрос, транзакция зависла. Не отключайте этот параметр — это бесплатный мониторинг.

Единственный случай, когда пул надо увеличивать

Общий совет — держать пул небольшим, и он верен ровно до одного исключения: когда одна бизнес-операция берёт два соединения одновременно. Так бывает при REQUIRES_NEW внутри транзакции, при работе с двумя базами в одном сценарии или при ручном получении второго соединения для служебного запроса.

Тогда маленький пул встаёт намертво. Десять потоков взяли по одному соединению из десяти и каждый ждёт второго; освободить его может только тот, кто сам стоит в этой очереди. Это не таймаут запроса, это тупик: приложение не разблокируется само.

Формула для безопасного минимума известна: пул ≥ Tn × (Cm − 1) + 1, где Tn — сколько таких операций может идти одновременно, Cm — сколько соединений одна операция берёт. Десять одновременных операций по два соединения требуют пула не меньше одиннадцати. Плюс единица в конце и есть гарантия: всегда найдётся один поток, который сможет доработать и освободить остальных.

Практический вывод: если вложенные транзакции в коде есть, пул считают по формуле, а не по ядрам. Если их нет — не заводите, и тогда работает обычное правило про 10–20 соединений.

Что видит приложение, когда пул кончился

Про это стоит договориться до аварии, а не во время. Когда за connection-timeout соединение не нашлось, HikariCP бросает SQLTransientConnectionException с текстом вида «HikariPool-1 - Connection is not available, request timed out after 3000ms», и в Spring он превращается в CannotGetJdbcConnectionException. То же самое в других стеках выглядит как ошибка получения соединения из пула, а не как ошибка базы: база в этот момент может быть совершенно здорова.

Отсюда три правила. Первое: такая ошибка — это 503, а не 500: сервис временно не может обслужить, клиенту имеет смысл повторить позже. Второе: connection-timeout должен быть заметно меньше таймаута HTTP-запроса и таймаута вызывающей стороны — иначе клиент отвалится раньше, чем ваш сервис успеет ответить внятной ошибкой, и в логах останется загадка. Третье: отдельная метрика и оповещение именно на этот класс ошибок; смешанный со всеми 500 он теряется, а означает он вполне конкретную вещь — либо утечку, либо слишком долгие транзакции.

Арифметика масштабирования

Пул живёт в процессе, а процессов бывает много, и max_connections считается от их числа. Десять подов по двадцать соединений — это двести соединений к базе; поды выросли до сорока под нагрузкой — стало восемьсот, и база с max_connections = 500 начала отказывать в подключении всем, включая мониторинг и резервное копирование.

Считать надо по максимуму, а не по обычному числу: максимум подов × размер пула × число сервисов + запас на обслуживание. И решение принимать заранее, потому что вариантов всего три. Ограничить число подов сверху (maxReplicas в автомасштабировании) и заложить их в расчёт. Уменьшить пул до реально нужного — при коротких транзакциях пять соединений на под часто хватает. Или поставить PgBouncer, который превратит тысячу клиентских соединений в пятьдесят серверных, — и тогда вопрос числа подов перестаёт касаться базы.

Отдельная арифметика — superuser_reserved_connections (по умолчанию 3) и reserved_connections: эти места база держит для себя, и рассчитывать на них нельзя.

keepalive-time и мёртвые соединения

max-lifetime обновляет соединения по возрасту, но не спасает от соединения, которое умерло молча. Так бывает, когда между приложением и базой стоит преобразователь адресов или межсетевой экран: он выбрасывает запись о простаивающем соединении через несколько минут, и следующий запрос уходит в никуда — приложение ждёт ответа до таймаута операционной системы, а это могут быть минуты.

Лечится двумя способами, и обычно применяют оба. Со стороны пула — keepalive-time (в HikariCP; по умолчанию выключен): пул периодически проверяет простаивающие соединения дешёвым запросом, и мёртвое заменяет заранее, до того как его выдаст приложению. Значение берут меньше таймаута преобразователя адресов, обычно 30–60 секунд, и оно должно быть меньше max-lifetime. Со стороны соединения — параметры проверки живости TCP у драйвера (tcpKeepAlive=true в JDBC и tcp_keepalives_idle на сервере): они не дают простаивающему соединению выглядеть мёртвым для сетевого оборудования.

Признак, по которому это узнают: запросы, зависающие ровно после периода тишины (ночь, обед), и исчезающие после перезапуска приложения.

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

Пул публикует метрики, которые стоит отслеживать:

• active — сколько соединений занято прямо сейчас.
• idle — сколько свободно.
• pending — сколько потоков ждут соединения. Если это число стабильно больше нуля — пул слишком мал или транзакции слишком долгие.
• timeout — сколько раз соединение не выдалось за отведённое время; растёт при утечке или нехватке пула.

Оповещение ставят на две последние: pending > 0 дольше минуты и любой рост timeout.

HikariCP экспортирует метрики через Micrometer (Spring Actuator). В Go используют pgxpool.Stat(), в Node — pool.totalCount / pool.idleCount / pool.waitingCount, в Python — pool.get_stats().

Когда нужен PgBouncer

Пул на уровне приложения хорошо работает для одного сервиса с несколькими инстансами. Но если у вас:

• десятки инстансов одного сервиса,
• много разных сервисов на одном кластере PostgreSQL,
• функции без постоянного состояния (serverless, короткоживущие процессы),

— суммарное число соединений от всех инстансов начинает давить на max_connections. Здесь помогает PgBouncer: он стоит между приложением и PostgreSQL и мультиплексирует тысячи клиентских соединений в десятки реальных.

Режимы работы PgBouncer

transaction — обычный выбор. Он даёт максимальную утилизацию при минимуме ограничений.

Ограничения transaction-режима целиком

Список в таблице выше сокращён, а выстреливают обычно тем, чего в нём нет. В transaction-режиме соединение возвращается в общий пул после каждой транзакции — значит, всё, что живёт в сессии, а не в транзакции, перестаёт работать предсказуемо.

Сессионный advisory-замок (pg_advisory_lock без _xact) — самый частый способ выстрелить себе в ногу: замок остаётся на серверном соединении, которое уехало к другому клиенту, а тот, кто его брал, снять уже не может. Транзакционная форма pg_advisory_xact_lock работает нормально, потому что снимается на коммите.

Курсор WITH HOLD, который должен жить после коммита, теряется вместе с соединением. Временные таблицы (CREATE TEMP TABLE) создаются на сессии — следующий запрос той же логики может прийти на другое серверное соединение и таблицы не найти. Ненадёжны и SET без LOCAL, LISTEN/NOTIFY и серверные подготовленные запросы (последние лечатся настройкой max_prepared_statements в PgBouncer 1.21+ или отключением подготовки на стороне драйвера).

Правило простое: в transaction-режиме сессия — это одна транзакция, и всё, что должно пережить коммит, туда не помещается.

Потолок самого PgBouncer

PgBouncer однопоточный: один процесс использует одно ядро, сколько бы их ни было на машине. Обычно это не мешает — он лёгкий, — но на десятках тысяч запросов в секунду он упирается сам и становится узким местом, причём незаметно: очередь растёт, а база недогружена.

Выхода два. Запустить несколько процессов PgBouncer на одном порту через so_reuseport (параметр so_reuseport = 1 плюс несколько процессов) — ядро распределит соединения между ними. Или взять многопоточную замену: Odyssey от Яндекса, pgcat, pgagroal — они решают ту же задачу и умеют использовать все ядра.

И отдельное следствие однопоточности: PgBouncer сам становится точкой отказа на пути к базе. Его ставят либо рядом с приложением (по одному на под), либо парой с плавающим адресом — но не одним экземпляром на весь кластер.

Read-реплика

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

Частые ошибки

Слишком большой пул. Двести соединений на четырёхъядерном сервере работают хуже двадцати: время уходит на переключение контекста. Ориентир прежний — 10–20 на инстанс.

Несколько пулов на одну базу. Если разные части приложения заводят свои пулы к одной базе, соединения складываются. Один пул на приложение.

Долгая операция внутри транзакции. Запрос к внешнему сервису, обработка файла, долгий цикл — всё это внутри открытой транзакции держит соединение занятым. Тяжёлое выносят за границы транзакции.

Нет предела со стороны базы. Все таймауты пула ограничивают ожидание в приложении, но не останавливают запрос, который уже работает на сервере. Отрубить его может только база, и для этого есть два параметра, которые в продакшене выставляют всегда. statement_timeout ограничивает один запрос: ALTER ROLE app SET statement_timeout = '30s' — и запрос, который завис на полчаса, будет снят сервером сам. idle_in_transaction_session_timeout ограничивает открытую и простаивающую транзакцию: ALTER ROLE app SET idle_in_transaction_session_timeout = '30s' — и забытая транзакция, которая держит соединение и мешает уборке, оборвётся вместе с сессией. Оба ставят на роль приложения, а не глобально: для миграций и резервного копирования нужны другие значения, и их задают отдельным ролям.

Реплика для сценария «записал — сразу читаю». Из-за задержки репликации свежая запись может не успеть появиться на реплике. Такие чтения идут на мастер.

Глубже: Механика пула и конфигурация по языкурасширенное

Ниже те же четыре параметра в деле: сначала механика пула на одном классе Java, потом конфигурация в четырёх библиотеках.

Пул целиком — на одном классе

Механику пула видно и без базы. Semaphore здесь — разрешения на занятые соединения, tryAcquire с таймаутом — очередь ожидания и тот самый connection-timeout.

живой пример

import java.util.concurrent.Semaphore;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

public class PoolDemo {
    static final Semaphore free = new Semaphore(2, true);
    static final AtomicInteger served = new AtomicInteger();
    static final AtomicInteger refused = new AtomicInteger();

    public static void main(String[] args) throws Exception {
        Thread[] requests = new Thread[6];
        for (int i = 0; i < requests.length; i++) {
            requests[i] = new Thread(PoolDemo::query);
            requests[i].start();
        }
        for (Thread t : requests) {
            t.join();
        }
        System.out.println("пул 2 соединения, запросов 6, ждём не дольше 300 мс");
        System.out.println("получили соединение: " + served.get());
        System.out.println("отказ по таймауту:   " + refused.get());
    }

    static void query() {
        try {
            if (!free.tryAcquire(300, TimeUnit.MILLISECONDS)) {
                refused.incrementAndGet();
                return;
            }
            try {
                Thread.sleep(200);
                served.incrementAndGet();
            } finally {
                free.release();
            }
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}

Два соединения, шесть одновременных запросов, каждый держит соединение 200 мс. Четверым его хватает: двое берут сразу, двое дожидаются освобождения. Последней паре ждать пришлось бы 400 мс — это дольше отведённых 300, и они получают отказ. Настоящий пул ведёт себя так же: сверх лимита соединение не появится, запрос ждёт в очереди и по таймауту получает отказ.

Конфигурация по языку

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 20
      connection-timeout: 3000
      max-lifetime: 1800000
      leak-detection-threshold: 60000
      auto-commit: false

HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);
config.setMinimumIdle(20);
config.setConnectionTimeout(3_000);
config.setMaxLifetime(1_800_000);
config.setLeakDetectionThreshold(60_000);
config.setAutoCommit(false);

DataSource ds = new HikariDataSource(config);

cfg, _ := pgxpool.ParseConfig("postgres://app:secret@localhost:5432/mydb")

cfg.MaxConns = 20
cfg.MinConns = 20
cfg.MaxConnLifetime = 30 * time.Minute
cfg.MaxConnIdleTime = 10 * time.Minute
cfg.HealthCheckPeriod = 30 * time.Second

// connection-timeout задаётся на уровне контекста запроса:
// ctx, cancel := context.WithTimeout(ctx, 3*time.Second)

pool, _ := pgxpool.NewWithConfig(context.Background(), cfg)

import { Pool } from "pg";

const pool = new Pool({
  max: 20,
  min: 20,
  idleTimeoutMillis: 600_000,
  connectionTimeoutMillis: 3_000,
});

from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    conninfo="host=localhost port=5432 dbname=mydb user=app password=secret",
    min_size=20,
    max_size=20,
    timeout=3.0,
    max_lifetime=1800.0,
    max_idle=600.0,
    open=True,
)

Глубже: PgBouncer в режиме transactionрасширенное

У режима transaction есть следствие, которое ломает приложение неожиданно, и типовая конфигурация самого PgBouncer — разберём оба.

Prepared statements и transaction mode

Сервер PostgreSQL хранит подготовленный запрос до конца сессии — с этим всё в порядке. Ломается другое: в режиме transaction соединение после каждого COMMIT уходит другому клиенту, и на следующей транзакции ваш драйвер получит уже другое серверное соединение, на котором нужного подготовленного запроса нет. Драйвер сошлётся на него по имени и получит prepared statement does not exist. Поэтому server-side prepared statements отключают на уровне драйвера.

spring:
  datasource:
    hikari:
      data-source-properties:
        prepareThreshold: 0

cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

// node-postgres по умолчанию не использует server-side prepared statements
// при вызове pool.query() — дополнительных действий не нужно.

pool = ConnectionPool(
    conninfo="...",
    kwargs={"prepare_threshold": None},  # None отключает; 0 — наоборот, готовить сразу
)

Альтернатива — PgBouncer: с версии 1.24 он кеширует prepared statements сам, max_prepared_statements по умолчанию 200. В 1.21–1.23 эта поддержка была, но выключенной.

Также: команды SET без SET LOCAL теряются после COMMIT. Для LISTEN/NOTIFY нужен отдельный пул в режиме session или другой механизм (очередь сообщений).

Пример конфигурации PgBouncer

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

[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
reserve_pool_size = 5

Типичное соотношение: приложение держит пул 50 соединений к PgBouncer, PgBouncer держит 20 реальных соединений к PostgreSQL. Пятьдесят потоков приложения могут «думать» параллельно, к базе идёт максимум 20.

Глубже: Маршрутизация на реплику в кодерасширенное

Выбор между мастером и репликой делается в коде приложения, и по языкам он выглядит так.

public enum DataSourceType { READ_WRITE, READ_ONLY }

public class TransactionRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                ? DataSourceType.READ_ONLY
                : DataSourceType.READ_WRITE;
    }
}

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("app.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("app.datasource.replica")
    public DataSource replicaDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DataSource dataSource(@Qualifier("masterDataSource") DataSource master,
                                 @Qualifier("replicaDataSource") DataSource replica) {
        var routing = new TransactionRoutingDataSource();
        routing.setTargetDataSources(Map.<Object, Object>of(
            DataSourceType.READ_WRITE, master,
            DataSourceType.READ_ONLY,  replica
        ));
        routing.setDefaultTargetDataSource(master);
        routing.afterPropertiesSet();
        return new LazyConnectionDataSourceProxy(routing);
    }
}

type DB struct {
    master  *pgxpool.Pool
    replica *pgxpool.Pool
}

func (db *DB) Pool(readOnly bool) *pgxpool.Pool {
    if readOnly {
        return db.replica
    }
    return db.master
}

const master  = new Pool({ host: "pg-master",  ...config });
const replica = new Pool({ host: "pg-replica", ...config });

export function getPool(readOnly: boolean): Pool {
    return readOnly ? replica : master;
}

master  = ConnectionPool(conninfo="host=pg-master  ...", min_size=20, max_size=20)
replica = ConnectionPool(conninfo="host=pg-replica ...", min_size=20, max_size=20)

def get_pool(read_only: bool) -> ConnectionPool:
    return replica if read_only else master

Коротко

• PostgreSQL запускает отдельный процесс ОС на каждое соединение. Простаивающее стоит около 1,4 МБ; платить приходится за work_mem (по умолчанию 4 МБ) на каждую сортировку и хеш работающего запроса и за переключение контекста между сотнями процессов.
• Больше соединений не значит быстрее: узкое место — ядра, а не соединения. Формула (ядра × 2) + effective_spindle_count считает шпиндели вращающихся дисков; на SSD остаётся только нижняя граница ядра × 2, дальше размер подбирают замером. Рабочий диапазон — 10–20 соединений на инстанс.
• max_connections (по умолчанию 100) — общий лимит на кластер, и считают его по максимуму: поды × пул × сервисы плюс запас на обслуживание. Десять подов по 20 соединений лимит уже превышают, а автомасштабирование до сорока кладёт базу: либо потолок на число подов, либо меньший пул, либо PgBouncer.
• max = min-idle — пул всегда полный, без разогрева на пике. В HikariCP это поведение по умолчанию, а idle-timeout при равенстве минимума и максимума не действует вовсе.
• connection-timeout: 3s — быстрый отказ вместо тихого зависания; max-lifetime: 30 мин держат меньше таймаута балансировщика; leak-detection-threshold: 60s не отключают — это бесплатный поиск утечек.
• Из метрик пула оповещение ставят на pending > 0 дольше минуты и на любой рост timeout.
• PgBouncer нужен, когда инстансов десятки или сервисов на кластере много, и он однопоточный — на больших потоках его запускают несколькими процессами через so_reuseport или берут Odyssey либо pgcat. Режим transaction — обычный выбор, но в нём теряется всё сессионное: подготовленные запросы, SET без LOCAL, LISTEN/NOTIFY, курсоры WITH HOLD, временные таблицы и сессионные advisory-замки.
• Реплике для чтения нужен свой пул, и сценарий «записал — сразу читаю» на неё не отправляют. Маршрутизацию по readOnly в Spring пишут руками: AbstractRoutingDataSource плюс обязательная обёртка LazyConnectionDataSourceProxy.
• Пул увеличивают в одном случае: когда операция берёт два соединения сразу — тогда пул ≥ Tn × (Cm − 1) + 1, иначе тупик без таймаута.
• Исчерпание пула — это 503 и своя метрика; connection-timeout держат меньше таймаута HTTP, а со стороны базы обязательны statement_timeout и idle_in_transaction_session_timeout.

Что почитать дальше

• Транзакции в PostgreSQL — как длинные транзакции удваивают нагрузку на пул.
• Уровни изоляции — readOnly и маршрутизация на реплику.
• Блокировки — lock_timeout и долгие транзакции.
• Репликация — откуда берётся задержка реплики, на которую нельзя полагаться после записи.