SQLAlchemy connection pool: pool_recycle vs pool_pre_ping for stale connections

Answer

No – enabling pool_pre_ping does not make pool_recycle redundant in every deployment. It eliminates most stale‑connection errors at checkout, but a timer‑based recycle is still useful when the database forces connections to close after a short idle period that is shorter than the typical time between checkouts.

Confirmed facts

• pool_pre_ping runs a lightweight validation query (e.g., SELECT 1) on every connection checkout and raises no error if the query succeeds.
• pool_recycle discards and recreates any connection that has lived longer than the configured number of seconds, regardless of its current state.
• When the database enforces an idle timeout (e.g., MySQL wait_timeout) that is shorter than the application’s typical checkout interval, pool_recycle guarantees the connection is refreshed before the server‑side limit is reached.
• If the validation query adds noticeable latency (high‑latency WAN) or the application checks out connections very frequently, relying only on pool_recycle with a suitably tuned interval can avoid the per‑checkout overhead of pool_pre_ping.

Likely explanation

The pre‑ping mechanism catches connections that have already been closed by the server, but it cannot prevent the server from closing a connection while it is idle in the pool. A recycle timer proactively replaces aging connections, reducing the chance that a pre‑ping will fail and thus lowering the frequency of costly reconnects.

Steps to configure both (when needed)

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://user:pwd@host/dbname",
    pool_pre_ping=True,          # validate on checkout
    pool_recycle=300,            # recycle connections older than 5 min
    pool_size=10,
    max_overflow=20
)

Adjust pool_recycle to be somewhat lower than the database’s idle timeout (e.g., if wait_timeout=600 seconds, set pool_recycle=500).