Connection Pool

A 03:00 page. The application is returning 500s. Five hundred of the last thousand requests have failed. The on-call engineer opens Grafana. The application pool panel shows hikaricp_connections_active at the configured maximum, hikaricp_connections_idle at zero, and hikaricp_connections_pending rising. The database’s pg_stat_activity_count{state="active"} is sitting at max_connections minus ten. The replica is idle. The application pool is the bottleneck and the database pool is already at its ceiling because of it.

This is the connection pool’s failure shape. The pool is not a worker queue or a thread pool. The pool is the deterministic upper bound on the number of simultaneous statements the application can run against the database. The bound is on either end of the connection: the application pool chooses how many to open; the database pool chooses how many to accept. The question is which end is exhausted and how to tell.

What a connection pool is

A connection pool is the bounded set of TCP connections between two software components that use connections as a unit of work. Production stacks have at least two pools in series:

  Application   Application   Connection      Database
   thread         pool        broker         server
  ---------> -----------> -------------> ---------------
  request       opens a         multiplexes     accepts up to
  runs          connection      many app        max_connections
  per query     on demand,      conns onto      (PostgreSQL) or
                reuses on       fewer broker     max_user_connections
                next request    conns            (MySQL)

The layers are not optional. The application pool exists because TCP setup with a database has a measurable cost (TLS handshake, authentication, statement-prepare caching). The broker pool exists because the application’s sum-of-pools is much larger than the database wants to accept. The database ceiling exists because every accepted connection holds memory that the engine cannot share.

The metrics split along the same lines. Each layer has its own counts for active (currently executing), idle (open but not running a statement) and waiting (the layer above has requested a slot that the pool cannot provide).

The shapes that matter, all observed through the same three numbers:

A pool at saturation looks the same in all three: active is at the maximum, idle is at zero, waiting is climbing. The only way to tell the layers apart is to read all three.

Why a sysadmin cares

Saturation is the most commonly diagnosed — and most commonly misdiagnosed — incident class in production database operations. Two distinct failure classes share the same symptom:

1. Legitimate saturation. The workload exceeds the pool’s capacity. Either the pool is too small for the workload, or the workload is too large for the pool. The fix is upstream (add capacity) or downstream (reduce query latency).
2. Leaked saturation. A code path opens connections but does not return them to the pool. Active stays at the maximum, idle stays at zero, but the cause is not workload — it is a missing conn.close(). The fix is in the application code, not in the pool size.

A third shape appears less often but is more dangerous: the idle-in-transaction leak. A connection opens a transaction, acquires a row-level lock, and never commits or rolls back. The pool’s active count is fine; the database’s idle-in-transaction count climbs. Eventually the database refuses new connections and the broker starts queuing. The application sees timeouts; the database sees a vacuum queue blocked on a row lock.

How it works

A connection pool is a small object that owns a fixed number of TCP connections. Application code calls pool.acquire(), receives a Connection object, runs queries, and calls Connection.close() to return the slot. The pool enforces the bound; code that holds more than the bound waits.

  Worker thread         Pool                Database
  ---------------       ----------------    -----------
  pool.acquire() -----> {active: 8, idle: 2,
                         max: 10, waiting: 0}
                        |
                        +---> existing idle conn reused
                        |
  statement; commit
                        |
  conn.close() --------> {active: 7, idle: 3,
                          waiting: 0}

The floor and ceiling are the same shape across pools:

• Minimum. The pool pre-opens a small number of connections on startup to avoid the cost of the first request.
• Maximum. Hard ceiling. Calls to acquire() past the ceiling either queue (HikariCP connectionTimeout, pgxpool conn_wait_timeout) or fail fast (PgBouncer server_reset_query, ProxySQL connect_retries_on_failure).
• Acquire timeout. After this many milliseconds of queueing, the call returns an error to the application. This timeout is the boundary between “user sees a slowdown” and “user sees a 500.”

The three numbers — active, idle, waiting — answer the question “what is the pool doing right now?” at any moment. The right pool, on the right host, with the right workload, spends most of its time idle with active running 30-60% of the maximum and waiting at zero.

How to configure it

Two surfaces: the application pool and the broker pool. Each needs its own metrics.

Application pool (HikariCP example, Java).

# application.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 3000       # ms before acquire fails
      idle-timeout: 600000           # how long idle is idle
      max-lifetime: 1800000          # recycle after 30 min
      pool-name: app-primary
      register-mbeans: true          # expose HikariCP MBeans
      metric-registry-class: io.prometheus.client.hikaricp.HikariCPCollector

The exporter exposes these metrics under the hikaricp_ prefix. The most useful for this lesson:

hikaricp_connections_active       pool connections in use
hikaricp_connections_idle         pool connections idle
hikaricp_connections_pending      threads waiting for a slot
hikaricp_connections_acquire_seconds  histogram of acquire time

Broker pool (PgBouncer example).

# pgbouncer.ini
[databases]
app = host=10.0.4.10 port=5432 dbname=app
[pgbouncer]
listen_addr = 10.0.4.11
listen_port = 6432
auth_type = md5
default_pool_size = 20              # per database-user
max_client_conn = 200               # per broker process
reserve_pool_size = 4
server_idle_timeout = 600
query_wait_timeout = 30             # seconds; user-facing timeout

The metrics are exposed via PgBouncer’s stats; the postgres_exporter can be configured to scrape them through a custom query file.

# queries.yaml (excerpt; for pgwatch2 or a custom exporter)
- name: pgbouncer_pools
  query: |
    SHOW POOLS;
  metrics:
    - app_name:
        usage: LABEL
    - cl_active:
        usage: COUNTER
    - cl_waiting:
        usage: GAUGE
    - sv_active:
        usage: COUNTER
    - sv_idle:
        usage: COUGE

The cl_* columns are client-side (application to broker); the sv_* columns are server-side (broker to database). The most useful single number for the operator is cl_waiting — a non-zero value is the application queuing at the broker.

Prometheus alerts for the three layers.

# /etc/prometheus/rules/connection_pool.yaml (excerpt)
groups:
  - name: connection_pool
    rules:
      - alert: AppPoolSaturated
        expr: |
          hikaricp_connections_active / hikaricp_connections_max
            > 0.9
        for: 5m
        labels:
          severity: ticket
        annotations:
          summary: 'App pool saturated on {{ $labels.instance }}'

      - alert: BrokerQueueing
        expr: pgbouncer_pools_cl_waiting > 5
        for: 2m
        labels:
          severity: page

      - alert: DatabaseAtCeiling
        expr: |
          pg_stat_activity_count
            / on(instance) pg_settings_max_connections
            > 0.85
        for: 2m
        labels:
          severity: page

      - alert: IdleInTransactionLeak
        expr: |
          pg_stat_activity_count{state="idle in transaction"} > 3
        for: 5m
        labels:
          severity: ticket

The four alerts cover the four common shapes. The thresholds are deliberately conservative; tune upward after the first month of operational use.

How to validate it

Every step is READ-ONLY.

# 1. From the application server, inspect HikariCP via the admin endpoint
#    (if Prometheus client is bound) or via JMX.
curl -sf http://app:8080/metrics | grep -E '^hikaricp_connections_'

# HELP hikaricp_connections_active Active connection count
hikaricp_connections_active{pool="app-primary"} 7
hikaricp_connections_idle{pool="app-primary"}  13
hikaricp_connections_pending{pool="app-primary"} 0
hikaricp_connections_max{pool="app-primary"}     20

# 2. From the broker host, query PgBouncer's stats directly.
psql -h 10.0.4.11 -p 6432 -U pgbouncer -d pgbouncer -c 'SHOW POOLS;'

 database | user  | cl_active | cl_waiting | sv_active | sv_idle | pool_mode
----------+-------+-----------+------------+-----------+---------+-----------
 app      | app   |         7 |          0 |         7 |      13 | transaction

# 3. From the database, inspect server-side state.
psql -h 10.0.4.10 -U db_exporter -d app \
  -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;"

     state      | count
----------------+-------
 active         |    12
 idle           |    31
 idle in transaction |  2
 fastpath function call | 5

# 4. Confirm the alert rules parse and evaluate.
promtool check rules /etc/prometheus/rules/connection_pool.yaml

# 5. Confirm Prometheus is recording the alert state transitions
#    on a staging instance by simulating load with a small script.
#    Validate against the staging environment first.

The four outputs confirm: the application pool is reporting three of the four expected gauges, the broker is reporting client- and server-side splits, the database’s pg_stat_activity is reporting the state breakdown, and the alert rules parse. The fifth step is load generation, which is service-impact and should never run first on a production host.

How it can fail

Five failure shapes cover the overwhelming majority of pool incidents in production.

1. Pool too small. The application pool is configured for baseline traffic; the workload has grown. Symptom: the active / max ratio sits above 0.9 most days; pending rises under traffic spikes. Fix: grow the pool, but only after confirming the database ceiling is not the binding constraint.
2. Idle-in-transaction leak. A business transaction holds a connection open without committing for minutes at a time. Symptom: pg_stat_activity_count\{state="idle in transaction"\} rises without bound; the application thread is otherwise idle; the database’s lock-wait graph shows the held locks. Fix: client-side statement_timeout and the application’s transaction discipline.
3. Server-side ceiling. The application opens more pool members than the database will accept. Symptom: pg_settings_max_connections is reached; new connections return FATAL: too many clients. The application pool is “fine” by its own metric; the database is the limit. Fix: cap the application pool below the database ceiling, or introduce a broker pool with multiplexing.
4. Broker queueing. PgBouncer is configured in transaction mode and the application’s default_pool_size is below the application’s pool size. Symptom: cl_waiting is non-zero while sv_active is below default_pool_size; application latency rises. Fix: align default_pool_size to the application’s actual concurrency.
5. Network dropouts. The application holds a connection that is dead on the database side; the application does not know for a while. Symptom: HikariCP’s connection-acquire-seconds histogram tail spike; pg_stat_activity shows the connection state as idle and the TCP socket as half-closed. Fix: enable keepalive_time and keepalive_interval on the application host.

How to troubleshoot it

Diagnose in this order. The cheapest evidence comes first.

1. Read the application’s pool metrics first. Decide whether the application pool is saturated or merely busy. If the pending count is non-zero and active / max is above 0.9, the application pool is the bottleneck.
2. Read the broker’s cl_waiting. A non-zero value means the broker is queueing. A zero value means the broker is not; the bottleneck is at the application or at the database.
3. Read the database’s pg_stat_activity breakdown. A state of “active” at the database ceiling is a server-side ceiling. A non-trivial “idle in transaction” count is a leak. A wait_event_type of Lock on multiple rows is a contention issue (next lesson).
4. Read the database’s max_connections. A single pg_settings_max_connections metric, plus the same pg_stat_activity_count, gives the saturation ratio.
5. Inspect a stuck connection. When the pending count is non-zero, sample one waiting application thread (jstack, pprof, or application-side logging) and note what statement it is running. The statement text is the next evidence.

Security implications

The pool is a trust boundary. Each connection holds credentials or assumes an authentication state. Two surfaces to control:

• Credentials on the application side. HikariCP keeps the database password in a DataSource configuration. Bind the exporter or the application side to a secret manager; do not put the password in plain text. PgBouncer’s auth file lives in /etc/pgbouncer/userlist.txt — same rule.
• Authentication state on the broker side. In transaction mode, PgBouncer reuses a server connection across multiple client connections. server_reset_query = DISCARD ALL is the safety belt; without it, session state can leak between users. Use pool_mode = transaction and server_reset_query_always = 1 for multi-tenant pools.
• Audit. Ship both the application logs and the broker’s stats to Loki. Alert on FATAL: too many clients from the database log and on any non-zero cl_waiting count above a threshold.

Performance implications

Performance is dominated by three numbers.

• Acquire latency. hikaricp_connections_acquire_seconds p99 should sit under 50 ms for a healthy application pool. A p99 above 200 ms means the pool is queueing or the application is spending most of its time outside the database.
• Wait time on the broker side. A non-zero cl_waiting for more than a few seconds at a time means the broker is the bottleneck. Either grow default_pool_size or move to transaction pooling on a faster broker host.
• Server-side tail. pg_stat_activity’s wait_event_type histogram (when present) is the early signal of contention. The next lesson addresses the lock-side numbers.

The trade-off of conservative pool sizing is predictable latency under load; the cost of an undersized pool is a tail that no cache can hide.

Production guidance

• Set the application pool ceiling such that app_pool_max * app_replicas < broker_pool_max. Otherwise the broker is the ceiling and the broker queueing is the page.
• Alert on any non-zero cl_waiting sustained for any duration; the number itself is rarely the issue, the existence of it is.
• Run SHOW POOLS from the broker against a synthetic load to confirm cl_waiting is the metric you think it is.
• Set server_reset_query_always = 1 on PgBouncer for multi-tenant pools.
• Treat idle-in-transaction as a metric that should be near zero in steady state.
• Avoid long-running transactions inside the application pool. The connection is the unit of work; the transaction is the unit of work inside it. Each can be tuned.

Verification

You should now be able to answer:

• What are the three layers of connection pools in a typical production stack and what does each one’s saturation look like?
• What is the difference between cl_waiting and sv_idle on PgBouncer?
• What four alerts are worth defining for pool saturation?
• How do you distinguish a leaked connection from an under-sized pool?
• What is the most common mis-diagnosis of a saturated pool?

Quiz