PgBouncer pool_size sizing: matching pool capacity to transaction time and throughput

pool_size decides how many queries can execute at once through PgBouncer. It is also the setting most teams guess at: leave the default, raise it when something breaks, lower it when PostgreSQL complains about connections. The correct value falls out of two numbers you can measure in a minute: peak transactions per second, and how long the average transaction holds a server connection.

This article gives you the sizing relationship, shows how to measure its inputs from PgBouncer’s own stats, works through concrete examples, and covers the constraints that bound the answer: per-database overrides, cold start behavior, and the hard ceiling imposed by PostgreSQL’s max_connections.

Scope: transaction pooling mode, where server connections return to the pool after each transaction. In session mode the arithmetic barely applies because a connection is held for the whole client session regardless of how much work it does.

The sizing relationship

The core formula is Little’s Law applied to the server connection pool:

required_pool_size ~= peak_transactions_per_second x avg_xact_time_in_seconds

A transaction holds a server connection from assignment until commit. If you serve 200 transactions per second and each holds a connection for 100 ms, then on average 20 connections are occupied at any instant. That is your pool size, before headroom.

The two failure directions are asymmetric:

• Too small: when sv_active reaches pool_size, the next client request queues. The degradation is cliff-edge: latency goes from roughly zero to unbounded at 100% utilization, because waiters sit in a FIFO queue with no upper bound on wait time until query_wait_timeout (default 120 s) disconnects them.
• Too big: you waste PostgreSQL connection slots and add backend memory, but queries still execute. Oversizing costs resources; undersizing costs latency and eventually availability.

The second sensitivity is transaction time. The formula is multiplicative, so if avg_xact_time doubles, pool capacity at a fixed size halves. A pool of 20 with 100 ms transactions handles ~200 TPS; the same pool with 200 ms transactions handles ~100 TPS. This is why a backend slowdown or an idle-in-transaction problem can push a previously fine pool over the cliff without any change in traffic.

flowchart LR
  C[Application clients] -->|connect: bounded by max_client_conn| Q[Pool wait queue, FIFO]
  Q -->|execute: bounded by pool_size| P[(Server connection pool per database,user)]
  P -->|one slot per connection| PG[(PostgreSQL max_connections)]
  P -.->|freed on COMMIT, transaction mode| Q

max_client_conn controls how many clients can connect. pool_size controls how many can execute. They are independent, and pool_size is almost always the first thing to saturate.

Measure the inputs

Both inputs come from the admin console. SHOW STATS_AVERAGES gives pre-computed per-second rates and microsecond averages per database:

# Transaction rate and average transaction time per database
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW STATS_AVERAGES;"

The columns you need:

• xact_count: transactions per second, averaged over the stats period. Use your peak value, not the daily average. The averages window smooths spikes, so for a true peak either shorten the sampling or derive rates from SHOW STATS deltas of total_xact_count around your busiest minutes.
• xact_time: average transaction time in microseconds. Divide by 1,000,000 to get seconds for the formula.

One nuance before you trust xact_time: it includes idle time inside a transaction. If your application runs BEGIN, does 400 ms of application work, then commits, all of that holds a server connection and counts. Compare xact_time against query_time (average statement execution time). If xact_time is many times larger than query_time, a large share of connection hold time is idle-in-transaction, and the cheapest capacity increase is fixing the application pattern rather than raising pool_size.

Worked examples

The headroom column assumes roughly 25-50% above the raw figure. How much you need depends on burst shape: smooth traffic can run tighter; sharp minute-scale bursts need the buffer, because the queue gives you no graceful degradation when the pool fills.

The last row is deliberate. A 100+ connection pool is a smell, not a target. If the formula says you need 100 concurrent transactions, the first question is why transactions take 100 ms, and the second is whether PostgreSQL can execute 100 concurrent transactions well. Beyond some point, adding connections makes the backend slower (lock contention, CPU scheduling, I/O), which raises avg_xact_time, which raises the required pool size again. The formula cuts both ways.

Where pool_size actually applies

Pools are per (database, user) pair, and sizing is per pool:

• default_pool_size (default 20) applies to every pool without an explicit override.
• Per-database pool_size in the [databases] section overrides the default for that database.

If one database serves your hot OLTP path and three others serve cron jobs and reporting, they should not share one number. Compute the formula per database from its own xact_count and xact_time in SHOW STATS_AVERAGES, and set per-database sizes accordingly. A single undersized hot pool saturates while the aggregate looks healthy, which is why per-pool visibility matters. Verify what each pool actually resolved to with SHOW DATABASES;, which reports the effective pool_size per database.

One operational caveat: before PgBouncer 1.16.0, changing default_pool_size and issuing RELOAD did not apply the new value to pools that already existed (GitHub issue #248, fixed in 1.16.0). If you are running 1.16.0 or later, RELOAD applies the change correctly, but verify with SHOW DATABASES; to confirm the effective value.

min_pool_size and cold start

After a PgBouncer restart, every pool starts empty. Server connections are created on demand, so the first requests pay TCP plus authentication setup latency, and under load this produces a connection establishment storm against PostgreSQL just when clients are reconnecting.

min_pool_size pre-creates that many server connections per pool at startup and keeps them around. Use it on hot pools where cold-start latency matters. Do not set it on every pool by reflex: it multiplies your steady-state PostgreSQL connection count by the number of (database, user) pools, and on a host with many pools that alone can eat a large share of max_connections.

The PostgreSQL max_connections ceiling

Every server connection PgBouncer opens consumes one PostgreSQL max_connections slot. The constraint:

sum of all pools' pool_size (plus reserve_pool_size, across all PgBouncer
instances targeting this backend) < PostgreSQL max_connections
    - superuser_reserved_connections
    - direct clients, replication, admin access

Keep total pool capacity under roughly 80% of max_connections. When PgBouncer cannot establish a server connection because PostgreSQL is full, new connections fail, sv_login climbs, existing connections drain as they hit server_lifetime, and the pool empties while clients queue. Multiple PgBouncer instances (or multiple processes via so_reuseport, each with independent pools) multiply the demand, so sum across all of them, not just one.

reserve_pool_size is not a sizing tool. It is overflow capacity that only activates after a client has already waited reserve_pool_timeout (default 5 s). Sustained reserve pool usage means the base pool is undersized; fix pool_size rather than growing the reserve.

Validate the sizing in production

Sizing is a hypothesis until production traffic confirms it. The signals that confirm or refute it, per pool:

Two states deserve attention because they look green but are not:

• sv_idle = 0 with cl_waiting = 0: nobody is waiting, but you have zero headroom. The next slow query or small burst starts a queue. Treat this as yellow.
• avg_wait_time near zero while avg_xact_time trends up: you have not saturated yet, but each connection is being held longer, consuming headroom. Wait time only moves once you are at the cliff edge, so by the time it alerts, the incident has started.

Do not shrink a pool because sv_idle looks high. In transaction mode, idle connections are the ready reserve that absorbs bursts. Idle inventory is the point of the pool.

Common sizing mistakes

• Sizing from max_client_conn. Client connections are cheap (about 2 KB each) and mostly idle. Execution capacity is pool_size. A deployment with max_client_conn = 10,000 and pool_size = 20 runs 20 concurrent queries; the other 9,980 clients queue.
• Using the daily average TPS. Pools must be sized for peak. Average-based sizing guarantees a daily queuing window.
• Ignoring idle-in-transaction time. If avg_xact_time is inflated by application work inside open transactions, raising pool_size masks the problem at PostgreSQL’s expense. Fix the hold time first.
• One number for every database. Per-pool demand varies by an order of magnitude. Override per database.
• Forgetting the backend ceiling. Raising pool_size without checking the sum against PostgreSQL max_connections converts a queuing problem into a login-failure problem.
• Assuming RELOAD applied. Verify with SHOW DATABASES; that the effective pool_size matches intent, especially after changing default_pool_size.

How Netdata helps

Sizing inputs and validation signals come from the same place, and Netdata collects both from the admin console continuously:

• xact_count and xact_time per database give you the two formula inputs as time series, so you can read off real peak values instead of guessing.
• sv_active per pool against configured pool_size shows utilization approaching saturation before clients queue, which is the earliest warning that a previously correct size no longer fits.
• cl_waiting, maxwait, and avg_wait_time together distinguish “pool at capacity but coping” from “pool injecting user-facing latency”, so you resize on evidence rather than on alert noise.
• The gap between avg_xact_time and avg_query_time surfaces idle-in-transaction hold time, telling you whether to grow the pool or fix the application.
• Per-pool breakdowns keep one hot database from hiding behind a healthy aggregate.

Related guides

• PgBouncer avg_wait_time high: the latency the pool itself is injecting
• How PgBouncer actually works in production: a mental model for operators
• PgBouncer maxwait high: the oldest client waiter and how close it is to timing out
• PgBouncer monitoring checklist: the signals every connection pooler needs
• PgBouncer monitoring maturity model: from survival to expert
• PgBouncer pool exhaustion: clients queue, wait times climb, and the retry cascade
• PgBouncer pool utilization high: sv_active approaching pool_size before clients queue
• PgBouncer query_wait_timeout: clients disconnected after waiting too long for a connection