Right-Sizing Database Connection Pools: From Guesswork to Capacity Budgeting

Distinguish Three Upper Limits

PostgreSQL's max_connections is the database hard limit; the application pool's maximumPoolSize is the per-process limit. The total connections the database may face equals the sum of all application instances, background jobs, admin tools, and monitoring connections. An experience formula should be expressed as a budget constraint, not a fixed constant:

per-instance pool upper bound ≤ floor((database allocatable connections - admin reserve - other workloads - safety margin) / peak application instances)

Check database version and connection parameters:

SELECT version();
SHOW max_connections;
SHOW superuser_reserved_connections;
SHOW shared_buffers;

superuser_reserved_connections

is only reserved when ordinary slots are exhausted and cannot be used for application capacity. Changing max_connections usually requires a PostgreSQL restart and alters the memory budget; prefer reducing invalid connections over simply increasing the limit.

Count current connections by user, database, and state:

SELECT datname, usename, application_name, state, count(*) AS connections
FROM pg_stat_activity
GROUP BY datname, usename, application_name, state
ORDER BY connections DESC;

idle

means the session is idle but still consumes resources; idle in transaction is more dangerous as it may hold snapshots or locks. Ensure application_name is configured to attribute connections to specific instances.

Identify long-running and idle transactions:

SELECT pid, usename, application_name, client_addr, state,
       now() - xact_start AS xact_age,
       now() - state_change AS state_age,
       wait_event_type, wait_event,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

Do not immediately pg_terminate_backend just because there are many connections. Terminating rolls back transactions and affects requests; first identify the owner, transaction impact, and retry semantics.

Set Initial Values Based on Workload, Not CPU Cores

The common "2× cores + disk count" is only a rough reminder for concurrent execution, not a direct per-instance pool size. Queries with external RPC, lock waits, long transactions, or heavy I/O occupy connections far longer than CPU time; instance scaling amplifies total pool capacity.

First derive arrival rate and database occupancy time from business metrics. By Little's Law, average concurrency ≈ throughput × average occupancy time. Capacity planning should use peak windows and percentiles, retaining explainable headroom. Example: 80 DB ops/sec per instance, 40 ms average occupancy → average concurrency 3.2. But tail latency, multi-statement transactions, and bursts raise required pool capacity. Start load testing with small values like 8–16 instead of jumping to 100 per instance.

Check actual vs. desired Kubernetes replicas:

kubectl -n <namespace> get deployment <deployment> \
  -o jsonpath='{.spec.replicas}{" desired, "}{.status.readyReplicas}{" ready
"}'
kubectl -n <namespace> get hpa

The budget must use the peak replica count that can be reached, including HPA maxReplicas, rolling surge, and parallel jobs — not just current ready count.

Extract HPA limits and rolling strategy:

kubectl -n <namespace> get hpa <hpa-name> \
  -o jsonpath='{.spec.minReplicas}{" "}{.spec.maxReplicas}{"
"}'
kubectl -n <namespace> get deployment <deployment> \
  -o jsonpath='{.spec.strategy.rollingUpdate.maxSurge}{" "}{.spec.strategy.rollingUpdate.maxUnavailable}{"
"}'

Percentage maxSurge is rounded based on replica count; verify against actual Kubernetes pod creation behavior.

Build a Connection Budget Table

Assume database ordinary connection capacity = 300, reserve 20 for admin/failure handling, other services budget 80, safety margin 40 → target application gets 160. If peak instances (including surge) = 12, per-instance theoretical upper bound = 13. Start with 10–12 and observe pool wait and database saturation.

Use a script to make assumptions explicit and avoid manual errors:

#!/usr/bin/env bash
set -euo pipefail

DB_MAX="<max-db-connections>"
RESERVED="<admin-reserve>"
OTHER="<other-workload-budget>"
HEADROOM="<safety-margin>"
PEAK_INSTANCES="<instance-count>"

available=$((DB_MAX - RESERVED - OTHER - HEADROOM))
if (( available <= 0 || PEAK_INSTANCES <= 0 )); then
  echo "invalid capacity assumptions" >&2
  exit 1
fi
printf 'application_budget=%d
per_instance_upper_bound=%d
' \
  "${available}" "$((available / PEAK_INSTANCES))"

The result is a constraint upper bound, not the performance optimum. Final value is still subject to query concurrency, transaction duration, database CPU and I/O limits.

Configure HikariCP

Spring Boot 3 base configuration:

spring:
  datasource:
    url: jdbc:postgresql://<db-host>:<db-port>/<db-name>?ApplicationName=<app-name>
    username: <app-user>
    hikari:
      maximum-pool-size: 12
      minimum-idle: 4
      connection-timeout: 2000
      validation-timeout: 1000
      idle-timeout: 600000
      max-lifetime: 1800000
      keepalive-time: 120000
      pool-name: orders-db

connection-timeout

is the max milliseconds waiting for a pool connection, not JDBC query timeout. max-lifetime should be shorter than load balancer, NAT, or database proxy max connection age, plus HikariCP's built-in random decay. keepalive-time must be less than max-lifetime. Validate applicability against current HikariCP version docs and startup logs.

Set statement and transaction timeouts at the database role level to prevent indefinite connection occupation:

ALTER ROLE <app-user> IN DATABASE <db-name>
SET statement_timeout = '5s';
ALTER ROLE <app-user> IN DATABASE <db-name>
SET idle_in_transaction_session_timeout = '30s';

These affect only new sessions. Confirm no legitimate long queries/transactions exist before applying; too short causes cancellations and failures. Rollback with ALTER ROLE ... RESET statement_timeout and RESET idle_in_transaction_session_timeout.

View role-level settings:

SELECT rolname, rolconfig FROM pg_roles WHERE rolname = '<app-user>';

If database, role, and session settings coexist, verify final value by opening a new application connection and running SHOW statement_timeout.

Observe Pool Queueing

Spring Boot Actuator with Micrometer exposes Hikari metrics. Open endpoint only on controlled network:

management:
  endpoints:
    web:
      exposure:
        include: health,prometheus
  endpoint:
    health:
      probes:
        enabled: true

Key metrics typically include active, idle, pending, max, min, and acquire time; exact names depend on Micrometer/HikariCP exposure. Confirm endpoint content directly:

kubectl -n <namespace> port-forward deployment/<deployment> 18080:8080
curl -fsS http://127.0.0.1:18080/actuator/prometheus \
  | grep -E '^hikaricp_connections(_active|_idle|_pending|_max|_min|_acquire)'

Port forwarding occupies the foreground terminal; Ctrl-C to stop. Replace 8080 if container port differs.

Query pending connection count (Prometheus metric names per actual exporter):

max_over_time(hikaricp_connections_pending{pool="orders-db"}[5m])

Occasional transient pending does not mandate pool expansion; sustained pending with database CPU, I/O, lock, and connection headroom supports increasing the pool.

Observe active connections as fraction of max:

max_over_time(hikaricp_connections_active{pool="orders-db"}[5m])
/
max_over_time(hikaricp_connections_max{pool="orders-db"}[5m])

Ratio near 1 only means pool is full; combine with acquisition latency and database state. If full pool stems from slow SQL or lock waits, expanding the pool amplifies the problem.

Observe connection acquisition latency (histogram suffix varies by Micrometer version/config; check actual exposed metrics):

histogram_quantile(
  0.95,
  sum by (le) (rate(hikaricp_connections_acquire_seconds_bucket{pool="orders-db"}[5m]))
)

If no buckets, only summary/count/sum may be exposed; check raw endpoint instead of inventing queries.

Determine If Database Is Saturated

Database connection usage via postgres_exporter (metric names per actual exporter):

sum(pg_stat_activity_count{datname="<db-name>"})
/
scalar(pg_settings_max_connections)

Exporter labels and metrics differ; retain admin connections and other database budgets — do not alert only at 100%.

View wait events on database side:

SELECT wait_event_type, wait_event, state, count(*)
FROM pg_stat_activity
WHERE datname = '<db-name>'
GROUP BY wait_event_type, wait_event, state
ORDER BY count(*) DESC;

Many Lock events indicate lock contention; ClientRead idle connections usually wait for client; I/O waits need storage metrics. Wait events indicate direction, not sole root cause.

Find blocking chains:

SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       now() - blocker.xact_start AS blocker_xact_age,
       left(blocked.query, 120) AS blocked_query,
       left(blocker.query, 120) AS blocker_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocker ON blocker.pid = b.pid;

Only terminate after confirming blocker, transaction content, business impact, and rollback cost. Prefer letting application finish transactions normally.

Check database process and system resources:

pidstat -u -r -d -p "$(head -n 1 < /var/lib/pgsql/15/data/postmaster.pid)" 1 10
iostat -xz 1 10
vmstat 1 10

Path suits RHEL PGDG layout; adjust to actual data_directory. Single sample insufficient for long-term saturation proof; align with same time-window application metrics.

Slow SQL and Execution Plans

Enable pg_stat_statements via shared_preload_libraries and restart — high-risk change; validate in pre-production, backup config, schedule maintenance window. If enabled, query total and average execution time:

SELECT queryid, calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 180) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = '<db-name>')
ORDER BY total_exec_time DESC
LIMIT 20;

Statistics are cumulative; compare pre/post release by recording reset points or analyzing monitoring increments. Do not run EXPLAIN ANALYZE directly on write statements in production — it actually executes them.

For read-only queries, set protective timeout before getting plan:

BEGIN;
SET LOCAL statement_timeout = '3s';
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT <columns> FROM <table> WHERE <condition>;
ROLLBACK;

Placeholders must be replaced with reviewed read-only SQL. ANALYZE executes the query and generates load even if rolled back; test on replica or pre-production first.

Leaks and Long-Holding Connections

Connection leaks typically show as active continuously rising, pending increasing, without corresponding request growth. HikariCP can temporarily set leak detection threshold:

spring:
  datasource:
    hikari:
      leak-detection-threshold: 10000

Unit is milliseconds. Threshold must exceed normal longest transaction, else false positives and stack logs flood; remove after identification to avoid log noise and overhead.

Filter application logs:

kubectl -n <namespace> logs deployment/<deployment> --since=30m \
  | grep -E 'Connection is not available|Apparent connection leak detected|SQLTransientConnectionException|timeout'

Logs must correlate with pod, version, request volume, and database timeline. Single timeout may stem from release restart or network blip.

Load Testing and Incremental Tuning

Connection pool tuning should be done in controlled load-test environment with fixed data scale, SQL, instance count, JVM params, and database resources. Change one variable per round (e.g., pool size 8, 12, 16); record throughput, end-to-end p95/p99, pool wait, database CPU, I/O, lock waits, error rate. Do not search limits by unlimited production stress.

Script to collect per-round database snapshot:

#!/usr/bin/env bash
set -euo pipefail

DB_URL="postgresql://<app-user>@<db-host>:<db-port>/<db-name>"
OUT_DIR="<collection-dir>"
mkdir -p "${OUT_DIR}"
stamp="$(date -u +%Y%m%dT%H%M%SZ)"

psql "${DB_URL}" -X -v ON_ERROR_STOP=1 -c \
  "SELECT state, wait_event_type, wait_event, count(*) FROM pg_stat_activity GROUP BY 1,2,3 ORDER BY 4 DESC" \
  > "${OUT_DIR}/activity-${stamp}.txt"
psql "${DB_URL}" -X -v ON_ERROR_STOP=1 -c \
  "SELECT now(), numbackends, xact_commit, xact_rollback, blks_read, blks_hit, deadlocks FROM pg_stat_database WHERE datname=current_database()" \
  > "${OUT_DIR}/database-${stamp}.txt"

Collection account needs only monitoring rights, not superuser. Output may contain operational data; store per organizational policy.

Deploy new pool size with rolling update and observable window:

kubectl -n <namespace> set env deployment/<deployment> \
  SPRING_DATASOURCE_HIKARI_MAXIMUM_POOL_SIZE=12
kubectl -n <namespace> rollout status deployment/<deployment> --timeout=10m
kubectl -n <namespace> get pods -l app=<app-label> -o wide

Replace <app-label> with actual label value. Confirm env var maps to Spring config via pod spec and startup config. Change rebuilds pods; verify PDB, readiness, capacity, and connection storm risk.

View changed deployment config and events:

kubectl -n <namespace> get deployment <deployment> -o yaml
kubectl -n <namespace> get events --sort-by='.metadata.creationTimestamp' | tail -n 40
kubectl -n <namespace> logs deployment/<deployment> --since=10m | tail -n 200

During pool shrink, old and new pods coexist briefly; database connection peak may rise then fall. Include surge capacity in release strategy.

PgBouncer Is Not Infinite Capacity

When many instances, high idle connection ratio, or function/short-lived workloads frequently create connections, evaluate PgBouncer transaction pooling. It reduces backend sessions but transaction-level pooling breaks features relying on session state: cross-transaction temp tables, session-level advisory locks, some prepared statement usages. Introducing a proxy is an architectural change requiring compatibility testing.

PgBouncer key config illustration:

[databases]
<db-name> = host=<db-host> port=5432 dbname=<db-name>

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
default_pool_size = 40
reserve_pool_size = 10
max_client_conn = 500
server_idle_timeout = 600

max_client_conn

is client connections, not backend database connections; file descriptor limits must match. Auth config, TLS, and password file not shown; do not use this snippet as complete production config.

View PgBouncer pool status via admin database:

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

Connect to PgBouncer admin database, not PostgreSQL. Focus on cl_waiting, server connections, and average query/transaction time.

Connection Storms and Failure Recovery

During database restart, network recovery, or large-scale scale-out, all instances may simultaneously fill minimumIdle, causing a connection storm. Lower minimumIdle, stagger pod startup, use readiness and retry jitter — more effective than simply raising database limit. Application retries must use exponential backoff with a cap.

// Business code sketch: retry only for confirmed idempotent operations
RetryConfig config = RetryConfig.custom()
  .maxAttempts(4)
  .waitDuration(Duration.ofMillis(200))
  .intervalFunction(IntervalFunction.ofExponentialRandomBackoff(200, 2.0, 0.5))
  .retryExceptions(SQLTransientConnectionException.class)
  .build();

Exact API depends on Resilience4j version. Non-idempotent transactions must not be blindly retried, else duplicate writes.

Rollback and Acceptance

Save deployment before change:

kubectl -n <namespace> get deployment <deployment> -o yaml \
  > <deployment>-before-pool-change.yaml
kubectl -n <namespace> rollout history deployment/<deployment>

File may contain env vars and references; restrict access. If new value causes timeouts, database connections near budget, or error rate rise, rollback previous version:

kubectl -n <namespace> rollout undo deployment/<deployment>
kubectl -n <namespace> rollout status deployment/<deployment> --timeout=10m
kubectl -n <namespace> get pods -l app=<app-label>

Rollback also triggers rolling and connection rebuild; cannot repeatedly operate without observation when database near exhaustion. If needed, pause scaling or reduce traffic per business emergency process.

Final acceptable value must simultaneously satisfy: peak pending and acquisition latency within target; database connection budget includes scale-out and release overlap; CPU, I/O, lock waits, slow SQL not worsened by pool expansion; timeouts release abnormal occupancy; database failure recovery avoids sustained connection storms; rollback values and change evidence traceable. So-called experience values only provide a starting point; true judgment must come from same time-window application pool metrics, database activity, system resources, and request outcomes.

Break Down "Connection Occupancy Time"

Time from borrowing a connection to returning it includes more than SQL execution. When transaction boundaries are too large, JSON serialization, remote calls, message sending, even user logic may occur while holding the connection. Database sees fast SQL but pool stays full — common cause is non-database work occupying connections in application code. Measure connection acquisition, transaction duration, single SQL, and total request latency separately.

In PostgreSQL, aggregate by transaction age:

SELECT application_name,
       count(*) FILTER (WHERE xact_start IS NOT NULL) AS in_tx,
       max(now() - xact_start) AS max_xact_age,
       percentile_cont(0.95) WITHIN GROUP (
         ORDER BY EXTRACT(EPOCH FROM (now() - xact_start))
       ) FILTER (WHERE xact_start IS NOT NULL) AS p95_xact_age_seconds
FROM pg_stat_activity
WHERE datname = '<db-name>'
GROUP BY application_name
ORDER BY in_tx DESC;

This is a point-in-time snapshot; low traffic yields few samples. Continuous trends need periodic monitoring collection. Avoid all instances using same application_name losing attribution. Can add instance identifier in JDBC URL but control label cardinality.

Spring's @Transactional should wrap minimal atomic DB operations, not entire web request. Code review focus — anti-pattern vs. improvement direction:

// Discouraged: remote call inside transaction, connection held during network wait
@Transactional
public void submit(Order order) {
  repository.save(order);
  remotePricingClient.confirm(order.id());
}

// Improvement direction: separate DB transaction from remote call via outbox
@Transactional
public void submitLocal(Order order) {
  repository.save(order);
  outboxRepository.append(OrderSubmitted.from(order));
}

Second snippet does not automatically guarantee full business correctness; still needs reliable delivery, idempotent consumption, and failure compensation. Judgment must be validated via code transaction boundaries, thread stacks, and connection occupancy metrics together.

Multiple Data Sources and Background Tasks

A process may have primary, read-replica, audit, etc., each with its own pool. Budget must be calculated per pool then summed; health checks, Flyway/Liquibase, Quartz, batch jobs, and message consumers also create connections. Looking only at main business pool underestimates true ceiling.

Identify client distribution from database:

SELECT client_addr, application_name, usename,
       count(*) AS sessions,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY client_addr, application_name, usename
ORDER BY sessions DESC;

If application_name is empty, fix observability first. NAT, proxies, service mesh may make multiple instances appear as same client_addr; cannot rely solely on IP.

When background batch shares pool with online traffic, batch jobs can saturate connections briefly. Safer: dedicated small pool, concurrency limit, or scheduling window to preserve capacity for online requests. Batch reads need reasonable fetch size to avoid single transaction long-holding.

Connection Lifecycle and Network Devices

Database, proxy, firewall, NAT, and connection pool each may have idle timeout. If middle device clears idle connections after 15 minutes while Hikari maxLifetime is 30 minutes with no effective keepalive, application may borrow already dead TCP sessions. Logs show connection reset, broken pipe, or SQLState 08006 — not fixed by enlarging pool.

Check PostgreSQL TCP keepalive settings:

SHOW tcp_keepalives_idle;
SHOW tcp_keepalives_interval;
SHOW tcp_keepalives_count;
SHOW client_connection_check_interval;

client_connection_check_interval

introduced in PostgreSQL 14, controls client disconnect checks during long queries; actual applicable version must be confirmed. Value 0 uses system default or disables behavior; interpretation depends on OS settings.

View Linux TCP keepalive defaults:

sysctl net.ipv4.tcp_keepalive_time \
  net.ipv4.tcp_keepalive_intvl \
  net.ipv4.tcp_keepalive_probes

Do not globally modify kernel values for one app — affects all TCP connections. Prefer local adjustments via database, JDBC, or pool supported settings, ensuring keepalive interval shorter than middle device idle timeout.

Warm-up, Minimum Idle, and Resource Cost

When minimumIdle equals maximumPoolSize, pool tends to keep fixed idle connections, reducing burst connection latency, but many instances long-occupy database slots. Setting minimumIdle very low saves idle connections but may cause synchronous connection storms on traffic bursts or recovery. Choose based on connection establishment latency, TLS/auth cost, traffic pattern, and database headroom.

Observe connection establishment rate from database logs or proxy metrics. PostgreSQL log_connections increases log volume; assess disk and privacy impact before enabling in production:

SHOW log_connections;
SHOW log_disconnections;
SHOW log_line_prefix;

If temporarily enabled, confirm parameter supports reload, change via ALTER SYSTEM or config management per org policy; revert after investigation. Do not retain full connection logs indefinitely without bounds.

Alert Design

Single "connections > 80%" alert has limited value. More actionable combo: pool pending continuously > 0, acquire p95 exceeds portion of SLO, database ordinary connections near budget, error rate rising. When database CPU or lock waits already saturated, alert should explicitly say "do not expand pool, address database bottleneck first".

Record connection pool timeout error rate (metric per actual application exposure):

sum(rate(hikaricp_connections_timeout_total{pool="orders-db"}[5m]))

Some Micrometer versions don't expose that name; confirm from /actuator/prometheus. Do not swap in different-meaning metric just to get a result.

Expression for remaining ordinary database connection budget (record at collection side; metrics per actual exporter):

pg_settings_max_connections
- pg_settings_superuser_reserved_connections
- sum(pg_stat_activity_count)

This is current free slots only, excluding org budget for failure handling and scale-out. Alert threshold should be above theoretical zero and dynamically assessed with total instance count changes.

Canary Method for Parameter Changes

Connection pool is per-instance parameter; suitable for canary deployment or few pods first. Canary and baseline must receive similar traffic, else comparison meaningless. Cover at least normal peak, slow queries, brief database unavailability, and rolling release. Change only maximumPoolSize or one timeout parameter per round, keeping other variables constant.

Verify a pod's actual env and startup state:

kubectl -n <namespace> get pod <pod-name> \
  -o jsonpath='{range .spec.containers[*].env[*]}{.name}{"="}{.value}{"
"}{end}' \
  | grep 'SPRING_DATASOURCE_HIKARI'
kubectl -n <namespace> logs <pod-name> --since=15m | grep -i 'Hikari'

Secret references won't show values — correct behavior; verify reference identity, not print secrets. Logs may not print full config; confirm via controlled Actuator configprops endpoint, but that endpoint may expose sensitive info — never open to public internet.

Emergency Sequence When Connections Exhausted

When database near limit, blindly rolling restart all apps causes reconnect storm. First preserve admin channel, pause non-critical batch jobs or scale-out, limit new traffic, identify idle in transaction and anomalous instances, then decide pool shrink or session termination. Superuser reserved slots are not routine ops capacity.

Generate candidate termination statements for manual review only:

SELECT format(
  'SELECT pg_terminate_backend(%s); -- app=%L age=%s',
  pid, application_name, now() - state_change
) AS reviewed_command
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes'
  AND usename = '<app-user>';

Output is review SQL, does not terminate. Before actual execution, contact app owner, confirm transaction rollback, assess idempotent retry, execute one by one with observation. Do not batch-run unverified candidate list.

After stabilization, retrospective should place peak replicas, each pool ceiling, actual connection peaks, pending, acquire latency, transaction age, slow SQL, and database resources on same timeline. Only then distinguish capacity shortage, connection leaks, slow queries, lock blocking, and network failures — avoiding "just increase pool" as default answer next time.