How to Size a PostgreSQL Connection Pool in Python: Find Where Throughput Stops Growing, Split max_connections Across Workers, and Stop Slow Handlers From Hoarding Connections
Measure what a PostgreSQL 18 connection costs, find the knee with pgbench, turn it into a psycopg_pool size with Little’s law, see why a pool of 64 gave 20% less throughput than a pool of 28 and why...
Sooner or later every web service that talks to PostgreSQL has to answer a plain question: how many database connections should it keep open? If the answer is too low, requests queue up inside your application while the database has spare capacity. If the answer is too high, the database spends its effort juggling connections instead of finishing queries, response times climb, and one busy afternoon the server starts refusing new connections. Many teams settle the question by guessing a number such as 10 or 100 and rarely look at it again.
Table Of Content
- The words used in this tutorial
- What you need before you start
- Start a throwaway server
- Step 1: See what one connection costs
- Step 2: Find the knee with pgbench
- The three workloads
- Count your cores
- Run the sweep
- Read the table
- A rule of thumb to check your reading
- Step 3: Turn the knee into a pool size with Little’s law
- Step 4: Put the numbers into a psycopg pool
- A helper that behaves like a busy web service
- Sweep the pool size
- Read the pool table
- Step 5: Do not hold a connection while you wait for something else
- Why this bug stays hidden
- Step 6: Fail fast instead of queueing forever
- Step 7: Split max_connections across every process that owns a pool
- See what going over the budget looks like
- Write the budget down as code
- Use it
- Step 8: Watch the pool from both sides
- Common mistakes and how to check each fix
- Opening a connection or a pool for every request
- Raising the pool size to cure slowness
- Forgetting that every worker has its own pool
- Holding a connection during slow work
- Leaving the 30-second timeout and the unlimited queue
- Letting session state leak to the next borrower
- Sizing on one machine and shipping to another
- Confirm the whole thing works end to end
- Where to go next
- Sources
In this tutorial you will measure the answer instead. You will start a throwaway PostgreSQL 18 server, see what one connection costs, use pgbench to find the point where more connections stop adding throughput, turn that into a pool size with a one-line formula called Little’s law, and then put the numbers into a psycopg_pool connection pool in Python. After that you will reproduce two classic mistakes (holding a connection while you wait for something else, and letting requests queue without a limit) and finish by splitting the server’s connection budget across every process that owns a pool, using a small calculator that comes with tests.
By the end you will be able to:
- measure how throughput and latency change as the number of client connections grows, for work that computes, work that waits, and work that does both;
- estimate the number of busy connections an application needs from two numbers it already knows, its request rate and the time each request holds a connection;
- configure a
psycopg_poolpool so that it fails fast under overload instead of queueing for 30 seconds; - spot a handler that hoards a connection while it waits on something else, and see what it costs;
- divide
max_connectionsacross workers, instances and rolling deploys so that the sum of all pools fits.
The words used in this tutorial
A connection is one session between your program and the database server. In the measurements below each connection is served by its own server process, so you will also see the word backend. A pool is a fixed set of connections that are opened once and then borrowed and returned by your request handlers instead of being opened for every request. Throughput is how many requests finish per second, and latency is how long one request takes from the caller’s point of view. The queue is where a request waits when every connection is busy. The knee is the number of connections beyond which throughput stops growing.
What you need before you start
I ran everything here on Windows 11 with 14 processors, PostgreSQL 18.7, Python 3.13.14, psycopg 3.3.9, psycopg_pool 3.3.3, psutil 7.2.2 and pytest 9.2.0, on November 19, 2026. Every output block below was produced on that machine. You can follow along on Linux or macOS too. Only the numbers will differ, and that is part of the lesson, because the right pool size depends on your hardware and on what your requests do.
You need a PostgreSQL 18 server that you are free to overload (a throwaway one, not production), with psql and pgbench on your PATH. You need Python 3.13 or newer and the packages from pip install "psycopg[binary]" psycopg_pool psutil pytest. You should be comfortable reading SQL and simple Python threads. No earlier knowledge of connection pooling is assumed.
Start a throwaway server
These four commands create a fresh data directory, start a server on port 55460 with the default limit of 100 connections, point every PostgreSQL tool at it, and create a login role that you will need in Step 7. The -A trust option turns off password checks, which is acceptable only for a disposable server that listens on the local machine. In PowerShell, write each environment variable as $env:PGHOST = "127.0.0.1" instead of using export.
initdb -D data -U postgres -A trust -E UTF8 --no-locale
pg_ctl -D data -l server.log -o "-p 55460 -c max_connections=100" -w start
export PGHOST=127.0.0.1 PGPORT=55460 PGUSER=postgres PGDATABASE=postgres
psql -X -c "CREATE ROLE app LOGIN"
Because psql, pgbench and psycopg all read the PG* environment variables, none of the scripts below carries a connection string. Now check what you are talking to:
$ psql -X -c "SELECT version()"
version
-------------------------------------------------------------------------
PostgreSQL 18.7 on x86_64-windows, compiled by msvc-19.44.35229, 64-bit
(1 row)
$ psql -X -c "SELECT name, setting FROM pg_settings WHERE name IN ('max_connections', 'superuser_reserved_connections', 'reserved_connections', 'shared_buffers') ORDER BY name"
name | setting
--------------------------------+---------
max_connections | 100
reserved_connections | 0
shared_buffers | 16384
superuser_reserved_connections | 3
(4 rows)
The server is PostgreSQL 18.7. The three settings that matter for this tutorial are all there. max_connections is 100, and the documentation describes it as “Determines the maximum number of concurrent connections to the database server.” It adds that “The default is typically 100 connections, but might be less if your kernel settings will not support it”. superuser_reserved_connections is 3 and reserved_connections is 0. You will use those two in Step 7. The shared_buffers value is counted in 8 kB pages, so 16384 is the default 128 MB.
Step 1: See what one connection costs
Before tuning anything, find out why connections deserve a budget at all. The script below connects, runs SELECT 1 and disconnects 200 times. Then it runs 2,000 queries on one connection that stays open, and 2,000 more through a pool, which for now you can think of as a box of connections that are already open (Step 4 covers it properly). Finally it opens 50 idle connections and asks the operating system about the server processes behind them.
"""step01_connect_cost.py: what does one connection cost, and what does reusing one save?"""
import time
import psutil
import psycopg
from psycopg_pool import ConnectionPool
FRESH, REUSED = 200, 2000
# 1. A brand-new connection for every query.
t0 = time.perf_counter()
for _ in range(FRESH):
with psycopg.connect() as conn:
conn.execute("SELECT 1").fetchone()
per_fresh = (time.perf_counter() - t0) / FRESH * 1000
# 2. One connection, reused.
with psycopg.connect(autocommit=True) as conn:
t0 = time.perf_counter()
for _ in range(REUSED):
conn.execute("SELECT 1").fetchone()
per_reused = (time.perf_counter() - t0) / REUSED * 1000
# 3. A pool: check a connection out, run the query, hand it back.
pool = ConnectionPool(min_size=2, max_size=2, open=False)
pool.open(wait=True, timeout=30)
t0 = time.perf_counter()
for _ in range(REUSED):
with pool.connection() as conn:
conn.execute("SELECT 1").fetchone()
per_pooled = (time.perf_counter() - t0) / REUSED * 1000
pool.close()
print(f"new connection per query : {FRESH} queries, {per_fresh:.1f} ms each")
print(f"one connection, reused : {REUSED} queries, {per_reused:.2f} ms each")
print(f"pool checkout per query : {REUSED} queries, {per_pooled:.2f} ms each")
# 4. What does an idle connection cost the server?
idle = [psycopg.connect(autocommit=True) for _ in range(50)]
pids = [c.info.backend_pid for c in idle]
private = [psutil.Process(pid).memory_info().private for pid in pids]
print(f"{len(idle)} idle connections = {len(set(pids))} server processes, "
f"average private memory {sum(private) / len(private) / 2**20:.1f} MiB each")
for c in idle:
c.close()
Run it with python step01_connect_cost.py.
new connection per query : 200 queries, 37.8 ms each
one connection, reused : 2000 queries, 0.05 ms each
pool checkout per query : 2000 queries, 0.12 ms each
50 idle connections = 50 server processes, average private memory 4.3 MiB each
Read the first three lines as a ratio. A brand-new connection cost 37.8 ms on this Windows machine. The same query on a connection that was already open cost 0.05 ms, about 750 times less. Taking a connection from the pool landed at 0.12 ms, which includes the pool’s bookkeeping and the COMMIT that the with block sends when it ends (I did not separate the two). The absolute numbers depend on the operating system, on authentication and on network distance, so measure yours. The ratio is what matters: opening a connection is work you do not want on the path of every request.
The last line shows the other cost. The 50 idle connections were 50 separate server processes, and each held about 4.3 MiB of private memory on this build. That is small for one connection and not small for several hundred. Memory is not the only price. The PostgreSQL wiki page on connection counts warns that “Some internal structures allocated based on max_connections scale at O(N^2) or O(N*log(N)).” The documentation says something similar about the setting itself: “PostgreSQL sizes certain resources based directly on the value of max_connections. Increasing its value leads to higher allocation of those resources, including shared memory.”
pgbench can measure the connect cost too. Its -C option, according to the documentation, will “Establish a new connection for each transaction, rather than doing it just once per client session. This is useful to measure the connection overhead.”
Step 2: Find the knee with pgbench
A pool exists to limit concurrency, so to pick the limit you need to know how throughput behaves as concurrency grows. That depends on what each request does while it holds a connection. pgbench, the benchmarking tool that ships with PostgreSQL, can replay any SQL script from many simultaneous clients. Its -c option sets the “Number of clients simulated, that is, number of concurrent database sessions.” Its -j option sets the number of worker threads inside pgbench, and the documentation notes that “Using more than one thread can be helpful on multi-CPU machines.”
The three workloads
Three tiny script files stand for three kinds of request. The first burns CPU by counting 200,000 generated rows:
SELECT count(*) FROM generate_series(1, 200000);
The second does no work at all and waits for 10 ms, like a query that is blocked on disk, on a lock, or on another server:
SELECT pg_sleep(0.01);
The third does both in sequence, which is what a typical web request looks like: some computing, then some waiting.
SELECT count(*) FROM generate_series(1, 200000);
SELECT pg_sleep(0.01);
Count your cores
The knee depends on how many cores the database server has, so ask the machine. The first number is physical cores and the second is logical processors, which differ when hyperthreading is on:
$ python -c "import psutil; print(psutil.cpu_count(logical=False), psutil.cpu_count())"
14 14
This machine reports 14 and 14, so there is no hyperthreading to worry about.
Run the sweep
The script below runs each workload with 1, 2, 4, 8, 14, 28, 56 and 90 clients, five seconds per cell. The options are -n (skip the vacuum step that pgbench does for its own standard tables, which these scripts do not use), -M simple (plain query protocol), -T 5 (run for five seconds), -c and -j (clients and threads) and -f (the script file). It stops at 90 clients to stay safely under the 100 connections the server allows, 3 of which are reserved.
"""pgbench_sweep.py: throughput and latency against the number of client connections, for three workloads."""
import os
import re
import subprocess
CLIENTS = [1, 2, 4, 8, 14, 28, 56, 90]
WORKLOADS = ["cpu.sql", "wait.sql", "mixed.sql"]
SECONDS = 5
def run_pgbench(script, clients):
cmd = ["pgbench", "-n", "-M", "simple", "-T", str(SECONDS),
"-c", str(clients), "-j", str(min(clients, 4)), "-f", script]
out = subprocess.run(cmd, capture_output=True, text=True).stdout
tps = float(re.search(r"tps = ([\d.]+)", out).group(1))
latency = float(re.search(r"latency average = ([\d.]+) ms", out).group(1))
return tps, latency
print(f"logical processors: {os.cpu_count()}")
for script in WORKLOADS:
print(f"=== {script}")
print(f"{'clients':>8} {'tps':>9} {'latency ms':>11}")
for clients in CLIENTS:
tps, latency = run_pgbench(script, clients)
print(f"{clients:>8} {tps:>9.0f} {latency:>11.1f}")
Run it with python pgbench_sweep.py. It takes about two and a half minutes.
logical processors: 14
=== cpu.sql
clients tps latency ms
1 55 18.2
2 113 17.7
4 220 18.2
8 369 21.7
14 470 29.8
28 428 65.4
56 311 179.9
90 311 289.8
=== wait.sql
clients tps latency ms
1 62 16.0
2 125 16.1
4 250 16.0
8 500 16.0
14 875 16.0
28 1751 16.0
56 3478 16.1
90 5606 16.1
=== mixed.sql
clients tps latency ms
1 26 38.5
2 53 38.0
4 112 35.8
8 192 41.6
14 284 49.3
28 452 62.0
56 223 251.6
90 295 305.4
Read the table
For the CPU-bound script the story is textbook. Throughput roughly doubles each time the client count doubles, 55, 113 and 220 transactions per second at 1, 2 and 4 clients. It keeps rising to 470 at 14 clients, which is the number of cores, and then it falls: 428 at 28 clients, 311 at 56 and 311 at 90. At 90 clients the server finished 34% fewer queries per second than at 14, and each query took 290 ms instead of 30 ms, which is 9.7 times longer. Every client is a process that wants a core. With more runnable processes than cores the operating system has to take turns. Each query still needs the same CPU time, but now it waits in line for its turn, and the switching between processes is not free (I did not measure that part separately).
The wait-only script behaves the opposite way. Every query took 16.0 ms, even though it sleeps for 10 ms (the sleep is stretched on this machine, and I did not investigate why), so each client completes about 62 queries per second, and throughput simply keeps growing in a straight line, up to 5,606 per second at 90 clients. Waiting does not occupy a core, so a connection that is waiting costs the server almost no CPU. The price of that connection is the memory and the bookkeeping you saw in Step 1.
The mixed script sits between the two. One client completes 26 per second (38.5 ms: roughly 18 ms of CPU, 16 ms of waiting and a few round trips). Throughput rises to 284 at 14 clients and 452 at 28 clients, and then the 56-client and 90-client cells show 223 and 295. A 56-client cell that is lower than the 90-client cell should make you suspicious of a single run, so repeat the interesting cells:
"""pgbench_repeat.py: repeat the mixed workload around and past the knee to see how steady the numbers are."""
import re
import subprocess
def run_pgbench(clients):
cmd = ["pgbench", "-n", "-M", "simple", "-T", "5", "-c", str(clients), "-j", "4", "-f", "mixed.sql"]
out = subprocess.run(cmd, capture_output=True, text=True).stdout
return float(re.search(r"tps = ([\d.]+)", out).group(1))
print(f"{'clients':>8} tps in three runs")
for clients in (28, 56, 90):
runs = [run_pgbench(clients) for _ in range(3)]
print(f"{clients:>8} " + " ".join(f"{tps:>6.0f}" for tps in runs))
clients tps in three runs
28 219 462 303
56 286 340 415
90 269 312 285
At 28 clients the three runs gave 219, 462 and 303, which is a spread of more than a factor of two for the same setting. Past the knee, results on this machine are noisy, so do not hunt for an exact peak. Look for the region where adding clients stops paying. For the mixed script that region starts somewhere between 14 and 28 clients, and anything beyond roughly 28 adds nothing you can measure through this much noise.
A rule of thumb to check your reading
You can predict that region. A request keeps a core busy for its CPU time C and then waits for a time W. One connection therefore keeps a core busy only a fraction C ÷ (C + W) of the time, so it takes (C + W) ÷ C connections per core to keep every core busy. With C = 18.2 ms (the single-client latency of cpu.sql), W = 16.0 ms (the latency of wait.sql) and 14 cores, that is 14 × 34.2 ÷ 18.2 = 26.3 connections, which sits inside the 14 to 28 band that the sweep found.
The PostgreSQL wiki gives an older rule of thumb: “for optimal throughput the number of active connections should be somewhere near ((core_count * 2) + effective_spindle_count).” It adds that “Core count should not include HT threads, even if hyperthreading is enabled” and that “Effective spindle count is zero if the active data set is fully cached, and approaches the actual number of spindles as the cache hit rate falls.” Here the count is 14 physical cores and the data fits in memory, so the rule gives (14 × 2) + 0 = 28. Two different formulas land in the same band as the measurements. Treat both as starting points, as the wiki itself does when it says that “you should probably try incremental adjustments with your production system”. The same page says to “Ignore mostly-idle connections used for system monitoring and control when working out an appropriate pool size.”
Step 3: Turn the knee into a pool size with Little’s law
Step 2 told you how many connections the server can use productively. The next question is how many your application actually needs, and the tool for it is Little’s law from queueing theory. Wikipedia states it like this: “the long-term average number of customers (L) in a stationary system is equal to the long-term average effective arrival rate (λ) multiplied by the average time that a customer spends in the system (W).” In symbols, L = λW.
Translate it into pool language. A customer is a request. Being in the system means holding a connection. The arrival rate λ is requests per second, and W is how long each request holds its connection. Then L is the average number of connections that are in use at any moment. If 400 requests arrive every second and each holds a connection for 12 ms, then 400 × 0.012 = 4.8 connections are busy on average. That is why a pool of 8 can serve 400 requests per second, which is often far fewer connections than people expect.
Turn the formula around to get a ceiling. A pool of 8 connections whose requests each hold a connection for 12 ms can finish at most 8 ÷ 0.012 = 667 requests per second. However many handlers you start, the pool cannot go faster than that, and the extra handlers simply wait. Two consequences follow. A pool can be small when requests are short. And the only way to need a smaller pool, or to get more out of the same one, is to shorten the time each request holds a connection. Steps 4 and 5 show that this time is not a constant: it stretches when the server is oversubscribed, and your own code can stretch it too.
Step 4: Put the numbers into a psycopg pool
Now watch the same effect from inside a Python program. The package psycopg_pool keeps a set of open connections and lends them to threads. Its documentation describes the mechanics: “If the pool has a connection ready in its state, it is served immediately to the connection() caller, otherwise the caller is put in a queue and is served a connection as soon as it’s available.” A with pool.connection() as conn: block borrows a connection and hands it back when the block ends. The pool can grow between min_size and max_size: “If more than min_size connections are requested concurrently, new ones are created, up to max_size.” In these experiments both numbers are equal, so the pool has a fixed size and nothing about the pool itself changes during a run.
A helper that behaves like a busy web service
The helper below is a closed loop. It starts a number of handler threads, and each thread repeatedly borrows a connection, runs one request (the two statements from cpu.sql and wait.sql in a single transaction) and hands the connection back. When the time is up it reports throughput, the median and 95th-percentile latency, the average time requests spent waiting for a connection, and the average number of connections in use. Those last two come from the pool’s own counters. The documentation lists requests_wait_ms as the “Total time in the queue for the clients waiting” and usage_ms as the “Total usage time of the connections outside the pool”, and it warns that “Keys whose value is 0 may not be returned”, which is why the code reads the counters with stats.get(name, 0).
A second thread, watch_states, samples pg_stat_activity from a connection that is not part of the pool, so you can see what the server thinks the pool’s connections are doing. You will use it in Step 5. Create the file in parts as the tutorial goes on; this first part holds everything the experiments share.
"""poolbench.py: closed-loop request handlers sharing one psycopg connection pool.
Usage: python poolbench.py sweep | hoard | failfast
"""
import sys
import threading
import time
import psycopg
from psycopg_pool import ConnectionPool, PoolTimeout, TooManyRequests
# One "request": the cpu.sql statement and the wait.sql statement, in one transaction.
REQUEST = ("SELECT count(*) FROM generate_series(1, 200000)", "SELECT pg_sleep(0.01)")
def pick(sorted_values, q):
return sorted_values[int(q * (len(sorted_values) - 1))] if sorted_values else float("nan")
def watch_states(stop, samples):
"""Sample pg_stat_activity from a connection that is not part of the pool."""
with psycopg.connect(autocommit=True) as conn:
while not stop.is_set():
row = conn.execute(
"SELECT count(*) FILTER (WHERE state = 'idle in transaction'),"
" count(*) FILTER (WHERE state = 'active') "
"FROM pg_stat_activity WHERE backend_type = 'client backend' AND pid <> pg_backend_pid()"
).fetchone()
samples.append(row)
stop.wait(0.25)
def run_case(pool_size, handlers, seconds, sql=REQUEST, hold=0.0, release_first=False,
think=0.0, timeout=30.0, max_waiting=0):
pool = ConnectionPool(min_size=pool_size, max_size=pool_size, timeout=timeout,
max_waiting=max_waiting, open=False)
pool.open(wait=True, timeout=60)
latencies, errors, lock = [], {}, threading.Lock()
stop_watch, samples = threading.Event(), []
watcher = threading.Thread(target=watch_states, args=(stop_watch, samples))
watcher.start()
started = time.perf_counter()
deadline = started + seconds
def handler():
while time.perf_counter() < deadline:
t0 = time.perf_counter()
try:
with pool.connection() as conn:
for statement in sql:
conn.execute(statement).fetchall()
if hold and not release_first:
time.sleep(hold) # a slow call to another service, connection still checked out
if hold and release_first:
time.sleep(hold) # the same call, connection already handed back
with lock:
latencies.append(time.perf_counter() - t0)
except (PoolTimeout, TooManyRequests) as exc:
with lock:
errors[type(exc).__name__] = errors.get(type(exc).__name__, 0) + 1
if think:
time.sleep(think)
threads = [threading.Thread(target=handler) for _ in range(handlers)]
for t in threads:
t.start()
for t in threads:
t.join()
elapsed = time.perf_counter() - started
stop_watch.set()
watcher.join()
stats = pool.get_stats()
pool.close()
latencies.sort()
n = max(stats.get("requests_num", 0), 1)
return {
"rps": len(latencies) / elapsed,
"p50": pick(latencies, 0.50) * 1000,
"p95": pick(latencies, 0.95) * 1000,
"wait": stats.get("requests_wait_ms", 0) / n,
"in_use": stats.get("usage_ms", 0) / (elapsed * 1000),
"idle_tx": sum(s[0] for s in samples) / max(len(samples), 1),
"errors": errors,
}
Sweep the pool size
The first experiment keeps the load constant at 64 handler threads and changes only the size of the pool: 1, 2, 4, 8, 14, 28 and 64 connections. Because single runs are noisy on this machine, each pool size is run three times and the table shows the middle run by throughput.
# poolbench.py (continued)
def sweep():
print(f"{'pool':>5} {'handlers':>9} {'req/s':>7} {'p50 ms':>7} {'p95 ms':>7} {'pool wait ms':>13} {'in use':>7} {'hold ms':>8}")
for size in (1, 2, 4, 8, 14, 28, 64):
runs = sorted((run_case(size, handlers=64, seconds=5) for _ in range(3)), key=lambda run: run["rps"])
r = runs[1] # the middle run of three, by requests per second
hold = r["in_use"] / r["rps"] * 1000 # Little's law turned around: time = connections in use / requests per second
print(f"{size:>5} {64:>9} {r['rps']:>7.0f} {r['p50']:>7.1f} {r['p95']:>7.1f} {r['wait']:>13.1f} {r['in_use']:>7.1f} {hold:>8.1f}")
Add the part above to poolbench.py, together with the entry point you will see at the end of Step 6, and run it with python poolbench.py sweep. It takes about two and a half minutes.
pool handlers req/s p50 ms p95 ms pool wait ms in use hold ms
1 64 25 2199.3 3006.9 2054.6 1.0 39.5
2 64 55 1070.5 1356.8 1018.1 2.0 35.9
4 64 109 509.7 824.8 517.5 3.9 35.9
8 64 208 288.0 412.9 259.6 7.8 37.6
14 64 275 220.4 397.2 176.5 13.7 50.0
28 64 437 111.3 277.3 80.4 27.3 62.4
64 64 349 93.4 606.1 0.0 59.9 171.6
Read the pool table
The columns are the throughput in requests per second, the median and 95th-percentile latency in milliseconds, the average time a request waited for a connection, the average number of connections in use, and one derived column. hold ms is in use divided by req/s, times 1,000. That is Little’s law turned around, and it tells you how long a request kept its connection on average.
Start with the small pools. From 1 to 8 connections the hold time stays at 36 to 40 ms, which is simply the length of one request (about 18 ms of CPU, 16 ms of waiting and a few round trips), and throughput grows in proportion to the pool: 25, 55, 109 and 208 requests per second. The ceiling from Step 3 predicts it well. A pool of 4 holding connections for 35.9 ms can finish 4 ÷ 0.0359 = 111 requests per second, and it measured 109. In these rows nearly all of a request’s time is spent in the pool’s queue. At a pool of 4 the average wait for a connection was 517 ms, and the median request took 510 ms in total.
The knee shows up where you expected it. A pool of 14 gave 275 requests per second with a hold time of 50 ms, and a pool of 28 gave 437 with a hold time of 62 ms. That is the best throughput in the sweep, in line with the band you found with pgbench and with the two rules of thumb in Step 2. Notice that the hold time has begun to grow, from about 36 ms to 62 ms. The same request now takes longer, which fits cores that are close to saturated.
Then look at the last row. With a pool of 64 the wait for a connection drops to 0.0 ms, because every handler owns a connection. That looks like a fix, and it is not one. Throughput fell to 349 requests per second, 20% below the pool of 28. The hold time grew to 172 ms, 2.75 times the 62 ms at 28 connections. The 95th-percentile latency more than doubled, from 277 ms to 606 ms. Only the median improved a little, from 111 ms to 93 ms. The waiting did not go away. It moved from the pool, where you can see it and limit it, into the server, where requests compete for cores and where all you can observe is slower queries. The 36 extra connections also cost memory, as Step 1 showed.
One caution about reading these numbers. Step 2 showed that repeated runs of the same setting can differ by tens of percent, so treat small differences as noise. The 2.75-fold rise in hold time and the 2.2-fold rise in the 95th percentile are far outside that noise. The 20% drop in throughput is borderline on its own, but it points the same way as the pgbench results for the CPU-bound script, where the drop beyond the knee was much larger (34% fewer queries per second at 90 clients than at 14).
Step 5: Do not hold a connection while you wait for something else
Now a bug that is easy to write and hard to see. A request handler often does database work and also something slow that has nothing to do with the database, such as calling another service or waiting for a file. If that slow part sits inside the with pool.connection() block, the handler keeps its connection for the whole time, and the database sees nothing happening on it.
The experiment below runs the same 64 handlers against a pool of 8. Each handler does the database work from Step 4 and then makes a 50 ms call to “another service”, simulated with time.sleep. In the first run the call is inside the with block. In the second run it happens right after the block, when the connection is already back in the pool. The only difference is where one line sits, which you can see in the two release_first branches of run_case.
# poolbench.py (continued)
def hoard():
print(f"{'connection held during the 50 ms call':<38} {'req/s':>6} {'p95 ms':>7} {'idle in transaction (avg)':>26}")
for label, release_first in (("yes (call inside the with block)", False), ("no (call after the with block)", True)):
r = run_case(8, handlers=64, seconds=5, hold=0.05, release_first=release_first)
print(f"{label:<38} {r['rps']:>6.0f} {r['p95']:>7.0f} {r['idle_tx']:>26.1f}")
Add the part above to poolbench.py and run it with python poolbench.py hoard.
connection held during the 50 ms call req/s p95 ms idle in transaction (avg)
yes (call inside the with block) 82 828 4.9
no (call after the with block) 195 512 2.0
Moving one line out of the block took throughput from 82 to 195 requests per second, a factor of 2.4. I repeated the pair twice. The first row gave 83 and 83 requests per second and the second row gave 222 and 207, so the gap is far larger than the run-to-run noise.
Little’s law predicts the size of the gain. In the Step 4 sweep, a pool of 8 held each connection for 37.6 ms per request. With the 50 ms call inside the block the hold time becomes 87.6 ms, and the ceiling is 8 ÷ 0.0876 = 91 requests per second. With the call outside it is 8 ÷ 0.0376 = 213. The runs measured 82 and 195, a little below each ceiling, which is normal because a ceiling is the best case. Notice what the pool did not need: more connections. It was already big enough for the database work, and the sleeping call was what used up its capacity.
Why this bug stays hidden
The last column shows what the server saw. By default psycopg runs statements inside a transaction that it starts with the first statement and ends when the with block ends. While a handler sleeps inside the block, its connection sits in an open transaction with no query running, which the PostgreSQL documentation describes as: “The backend is in a transaction, but is not currently executing a query.” On average 4.9 of the 8 connections were in that state in the first run, against 2.0 in the second, where the only idle moments are the short gaps between statements.
That is more than a throughput problem. The documentation for idle_in_transaction_session_timeout explains another cost: “an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction; so remaining idle for a long time can contribute to table bloat.” The setting will “Terminate any session that has been idle (that is, waiting for a client query) within an open transaction for longer than the specified amount of time”, and it is off by default: “A value of zero (the default) disables the timeout.” Treat it as a safety net that cuts off stuck sessions, not as the fix. The fix is to keep slow work outside the block. Our zero-downtime migrations tutorial shows another price of an idle transaction: in its lab an ALTER TABLE that queued behind one idle transaction made reads and writes wait 2.7 seconds, and a 200 ms lock_timeout with retries cut the worst call to 0.21 seconds. If the slow call itself can hang, give it a deadline, as in our HTTP timeouts tutorial.
Step 6: Fail fast instead of queueing forever
What happens when more requests arrive than the pool can serve? By default they wait, for a long time. The pool’s timeout is “The default maximum time in seconds that a client can wait to receive a connection from the pool (using connection() or getconn()).” Its default is 30 seconds, which is longer than many clients and proxies will wait. The second limit is max_waiting: “Maximum number of requests that can be queued to the pool, after which new requests will fail, raising TooManyRequests. 0 means no queue limit.” The default is 0, so by default the queue has no limit at all. A queue without a limit turns a short overload into a long one, because requests keep waiting in line and the server may still do the work for callers that have already given up.
The next experiment uses 100 handlers against a pool of 4. Each request is a 0.1 second query, and each handler pauses 0.2 seconds between requests. A pool of 4 can finish at most 4 ÷ 0.1 = 40 of those per second, so the load is a steady overload. The experiment compares the default policy with timeout=0.25 and max_waiting=8, and it counts the two kinds of rejection (PoolTimeout and TooManyRequests, both imported from psycopg_pool).
# poolbench.py (continued)
def failfast():
sql = ("SELECT pg_sleep(0.1)",)
print(f"{'policy':<28} {'ok/s':>5} {'p95 ms':>7} {'rejected':>9} errors")
for label, kwargs in (("wait up to 30 s, unbounded", {}),
("timeout 0.25 s, max_waiting 8", {"timeout": 0.25, "max_waiting": 8})):
r = run_case(4, handlers=100, seconds=6, sql=sql, think=0.2, **kwargs)
rejected = sum(r["errors"].values())
print(f"{label:<28} {r['rps']:>5.0f} {r['p95']:>7.0f} {rejected:>9} {r['errors'] or '-'}")
Add the last part to poolbench.py. Now the file is complete, and you can run the experiment with python poolbench.py failfast.
policy ok/s p95 ms rejected errors
wait up to 30 s, unbounded 35 2618 0 -
timeout 0.25 s, max_waiting 8 35 330 2468 {'TooManyRequests': 2468}
Both policies served about 35 requests per second, close to the 40 that the pool allows, so rejecting requests did not reduce the work that got done. What changed is what the served requests experienced. With unbounded waiting the 95th-percentile latency was 2,618 ms. With the limits it was 330 ms, and 2,468 requests were turned away at once with TooManyRequests instead of being left to wait. Two repeats gave the same picture: 2,616 and 2,617 ms against 325 and 331 ms, with 2,472 and 2,468 rejections. The queue limit tripped first. Eight waiting requests take about 8 ÷ 40 = 0.2 seconds to clear, less than the 0.25 second timeout, so the timeout never fired here. It is your second line of defence for the day the server itself slows down, for example when a lock holds queries up.
In a web application, catch both exceptions and turn them into an HTTP 503 with a Retry-After header, so that clients and load balancers back off instead of piling on. Our load shedding and backpressure tutorial goes deeper on turning work away early, and our rate limiter tutorial shows how to compute an honest Retry-After value. If your application uses async code, the documentation points you to AsyncConnectionPool, whose interface is very similar.
Step 7: Split max_connections across every process that owns a pool
A pool belongs to one process. A service with 4 worker processes and a pool of 30 in each wants 120 connections, and three copies of that service want 360. PostgreSQL has a single budget, max_connections, and it keeps part of it back. The documentation says: “At most max_connections connections can ever be active simultaneously.” It also says that “Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only for superusers.” That reserve is small: “The default value is three connections.” A second setting, reserved_connections, keeps further slots for roles with the pg_use_reserved_connections privilege, and it is 0 here, as the settings listing in the setup section showed.
See what going over the budget looks like
Restart the throwaway server with a limit of only 20 connections, so that the effect is easy to see. Normal roles may use 20 minus the 3 reserved slots, which is 17.
pg_ctl -D data -m fast stop
pg_ctl -D data -l server.log -o "-p 55460 -c max_connections=20" -w start
The script below starts several pools of 8 as the role app, which has no superuser rights and therefore cannot use the reserved slots. It waits three seconds, then reports how many connections each pool holds ready, how many connections the server counts for that role, and which warnings the pools logged through Python’s logging module. Its arguments are the number of pools and their size.
"""step_budget_failure.py: several pools of 8 against a server that allows 20 connections (3 reserved)."""
import logging
import sys
import time
import psycopg
from psycopg_pool import ConnectionPool
messages = {}
class Collect(logging.Handler):
def emit(self, record):
text = record.getMessage().replace("\n", " ")
text = text.split("': ", 1)[-1] # drop the pool name so repeats collapse
messages[text] = messages.get(text, 0) + 1
logging.getLogger("psycopg.pool").addHandler(Collect())
def try_pools(count, size):
pools = [ConnectionPool("user=app", min_size=size, max_size=size, name=f"worker-{i + 1}", open=False)
for i in range(count)]
for pool in pools:
pool.open(wait=False)
time.sleep(3)
opened = [pool.get_stats().get("pool_available", 0) for pool in pools]
with psycopg.connect() as admin:
total = admin.execute("SELECT count(*) FROM pg_stat_activity WHERE usename = 'app'").fetchone()[0]
print(f"{count} pools x {size} = {count * size} wanted; connections ready per pool {opened}; "
f"server sees {total} connections for role app")
for pool in pools:
pool.close(timeout=5)
count, size = int(sys.argv[1]), int(sys.argv[2])
try_pools(count, size)
for text, n in messages.items():
print(f" pool warning x{n}: {text}")
Run python step_budget_failure.py 3 8 to start three pools of 8:
3 pools x 8 = 24 wanted; connections ready per pool [5, 6, 6]; server sees 17 connections for role app
pool warning x19: connection failed: connection to server at "127.0.0.1", port 55460 failed: FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute
Three pools of 8 want 24 connections, and the server will give a normal role only 17. The pools ended up with 5, 6 and 6 ready connections, 17 in total, and the other attempts kept failing and being retried: 19 warnings in the few seconds the script ran, each carrying the server’s answer, FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute. Nothing crashed. The program carried on with pools smaller than configured, and the only trace was a stream of warnings in the psycopg.pool logger. Now run python step_budget_failure.py 2 8, two pools of 8:
2 pools x 8 = 16 wanted; connections ready per pool [8, 8]; server sees 16 connections for role app
Two pools of 8 want 16 connections, the server counts 16, every connection is ready, and no warnings appear. The difference between the two runs is only arithmetic, and arithmetic is something you can do before deploying.
Write the budget down as code
The calculator below holds that arithmetic. usable subtracts the two reserves and an allowance for people and tools, per_process_cap divides what is left among every process that will hold a pool, busy_connections is Little’s law, and recommend returns the pool size one process should use: what the peak load needs, with headroom, but never more than the cap. Two of its inputs are assumptions of mine and not PostgreSQL settings. admin_headroom keeps a few slots free for psql sessions, migrations, monitoring and backups. surge counts extra instances that exist at the same time during a rolling deploy, if your deploy tool starts new instances before it stops the old ones.
"""poolbudget.py: how many connections may each process hold open, and how many does it need?"""
import argparse
import math
from dataclasses import dataclass
@dataclass(frozen=True)
class Plan:
max_connections: int = 100
superuser_reserved: int = 3 # superuser_reserved_connections
reserved: int = 0 # reserved_connections
admin_headroom: int = 10 # psql sessions, migrations, monitoring, backups
instances: int = 1 # application instances (pods, VMs) in steady state
surge: int = 0 # extra instances alive at once during a rolling deploy
workers: int = 1 # worker processes per instance
def usable(plan):
free = plan.max_connections - plan.superuser_reserved - plan.reserved - plan.admin_headroom
if free < 1:
raise ValueError(f"nothing left for the application: {free}")
return free
def per_process_cap(plan):
processes = (plan.instances + plan.surge) * plan.workers
cap = usable(plan) // processes
if cap < 1:
raise ValueError(f"{processes} processes cannot share {usable(plan)} connections")
return cap
def busy_connections(requests_per_second, seconds_holding):
"""Little's law: connections in use = arrival rate x time each request holds one."""
return requests_per_second * seconds_holding
def recommend(plan, peak_rps, hold_seconds, headroom=1.5):
"""Return (max_size, cap, need) for the pool of one process."""
processes = plan.instances * plan.workers
need = math.ceil(busy_connections(peak_rps / processes, hold_seconds) * headroom)
cap = per_process_cap(plan)
return max(1, min(need, cap)), cap, need
def main():
ap = argparse.ArgumentParser()
ap.add_argument("--max-connections", type=int, default=100)
ap.add_argument("--superuser-reserved", type=int, default=3)
ap.add_argument("--reserved", type=int, default=0)
ap.add_argument("--admin-headroom", type=int, default=10)
ap.add_argument("--instances", type=int, default=1)
ap.add_argument("--surge", type=int, default=0)
ap.add_argument("--workers", type=int, default=1)
ap.add_argument("--peak-rps", type=float, required=True)
ap.add_argument("--hold-ms", type=float, required=True)
a = ap.parse_args()
plan = Plan(a.max_connections, a.superuser_reserved, a.reserved, a.admin_headroom,
a.instances, a.surge, a.workers)
size, cap, need = recommend(plan, a.peak_rps, a.hold_ms / 1000)
print(f"usable for the app : {usable(plan)}")
print(f"cap per process : {cap}")
print(f"need per process (peak) : {need}")
print(f"pool max_size : {size}")
if __name__ == "__main__":
main()
Here are the tests. They pin down each rule, including the two error cases:
"""test_poolbudget.py: checks for poolbudget.py."""
import pytest
from poolbudget import Plan, busy_connections, per_process_cap, recommend, usable
def test_default_server_leaves_87_for_the_app():
assert usable(Plan()) == 87
def test_reserved_connections_are_subtracted():
assert usable(Plan(reserved=5)) == 82
def test_one_process_may_use_everything_usable():
assert per_process_cap(Plan()) == 87
def test_workers_and_instances_share_the_budget():
assert per_process_cap(Plan(instances=3, workers=4)) == 7
def test_rolling_deploy_surge_counts():
assert per_process_cap(Plan(instances=3, surge=1, workers=4)) == 5
def test_budget_that_cannot_be_split_is_an_error():
with pytest.raises(ValueError):
per_process_cap(Plan(max_connections=30, instances=10, workers=4))
def test_headroom_that_eats_everything_is_an_error():
with pytest.raises(ValueError):
usable(Plan(max_connections=13))
def test_littles_law():
assert busy_connections(400, 0.012) == pytest.approx(4.8)
def test_recommendation_rounds_up_with_headroom():
size, cap, need = recommend(Plan(), peak_rps=400, hold_seconds=0.012)
assert (size, need) == (8, 8)
def test_recommendation_never_exceeds_the_cap():
size, cap, need = recommend(Plan(instances=3, surge=1, workers=4), peak_rps=9000, hold_seconds=0.2)
assert size == cap == 5 and need > cap
def test_recommendation_is_at_least_one():
assert recommend(Plan(), peak_rps=0.1, hold_seconds=0.001)[0] == 1
def test_load_is_split_across_processes():
size, cap, need = recommend(Plan(instances=2, workers=2), peak_rps=800, hold_seconds=0.01)
assert need == 3
Run them with python -m pytest -q test_poolbudget.py:
............ [100%]
12 passed in 0.01s
Passing tests only help if they would fail when the code is wrong, so the next script plants eight bugs in poolbudget.py one at a time (forgetting the superuser reserve, forgetting reserved_connections, ignoring the deploy surge, ignoring the number of workers, dividing instead of multiplying in Little’s law, rounding down instead of up, dropping the headroom, and ignoring the cap) and counts how many tests fail for each. It restores the file afterwards.
"""mutants.py: plant one bug at a time in poolbudget.py and count how many tests notice."""
import os
import pathlib
import re
import subprocess
import sys
SRC = pathlib.Path("poolbudget.py")
ORIGINAL = SRC.read_text(encoding="utf-8")
MUTANTS = [
("forget the superuser reserve", "plan.max_connections - plan.superuser_reserved - plan.reserved",
"plan.max_connections - plan.reserved"),
("forget reserved_connections", " - plan.superuser_reserved - plan.reserved - plan.admin_headroom",
" - plan.superuser_reserved - plan.admin_headroom"),
("ignore the rolling-deploy surge", "(plan.instances + plan.surge) * plan.workers",
"plan.instances * plan.workers"),
("ignore workers per instance", "(plan.instances + plan.surge) * plan.workers",
"(plan.instances + plan.surge)"),
("Little's law divides", "requests_per_second * seconds_holding", "requests_per_second / seconds_holding"),
("round down instead of up", "math.ceil(", "int("),
("drop the headroom factor", "hold_seconds) * headroom)", "hold_seconds))"),
("recommend ignores the cap", "return max(1, min(need, cap)), cap, need", "return max(1, need), cap, need"),
]
env = dict(os.environ, PYTHONDONTWRITEBYTECODE="1")
caught = 0
try:
for name, old, new in MUTANTS:
assert ORIGINAL.count(old) == 1, name
SRC.write_text(ORIGINAL.replace(old, new), encoding="utf-8")
out = subprocess.run([sys.executable, "-m", "pytest", "-q", "-p", "no:cacheprovider", "test_poolbudget.py"],
capture_output=True, text=True, env=env).stdout
failed = int(m.group(1)) if (m := re.search(r"(\d+) failed", out)) else 0
caught += failed > 0
print(f"{name:<34} {failed} tests fail")
finally:
SRC.write_text(ORIGINAL, encoding="utf-8")
print(f"caught {caught} of {len(MUTANTS)} planted bugs")
Run it with python mutants.py:
forget the superuser reserve 4 tests fail
forget reserved_connections 1 tests fail
ignore the rolling-deploy surge 2 tests fail
ignore workers per instance 4 tests fail
Little's law divides 4 tests fail
round down instead of up 1 tests fail
drop the headroom factor 2 tests fail
recommend ignores the cap 1 tests fail
caught 8 of 8 planted bugs
Every planted bug is noticed by at least one test. Three of them (forgetting reserved_connections, rounding down instead of up, and ignoring the cap) are noticed by exactly one test each, which tells you those rules rest on a single test and are the first place to add another if you change the code.
Use it
Suppose a service runs as 3 instances with 4 worker processes each, your deploy tool adds one extra instance during a rolling deploy, the peak is 2,400 requests per second in total, and each request holds a connection for 12 ms. These numbers are an example, not a measurement.
python poolbudget.py --max-connections 100 --instances 3 --workers 4 --surge 1 --peak-rps 2400 --hold-ms 12
usable for the app : 87
cap per process : 5
need per process (peak) : 4
pool max_size : 4
The budget leaves 87 connections after the reserves and the headroom. During a deploy there are (3 + 1) × 4 = 16 processes, so each may hold at most 87 ÷ 16 = 5 connections. At peak each of the 12 steady-state processes sees 200 requests per second, which by Little’s law keeps 200 × 0.012 = 2.4 connections busy, and with 1.5 times headroom that rounds up to a pool of 4, below the cap. Now give the same service a peak of 6,000 requests per second:
python poolbudget.py --max-connections 100 --instances 3 --workers 4 --surge 1 --peak-rps 6000 --hold-ms 12
usable for the app : 87
cap per process : 5
need per process (peak) : 9
pool max_size : 5
The need is 9 connections per process and the cap is 5, so the calculator returns 5 and shows both numbers. When need exceeds cap you have three honest options. Shorten the hold time, which Step 5 and a look at your slow queries can do (our EXPLAIN ANALYZE tutorial is a good start). Raise max_connections, but only after Step 2 has told you the server can use more connections productively and Step 1 has reminded you what they cost. Or put a separate pooler between the application and the server. If the knee for your workload is around 28 connections, as it was for ours, asking the server for many more than that in total will not make it faster.
Step 8: Watch the pool from both sides
Once the pool is configured you need to see it working. There are two places to look, and they show different things. The pool knows how many requests are waiting. The server knows what its connections are doing. This script starts 12 handlers against a pool of 4, waits 1.5 seconds, and then prints both views.
"""step_monitor.py: what a pool under load looks like from the pool side and from the server side."""
import threading
import time
import psycopg
from psycopg_pool import ConnectionPool
pool = ConnectionPool(min_size=4, max_size=4, open=False)
pool.open(wait=True, timeout=30)
def handler():
end = time.time() + 3
while time.time() < end:
with pool.connection() as conn:
conn.execute("SELECT pg_sleep(0.1)")
threads = [threading.Thread(target=handler) for _ in range(12)]
for t in threads:
t.start()
time.sleep(1.5)
stats = pool.get_stats()
keys = ("pool_size", "pool_available", "requests_waiting", "requests_queued")
print("pool side :", {k: stats.get(k, 0) for k in keys})
with psycopg.connect(autocommit=True) as admin:
rows = admin.execute(
"SELECT state, count(*) FROM pg_stat_activity "
"WHERE backend_type = 'client backend' AND pid <> pg_backend_pid() GROUP BY state ORDER BY state"
).fetchall()
print("server side:", rows)
for t in threads:
t.join()
pool.close()
Run it with python step_monitor.py against the server with 100 connections (stop the 20-connection server and start the first one again):
pool side : {'pool_size': 4, 'pool_available': 0, 'requests_waiting': 8, 'requests_queued': 60}
server side: [('active', 4)]
The pool side shows 4 connections, none available, 8 requests waiting in the queue, and 60 requests that had to be queued so far. The server side shows 4 sessions, all active. The 8 waiting requests do not exist for the database at all. They are visible only in the pool’s statistics, so those are the numbers to export to your monitoring system. The documentation says that “The values can be sent to a monitoring system such as Graphite or Prometheus”, and that get_stats() and pop_stats() “return the same values, but the latter reset the counters after its use”, so use pop_stats() if you want a per-interval number. Alert on a request queue that stays above zero, on rising requests_wait_ms, and on idle in transaction sessions in pg_stat_activity.
Common mistakes and how to check each fix
Opening a connection or a pool for every request
Step 1 priced it: 37.8 ms against 0.12 ms on this machine. Create the pool once when the process starts and close it when the process stops (our graceful shutdown tutorial covers the stop sequence). To check, look at the backend_start column of pg_stat_activity, which for client backends is the time the client connected. In a service that pools correctly most sessions are old: psycopg_pool, for instance, replaces a connection only after its max_lifetime, which defaults to one hour.
Raising the pool size to cure slowness
Step 4 showed that a pool of 64 removed the wait for a connection and made the tail worse. Before you raise the number, look at the hold time and at what the server is doing. If the hold time is already well above the length of one request, you are past the knee.
Forgetting that every worker has its own pool
Step 7. Multiply the pool size by the processes per instance and by the instances, add the deploy surge, and compare it with max_connections minus the reserves. To check, run the calculator with your real numbers and look at pg_stat_activity during a deploy.
Holding a connection during slow work
Step 5. To check, sample pg_stat_activity for idle in transaction. A healthy service shows few of them and none for long.
Leaving the 30-second timeout and the unlimited queue
Step 6. To check, overload a staging copy on purpose and look at the 95th-percentile latency of the requests that succeed.
Letting session state leak to the next borrower
A setting applied to a pooled connection can still be there when another request borrows it. Our row-level security tutorial measures that leak with set_config and shows how to scope a setting to the transaction.
Sizing on one machine and shipping to another
The knee moved with the mix of CPU and waiting in Step 2, and it will move with your core count and your data. Treat the pool size as a configuration value you re-measure when the hardware or the queries change.
Confirm the whole thing works end to end
Here is the whole procedure on one page, in the order you would apply it to a real service:
- Run the Step 2 sweep with your own queries in the script files and note the band of client counts where throughput stops growing.
- Estimate the busy connections at your peak with Little’s law (requests per second times the time each request holds a connection).
- Set each process’s pool
max_sizeto the smaller of that need, with headroom, and the cap from the budget calculator. - Set
timeoutandmax_waiting, and turnPoolTimeoutandTooManyRequestsinto HTTP 503 responses. - Run the Step 4 sweep against your settings. You should see a small wait for a connection at normal load and a hold time within a small multiple of one request’s length.
- In production, watch
requests_waiting,requests_queuedandrequests_wait_msfrom the pool and the session states inpg_stat_activity. The queue should be empty most of the time, andidle in transactionshould stay near zero.
Where to go next
A separate pooler process such as PgBouncer can sit between many application connections and a few server connections. It is a different tool with its own trade-offs and is outside this tutorial. Inside psycopg, the documentation also describes a null pool, and it says: “A null pool is not only a configuration convenience, but can also be used to regulate the access to the server by a client program.” The pool’s size can be changed at runtime with resize(), so a measured value does not have to be baked into a deploy. The psycopg documentation has a section called “What’s the right size for the pool?” and its answer is worth keeping in mind: “Big question. Who knows. However, probably not as large as you imagine.” You now have a way to replace the words “Who knows” with a measurement.
Sources
- PostgreSQL 18 documentation: connection settings (
max_connections,superuser_reserved_connections,reserved_connections), client defaults (idle_in_transaction_session_timeout), pgbench and the statistics views (pg_stat_activitystates). - psycopg 3 documentation: connection pools and the pool API reference (
timeout,max_waiting, statistics). - PostgreSQL wiki: Number of Database Connections, and the HikariCP write-up of the same formula, About Pool Sizing.
- Wikipedia: Little’s law.








No Comment! Be the first one.