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?
answer
- kept idle versus opened for bursts
- five, ten and thirty
- the sum is the ceiling
- wait, then a TimeoutError
basics
~20 spool_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 linesfrom 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 keptgo deeper
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.
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.
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.
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