PostgreSQL: HikariCP и connection pooling

PostgreSQL держит один процесс ОС на каждое соединение. Тысяча соединений — тысяча процессов, каждый со своей памятью (~10 MB) и расходом на context switching. Кластер захлёбывается раньше, чем под бизнес-нагрузкой. Connection pool в приложении ограничивает число соединений, переиспользует их между запросами.

Эта статья — про HikariCP (стандарт в Spring Boot) и PgBouncer (внешний пул). Правила пронумерованы кодами PG-CP-NNN — на них ссылается скилл ucp-pg-runtime-review.

1. Размер пула — формула Wooldridge

PG-CP-001 Базовая формула: connections = ((core_count × 2) + effective_spindle_count). Для современных SSD-серверов с N CPU-ядрами оптимум обычно 2N+1 = ~10–20 соединений.

Это контр-интуитивно. «Больше соединений = больше throughput» — миф. На самом деле:

• Контекст-свитч между процессами PG имеет цену.
• Lock contention растёт квадратично от числа конкурентов.
• При размере пула 20 на 8-ядерном сервере throughput может быть выше, чем при 100.

PG-CP-002 Целевой размер пула на инстанс: 10–20. Если кажется, что нужно больше — сначала измерь, потом увеличивай. Часто узкое место — медленные запросы или долгие транзакции, не нехватка пула.

PG-CP-003 Бюджет соединений PostgreSQL = max_connections (default 100). Раздели между всеми инстансами всех сервисов. На 10 инстансов сервиса по 20 соединений — уже 200 (превышает default). Решение — увеличить max_connections (до 300–500 разумно) или PgBouncer (см. §5).

2. HikariCP — основные параметры

PG-CP-010 Минимальная конфигурация в Spring Boot (application.yml):

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 20             # держи равным maximum — иначе pool ramp-up на холодную
      connection-timeout: 3000     # 3 сек ждать соединение
      idle-timeout: 600000         # 10 мин до закрытия idle (с min-idle = max не сработает)
      max-lifetime: 1800000        # 30 мин — обновлять соединения
      leak-detection-threshold: 60000  # 60 сек — алёрт на утечку
      validation-timeout: 5000
      auto-commit: false           # см. §3

PG-CP-011 maximum-pool-size = minimum-idle — пусть пул всегда полный. Динамика «то 5, то 20» создаёт latency-всплески при ramp-up на пиках.

PG-CP-012 connection-timeout: 3 сек — если за 3 секунды не дали соединение, лучше упасть, чем держать поток. На веб-фронте таймаут запроса обычно 30 сек, но 3 сек ожидания пула — индикатор перегрузки, нужна реакция.

PG-CP-013 max-lifetime: 30 мин — каждое соединение через полчаса закрывается и пересоздаётся. Это защищает от утечек памяти на стороне PG (подросший work_mem, накопившиеся prepared statements) и от «тихой» поломки соединений за load balancer'ом.

Должен быть меньше, чем server-side idle_in_transaction_session_timeout и timeout-ы балансировщиков.

PG-CP-014 leak-detection-threshold: 60 сек — HikariCP залогирует stack trace, если соединение не вернулось в пул за минуту. Признак забытого close(), @Transactional вокруг долгого HTTP-вызова, висящей транзакции.

PG-CP-015 Не отключай leak-detection! Это бесплатный мониторинг утечек.

3. auto-commit — нюансы со Spring

PG-CP-020 auto-commit = false в Spring Boot — обычно правильно. Spring управляет транзакциями сам через @Transactional. Соединение берётся из пула, открывается транзакция, после метода — commit/rollback.

PG-CP-021 Если включить auto-commit = true: каждый отдельный statement автокоммитится сразу. Spring ставит autocommit в false перед @Transactional методом и возвращает в true после. Это требует доп. round-trip к БД на каждый borrow и return соединения. На high-throughput заметная latency.

В Spring Boot по умолчанию auto-commit = true (HikariCP default). Меняешь в конфиге — но проверь, что код не делает явные setAutoCommit(false) без откатки.

4. Diagnostic — как понять «пул маленький / пул протекает»

PG-CP-030 Метрики HikariCP в Micrometer:

management:
  metrics:
    enable:
      hikaricp: true

Что мониторить:

• hikaricp.connections.active — занято.
• hikaricp.connections.idle — свободно.
• hikaricp.connections.pending — потоки, ждущие соединение (если > 0 на постоянке — пул мал).
• hikaricp.connections.usage — гистограмма «сколько секунд держали».
• hikaricp.connections.timeout — счётчик таймаутов connection-timeout.

PG-CP-031 Если pending > 0 стабильно — увеличь пул или сократи длительность транзакций.

PG-CP-032 Если usage p99 > секунды — где-то долгие транзакции. Long @Transactional на HTTP-вызов, забытый commit, лок-wait.

PG-CP-033 Утечки соединений — leak-detection-threshold + alert на hikaricp.connections.timeout.

5. PgBouncer — внешний пулер

PG-CP-040 PgBouncer оправдан, когда:

• Много инстансов одного сервиса (десятки), и каждому нужны соединения.
• Много разных сервисов на один кластер PG.
• Serverless / lambda — частые коротко-живущие воркеры, каждому открывать соединение к PG дорого.
• Нужно ограничить общее число соединений к PG ниже суммы всех app-pool'ов.

PgBouncer — лёгкий процесс, обслуживает тысячи клиент-соединений на маленьком числе real-соединений к PG.

5.1. Уровни pooling

PG-CP-041 PgBouncer поддерживает три уровня:

Уровень	Когда соединение возвращается в пул	Что не работает
session (default)	После disconnect клиента	Никаких ограничений
transaction	После каждого COMMIT/ROLLBACK	Prepared statements (на client side), session vars (SET LOCAL ok), advisory locks (sessionном scope), LISTEN/NOTIFY, WITH HOLD cursors
statement	После каждого SQL-statement	Транзакции из >1 statement, prepared statements, всё то же

PG-CP-042 transaction — обычный выбор. Даёт максимальную утилизацию пула при минимуме ограничений. Spring Boot + jOOQ нормально с ним работают.

PG-CP-043 session — когда нужны server-side prepared statements или LISTEN/NOTIFY. Менее эффективен, ближе к «никакого пулинга».

PG-CP-044 statement — почти никогда. Ломает даже очень простые транзакции.

5.2. Ограничения transaction-level

PG-CP-045 На transaction мode не работают server-side prepared statements. В Spring/JDBC по умолчанию HikariCP использует prepareThreshold > 0 — после 5 выполнений statement становится server-side prepared. Через PgBouncer этот state теряется.

Решение: на стороне приложения отключить server-side prepares для PgBouncer:

spring:
  datasource:
    hikari:
      data-source-properties:
        prepareThreshold: 0   # отключить server-side prepared

Или PgBouncer 1.21+ умеет prepared-statements caching сам. Проверь конкретную версию.

PG-CP-046 SET команды (без LOCAL) не работают. Любые SET work_mem = '64MB' теряются после COMMIT. Используй SET LOCAL внутри транзакции.

PG-CP-047 LISTEN/NOTIFY не работает на transaction mode — connection не сохраняется между транзакциями. Если нужен PG NOTIFY — отдельный пул в session mode или другой механизм (Kafka, Redis pub/sub).

5.3. Размер пулов с PgBouncer

PG-CP-050 С PgBouncer пул в HikariCP может быть БОЛЬШЕ. Например, у каждого инстанса HikariCP пул 50, но PgBouncer держит к PG только 20 real-соединений. Эффект: 50 потоков могут одновременно «думать в коде», но к PG идёт максимум 20.

Соотношение: app_pool : pgbouncer_to_pg = 5:1 или больше.

# pgbouncer.ini
[databases]
mydb = host=postgres-master port=5432 dbname=mydb pool_mode=transaction

[pgbouncer]
pool_mode = transaction
default_pool_size = 20      # к PG
max_client_conn = 1000      # от приложений
reserve_pool_size = 5

6. Read-replica — отдельный пул

PG-CP-060 Если есть read-replica — отдельный DataSource + отдельный HikariCP пул.

@Configuration
public class DataSourceConfig {
    @Bean @Primary
    @ConfigurationProperties("spring.datasource.master")
    public HikariConfig masterConfig() { return new HikariConfig(); }

    @Bean
    @ConfigurationProperties("spring.datasource.replica")
    public HikariConfig replicaConfig() { return new HikariConfig(); }

    @Bean @Primary
    public DataSource masterDataSource(HikariConfig masterConfig) {
        return new HikariDataSource(masterConfig);
    }

    @Bean
    public DataSource replicaDataSource(HikariConfig replicaConfig) {
        return new HikariDataSource(replicaConfig);
    }

    @Bean
    public DataSource routingDataSource(DataSource masterDataSource, DataSource replicaDataSource) {
        var routing = new TransactionRoutingDataSource();
        routing.setTargetDataSources(Map.of(
            DataSourceType.READ_WRITE, masterDataSource,
            DataSourceType.READ_ONLY,  replicaDataSource
        ));
        routing.setDefaultTargetDataSource(masterDataSource);
        return routing;
    }
}

С AbstractRoutingDataSource Spring может выбирать DataSource на основе TransactionSynchronizationManager.isCurrentTransactionReadOnly() — @Transactional(readOnly = true) отправляется на реплику, остальное — на мастер.

PG-CP-061 Read-replica запросы — eventual consistency. Replication lag — обычно миллисекунды, но при нагрузке может расти до секунд. Не используй реплику для read-after-write сценариев («только что создал, сразу читаю»).

7. Антипаттерны

PG-CP-080 Огромный пул (maximum-pool-size = 200) — почти всегда пеcсимизация, не оптимизация. Сначала найди узкое место.

PG-CP-081 Разные пулы на одну БД для одного приложения — каждый пул думает, что владеет всеми соединениями, в сумме перерасход.

PG-CP-082 @Transactional вокруг внешнего HTTP-вызова — соединение из пула удерживается на всё время вызова. См. WAL §8 и PG-W-061.

PG-CP-083 Отключение leak-detection-threshold — не «решение проблемы», а её сокрытие.

PG-CP-084 auto-commit = true глобально с явным @Transactional — лишний round-trip на каждый borrow.

PG-CP-085 PgBouncer на session mode без явной причины — теряется главный выигрыш (большая утилизация).

PG-CP-086 PgBouncer на transaction mode + server-side prepared statements — теряется prepared cache, JDBC начинает дёргать PG на каждом запросе.

8. Чек-лист настройки

• [ ] maximum-pool-size = minimum-idle = 10–20 на инстанс приложения.
• [ ] connection-timeout = 3 сек.
• [ ] max-lifetime = 30 мин (меньше LB-таймаута).
• [ ] leak-detection-threshold = 60 сек включен.
• [ ] HikariCP-метрики экспортированы в Micrometer.
• [ ] Алёрт на hikaricp.connections.pending > 0 в течение минуты.
• [ ] Алёрт на hikaricp.connections.timeout count > 0.
• [ ] max_connections PG ≥ сумме всех пулов всех инстансов всех сервисов.
• [ ] При наличии PgBouncer — выбран transaction mode (или явно обоснован session).
• [ ] При PgBouncer + JDBC: prepareThreshold = 0 или PgBouncer 1.21+ с native prepared support.
• [ ] Read-replica — отдельный пул, отдельный DataSource, routing через @Transactional(readOnly = true).

---

Связанные

• WAL — длинные транзакции = занятые connections + блокировка autovacuum.
• Блокировки — лок-wait увеличивает время удержания соединения.
• Антипаттерны.