skip to content

Pool Sizing & Health

The QueuePool behind an Engine caps open connections and lends one per checkout. Interviewers ask about pool_size against max_overflow, stale connections and pools copied into forked workers.

on this pageshow

explore

questions

6

In SQLAlchemy 2.1, what do create_engine's pool_size, max_overflow and pool_timeout control, and what happens when every pooled connection is checked out?

level: middleimportance: must knowfreq 48%

answer

  1. kept idle versus opened for bursts
  2. five, ten and thirty
  3. the sum is the ceiling
  4. wait, then a TimeoutError

basics

~20 s

pool_size (default 5) is how many connections the QueuePool keeps open when idle, max_overflow (default 10) how many extra it may open, and pool_timeout (default 30 s) how long a checkout waits at that ceiling before raising TimeoutError.

solid answer

~40 s

`create_engine()` passes these to the `QueuePool` it builds for most dialects. The pool opens connections lazily: `pool_size=5` is how many it keeps open while idle, and `max_overflow=10` lets it open ten more during a burst, so one engine has at most 15 checked out at once. When a connection comes back and the pool already holds `pool_size` idle ones, the returned connection is closed. When all 15 are in use, the next checkout blocks for `pool_timeout` seconds (30) and then raises `sqlalchemy.exc.TimeoutError`: "QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00". `pool_size=0` or `max_overflow=-1` lifts a limit, which removes the safety valve rather than fixing a connection leak.

code

python · 17 lines
python
from sqlalchemy import create_engine, exc

engine = create_engine(
    "sqlite:///orders.db", pool_size=2, max_overflow=1, pool_timeout=0.5
)

held = [engine.connect() for _ in range(3)]  # 2 from pool_size, 1 overflow
print(engine.pool.status())

try:
    engine.connect()  # a fourth checkout waits 0.5 s, then gives up
except exc.TimeoutError as err:
    print(err)

for conn in held:
    conn.close()
print(engine.pool.status())  # only pool_size connections are kept

go deeper

for a junior

Recall the three defaults, five, ten and thirty seconds, and that closing a Connection or Session hands its connection back to the pool rather than closing it.

for a middle

Explain the checkout sequence: reuse an idle connection, grow up to pool_size plus max_overflow, wait pool_timeout, raise TimeoutError, and close returned connections beyond pool_size.

for a senior

Read the TimeoutError message as evidence: decide whether concurrency really exceeds the ceiling or whether unclosed Sessions and long transactions are holding connections, and prove it with pool metrics.

for a principal

Treat the pool limits as a safety valve the team owns: say when raising them is right, why unlimited overflow just moves the failure to the database, and how the limits are monitored.

## What the QueuePool is When you call `create_engine()` for PostgreSQL, MySQL or a SQLite **file**, SQLAlchemy builds the `Engine` around a **`QueuePool`** (`sqlalchemy.pool.QueuePool`). The pool holds real DBAPI connections. Every `Engine.connect()`, `Engine.begin()` and every ORM `Session` that needs to talk to the database **checks out** one connection from it, and closing that `Connection` or `Session` **checks it back in**. A checked-in connection stays open to the database so the next checkout can reuse it without a new handshake. Three `create_engine()` keywords size this pool. `create_engine` hands them to the pool class (it translates `pool_timeout` into the pool's own `timeout` argument), and they only mean something for `QueuePool`: | keyword | default | what it limits | |---|---|---| | `pool_size` | `5` | the most connections the pool **keeps open while idle** | | `max_overflow` | `10` | how many **extra** connections it may open beyond `pool_size` while demand is high | | `pool_timeout` | `30` (seconds) | how long a checkout **waits** once `pool_size + max_overflow` are all checked out | So a default engine allows **15 connections checked out at once** and keeps at most **5** open when the load drops. ## The life of a checkout 1. **Nothing is opened up front.** No SQLAlchemy pool pre-creates connections; the first `connect()` opens the first one. 2. **Reuse first.** A checkout takes an idle connection from the queue when one is there (first in, first out by default). 3. **Grow up to `pool_size`, then overflow.** With no idle connection, the pool opens a new one, as long as fewer than `pool_size + max_overflow` are in play. 4. **Wait.** When all of them are checked out, the caller **blocks** for up to `pool_timeout` seconds for someone to return one. 5. **Give up.** If nothing comes back in time, the pool raises **`sqlalchemy.exc.TimeoutError`**. 6. **Shrink on return.** When a connection is checked in and the pool already holds `pool_size` idle connections, the returned one is **closed and discarded** instead of queued. That is how the overflow disappears again after a burst. ## Reading the TimeoutError The message names the limits that were hit: ```plaintext QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/21/3o7r) ``` SQLAlchemy's error guide (code `3o7r`) lists the usual causes. Only the first one is fixed by bigger numbers: - **More concurrent work than the pool allows**, for example 30 threads each holding a connection against a 15-connection pool. - **Connections that are never returned**: a `Session` or `Connection` that is not closed, or that waits on garbage collection to close it. - **Long transactions**: code that holds a connection while it waits for a user, a queue or a slow remote call. - **Deadlocks**, in the application or in the database, that stop connections from coming back. ## Watching the pool `engine.pool.status()` prints the pool size, idle count, current overflow and checked-out count, and `engine.pool.checkedout()` returns that last number on its own, which is easy to export as a metric. `create_engine(..., echo_pool="debug")` logs every checkout and checkin under the `sqlalchemy.pool` logger, which shows which code path borrows a connection and never gives it back. ## Switching the limits off - **`pool_size=0`** means *no size limit* on a `QueuePool`; it does **not** disable pooling. To disable pooling you pass `poolclass=NullPool`. - **`max_overflow=-1`** means *no overflow limit*: the pool never blocks and never raises `TimeoutError`, it simply opens another connection. SQLAlchemy's own error guide calls the pool a **safety valve**: unlimited overflow does not fix a leak, it moves the failure to the database's connection limit, where it can also lock out every other client. Raise the limits when the concurrency is real, and fix the code when connections are not being returned. ## Misreadings worth avoiding - **Overflow connections are not second-class.** While they are open they are reused like any other; the pool closes whichever connection comes back when the idle queue is already full. - **A `Session` counts too.** An ORM `Session` checks out a connection the first time its transaction runs SQL and gives it back on `commit()`, `rollback()` or `close()`, so a Session left open between requests holds a slot the whole time. - **The numbers are per Engine.** Two engines in one process, or one engine in each of four processes, each have their own pool and their own ceiling. - **`pool_timeout` is not a query timeout.** It bounds only the wait for a free connection; a slow statement on a connection already checked out is not affected by it. ## Async engines `create_async_engine()` uses **`AsyncAdaptedQueuePool`**, a `QueuePool` built on an asyncio-compatible queue, and the same three keywords apply. The plain `QueuePool` is not compatible with the asyncio extension.

  • Does a SQLAlchemy Engine open pool_size connections when create_engine() is called?
    No. None of SQLAlchemy's pool classes pre-create connections. The first checkout opens the first connection, and the pool only grows to five if five checkouts are ever outstanding at the same time. That is why a default of `pool_size=5` costs nothing for a script that uses one connection.
  • Why is max_overflow=-1 a poor answer to QueuePool TimeoutError errors?
    With `max_overflow=-1` the pool never blocks: it opens a new connection for every checkout it cannot serve. If the cause is a leak or a long transaction, connections keep piling up until the database's own connection limit refuses them, which can also lock out other clients. The timeout was the warning; find what holds connections, or raise the limits only for real concurrency.
  • How do you find out who is holding the connections when a SQLAlchemy pool runs dry?
    Export `engine.pool.checkedout()` or log `engine.pool.status()` to see whether the pool sits at its ceiling. Then turn on `echo_pool="debug"` (or the `sqlalchemy.pool` logger at DEBUG) to log every checkout and checkin, and look for Sessions or Connections that are never closed, or code that keeps a transaction open while it waits on something slow.

A bike rental desk keeps five bikes on its rack and may borrow ten more from next door during a rush. A customer who arrives when all fifteen are out waits at the desk, but only for so long before being turned away. When a bike comes back and the rack already holds five, that bike goes back next door.

  • create_engine opens pool_size connections to the database at startup
  • max_overflow is the total limit, so pool_size counts inside it
  • When the pool is exhausted the request waits until a connection frees, however long
  • Every connection the pool ever opens stays open until the engine is disposed
  • Setting max_overflow=-1 is the standard fix for QueuePool timeout errors
open as a page

In SQLAlchemy, when would you pass poolclass=NullPool to create_engine instead of keeping the default QueuePool, and what does that cost?

level: middleimportance: should knowfreq 28%

basics

~20 s

NullPool opens a new DBAPI connection for every checkout and closes it on return. Use it behind an external connection pooler, for short batch jobs or around forking; the cost is a handshake per checkout and no connection ceiling.

open as a page

In SQLAlchemy, a firewall silently drops database connections idle for 30 minutes; how do pool_pre_ping and pool_recycle each stop stale pooled connections from failing requests?

level: middleimportance: should knowfreq 42%

basics

~10 s

pool_pre_ping=True pings each connection at checkout and replaces a dead one; pool_recycle=N replaces any connection older than N seconds at checkout. Set recycle below 1800 seconds, enable pre-ping, or both.

open as a page

In SQLAlchemy, an Engine created before a pre-forking server starts four workers begins returning protocol errors and mixed-up results; why, and how does engine.dispose(close=False) fix it?

level: seniorimportance: should knowfreq 35%

basics

~20 s

Pooled connections are sockets, and fork copies them into every worker, so several processes talk over one connection. Calling engine.dispose(close=False) in each child replaces its pool with an empty one without closing the parent's connections.

open as a page

How would you set SQLAlchemy's pool_size and max_overflow for an API of four worker processes plus a nightly batch job against a PostgreSQL server allowing 100 connections?

level: principalimportance: should knowfreq 30%

basics

~20 s

Each process has its own pool, so the database sees processes x (pool_size + max_overflow) per engine, plus the batch job, deploy overlap and admin connections. Size pool_size to steady concurrency, use max_overflow as headroom, keep the sum under 100.

open as a page

In SQLAlchemy, what does the connection pool do to a connection when it is returned, and when is pool_reset_on_return=None appropriate?

level: middleimportance: nice to knowfreq 18%

basics

~20 s

By default the pool calls the DBAPI rollback() on every returned connection, unless the Connection already rolled back, so no transaction, locks or pending writes leak to the next borrower. None fits pure autocommit use or a custom reset handler.

open as a page