Sizing Connection Pools for CPU-Bound versus IO-Bound Workloads

This guide is part of Connection Pool Sizing Formulas. Two services can have identical request rates, identical query counts and identical mean latencies, and need pools that differ by a factor of five. The difference is what the database is doing during those milliseconds: burning CPU on sorts, joins and function evaluation, or waiting on storage, locks and network. A pool sized as though CPU-bound work were IO-bound pushes the database past its core count and makes every query slower; the reverse leaves throughput on the table.

-- same mean latency, very different sizing
workload A:  8 ms per query, 95 % of it on CPU        → pool near the core count
workload B:  8 ms per query, 85 % of it waiting on IO → pool several times the core count

The objective is to classify the workload from evidence the database already provides, apply the right formula, and re-check when the mix changes.

Rapid incident diagnosis

Measure the split from wait events. On PostgreSQL, sampling pg_stat_activity tells you what active sessions are doing. Sessions with a null wait_event are running on CPU; the rest are waiting on something named:

SELECT coalesce(wait_event_type, 'CPU') AS state, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY 1 ORDER BY 2 DESC;

Sample it every second for a minute rather than once; a single snapshot of a fast workload is mostly noise. A CPU share above about 70 % means CPU-bound; an IO or Lock share above 50 % means IO-bound or contended.

Use statement statistics for a per-query view. pg_stat_statements with track_io_timing enabled separates execution time from block read time:

SELECT substr(query, 1, 60) AS query, calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round((shared_blk_read_time + shared_blk_write_time) / nullif(calls, 0)::numeric, 2) AS mean_io_ms
FROM pg_stat_statements
ORDER BY mean_exec_time * calls DESC
LIMIT 10;

A query whose IO time is a small fraction of its execution time is CPU-bound; one dominated by block read time is IO-bound. On MySQL, the equivalent comes from performance_schema.events_statements_summary_by_digest alongside events_waits_summary_global_by_event_name.

Check the database’s own utilisation. A CPU-bound workload shows database CPU near saturation while the pool is full; an IO-bound one shows modest CPU with high storage latency and a high count of active sessions.

Beware the mixed case. Most real services are a mixture: short CPU-bound lookups plus a few IO-heavy reports. Sizing for the average serves neither; the right answer is usually separate pools, which the remediation section covers.

Mathematical sizing or parameter formula

CPU-bound work. Concurrency beyond the number of cores does not add throughput; it adds context switching and lengthens every query. The classical starting point, from connection pool sizing literature, is:

pool_total ≈ cores × 2 + effective_spindles

For modern servers with fast storage, effective_spindles is small — often 1 or 2 — so a 16-core database lands near 34 connections in total, across every client. Divided across a fleet:

pool_per_process = max(1, floor( (cores × 2 + spindles) ÷ processes ))

Twelve processes against a 16-core database gives 2 or 3 connections each. That feels small, and it is correct: the database cannot execute more than 16 queries at once regardless of how many connections exist.

IO-bound work. Each connection spends most of its time waiting, so the database can usefully hold many more in flight. Little’s Law gives the answer directly:

pool_total = ceil( queries_per_second × mean_query_seconds ÷ target_utilisation )

At 1,800 queries per second and 8 ms mean time, that is 1800 × 0.008 = 14.4 busy, so 21 at a 70 % target — and because most of those 8 ms are waiting, the database’s cores are not the constraint. Keep an eye on the storage system instead: at some point added concurrency raises queue depth and latency rather than throughput.

The knee is the real limit. Whatever the classification, the useful pool size is the concurrency at which added connections stop adding throughput:

increase concurrency until throughput flattens; the knee is the ceiling
pool_total = knee × 0.8

Measuring it is a short load test at increasing concurrency, and it subsumes both formulas — CPU-bound workloads knee near the core count, IO-bound ones much later.

Mixed workloads. Size each class separately and give each its own pool:

pool_interactive = ceil( interactive_qps × interactive_mean_seconds ÷ 0.7 )
pool_reporting   = small and fixed, e.g. 2–4

Exact remediation & configuration

Classify, then split. The most common effective change is not a number but a separation: interactive queries and analytical queries in different pools, sized by their own class and, where possible, pointed at different servers.

# Spring Boot: two data sources, two classes of work
app:
  datasource:
    interactive:                     # CPU-bound lookups, short
      jdbc-url: jdbc:postgresql://orders-db.internal:5432/orders
      maximum-pool-size: 3           # (16 cores × 2 + 2) ÷ 12 processes
      connection-timeout: 1000
    reporting:                       # IO-bound scans, long
      jdbc-url: jdbc:postgresql://orders-replica.internal:5432/orders
      maximum-pool-size: 4
      connection-timeout: 10000

Give the database a matching guard. A CPU-bound database benefits from a statement timeout that stops a runaway analytical query from occupying a core:

ALTER ROLE orders_app      SET statement_timeout = '2s';
ALTER ROLE orders_reporting SET statement_timeout = '5min';

Reduce the CPU per query before enlarging the pool. For CPU-bound work the pool cannot be the answer — the database is already using its cores. The levers are the query and the schema:

-- find the CPU-heaviest statements
SELECT substr(query, 1, 80), calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       round(100 * (mean_exec_time - (shared_blk_read_time + shared_blk_write_time) / nullif(calls,0))
             / nullif(mean_exec_time, 0)) AS cpu_pct
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time * calls DESC LIMIT 10;

An index that turns a sort into an ordered scan, or a count(*) replaced by an estimate, frees far more capacity than any pool change.

For IO-bound work, check the storage before adding connections. If storage latency rises with concurrency, added connections increase queue depth without adding throughput — the IO-bound version of the CPU knee.

Where the fleet cannot be small enough, a proxy decouples process count from database concurrency: the application pools stay small per process, and PgBouncer’s default_pool_size becomes the single number that matches the database’s capacity. That is the standard answer when (cores × 2) ÷ processes rounds to less than one connection per process.

Re-checking as the workload changes

A classification is a snapshot. Three things change it, and each is worth a scheduled review rather than an incident.

Schema and query changes. Adding an index converts IO-bound scans into CPU-light lookups; removing one does the reverse. A query rewritten to sort in the database shifts work onto CPU. Any release that changes the top ten statements by total time is a reason to re-sample the wait events.

Data growth. A table that fits in the buffer cache is CPU-bound; the same query becomes IO-bound once the working set outgrows memory. The transition is gradual and shows up as a rising cache miss ratio:

SELECT round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0), 2) AS cache_hit_pct
FROM pg_statio_user_tables;

A hit ratio falling from 99 % to 90 % over months means the workload is drifting towards IO-bound, and the pool that was correct is now too small.

Hardware changes. Moving to an instance with more cores raises the CPU-bound ceiling proportionally; moving to faster storage raises the IO-bound one. Both invalidate a pool size derived before the change, and both are easy to forget because nothing in the application changed.

A quarterly review that re-runs the wait-event sample and the knee test takes under an hour and keeps the sizing honest.

Validation & verification

The classification is stable. Repeat the wait-event sample at three different times of day. A workload that is 90 % CPU at every sample is genuinely CPU-bound; one that swings between classes is mixed and needs separate pools.

Throughput confirms the knee. Run a step load test at increasing concurrency against a staging copy and record delivered throughput and p99 latency at each step. Throughput should rise until the knee and then flatten while latency climbs. Size at 80 % of the knee and verify that p99 latency at that point is acceptable.

The database is not oversubscribed. With the sized pool in production, active sessions should stay below the core count for CPU-bound workloads:

SELECT count(*) FILTER (WHERE state = 'active' AND wait_event IS NULL) AS on_cpu,
       current_setting('max_parallel_workers')::int AS parallel_workers
FROM pg_stat_activity;

Separated pools stay separated. After splitting interactive and reporting work, confirm from application_name that reporting queries never appear on the interactive pool’s sessions.

Frequently Asked Questions

Related

• Connection Pool Sizing Formulas — the parent guide to sizing a pool.
• Applying Little’s Law to Connection Pool Sizing — the arithmetic used for the IO-bound case.
• Interpreting pg_stat_activity Wait Events for Pool Issues — reading the wait events this page samples.
• Benchmarking Connection Pool Algorithms for Read-Heavy Workloads — measuring the knee properly.