A connection pool amortizes setup across several uses of a physical connection. It does not make setup disappear, and creating a pool does not necessarily open anything. Distinguish the application’s short-lived lease from the underlying database connection: closing the former normally returns the latter for reuse.

Reuse is a lifecycle, not a promise of free work

A remote database connection may require TCP, TLS, authentication, and session initialization. Reusing it avoids repeating those steps on every request, while SQL execution still costs time. Expiry, disconnection, invalidation, or process replacement can require setup again. Keep an SQLAlchemy Engine long-lived within its owning process; creating a fresh Engine for every request defeats that reuse.

SQLAlchemy pools grow lazily. A QueuePool configured with pool_size=2 starts empty, not with two warm connections. Two sequential leases can reuse one physical connection. Concurrent demand can grow this example to two retained connections plus one overflow connection. With finite max_overflow=1, three simultaneous leases exhaust its capacity; returning them leaves at most two idle connections.

These meanings belong to this pool implementation, not every library. In QueuePool, pool_size=0 removes the size limit rather than disabling pooling, and max_overflow=-1 permits unlimited overflow. Use explicit finite values when the pool is meant to enforce a connection budget.

When it breaks

A pool can be exhausted by legitimate concurrent work, slow transactions, or leases that are never returned. Pool acquisition time is therefore distinct from query time. pool_timeout bounds waiting for an available slot, not physical connection establishment or the SQL statement itself; its small floating-point value is not a hard real-time deadline.

Returning a connection also does not erase every trace of its previous borrower. SQLAlchemy’s normal reset removes transactional state through rollback, coordinated with Connection.close(). Committed temporary tables and other session state may survive. Do not infer tenant isolation from a returned lease; explicitly define any session setup and cleanup required by the backend and application.

A disconnected connection presents a different boundary. Explicit invalidation discards its underlying DBAPI connection. Later use obtains a replacement from the pool, possibly another idle connection rather than a newly opened one. If a transaction was active when invalidated, it must be ended before reconnecting. This is not transparent recovery of a transaction that may already have had effects.

Check it quickly

Use an empty directory on macOS or Linux. Run python3 -m venv .venv, then .venv/bin/python -m pip install SQLAlchemy==2.0.52. Save the following as pool_probe.py and run .venv/bin/python pool_probe.py. This was checked with Python 3.14.4 and SQLite 3.53.0. It creates and removes its own temporary database and does not contact a database server.

from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory

import sqlalchemy
from sqlalchemy import create_engine, event
from sqlalchemy.exc import TimeoutError as PoolTimeout
from sqlalchemy.pool import QueuePool


assert sqlalchemy.__version__ == "2.0.52"
with TemporaryDirectory(prefix="pool-reuse-") as directory:
    engine = create_engine(
        "sqlite:///" + str(Path(directory) / "probe.db"),
        poolclass=QueuePool, pool_size=2, max_overflow=1, pool_timeout=0.05,
    )
    events = {"opened": 0, "closed": 0}

    @event.listens_for(engine, "connect")
    def opened(dbapi_connection, entry):
        events["opened"] += 1
        entry.info["serial"] = events["opened"]

    @event.listens_for(engine, "close")
    def closed(dbapi_connection, entry):
        events["closed"] += 1

    try:
        assert engine.pool.checkedin() == 0 and events["opened"] == 0
        with engine.begin() as connection:
            serial = connection.info["serial"]
            connection.exec_driver_sql("CREATE TEMP TABLE marker(value INTEGER)")
            connection.exec_driver_sql("INSERT INTO marker VALUES (42)")
        with engine.connect() as connection:
            assert connection.info["serial"] == serial
            assert connection.exec_driver_sql("SELECT value FROM marker").scalar_one() == 42
        assert events["opened"] == 1 and engine.pool.checkedout() == 0
        print("lazy start: 0 physical connections; two sequential leases reuse 1; committed temp state persists")

        held = []
        try:
            for _ in range(3):
                held.append(engine.connect())
            assert len({connection.info["serial"] for connection in held}) == 3
            assert engine.pool.checkedout() == 3 and events["opened"] == 3
            try:
                unexpected = engine.connect()
            except PoolTimeout:
                pass
            else:
                unexpected.close()
                raise AssertionError("the fourth concurrent lease must time out")
            assert events["opened"] == 3
        finally:
            for connection in held:
                connection.close()
        assert engine.pool.checkedout() == 0 and engine.pool.checkedin() == 2
        assert events["closed"] == 1
        print("2 persistent slots + 1 overflow allow 3 leases; the fourth times out; only 2 remain idle")

        with engine.connect() as connection, engine.connect() as occupied:
            previous = connection.info["serial"]
            occupied_serial = occupied.info["serial"]
            connection.invalidate()  # No transaction has started on this lease.
            assert connection.exec_driver_sql("SELECT 1").scalar_one() == 1
            assert connection.info["serial"] != previous
            assert occupied.info["serial"] == occupied_serial
            assert connection.exec_driver_sql(
                "SELECT count(*) FROM sqlite_temp_master WHERE name = 'marker'"
            ).scalar_one() == 0
        assert events["opened"] == 4
        print("with the other slot occupied, invalidation forces a new physical connection without the old temp table")
    finally:
        engine.dispose()
    assert events == {"opened": 4, "closed": 4}
print("all 4 connect events have matching close events; SQLite", sqlite3.sqlite_version)

The first output proves lazy creation and reuse using connect events and a serial stored with the DBAPI connection. The committed marker remains visible on the next lease: transaction reset is not a complete session reset. Next, three retained leases reach the configured cap; the fourth raises PoolTimeout. Returning all three leaves two idle connections and one close event.

For the invalidation check, the other idle connection is deliberately occupied. That makes the next SQL require a newly created physical connection, whose temporary schema lacks the old marker. Without that controlled occupancy, invalidation can simply borrow the other idle connection. Final disposal follows return of every lease, and four connect events match four close events. These are observed library events, not a guarantee about failed closes on an arbitrary backend.

This experiment demonstrates lifecycle and capacity, not TCP/TLS savings or throughput. For performance decisions, measure setup frequency, acquisition wait, lease duration, and query latency on the actual backend. A larger pool is justified only within its shared connection budget; it cannot by itself repair leaked leases or slow queries.

Sources