Skip to content

Health-Checking Pooled Connections Before Use

A pooled connection can die while it sits idle — the database restarts, a failover moves the primary, a firewall forgets the flow — and the next request to check it out pays for it. Pools offer a health check before use, "pre-ping", and it is often switched on as a general fix. Measured on Python 3.14 with SQLAlchemy 2.1 over asyncpg 0.31 and PostgreSQL 17: after the server terminated all 10 pooled connections, SQLAlchemy without pre-ping failed 1 of the next 50 queries, with pre-ping 0, and a plain asyncpg pool 0 — it noticed the closed sockets itself. Pre-ping raised sequential query time from 350 µs to 586 µs, 67% more. Against a connection dropped silently — no reset, packets simply lost, which a test proxy reproduced — pre-ping did not help: the check itself hung for more than 30 s, and wrapping the query in asyncio.timeout(4) did not get it back. Setting the driver's command_timeout=2 as well bounded the failure at 4.0 s, after which the next query succeeded in 0.02 s; with plain asyncpg, terminating the connection on timeout gave 2.00 s and then 0.03 s. This guide covers both failure kinds.

Prerequisites

1. Measure what happens when connections are closed

The common case is a connection the server closed — a restart, pg_terminate_backend, an idle timeout. The client's socket sees the close. Reproduce it by terminating the pool's backends, tagged with an application name:

engine = create_async_engine(URL, pool_size=10, max_overflow=0, pool_pre_ping=False,
                             connect_args={"server_settings": {"application_name": "svc"}})

await admin.fetchval(
    "SELECT count(pg_terminate_backend(pid)) FROM pg_stat_activity WHERE application_name = $1",
    "svc",
)

Measured over the next 50 queries: SQLAlchemy without pre-ping failed 1 with InterfaceError and then recovered — on a disconnect error it invalidates the whole pool, so the remaining connections were replaced without further errors. With pool_pre_ping=True, none failed. A plain asyncpg pool failed none either: asyncpg detected the closed connections when they were acquired and opened new ones. For closed connections, the cost of not checking was one failed request per pool, not one per connection.

Verify: after terminating every pooled backend in a test, the number of failed requests is known — here zero or one — and the retry policy covers it.

Dead pooled connections, PostgreSQL 17 A grid of 5 rows by 3 columns. Dead pooled connections, PostgreSQL 17 setup server closed connections silently dropped connection SQLAlchemy, no pre-ping 1 of 50 failed not measured SQLAlchemy, pool_pre_ping=True 0 of 50 failed hung > 30 s SQLAlchemy, pre-ping + command_timeout=2 0 failed 1 failure at 4.0 s, then ok asyncpg pool, defaults 0 of 50 failed hung > 25 s under asyncio.timeout(4) asyncpg, command_timeout=2 + terminate 0 failed 1 failure at 2.00 s, then ok SQLAlchemy 2.1.2, asyncpg 0.31.0; silent drops reproduced with a blackholing TCP proxy.

2. Measure what pre-ping costs

Pre-ping runs a trivial statement on every checkout, so it adds a round trip to every request that uses the pool:

async def one_query(engine):
    async with engine.connect() as conn:
        await conn.execute(text("SELECT 1"))

# 2,000 sequential calls, with and without pool_pre_ping

Measured: 350 µs per query without pre-ping and 586 µs with it — 67% more for a query this cheap, on a local server where a round trip costs little. Over a network with a 1 ms round trip, the absolute cost per checkout rises accordingly. A plain asyncpg pool ran the same query in 153 µs. For a request that checks out a connection once and runs several queries, the overhead is spread across them; for many tiny checkouts, it dominates.

Verify: the service's request latency with and without pre-ping is measured, and the cost is compared with the failure it prevents — one request per pool in step 1.

3. Reproduce a silent drop

A connection dropped by a NAT gateway, firewall or failed network path produces no close at all: writes disappear and reads wait. A small TCP proxy that stops forwarding on existing connections, while still accepting new ones, reproduces it:

async def pump(reader, writer, pipe):
    while data := await reader.read(65536):
        if pipe.dead:
            continue                     # swallow silently: no forward, no close
        writer.write(data)
        await writer.drain()

Measured through that proxy: SQLAlchemy with pool_pre_ping=True hung inside the pre-ping for more than 30 seconds — the ping was waiting for a reply that would never come. A plain asyncpg pool with a query wrapped in asyncio.timeout(4) was still stuck 25 seconds later: the timeout fired, but cleanup after the cancellation also waited on the dead connection. A health check that has no time limit of its own cannot detect a connection that never answers.

Verify: the service is tested against a silently dropped connection, not only a closed one, and the test has a watchdog so a hang fails it.

Pre-ping against a silently dropped connection A sequence of 6 messages between 4 participants. Pre-ping against a silently dropped connection request pool network path PostgreSQL checkout pre-ping SELECT 1 dropped, no reset command_timeout fires, connection invalidated TimeoutError at 4.0 s next checkout: new connection, 0.02 s Without a driver timeout, the wait has no end.

4. Bound every database call at the driver

The fix for silent drops is a time limit at the driver level, which also applies to the pre-ping and to cleanup, plus an outer deadline for the request. For SQLAlchemy over asyncpg:

engine = create_async_engine(
    URL,
    pool_pre_ping=True,
    pool_recycle=1800,                              # replace connections older than 30 min
    connect_args={"command_timeout": 2},            # asyncpg: bound every statement
)

async with asyncio.timeout(4):
    async with engine.connect() as conn:
        await conn.execute(text("SELECT ..."))

Measured against the silently dropped connection: the first query raised TimeoutError after 4.0 s, SQLAlchemy invalidated the connection, and the second query opened a new one and returned in 0.02 s. With plain asyncpg, set command_timeout on the pool and terminate a connection that timed out, so the pool never hands it out again:

async def query(pool, sql, *args):
    async with pool.acquire() as conn:
        try:
            async with asyncio.timeout(4):
                return await conn.fetchval(sql, *args)
        except TimeoutError:
            conn.terminate()                 # a timed-out connection is not reused
            raise

Measured: the first query failed after 2.00 s — command_timeout — and the next returned in 0.03 s on a fresh connection. pool.expire_connections() did not help here: the next query still hung until the 4-second timeout.

Verify: against a silent drop, the first affected request fails within the driver timeout and the next succeeds on a new connection.

Time to recover from a silently dropped connection 4 horizontal bars comparing SQLAlchemy pre-ping only with the others. Time to recover from a silently dropped connection SQLAlchemy pre-ping only > 30 s (hung) asyncpg, asyncio.timeout(4) only > 25 s (hung) SQLAlchemy pre-ping + command_timeout=2 4.0 s, then 0.02 s asyncpg command_timeout=2 + terminate 2.00 s, then 0.03 s The bars for the hung cases are lower bounds.

5. Replace connections before they go stale

Health checks react; lifetimes prevent. Retire connections before any middlebox or server timeout can reach them, and let the kernel probe long-idle sockets:

pool = await asyncpg.create_pool(
    DSN,
    command_timeout=2,
    max_inactive_connection_lifetime=300,   # close connections idle for 5 minutes
)
engine = create_async_engine(URL, pool_recycle=1800, pool_pre_ping=True,
                             connect_args={"command_timeout": 2})

Set the idle lifetime below the shortest idle timeout on the path — load balancers and NAT gateways commonly drop flows idle for a few minutes. TCP keepalive with short intervals turns a silent drop into a socket error within a bounded time; see tuning TCP keepalive for long-lived async connections. With lifetimes, driver timeouts and keepalive in place, pre-ping becomes optional: keep it where a failed first query is costly, and drop it where 67% extra on tiny queries matters more.

Verify: the pool's idle lifetime is below every idle timeout between the service and the database, and every call has a driver-level timeout.

Verification

Pooled connections are checked well when:

  • Every database call has a driver timeout (command_timeout), so no check or query can wait forever.
  • A timed-out connection is discarded, not returned to the pool.
  • Idle lifetimes are shorter than the path's idle timeouts.
  • Pre-ping is a measured choice, with its per-checkout cost known.

Diagnostic Hook: when requests hang for minutes after a network event while new connections work, look for queries without a driver timeout. A silently dropped connection held a pre-ping for more than 30 seconds here, and asyncio.timeout alone did not release it.

Pitfalls & edge cases

  • Pre-ping without a timeout. Measured: hung for more than 30 s on a silent drop.
  • asyncio.timeout as the only bound. Cleanup waited on the dead connection.
  • Reusing a connection after a timeout. Terminate it.
  • Pre-ping everywhere by default. Measured: 67% more time per tiny query.

Frequently Asked Questions

Should I enable pool_pre_ping in SQLAlchemy async?

It prevented 1 failure in 50 after the server closed every connection, at 67% more time per tiny query. With driver timeouts and idle lifetimes in place, it is optional.

Why does pool_pre_ping hang?

Against a silently dropped connection the ping waits for a reply that never comes. Set command_timeout in connect_args: the failure was then bounded at 4.0 s.

Does asyncio.timeout stop a hung asyncpg query?

Not reliably on a dead connection: with asyncio.timeout(4) alone the call was stuck after 25 s. Add command_timeout and terminate the connection on timeout.

Does asyncpg check connections before use?

It detected connections the server had closed: 0 of 50 queries failed after all were terminated. It cannot detect a silently dropped one without a timeout.