Forecasting Pool Saturation from Traffic Growth

This guide is part of Detecting Connection Pool Saturation. Saturation alerts fire when the pool is already full and requests are already waiting. That is the right moment to page someone, and the wrong moment to discover that the database’s connection budget has no room left for the extra connections the fix requires. A forecast turns the same metrics into a date:

pool utilisation, weekly peaks:  54 %  →  61 %  →  68 %  →  74 %
at this rate, 100 % in about 4 weeks; the database budget allows +12 connections

The objective is a small set of derived series that say how much headroom remains, how fast it is being consumed, and which constraint — the pool, the database’s connections, or its CPU — will bind first.

Rapid incident diagnosis

Establish the current headroom. Three numbers, from three layers:

# pool: peak utilisation over the last week
max_over_time(
  (sum by (service) (hikaricp_connections_active) / sum by (service) (hikaricp_connections_max))[7d:1m]
)

# database connections: free slots at peak
min_over_time((pg_settings_max_connections - pg_stat_activity_count)[7d:1m])

# database CPU: peak utilisation
max_over_time(rate(node_cpu_seconds_total{mode!="idle",instance="orders-db"}[5m])[7d:1m])

Decompose demand into its two drivers. Pool demand is arrival rate times hold time, and they grow for different reasons:

# arrival rate
sum(rate(http_server_requests_seconds_count{service="orders-api"}[5m]))

# hold time
rate(hikaricp_connections_usage_seconds_sum[5m]) / rate(hikaricp_connections_usage_seconds_count[5m])

Traffic growth is usually gradual and predictable; hold-time growth is usually a step change from a release, and it is the one that turns a comfortable pool into a saturated one overnight.

Check which constraint is closest. The pool is rarely the binding limit on its own — it can be raised, if the database has slots and CPU. Compare the three headrooms on one panel before deciding what to plan for.

Three headrooms compared Pool utilisation is at 74 percent of its maximum, the database has 12 free connections out of 400, and database CPU is at 55 percent, so the connection budget is the binding constraint. Headroom by layer pool utilisation 74 % database connections 97 % database CPU 55 % raising the pool is only possible if the middle bar has room
The pool, the database's connection budget and its CPU each have headroom. The forecast should name whichever runs out first, not only the one that alerts.

Mathematical sizing or parameter formula

Demand model. Little’s Law gives the number of connections a service needs:

demand(t) = arrival_rate(t) × hold_time(t)
utilisation(t) = demand(t) ÷ pool_max

Projection. With weekly peaks, a linear fit over four to eight weeks is usually enough; anything more elaborate implies a precision the data does not support:

growth_per_week = (demand_now − demand_4w_ago) ÷ 4
weeks_to_saturation = (pool_max × 0.8 − demand_now) ÷ growth_per_week

Using 80 % rather than 100 % as the target leaves the burst headroom that keeps p99 latency stable.

Worked example. Demand four weeks ago was 8.6 connections; today it is 11.4. Growth is 0.7 per week. With a pool of 16, the 80 % line is 12.8, so (12.8 − 11.4) ÷ 0.7 = 2 weeks. Raising the pool to 20 moves the line to 16 and buys (16 − 11.4) ÷ 0.7 ≈ 6.5 weeks — if the database has four more connections per replica available.

Fleet effect. The database sees the pool times the replica count, and autoscaling makes that a range:

fleet_demand = replicas(t) × demand_per_replica(t)
fleet_max    = max_replicas × pool_max

A service whose replica count grows with traffic consumes the connection budget twice as fast as its per-replica demand suggests.

Which constraint binds. Compute weeks-to-limit for each and take the minimum:

weeks_pool       = (pool_max × 0.8 − demand) ÷ growth
weeks_connections = (usable_connections − fleet_demand) ÷ (growth × replicas)
weeks_cpu        = (cpu_limit × 0.7 − cpu_now) ÷ cpu_growth
Projecting pool demand to the saturation line Weekly peak demand rises from 8.6 to 11.4 connections over four weeks; extrapolated, it crosses the 80 percent line of a pool of sixteen in about two weeks. Weekly peak connections in use, pool max 16 80 % of pool max = 12.8 pool max 16 crosses in ~2 weeks 4 weeks ago +3 weeks dashed extrapolation from the last four weekly peaks
The forecast is a straight line through weekly peaks. Its value is the date it crosses the target, not the precision of the fit.

Exact remediation & configuration

Record the derived series. Recording rules keep the forecast cheap and make the panels simple:

groups:
  - name: pool-capacity
    interval: 1m
    rules:
      - record: pool:demand:connections
        expr: sum by (service) (hikaricp_connections_active)
      - record: pool:max:connections
        expr: sum by (service) (hikaricp_connections_max)
      - record: pool:utilisation:ratio
        expr: pool:demand:connections / pool:max:connections
      - record: pool:hold_seconds:mean
        expr: rate(hikaricp_connections_usage_seconds_sum[5m])
              / rate(hikaricp_connections_usage_seconds_count[5m])
      - record: db:connections:free
        expr: pg_settings_max_connections - pg_stat_activity_count

Project with predict_linear. Over a long enough window it is a reasonable fit for gradual growth:

# connections predicted in 14 days, from 7 days of history
predict_linear(pool:demand:connections[7d], 14 * 24 * 3600)

# free database connections predicted in 30 days
predict_linear(db:connections:free[14d], 30 * 24 * 3600)

Alert on the forecast, not only on the state. A warning that fires weeks ahead is the point of the exercise:

      - alert: PoolSaturationForecast
        expr: predict_linear(pool:demand:connections[7d], 14 * 24 * 3600)
              > 0.8 * pool:max:connections
        for: 6h
        labels: { severity: warning }
        annotations:
          summary: "{{ $labels.service }}: pool demand forecast to pass 80 % within two weeks"

      - alert: DatabaseConnectionBudgetForecast
        expr: predict_linear(db:connections:free[14d], 30 * 24 * 3600) < 20
        for: 12h
        labels: { severity: warning }
        annotations:
          summary: "{{ $labels.instance }}: fewer than 20 free connections forecast within 30 days"

The for clauses are long deliberately: a forecast that flickers on a single noisy sample is worse than no forecast.

Watch hold time separately. Traffic growth is gradual; hold time changes in steps, and a step is not something a linear fit predicts:

      - alert: PoolHoldTimeStep
        expr: pool:hold_seconds:mean > 1.5 * avg_over_time(pool:hold_seconds:mean[7d] offset 1d)
        for: 30m
        labels: { severity: warning }
        annotations:
          summary: "{{ $labels.service }}: mean connection hold time up 50 % versus last week"

Review the forecast on a schedule. A monthly capacity review that looks at the three headrooms, the projected crossing dates and the planned traffic changes turns the alerts into decisions. The output is a small set of actions: raise a pool where the budget allows, reduce hold time where it has grown, or plan a proxy or a larger instance where neither is possible.

Acting on the forecast

A forecast is only useful if the response is decided before the date arrives. Four responses cover most cases, in increasing order of cost.

Reduce hold time. The cheapest capacity is the connection time that is not doing database work: an external call inside a transaction, a session held through rendering, a lazy load after the transaction. Halving hold time halves demand at any traffic level, and it usually needs no infrastructure change at all.

Raise the pool. Immediate and effective when the database has both free connections and spare CPU. It is bounded by the connection budget, so the fleet arithmetic decides whether it is available.

Add capacity to the database. More CPU raises the useful concurrency; more memory raises the connection ceiling. It is a planned change with a restart, which is exactly why a forecast weeks ahead is worth having.

Introduce a pooler. When the fleet’s connection count rather than its work is the constraint, PgBouncer or a managed pooler decouples the two. It is the standard answer once replicas × pool cannot fit any sensible budget.

Response Lead time Cost When the forecast points here
Reduce hold time days engineering time hold time grew faster than traffic
Raise the pool minutes database connections connection budget has room
Scale the database days to weeks money, a restart CPU or memory is the binding limit
Add a pooler weeks a component to run fleet connection count is the limit

Re-forecast after acting. Each response changes the slope or the ceiling, so the projection must be recomputed — and the new crossing date recorded, so the next review starts from a number rather than from a feeling.

Four responses to a saturation forecast Reducing hold time lowers the slope, raising the pool lifts the ceiling, scaling the database raises the underlying limits, and a pooler decouples the fleet's connection count from the database entirely. Reduce hold time lowers the slope Raise the pool lifts the ceiling Scale the database raises the limits Add a pooler breaks the link Cheapest first after each, re-run the projection and record the new crossing date
Each response moves a different part of the picture: the slope, the pool ceiling, or the database's limit. Recording which one was used keeps the next review honest.

Validation & verification

The model matches reality. Compare last month’s forecast with what actually happened. A projection that was consistently wrong in one direction means the window is too short or the peaks are being taken from the wrong aggregation.

Demand matches the formula. Pick an hour and check that arrival_rate × hold_time agrees with the observed active connections within a small margin; a large gap means one of the two series is measured differently from the assumption — for example, a hold-time metric that excludes waiting.

Alerts have useful lead time. After a forecast alert fires, record how long it was before the state alert would have fired. Two weeks is useful; two hours is not, and means the projection window needs lengthening.

The three headrooms are all visible. During a capacity review, confirm that the dashboard shows pool, database connections and database CPU together. A review that only sees the pool will recommend raising it into a budget that has no room.

Frequently Asked Questions

Why use weekly peaks rather than averages?
Pools saturate at peaks, not at averages. A service averaging 30 % utilisation can be at 95 % for one hour each day, and that hour is what determines whether requests wait.
Is predict_linear good enough?
For gradual organic growth over weeks, yes. It cannot predict a product launch, a marketing campaign or a step change from a release — those come from the roadmap and from the hold-time alert respectively.
Should the forecast be per replica or per service?
Both. Per-replica demand drives the pool size; the service total drives the database budget. A service that scales horizontally can be flat per replica while consuming the budget rapidly.
What if traffic is seasonal?
Compare like periods — this Monday’s peak against last Monday’s — rather than a rolling window that mixes weekdays with weekends. predict_linear over a window that spans several weeks smooths the weekly cycle adequately.