How to Determine the Right Database Connection Pool Size: Practical Guidelines and Benchmarks

Upper Limits

PostgreSQL max_connections is the hard database limit. HikariCP maximumPoolSize limits connections per application process. The total possible connections equal the sum of all application instances, background jobs, monitoring tools, and reserved admin connections. Use a budgeting formula instead of a fixed constant:

single_instance_pool_limit = floor((db_max_connections - admin_reserved - other_workloads - safety_headroom) / peak_app_instances)

Query the database version and connection parameters:

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

superuser_reserved_connections

is reserved for superusers only and should not be counted toward application capacity.

Capacity Budgeting and Workload‑Driven Initial Values

Estimate arrival rate and average DB occupancy using Little’s Law (throughput × average service time). For example, 80 requests/s with 40 ms average DB time yields ~3.2 concurrent connections; start with a small pool (8‑16) and adjust based on observed wait times, DB saturation, and slow SQL.

Account for Kubernetes replica counts, HPA maxReplicas, and rolling‑update surge capacity when calculating the budget.

# Example budget script (bash)
DB_MAX="<db_max_connections>"
RESERVED="<admin_reserved>"
OTHER="<other_workloads>"
HEADROOM="<safety_headroom>"
PEAK_INSTANCES="<instance_count>"
available=$((DB_MAX - RESERVED - OTHER - HEADROOM))
printf 'application_budget=%d
per_instance_upper_bound=%d
' "$available" $((available / PEAK_INSTANCES))

The result is a constraint, not a performance optimum; real limits also depend on query concurrency, transaction duration, and DB CPU/I‑O capacity.

Configuring HikariCP in Spring Boot 3

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

limits waiting for a pool connection, not query timeout. max-lifetime should be shorter than any external load balancer, NAT, or proxy timeout, and keepalive-time must be less than max-lifetime.

Set statement and transaction timeouts at the role level to avoid connections being held indefinitely:

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';

Observing Pool Queues with Spring Boot Actuator and Micrometer

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

Key metrics usually include active, idle, pending, max, min, and acquire latency. Example PromQL queries:

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

Persistent high pending or an active -to- max ratio near 1 indicates a full pool; investigate slow SQL or lock contention before increasing the pool.

Detecting Saturation and Slow SQL

Monitor connection usage with postgres_exporter:

sum(pg_stat_activity_count{datname="<db_name>"}) / scalar(pg_settings_max_connections)

Enable pg_stat_statements (requires shared_preload_libraries and a restart) to find top‑consuming queries:

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;

Analyze execution plans with a protected timeout:

BEGIN;
SET LOCAL statement_timeout = '3s';
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) SELECT ...;
ROLLBACK;

Leak Detection

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

Search application logs for connection‑related errors (e.g., "Connection is not available", "SQLTransientConnectionException").

Load Testing and Incremental Tuning

Perform controlled load tests, varying only one variable (e.g., pool size 8 → 12 → 16) and record throughput, p95/p99 latency, pool wait time, DB CPU/I‑O, lock waits, and error rates.

# Capture DB snapshot during tests (bash)
DB_URL="postgresql://<app_user>@<db_host>:<db_port>/<db_name>"
psql "$DB_URL" -c "SELECT state, wait_event_type, wait_event, count(*) FROM pg_stat_activity GROUP BY 1,2,3 ORDER BY 4 DESC" > activity.txt
psql "$DB_URL" -c "SELECT now(), numbackends, xact_commit, xact_rollback, blks_read, blks_hit, deadlocks FROM pg_stat_database WHERE datname=current_database()" > db_stats.txt

Deploy new pool sizes via rolling updates, ensuring readiness probes and PodDisruptionBudgets prevent a full‑pool storm:

kubectl -n <namespace> set env deployment/<deployment_name> SPRING_DATASOURCE_HIKARI_MAXIMUM_POOL_SIZE=12
kubectl -n <namespace> rollout status deployment/<deployment_name> --timeout=10m

PgBouncer Considerations

Transaction‑pooled PgBouncer can reduce backend sessions but is incompatible with session‑state features (temporary tables, advisory locks, certain prepared statements). Example PgBouncer config:

[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

Monitor PgBouncer pools with SHOW POOLS;, SHOW STATS;, focusing on cl_waiting and average query time.

Connection Storms and Failure Recovery

During restarts or scaling events, many pods may simultaneously open connections. Reduce minimumIdle, stagger pod startups, and use exponential backoff retries (e.g., Resilience4j) to avoid overwhelming the DB.

RetryConfig config = RetryConfig.custom()
    .maxAttempts(4)
    .waitDuration(Duration.ofMillis(200))
    .intervalFunction(IntervalFunction.ofExponentialRandomBackoff(200, 2.0, 0.5))
    .retryExceptions(SQLTransientConnectionException.class)
    .build();

Transaction Boundaries and Connection Lifetime

Measure transaction age per application_name to spot outliers:

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;

Ensure @Transactional wraps only the minimal atomic DB operation, not the whole request.

Multi‑Datasource and Background Tasks

Applications often have primary, read‑replica, audit, and batch pools. Budget each separately and sum them; otherwise, you’ll underestimate total connections.

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 missing, add it via the JDBC URL for proper attribution.

Connection Lifecycle and Network Devices

Idle timeouts in firewalls/NAT devices can close TCP sessions before Hikari’s maxLifetime expires, leading to "08006" errors. Align keepalive intervals across PostgreSQL ( tcp_keepalives_*), the OS, and the connection pool.

SHOW tcp_keepalives_idle;
SHOW tcp_keepalives_interval;
SHOW tcp_keepalives_count;
SHOW client_connection_check_interval;  -- PostgreSQL 14+

Do not change system‑wide kernel keepalive values for a single app; prefer per‑connection settings.

Alert Design

Combine multiple signals: non‑zero pending, high acquire latency (p95), DB connection usage approaching the budget, and rising error rates.

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

# Remaining DB connections (excluding admin reserve)
pg_settings_max_connections - pg_settings_superuser_reserved_connections - sum(pg_stat_activity_count)

Gray‑Scale Parameter Changes

Test a new maximumPoolSize or timeout value on a canary deployment or a small subset of pods before full rollout. Verify the environment variables are applied and the pool metrics behave as expected.

# Verify env on a specific pod
kubectl -n <namespace> get pod <pod_name> -o jsonpath='{range .spec.containers[*].env[*]}{.name}={.value}
{end}' | grep SPRING_DATASOURCE_HIKARI

Emergency Procedure When Connections Exhaust

Do not restart all pods at once. Keep admin connections, pause non‑critical workloads, limit new traffic, and identify long‑idle‑in‑transaction sessions before terminating them. Generate reviewable termination commands:

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>';

After stabilization, roll back the deployment if errors persist, and conduct a post‑mortem aligning pool limits, observed peaks, pending counts, transaction ages, slow SQL, and DB resource usage.

Splitting Connection Usage Time

Separate connection acquisition time from transaction duration. Measure transaction age per application to detect connections held for non‑DB work:

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;

Rollback and Acceptance

Save the current Deployment before changing pool parameters:

kubectl -n <namespace> get deployment <deployment_name> -o yaml > <deployment_name>-before-pool-change.yaml

If the new value causes timeouts, high error rates, or DB saturation, undo the rollout:

kubectl -n <namespace> rollout undo deployment/<deployment_name>
kubectl -n <namespace> rollout status deployment/<deployment_name> --timeout=10m

Do not repeatedly roll back while the DB is near its connection limit; follow the organization’s emergency process, pause traffic, and address the root cause before further changes.

Final Validation Checklist

Peak‑period pending and acquire latency are within SLO targets.

Database connection budget includes surge capacity from HPA and rolling updates.

CPU, I/O, lock waits, and slow SQL have not worsened after pool changes.

Timeout settings release long‑running connections.

Failure recovery does not trigger a connection storm.

Rollback values and change evidence are documented and reproducible.

Experience‑based “golden numbers” can serve as a starting point, but the final pool size must be justified by concrete metrics from the same observation window covering application pool metrics, database activity, system resources, and request outcomes.