Skip to content

Timing Out Database Queries in asyncio

A database query can be slow because the query is slow, because the server is overloaded, or because no connection is free to run it — and each needs a bound. With asyncpg and PostgreSQL there are three places to put a query timeout, and they behave alike in the normal case and differently in the abnormal ones. Measured on Python 3.14 with asyncpg 0.31 against PostgreSQL 17, timing out SELECT pg_sleep(3) at 200 ms: asyncio.timeout(0.2) raised TimeoutError after 0.20 s; the pool's command_timeout=0.2 raised TimeoutError after 0.20 s; and the server's statement_timeout of 200 ms raised QueryCanceledError: canceling statement due to statement timeout after 0.20 s. In all three, the query was no longer running on the server 0.3 s later, and the next query on the same pool connection succeeded at once. A timed-out transaction left 0 rows behind. A query waiting for a connection from a busy pool was cut off by asyncio.timeout after 0.30 s — a timeout that command_timeout does not cover — and pool.acquire(timeout=0.3) bounds that wait on its own. Wrapping each query in asyncio.timeout cost 7 µs on a 134 µs query. This guide combines the layers.

Prerequisites

1. Bound the whole call with asyncio.timeout

The outermost layer is the caller's deadline, covering the wait for a connection, the query, and reading the result:

async def get_order(pool, order_id: int, deadline: float = 2.0):
    async with asyncio.timeout(deadline):
        async with pool.acquire() as conn:
            return await conn.fetchrow("SELECT * FROM orders WHERE id = $1", order_id)

Measured with pg_sleep(3) under asyncio.timeout(0.2): TimeoutError after 0.20 s. On cancellation, asyncpg sent PostgreSQL a cancel request for the running statement, so 0.3 s later pg_stat_activity showed no active pg_sleep, and the connection, returned to the pool, served the next query immediately. The overhead was small: a SELECT 1 took 134 µs bare and 141 µs inside asyncio.timeout.

Verify: after a timed-out query in a test, pg_stat_activity shows it gone and the pool's next query succeeds.

Timing out SELECT pg_sleep(3) at 200 ms, asyncpg 0.31 A grid of 5 rows by 4 columns. Timing out SELECT pg_sleep(3) at 200 ms, asyncpg 0.31 mechanism raised after query on server 0.3 s later asyncio.timeout(0.2) TimeoutError 0.20 s gone; connection reusable create_pool(command_timeout=0.2) TimeoutError 0.20 s gone server_settings statement_timeout=200 QueryCanceledError 0.20 s gone asyncio.timeout(0.3), pool of 1 busy TimeoutError while acquiring 0.30 s - pool.acquire(timeout=0.3), pool busy TimeoutError 0.30 s - PostgreSQL 17, Python 3.14; timed-out transaction left 0 rows.

2. Add a driver default with command_timeout

command_timeout on the pool applies to every statement that does not pass its own timeout=, so a query nobody remembered to bound still has one:

pool = await asyncpg.create_pool(DSN, min_size=5, max_size=20, command_timeout=10)

await pool.fetch("SELECT * FROM report_view")                 # bounded at 10 s
await pool.fetch("SELECT * FROM big_report", timeout=60)       # per-call override

Measured: with command_timeout=0.2, the 3-second query raised TimeoutError after 0.20 s and was cancelled on the server. The driver timeout also bounds parts of asyncpg's own cleanup, which matters when a connection dies silently — in health-checking pooled connections before use, a query without it stayed hung past an outer asyncio.timeout. Set it as a generous ceiling, above every legitimate query, with per-call overrides for the known long ones.

Verify: the pool has a command_timeout, and every query that legitimately exceeds it passes an explicit timeout=.

3. Add a server-side ceiling with statement_timeout

The server can enforce its own limit, independent of whether the client is still listening:

pool = await asyncpg.create_pool(
    DSN, command_timeout=10,
    server_settings={"statement_timeout": "15000"},      # milliseconds; above command_timeout
)

Measured with a 200 ms setting: PostgreSQL cancelled the statement itself and asyncpg raised QueryCanceledError: canceling statement due to statement timeout after 0.20 s. This layer matters when the client cannot cancel — its process was killed, or its connection silently dropped — because the server then stops the query on its own, as measured in closing pools cleanly on shutdown. Set it a little above the driver's timeout, so the driver's normal path handles ordinary cases and the server's catches abandoned ones. MySQL's equivalent for SELECTs is the MAX_EXECUTION_TIME hint, shown in using MySQL from asyncio.

Verify: SHOW statement_timeout on a pooled connection returns the configured value.

Layers of a database timeout 4 stacked layers. Layers of a database timeout request deadline asyncio.timeout: acquire + query + read pool wait pool.acquire(timeout=...) driver command_timeout per statement server statement_timeout, even if the client is gone Each layer catches a failure the others miss.

4. Bound the wait for a connection separately

A query that never starts because the pool is exhausted is not covered by command_timeout or statement_timeout — both start counting when the statement is sent. Measured with a pool of one connection held by another task: a query under asyncio.timeout(0.3) timed out after 0.30 s while still waiting to acquire. pool.acquire(timeout=0.3) bounds that phase on its own:

async def query_with_budgets(pool, sql, *args, acquire_s=0.5, query_s=2.0):
    try:
        conn = await pool.acquire(timeout=acquire_s)
    except asyncio.TimeoutError:
        raise PoolExhausted(f"no connection within {acquire_s}s") from None
    try:
        return await conn.fetch(sql, *args, timeout=query_s)
    finally:
        await pool.release(conn)

Distinguishing the two failures pays off in an incident: "pool exhausted" points to too little capacity or connections held too long; "query timed out" points to the database. Sizing the pool to avoid the first is covered in sizing async connection pools for throughput.

Verify: metrics separate acquire timeouts from query timeouts.

5. Keep transactions consistent on timeout

A timeout inside a transaction must leave nothing half-done. Measured: a transaction that inserted a row and then ran pg_sleep(3) under asyncio.timeout(0.2) left 0 rows — the cancellation propagated out of conn.transaction(), which rolled back, and the pool's next query found the connection clean:

async with asyncio.timeout(1.0):
    async with pool.acquire() as conn:
        async with conn.transaction():
            await conn.execute("INSERT INTO ledger (account, amount) VALUES ($1, $2)", acct, amt)
            await conn.execute("UPDATE accounts SET balance = balance + $2 WHERE id = $1", acct, amt)

Keep the transaction's statements inside the timed block and the commit with them, so a timeout can only ever roll back. Retrying a timed-out transaction is safe only if it is idempotent or its effects are checked first; see making retry loops cancellation-safe.

Verify: a test that times out mid-transaction finds no partial writes and a reusable connection.

Time to give up on a 3-second query, 200 ms limit 4 horizontal bars comparing asyncio.timeout(0.2) with the others. Time to give up on a 3-second query, 200 ms limit asyncio.timeout(0.2) 0.20 s command_timeout=0.2 0.20 s statement_timeout=200 ms 0.20 s no timeout 3.0 s Alike when healthy; they differ when the client or connection fails.

Verification

Database queries are bounded when:

  • The caller's deadline wraps acquire and query with asyncio.timeout.
  • The pool has command_timeout, with explicit timeout= on legitimately long queries.
  • The server has statement_timeout, slightly above the driver's.
  • Pool waits are bounded and reported separately from query timeouts.

Diagnostic Hook: when requests time out but the database shows no slow queries, measure how long requests wait in pool.acquire. A query timed out after 0.30 s here without ever reaching the server, because the only connection was busy.

Pitfalls & edge cases

  • Relying on command_timeout for pool waits. It starts when the statement is sent.
  • No server-side limit. Queries from a killed client can run to completion.
  • Commit outside the timed block. A timeout cannot then guarantee a rollback.
  • One error type for both failures. Pool exhaustion and slow queries need different fixes.

Frequently Asked Questions

Does asyncio.timeout cancel an asyncpg query on the server?

On a healthy connection, yes: asyncpg sent a cancel request, and the 3 s query was gone from pg_stat_activity 0.3 s after the 0.2 s timeout.

What is the difference between command_timeout and statement_timeout?

command_timeout is enforced by asyncpg per statement; statement_timeout by PostgreSQL. Both stopped the query at 0.20 s; only the server's works if the client is gone.

Why do my queries time out when the database is idle?

They may be waiting for a pool connection. A query timed out after 0.30 s while acquiring from a busy pool of one; bound acquire separately.

Is a timed-out asyncpg transaction rolled back?

Yes, when the timeout covers the transaction block: a timed-out transaction that had inserted a row left 0 rows behind.