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?
answer
- both act only at checkout
- test it, or retire it by age
- age since connect, not idle time
- mid-transaction drops are not covered
basics
~10 spool_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.
solid answer
~40 sBoth work at checkout only. `pool_pre_ping=True` (off by default) has the dialect ping the connection, through the driver's ping method or a `SELECT 1`; if it is dead the pool reconnects and invalidates every pooled connection older than now, so one probe cleans the pool after a restart. It costs a round trip per checkout and finds a dead connection whatever killed it. `pool_recycle=N` (default `-1`, off) replaces a connection whose age since it was opened exceeds N seconds; because age is never shorter than idle time, `pool_recycle=1500` guarantees nothing idle for 30 minutes is reused. Neither rescues a connection that dies mid-transaction: that statement fails and the application retries the whole transaction.
code
python · 18 linesimport logging
import time
from sqlalchemy import create_engine, text
logging.basicConfig(format="%(levelname)s %(name)s %(message)s")
logging.getLogger("sqlalchemy.pool").setLevel(logging.INFO)
# pool_recycle is in seconds; 1 here only so the demo runs fast
engine = create_engine("sqlite:///orders.db", pool_recycle=1, pool_pre_ping=True)
with engine.connect() as conn:
conn.execute(text("SELECT 1"))
time.sleep(1.5) # the pooled connection is now older than pool_recycle
with engine.connect() as conn: # checkout closes it and opens a new one
conn.execute(text("SELECT 1"))go deeper
Recall the two option names and what each does at checkout: pre-ping tests the connection first, recycle replaces connections past an age limit.
Explain the mechanics: the dialect ping or SELECT 1, pool-wide invalidation after a failed ping, recycle measuring age since connect, and both acting only at checkout.
Pick settings for a known idle cutoff with margin, and explain what still fails: a connection lost mid-transaction raises, and the transaction has to be retried by the application.
Decide the team default: pre-ping everywhere for unknown network paths, recycle matched to known timeouts, and how LIFO ordering and idle-connection limits interact with both.
## Where stale connections come from A pooled connection can sit idle for a long time between checkouts. While it waits, the network or the server may end it without the client noticing: a firewall or load balancer that **silently drops idle TCP connections** (30 minutes in this scenario), a database restart, or a server-side idle timeout such as MySQL's `wait_timeout`. The DBAPI connection object in the pool still looks open. The first statement sent over it fails with a driver error, and the request that borrowed it fails with it. SQLAlchemy offers two `create_engine()` options against this. Both act **at checkout**, before the connection is handed to your code. ## `pool_pre_ping`: test before use `pool_pre_ping=True` (default `False`) is the **pessimistic** approach: - On checkout the pool asks the dialect to **ping** the connection, either through a DBAPI-specific ping method or by emitting SQL equivalent to `SELECT 1`. - If the ping shows a dead connection, the pool **replaces** it with a new one before returning it, and it **invalidates every other pooled connection older than now**, so they are replaced on their next checkout too. One failed probe cleans the whole pool after a restart. - A connection that was **just created** is not pinged; with `echo_pool="debug"` the pool logs that it is fresh and skips the ping. - The cost is one extra round trip per checkout, which is small next to a failed request. - It finds a dead connection **whatever killed it**: firewall, restart, failover, server timeout. ## `pool_recycle`: replace by age `pool_recycle=N` (default `-1`, meaning never) is a time limit: 1. Every pooled connection records when it was **opened**. 2. At checkout the pool compares that **age** with `N` seconds. 3. If the connection is older, the pool closes it and opens a new one, logging that the connection "exceeded timeout; recycling". Two details matter: - The measure is **age since connect, not idle time**. A connection's age is always at least as long as its idle time, so `pool_recycle=1500` guarantees that nothing idle for 30 minutes is reused. Setting it to exactly 1800 leaves no margin against the firewall's clock. - Recycling happens **only at checkout**. Nothing closes connections in the background, and a connection that is checked out is never recycled. ## Side by side | | `pool_pre_ping=True` | `pool_recycle=N` | |---|---|---| | default | `False` | `-1` (off) | | when it acts | every checkout | checkout, if age > N | | what it detects | a connection that is actually dead | a connection that might be dead by now | | cost | one ping round trip | a reconnect every N seconds per connection | | detects restarts and failovers | yes | no | For the firewall in this scenario, either works. Many teams set **both**: `pool_recycle` below the known cutoff so healthy traffic never hits it, and `pool_pre_ping` for everything nobody predicted. ## What neither option covers Both checks run **before** the connection is used. If a connection drops **in the middle of a transaction**, the statement fails, SQLAlchemy raises a `DBAPIError` whose `connection_invalidated` attribute is `True`, and the pool marks its older connections invalid. SQLAlchemy does not reconnect and replay a transaction. The application has to retry the whole unit of work. Without pre-ping this is the **optimistic** approach: each connection in use when the database went away fails once, and the pool recovers on its own afterwards. ## Settings for this scenario For a firewall that drops connections idle for 30 minutes: 1. `pool_recycle=1500`, so no connection older than 25 minutes is handed out, with a margin against the firewall's timer. 2. `pool_pre_ping=True`, to catch everything else: a database restart, a failover, or a firewall rule that changes without notice. 3. `pool_use_lifo` left at its default `False`, unless idle connections should also age out during quiet hours. ```python engine = create_engine( "postgresql://[email protected]/orders", pool_recycle=1500, pool_pre_ping=True, ) ``` With the `sqlalchemy.pool` logger at INFO, a recycled connection appears as "exceeded timeout; recycling", and a failed ping as "Disconnection detected on checkout, invalidating all pooled connections prior to current timestamp", so both are easy to count. ## LIFO and idle connections `pool_use_lifo=True` makes the `QueuePool` hand out the **most recently returned** connection first instead of rotating through all of them. During quiet periods the same few connections do all the work and the rest stay idle, so a server-side timeout can close the unused ones. That only works safely with `pool_pre_ping` or `pool_recycle` in place, because those idle connections will be dead the next time a burst needs them.
- With pool_pre_ping=True, which SQLAlchemy checkouts skip the ping?A connection the pool has just created is marked fresh, and the first checkout of it skips the ping, since it was opened a moment ago. With `echo_pool="debug"` the pool logs that the connection is fresh and skips the pre-ping. Every later checkout of that connection is pinged.
- What does pool_use_lifo=True change, and why does SQLAlchemy's documentation pair it with pre-ping?It makes the QueuePool hand out the most recently returned connection first, so in quiet periods a few connections do all the work and the rest sit idle long enough for a server-side timeout to close them. Those idle connections are dead when the next burst needs them, so `pool_pre_ping` or `pool_recycle` must be there to replace them.
- Without pre-ping, what happens in SQLAlchemy when a request uses a connection the database has dropped?The statement raises a `DBAPIError` whose `connection_invalidated` is `True`. SQLAlchemy invalidates that connection and marks the pool's older connections invalid so they are replaced on their next checkout. That request fails, and each other connection in use at the time fails once; the application decides whether to retry the transaction.
- pool_recycle closes idle connections in the background on a timer
- pool_recycle measures how long a connection has sat idle in the pool
- pool_pre_ping reconnects and replays a transaction that lost its connection
- pool_pre_ping is enabled by default in SQLAlchemy 2.x
- pool_pre_ping only replaces the one connection it found dead