TRENDING
A person sweeping a metal detector across a wet sandy beach with a sea fort on the horizon
November 22, 2026
How to Write YARA Rules With YARA-X to Hunt Malicious Python .pth Startup Files and Measure False Positives Before You Trust Them
A peeling black-and-white metal door standing ajar, with a dark empty hall and bright windows beyond
November 22, 2026
AWS’s “Found a Zero-Day on Their Own” Post Never Names the Product; the Advisory Behind Its Link Shows a Server That Ships With No Login, a README Now Saying Keep It Off the Internet, and a Fix Merged October 19 That PyPI and Docker Hub Still Do Not Carry
A hand paging through index cards in an open wooden library card-catalog drawer labeled Agriculture, with a second drawer open below to the right
November 22, 2026
Microsoft Tells Customers Unauthorized Parties Read Admin Email Addresses and Tenant Metadata Through an Entra Connect Health Flaw Introduced July 23, but Gives No Tenant Count or Start Date, and Its Own Docs Send Tenant Security Notices to the Same Kinds of Contacts
Swimmers in separate lanes of a blue swimming pool, seen from above the water, with lane lines running across the picture
November 22, 2026
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
Workers in caps lean over a 1913 Ford assembly line, each assembling parts as they pass on the moving line
November 22, 2026
OpenAI’s Performance Factory Turns Regression Response Into a Task Queue for Agents, but Its Numbers Stop at 600 Root-Caused Cases in 1,700 Investigations and a 70% Grade on 100 Suggestions, and in a Toy World Each of Its Reviewer’s Two Counterfactual Questions Can Return a Confident Wrong Answer
22 Nov 2026
SXZ.io SXZ.io
  • Home
Search the Site
Popular Searches:
Technology Amazon AI
Recent Posts
The sun setting over the sea behind a pyramid-shaped stone monument at Cape Soya, the northernmost point of Japan
How to Retire an API Endpoint in FastAPI: Send Deprecation and Sunset Headers, Find the Clients Still Calling It, Schedule Brownouts, and Block an Early Removal With oasdiff
November 22, 2026
Brass sextant in an open wooden case inside a museum display, an instrument for measuring the angle to the horizon
MIT Technology Review’s “Most Misunderstood Graph” Update Reports METR’s Judgment Findings Mostly Fairly, but It Never Says Each Point Is a 50% Success Rate or Gives the New 3.6-Month Doubling Time and 2.1 to 4.9 Month Range, and It Credits OpenAI With a Comparison METR Built
November 22, 2026
Brass telescope on a wooden tripod inside a white observatory dome with the roof slit open to the sky
Rackspace Says Leaked Credentials Let an Intruder Act as Legitimate Users in Some Spot Customer Organizations and Reach Their Kubernetes Secrets, a Platform9 Co-Founder Names a Breached Observability System as the Root Cause, and the Status Page Says Only “System Maintenance Operations”
November 22, 2026
SXZ.io SXZ.io
  • Home

Categories

Articles 310 Posts
News 316 Posts
Learning Hub 282 Posts
Home/Learning Hub/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
Learning Hub

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...

November 19, 2026 33 Min Read
26

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_pool pool 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_connections across 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:

  1. Run the Step 2 sweep with your own queries in the script files and note the band of client counts where throughput stops growing.
  2. Estimate the busy connections at your peak with Little’s law (requests per second times the time each request holds a connection).
  3. Set each process’s pool max_size to the smaller of that need, with headroom, and the cap from the budget calculator.
  4. Set timeout and max_waiting, and turn PoolTimeout and TooManyRequests into HTTP 503 responses.
  5. 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.
  6. In production, watch requests_waiting, requests_queued and requests_wait_ms from the pool and the session states in pg_stat_activity. The queue should be empty most of the time, and idle in transaction should 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_activity states).
  • 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.

Tags:

Connection PoolingPerformance EngineeringpgbenchPostgreSQLpsycopgPython

Share

Workers in caps lean over a 1913 Ford assembly line, each assembling parts as they pass on the moving line
Previous Post

OpenAI’s Performance Factory Turns Regression Response Into a Task Queue for Agents, but Its Numbers Stop at 600 Root-Caused Cases in 1,700 Investigations and a 70% Grade on 100 Suggestions, and in a Toy World Each of Its Reviewer’s Two Counterfactual Questions Can Return a Confident Wrong Answer

A hand paging through index cards in an open wooden library card-catalog drawer labeled Agriculture, with a second drawer open below to the right
Next Post

Microsoft Tells Customers Unauthorized Parties Read Admin Email Addresses and Tenant Metadata Through an Entra Connect Health Flaw Introduced July 23, but Gives No Tenant Count or Start Date, and Its Own Docs Send Tenant Security Notices to the Same Kinds of Contacts

No Comment! Be the first one.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Latest
22 Nov
How to Retire an API Endpoint in FastAPI: Send Deprecation and Sunset Headers, Find the Clients Still Calling It, Schedule Brownouts, and Block an Early Removal With oasdiff
22 Nov
MIT Technology Review’s “Most Misunderstood Graph” Update Reports METR’s Judgment Findings Mostly Fairly, but It Never Says Each Point Is a 50% Success Rate or Gives the New 3.6-Month Doubling Time and 2.1 to 4.9 Month Range, and It Credits OpenAI With a Comparison METR Built
Trending
November 22, 2026
How to Retire an API Endpoint in FastAPI: Send Deprecation and Sunset Headers, Find the Clients Still Calling It, Schedule Brownouts, and Block an Early Removal With oasdiff
November 22, 2026
MIT Technology Review’s “Most Misunderstood Graph” Update Reports METR’s Judgment Findings Mostly Fairly, but It Never Says Each Point Is a 50% Success Rate or Gives the New 3.6-Month Doubling Time and 2.1 to 4.9 Month Range, and It Credits OpenAI With a Comparison METR Built
November 22, 2026
Rackspace Says Leaked Credentials Let an Intruder Act as Legitimate Users in Some Spot Customer Organizations and Reach Their Kubernetes Secrets, a Platform9 Co-Founder Names a Breached Observability System as the Root Cause, and the Status Page Says Only “System Maintenance Operations”
November 21, 2026
How to Write YARA Rules With YARA-X to Hunt Malicious Python .pth Startup Files and Measure False Positives Before You Trust Them
November 21, 2026
AWS’s “Found a Zero-Day on Their Own” Post Never Names the Product; the Advisory Behind Its Link Shows a Server That Ships With No Login, a README Now Saying Keep It Off the Internet, and a Fix Merged October 19 That PyPI and Docker Hub Still Do Not Carry
November 21, 2026
Microsoft Tells Customers Unauthorized Parties Read Admin Email Addresses and Tenant Metadata Through an Entra Connect Health Flaw Introduced July 23, but Gives No Tenant Count or Start Date, and Its Own Docs Send Tenant Security Notices to the Same Kinds of Contacts

Related Posts

A laptop wrapped in a chain and padlock, illustrating least-privilege controls for AI agents.
Learning Hub

How to Secure Tool-Using AI Agents Before They Touch Production

June 8, 2026
Colorful sticky notes arranged on an office wall, symbolizing governance checklists and planning.
Learning Hub

AI Governance for Agentic Apps: A Practical Checklist for Builders

June 8, 2026
A technician connects green fiber optic cables at a data center, representing a private production inference endpoint.
Learning Hub

How to Deploy a Fine-Tuned LLM Behind a Private Production Inference Endpoint

June 8, 2026
Narrow aisle behind black supercomputer racks in a data center
Learning Hub

Kubernetes SELinux Volume Labeling: What Cluster Operators Should Audit Before v1.37

June 8, 2026
SXZ.io SXZ.io
  • [email protected]

Categories

Articles
Learning Hub
News

All Rights Reserved by SXZ.io ©2026