Connection pool fundamentals

Capsule overview

Before tuning a single parameter, you need to understand what exactly "a connection to PostgreSQL" is, why opening one costs so much, and what a pool does with those connections. Without that mental model, the parameters you're going to adjust in the following capsules (pool_size, max_overflow, pool_recycle) are just magic knobs.

This capsule builds that mental model from scratch. It doesn't assume you already know pooling — you saw it mentioned in guide #8 but here you break it down for real.

By the end you'll be able to:

• Explain what physically happens when your app opens a connection to PostgreSQL: TCP handshake, authentication, process assignment, RAM assignment.
• Distinguish between three concepts that get confused: the SQLAlchemy pool (client), the external pool (PgBouncer), and PostgreSQL's max_connections (server).
• Identify the four typical states of a connection in pg_stat_activity (active, idle, idle in transaction, idle in transaction (aborted)) and what each one means.
• Anticipate the costs of "one more connection": ~10MB of RAM per connection in PostgreSQL, opening latency, contention on internal locks.

Mental model: a hotel with limited rooms and guests who arrive in waves

Imagine a hotel with 100 rooms. Each guest who arrives needs a room. A guest can:

• Arrive and check in (TCP handshake + authentication = check-in: it takes time).
• Stay active in the lobby doing things (query running).
• Be in their room without asking for anything (idle).
• Be in their room but with a half-finished session (idle in transaction — dangerous).
• Leave (close connection = check-out).

If there are only 100 rooms and 200 guests arrive at once, the last 100 wait at the door or leave.

Three ways to handle this:

Option 1 (no pool): each guest who enters does a full check-in, uses the room for a while, does a check-out. The check-in/check-out process takes so much time that the hotel spends more time managing arrivals than serving guests. This is opening and closing a connection for each query — it works in demos, collapses in production.

Option 2 (client pool): the hotel keeps a fixed group of "permanent" guests. When a staff member needs one, they grab it from the group, use it, return it. If the group is empty, they wait or create a temporary one. This is the SQLAlchemy pool.

Option 3 (external pool + client pool): between the hotel and the visitors, there's a concierge who receives people, puts them in an orderly waiting room, and when a room frees up assigns the next one. The hotel only sees the concierge's "permanent guests", never the visitors directly. This is PgBouncer + the SQLAlchemy pool.

Each option has tradeoffs we're going to break down. But first, you need to see the real cost of "a connection to PostgreSQL".

Anatomy of a connection to PostgreSQL

When your app calls engine.connect() or asyncpg runs an await pool.acquire(), this happens physically:

1. TCP handshake (3-way: SYN, SYN-ACK, ACK)        →  ~1ms on LAN, 30-100ms cross-region
2. SSL handshake (if enabled)                       →  ~10-50ms
3. SCRAM-SHA-256 authentication (several rounds)    →  ~5-20ms
4. PostgreSQL fork() of a new backend process       →  ~1-5ms + RAM alloc
5. Session setup (search_path, application_name)    →  ~1ms
6. Connection ready for queries

Typical total: 50-200ms per new connection. Cross-region (app in us-east, DB in eu-west) it can exceed 500ms.

Why the fork() costs

PostgreSQL is not threaded. Each connection gets its own operating system process (a postgres backend). That means:

• ~10MB of RAM minimum per connection, just for the process.
• More memory if the session runs queries that require work_mem (sorts, hash joins).
• Kernel context switching cost when many processes compete.
• PostgreSQL internal locks (ProcArrayLock, etc.) that contend when there are hundreds of processes.

This is the reason max_connections rarely exceeds 100-200 in normal setups: raising it doesn't scale linearly, and past a certain point performance worsens (more connections = more contention).

Verify it in your own PostgreSQL

-- See the current connection limit:
SHOW max_connections;
-- Typically: 100

-- See how many are in use now:
SELECT count(*) FROM pg_stat_activity;

-- See the breakdown by state:
SELECT state, count(*)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()  -- exclude your own connection
GROUP BY state;

-- Example output:
--      state         | count
-- -------------------+-------
--  active            |     3
--  idle              |    14
--  idle in transaction |  1
--                    |     2  (background workers, no state)

Memory PostgreSQL consumes per connection

-- A rough estimate:
SELECT
    setting::int AS max_connections,
    setting::int * 10 AS approx_mem_mb_min,
    setting::int * 30 AS approx_mem_mb_max
FROM pg_settings
WHERE name = 'max_connections';

-- Example: max_connections=100 → between 1GB and 3GB just in backend process memory

This doesn't count shared_buffers, wal_buffers, or work_mem per query. On 4GB RAM servers, leaving max_connections=500 is a recipe for OOM.

Lifecycle of a connection without a pool

So you understand what a pool solves, look first at what happens without one:

# Antipattern: open a connection for each query
import psycopg

def get_user_by_id(user_id: int):
    conn = psycopg.connect("postgresql://user:pass@localhost/db")  # ← 50-200ms
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
            return cur.fetchone()  # ← 5ms
    finally:
        conn.close()  # ← 1-5ms

# Total time per request: ~60-210ms.
# "Useful" time doing the query: 5ms (~3-8%).
# The rest is overhead of opening/closing the connection.

At 100 sustained RPS, this pattern:

• Makes 100 new connections per second.
• Each one competes for PostgreSQL's max_connections.
• Generates CPU load on the server just to do the repeated fork().
• p50 latency dominated by the handshake, not by the query.

That's why it's never done in production. Pooling is the answer.

Lifecycle of a connection with a pool (client)

With a pool, the pattern changes to:

# Pattern with the SQLAlchemy pool
from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+psycopg://user:pass@localhost/db",
    pool_size=10,        # ← keeps 10 connections permanently open
    max_overflow=5,      # ← can open up to 5 extras on demand
)

def get_user_by_id(user_id: int):
    with engine.connect() as conn:  # ← 0ms if there's a connection in the pool, 50-200ms if not
        result = conn.execute(
            text("SELECT * FROM users WHERE id = :id"),
            {"id": user_id}
        )
        return result.fetchone()  # ← 5ms
    # On exiting the with, the connection returns to the pool, it doesn't close

The key change: engine.connect() doesn't open a new connection — it grabs one from the pool if available. It only opens a new one if the pool is empty and max_overflow allows it. And on closing the with, it doesn't close the TCP connection — it returns it to the pool so the next request reuses it.

Amortized time per request in a steady state: ~5ms (only the query). The handshake is paid once at the start of the process, not for each request.

Pool states

A pool at any moment has three types of connections:

┌─────────────────────────────────────────────────────┐
│ SQLAlchemy pool (pool_size=10, max_overflow=5)      │
├─────────────────────────────────────────────────────┤
│ Checked out (in use): 7                             │
│ Available (available to grab): 3                    │
│ Overflow open: 0                                    │
│                                                     │
│ Current total capacity: 10 (no overflow)            │
│ Max possible capacity: 15 (with overflow)           │
└─────────────────────────────────────────────────────┘

When a request asks for a connection:

1. If there are available ones: it delivers one immediately (0ms overhead).
2. If all are in use but current_size < pool_size + max_overflow: it opens a new one (50-200ms overhead, just once).
3. If all are in use and it already reached pool_size + max_overflow: it waits up to pool_timeout seconds (default 30s).
4. If pool_timeout passes without one freeing up: it raises QueuePool limit of size X overflow Y reached, connection timed out.

That error in production is the one you want to avoid. Capsules 03 and 07 teach you to size it so it rarely appears.

The four states of a connection in pg_stat_activity

pg_stat_activity is PostgreSQL's official view for inspecting connections in real time. It's your first debugging tool when the pool gives problems.

SELECT pid, usename, application_name, state,
       query_start, state_change, wait_event,
       left(query, 60) AS query_preview
FROM pg_stat_activity
WHERE datname = 'bookstore'
  AND pid <> pg_backend_pid()
ORDER BY state, query_start;

The four states that matter:

1. active

The connection is running a query right now. Expected, normal. You only worry if you have many active ones for a long time (slow queries saturating the pool).

state  | wait_event | query_preview
-------+------------+----------------------------------
active | NULL       | SELECT * FROM books WHERE author_

2. idle

The connection is open but not doing anything. It's what you expect from pool connections between requests. It's not a problem — the pool keeps them that way on purpose.

state | wait_event | query_preview
------+------------+--------------
idle  | ClientRead | (NULL)

3. idle in transaction

Here's where the problems begin. The connection started a transaction (BEGIN) but didn't do a COMMIT or ROLLBACK. It holds locks, holds MVCC snapshots, and blocks autovacuum.

Typical causes:

• An endpoint forgot to do await session.commit().
• An exception interrupted the code before the commit, without handling the rollback.
• The client disconnected (HTTP timeout) but the transaction stayed hung.

state               | wait_event | query_preview
--------------------+------------+----------------------
idle in transaction | ClientRead | UPDATE orders SET ...

If you see this in pg_stat_activity with a state_change from minutes ago, there's a leak. Capsule 04 teaches you the FastAPI patterns that prevent it.

4. idle in transaction (aborted)

The transaction had an error but no rollback was done. The connection is useless until someone does a ROLLBACK. SQLAlchemy normally handles this, but if your code catches SQL exceptions and doesn't propagate, you can end up with connections in this state.

Three levels of pool: client, proxy, server

This is confusion #1 when talking about pooling. There are three places where the connection cap is counted:

┌─────────────────────────────┐
│  FastAPI (instance 1)       │
│  pool_size=10, overflow=5   │  ← SQLAlchemy pool (client)
│  Maximum: 15 connections    │
└─────────────────────────────┘
              ↓
┌─────────────────────────────┐
│  PgBouncer                  │
│  default_pool_size=20       │  ← External pool (proxy)
│  max_client_conn=200        │
└─────────────────────────────┘
              ↓
┌─────────────────────────────┐
│  PostgreSQL                 │
│  max_connections=100        │  ← Server's absolute cap
└─────────────────────────────┘

Who has the final say is PostgreSQL. If max_connections=100 and all your combined pools try to open 150, the last 50 get FATAL: too many connections for role. PgBouncer (when you add it in capsules 05-06) serves precisely so your apps never open real connections beyond what PostgreSQL can handle.

Without PgBouncer (only the SQLAlchemy pool):

4 FastAPI instances × pool_size=20 = 80 potential real connections to PostgreSQL.
If max_connections=100, there's margin but it's tight.
If you add a 5th instance → 100 connections → you saturate.

With PgBouncer in transaction mode:

4 FastAPI instances × pool_size=20 = 80 connections to PgBouncer (not to PostgreSQL).
PgBouncer multiplexes those 80 over, for example, 25 real connections to PostgreSQL.
PostgreSQL sees 25 connections, not 80. Ample margin.
You can scale to 10 FastAPI instances without touching PostgreSQL.

That multiplexing is PgBouncer's magic and why it's essential past a certain scale.

Why this matters in real work

1. It's the #1 problem that takes down APIs when they grow. "The app works in staging" + "the app fails in production at 100 RPS" usually has misconfigured pooling at the root. The one who knows pooling is the one who keeps the app up during Black Friday.

2. Without understanding the connection lifecycle, all the parameters are magic. pool_recycle=3600 means nothing if you don't understand that old connections accumulate state and firewalls cut idle ones. This capsule gives you the "why" of the parameters you'll touch later.

3. pg_stat_activity is debugging table stakes. In any senior backend role, they'll ask you to "diagnose why the app is slow" and the first query you'll run is SELECT * FROM pg_stat_activity. If you don't understand the states, you don't know how to interpret what you see.

4. Three levels of pool appears in any serious setup. RDS Proxy, Supabase Pooler, Neon Pooler — they all use PgBouncer internally. Understanding the three levels is understanding why sometimes your app fails even though "you yourself didn't add anything".

5. PostgreSQL memory scales with connections. In senior interviews they ask you "why don't you raise max_connections to 1000?". If you can't answer with numbers (RAM per connection, contention on ProcArrayLock), you stay mid-level.

Traps and common mistakes

Mistake 1 (conceptual): confusing max_connections with pool_size

Symptom: "I configured max_connections=500 in PostgreSQL but my app still gives 'pool exhausted'."

Why it happens: they're two different things. max_connections is the PostgreSQL server's limit. pool_size is the pool of your SQLAlchemy client. Raising max_connections doesn't affect how many connections your SQLAlchemy pool tries to use. If pool_size=10, your app never opens more than 10 (without overflow), regardless of max_connections.

How to distinguish: "pool exhausted" = client problem, raise pool_size. "too many connections for role" = server problem, raise max_connections or add PgBouncer.

How to fix it: understand the three levels of pool. Each one has its parameter and its error.

Mistake 2 (operational): raising max_connections without understanding the cost

Symptom: "I raised max_connections to 1000 and now PostgreSQL consumes 30GB of RAM just in backend processes."

Why it happens: each connection = a backend process = ~10-30MB of RAM minimum. 1000 connections × 20MB = 20GB just for connections, not counting shared_buffers or active queries.

How to distinguish: monitor PostgreSQL's memory after raising max_connections. If it grows linearly with allowed connections, this is it.

How to fix it: don't raise max_connections arbitrarily. If you need more connection capacity, add PgBouncer (capsule 05). PgBouncer multiplexes many client connections over few real connections.

Mistake 3 (conceptual): assuming that "idle in transaction" is benign

Symptom: "I see 5 connections in the 'idle in transaction' state for 20 minutes. Is it normal?"

Why it happens: No, it's not normal. It means some endpoint started a BEGIN but never did a COMMIT/ROLLBACK. That connection:

• Holds locks that can block other queries.
• Prevents autovacuum on the tables it touched.
• Holds an MVCC snapshot that grows bloat indefinitely.

How to distinguish: pg_stat_activity shows state = 'idle in transaction' with a state_change from minutes/hours ago.

How to fix it: audit the suspicious endpoints (the ones that appear in query before the idle). Ensure that all AsyncSessions close with a context manager or that commit/rollback is called explicitly. PostgreSQL has idle_in_transaction_session_timeout to kill these connections automatically — configure it at 30s or 60s in production so the leaks don't accumulate.

Mistake 4 (operational): not separating concerns between the client pool and the external pool

Symptom: "I enabled PgBouncer but kept pool_size=20 in SQLAlchemy. The throughput didn't improve."

Why it happens: if your client pool is still the bottleneck, adding PgBouncer doesn't help. PgBouncer helps when your client tries to open more connections than PostgreSQL can handle. If the client never reaches that amount because its own pool saturates first, PgBouncer isn't exercised.

How to distinguish: if SHOW POOLS in PgBouncer always shows cl_active < default_pool_size, PgBouncer isn't being the bottleneck.

How to fix it: raise the client pool so the pressure moves toward PgBouncer. Capsule 07 covers how to size the two pools together.

Exercises

Exercise 1: measure the cost of opening connections without a pool

Write a Python script that opens and closes 100 connections sequentially with psycopg (no pool) and measures the total time. Compare against opening 100 connections reusing a single one from the SQLAlchemy pool.

# bench_pool.py
import time
import psycopg
from sqlalchemy import create_engine, text

DSN = "postgresql://bookstore:bookstore@localhost:5432/bookstore"
SQLA_DSN = "postgresql+psycopg://bookstore:bookstore@localhost:5432/bookstore"

# 1. No pool: open/close a connection for each query
def no_pool():
    start = time.perf_counter()
    for _ in range(100):
        conn = psycopg.connect(DSN)
        with conn.cursor() as cur:
            cur.execute("SELECT 1")
            cur.fetchone()
        conn.close()
    return time.perf_counter() - start

# 2. With pool: a single reused connection
def with_pool():
    engine = create_engine(SQLA_DSN, pool_size=5, max_overflow=0)
    start = time.perf_counter()
    for _ in range(100):
        with engine.connect() as conn:
            conn.execute(text("SELECT 1")).fetchone()
    elapsed = time.perf_counter() - start
    engine.dispose()
    return elapsed

print(f"No pool:    {no_pool():.3f}s")
print(f"With pool:  {with_pool():.3f}s")

No pool:    2.150s
With pool:  0.080s

Exercise 2: inspect connection states with pg_stat_activity

Connect to your PostgreSQL and show how many connections are in each state. Then open 3 connections in parallel from Python that do a BEGIN but no COMMIT, and verify that they appear as idle in transaction.

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c "
SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'bookstore' AND pid <> pg_backend_pid()
GROUP BY state;
"

 state | count
-------+-------
 idle  |     2
       |     1

# leak_in_tx.py
import time
import psycopg

DSN = "postgresql://bookstore:bookstore@localhost:5432/bookstore"

# Open 3 connections, do BEGIN, NO commit
conns = []
for _ in range(3):
    conn = psycopg.connect(DSN, autocommit=False)
    with conn.cursor() as cur:
        cur.execute("UPDATE books SET title = title WHERE id = 1")
    conns.append(conn)

print("3 connections in 'idle in transaction'. Check pg_stat_activity from another terminal.")
print("Press Enter to close and release...")
input()

for conn in conns:
    conn.rollback()
    conn.close()

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c "
SELECT pid, state, query_start, state_change,
       left(query, 50) AS query_preview
FROM pg_stat_activity
WHERE datname = 'bookstore' AND pid <> pg_backend_pid()
ORDER BY state;
"

  pid  |        state        |        query_start         | query_preview
-------+---------------------+----------------------------+----------------------------
 12345 | idle in transaction | 2026-05-02 10:30:15.123    | UPDATE books SET title = ti
 12346 | idle in transaction | 2026-05-02 10:30:15.124    | UPDATE books SET title = ti
 12347 | idle in transaction | 2026-05-02 10:30:15.125    | UPDATE books SET title = ti

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

Exercise 3: understand what happens when the pool fills up

Create an engine with pool_size=2, max_overflow=0, pool_timeout=5. Open 3 connections in parallel. Predict what happens with the third. Then run it and verify.

# pool_exhaustion.py
import threading
import time
from sqlalchemy import create_engine, text

DSN = "postgresql+psycopg://bookstore:bookstore@localhost:5432/bookstore"
engine = create_engine(DSN, pool_size=2, max_overflow=0, pool_timeout=5)

def hold_connection(idx):
    print(f"[{idx}] trying connection...")
    try:
        with engine.connect() as conn:
            print(f"[{idx}] GOT connection")
            conn.execute(text("SELECT pg_sleep(10)"))  # blocks 10s
            print(f"[{idx}] finished")
    except Exception as e:
        print(f"[{idx}] ERROR: {type(e).__name__}: {str(e)[:80]}")

threads = [threading.Thread(target=hold_connection, args=(i,)) for i in range(3)]
for t in threads:
    t.start()
    time.sleep(0.1)  # ensure order
for t in threads:
    t.join()

[0] trying connection...
[0] GOT connection
[1] trying connection...
[1] GOT connection
[2] trying connection...
[2] ERROR: TimeoutError: QueuePool limit of size 2 overflow 0 reached, connection time...
[0] finished
[1] finished

Exercise 4: count the memory PostgreSQL consumes per connection

Measure how much RAM each PostgreSQL backend process consumes in your installation. Compare with the rough "10MB per connection" estimate.

# In the PostgreSQL container:
docker exec -it bookstore-pg ps aux | grep postgres

USER     PID  RSS COMMAND
postgres   1 24000 postgres
postgres  20  8000 postgres: checkpointer
postgres  21  7000 postgres: background writer
postgres  22  9000 postgres: walwriter
postgres  23 11000 postgres: autovacuum launcher
postgres  24  8500 postgres: stats collector
postgres  35 16000 postgres: bookstore bookstore [local] idle
postgres  36 18000 postgres: bookstore bookstore [local] SELECT

docker exec -it bookstore-pg ps -o rss,command -p $(docker exec bookstore-pg pgrep -f 'postgres: bookstore' | tr '\n' ',' | sed 's/,$//')

  RSS COMMAND
16000 postgres: bookstore bookstore [local] idle
18000 postgres: bookstore bookstore [local] SELECT

If max_connections=100 and each backend consumes 18MB:
  100 × 18MB = 1800MB = 1.8GB just in backend processes.

If max_connections=500:
  500 × 18MB = 9000MB = 9GB. ← unacceptable on small servers.

SELECT
    setting AS max_conn,
    pg_size_pretty(setting::bigint * 18 * 1024 * 1024) AS estimated_min_mem
FROM pg_settings
WHERE name = 'max_connections';

Exercise 5: identify the three levels of pool in your current setup

Document for the bookstore you're building:

• How many FastAPI instances you run in production.
• SQLAlchemy's pool_size and max_overflow.
• PostgreSQL's max_connections.
• Calculate: how many potential real connections can you open? Do you stay within the limit?

Current setup:
- 4 FastAPI instances (each with uvicorn, 1 worker)
- pool_size=10, max_overflow=5 on each instance
- max_connections=100 in PostgreSQL

Calculation:
- Per instance: 10 + 5 = 15 potential connections
- Total cluster: 4 × 15 = 60 connections
- max_connections - reserved (autovacuum, replication, superuser): 100 - 10 = 90
- Margin: 60 / 90 = 67% of the cap

Diagnosis:
- Tight margin but viable TODAY.
- If you scale to 6 instances: 6 × 15 = 90 connections. Right at the cap.
- If you scale to 8 instances: 8 × 15 = 120. You exceed max_connections → "too many connections".

Recommendation:
- Before going past 5 instances, introduce PgBouncer.
- With PgBouncer (transaction mode, default_pool_size=25),
  PostgreSQL sees only 25 real connections regardless of how many instances you have.
- You can scale from 4 to 20 instances without touching PostgreSQL.

Summary and next step

In this capsule you:

• Built the mental model: client pool, external pool, server's max_connections.
• Understood the physical cost of a connection: TCP handshake + authentication + process fork = 50-200ms + ~10MB of RAM.
• Identified the four states of pg_stat_activity and why idle in transaction is dangerous.
• Compared the lifecycle without a pool (each query pays the handshake) vs with a pool (amortized handshake).
• Calculated three levels of pool to diagnose where your bottleneck is.

Before moving on, you should be able to:

• Explain why max_connections=1000 isn't a solution (memory + contention).
• Distinguish when "pool exhausted" is the client's vs the server's.
• Inspect pg_stat_activity and read the states.
• Calculate total potential connections in a multi-instance cluster.

Next capsule — SQLAlchemy pool tuning. Now that you understand what a pool is and why it exists, you're going to tune the client pool in SQLAlchemy 2.0. We're going to break down pool_size, max_overflow, pool_pre_ping, pool_recycle, pool_timeout: what each one does, what default it brings, and how to choose values with criteria instead of copying them. It's the capsule you'll reference most later in production.

Resources

1. PostgreSQL 16 — pg_stat_activity — the official reference for the connection state view.
2. SQLAlchemy — Connection Pooling overview — the canonical introduction to the SQLAlchemy pool.
3. Brandur Leach — "Postgres connection pooling" — an architectural explanation of the general problem.
4. Hussein Nasser — "PostgreSQL Connections Memory Usage" — a breakdown of the memory cost per connection.
5. PgAnalyze — "Identifying connection issues" — operational diagnosis in production.
6. PostgreSQL Wiki — Number of Database Connections — the project's canonical position on why "more connections" is not better.

Module 6 — Database Performance & Query Tuning Guide