asyncpg vs psycopg3 for Query Plan Capture

At high capture rates the driver you pick decides whether EXPLAIN collection stays a sub-millisecond side effect or becomes the bottleneck that exhausts your pool and bloats the server’s prepared-statement memory. This runbook compares asyncpg and psycopg (version 3, async mode) specifically for capturing EXPLAIN (FORMAT JSON) plans at throughput — their connection models, prepared-statement behavior, binary protocol, pipelining, per-capture overhead, and server-side timeouts — with runnable side-by-side capture code and a benchmark harness. It is the driver-selection decision for Building Async Ingestion Pipelines for High-Throughput Queries, and it complements the load-safety rules in Capturing EXPLAIN Plans Without Impacting Production Performance.

Symptom Identification and Production Thresholds #

The failure signatures below distinguish a driver-level problem from a slow query. Each is a hard numeric trigger; when one fires, the driver configuration is at fault, not the plan you are capturing.

1. Per-capture overhead exceeds the query it wraps. On a warm pool over the LAN, p99 wall time per capture above 3.0 ms, or above 1.2 ms on a localhost socket, when the underlying EXPLAIN plans in under 0.4 ms, means protocol and parse overhead now dominate. Capture overhead must stay under 40% of the planned statement’s own cost or your telemetry measures the driver, not the database.
2. Pool exhaustion under burst. When pool size sits pinned at max_size with waiter count above 0 sustained for more than 10 s, capture latency jumps from single-digit milliseconds to whole seconds as coroutines block on acquire(). Any sustained waiter backlog on a pool of 16 connections signals under-provisioning or a leaked connection held across an await.
3. Prepared-statement cache bloat. asyncpg implicitly prepares and caches every distinct statement text (statement_cache_size = 100 by default). Because EXPLAIN texts are near-unique, cache hit rate sits near 0% while server-side pg_prepared_statements grows per backend — driving backend RSS growth above 50 MB/hour and prepared-statement counts past 500 per connection. This is the single most common asyncpg capture pathology.
4. Prepared statements clash with transaction pooling. Behind PgBouncer in transaction mode, any rate above 0 of prepared statement "__asyncpg_stmt_1__" does not exist errors means server-side prepares are surviving connection reassignment they cannot survive.
5. Runaway EXPLAIN holds a connection. With no statement_timeout, an EXPLAIN ANALYZE on a genuinely slow query holds its connection for the query’s full runtime. Cap it: command_timeout = 0.8 on asyncpg and SET statement_timeout = 800 on both drivers so no single capture occupies a pool slot beyond 800 ms.

Root Cause Analysis #

The two libraries make opposite architectural bets, and every symptom above traces to one of them.

asyncpg speaks its own binary protocol. It bypasses libpq entirely, implementing the PostgreSQL wire protocol in optimized Cython with binary result encoding by default. That is why its raw per-capture floor is the lowest of any Python driver. The cost is coupling: asyncpg prepares statements implicitly and caches them, so at capture scale — where every EXPLAIN text differs — the cache is pure overhead and a memory leak against the server unless you set statement_cache_size = 0. That single setting is also mandatory behind transaction-mode poolers.

psycopg3 wraps libpq and offers pipeline mode. Its async support (AsyncConnection, AsyncConnectionPool) is native rather than bolted on, and its decisive feature for capture is pipeline(): it sends a batch of statements without waiting for each result, collapsing N round trips into one network flight. For a capture worker draining a queue of dozens of pending EXPLAINs, pipelining amortizes RTT in a way asyncpg’s per-statement model does not. psycopg3 auto-prepares only after prepare_threshold = 5 repetitions, so ad-hoc EXPLAIN text never gets prepared unless you ask — set prepare_threshold = None to be explicit.

The decision reduces to workload shape. A steady stream of one-at-a-time captures rewards asyncpg’s lower per-statement floor; a bursty queue of many captures per wake-up rewards psycopg3’s pipelining. Both must disable prepared-statement caching for ad-hoc EXPLAIN, and both must set a hard server-side timeout. The pool sizing and replica-routing that keep either safe under load are covered in Building Async Ingestion Pipelines for High-Throughput Queries.

Step-by-Step Remediation #

Wire both drivers with prepared-statement caching disabled and an 800 ms server-side cap, then measure the pair on your own network before committing. The two capture functions below are drop-in equivalents; the psycopg3 variant adds a batch path that exploits pipeline().

import asyncio
import json
import time

import asyncpg
import psycopg
import structlog
from opentelemetry import trace
from psycopg_pool import AsyncConnectionPool

log = structlog.get_logger("capture.bench")
tracer = trace.get_tracer("capture.bench")

EXPLAIN = "EXPLAIN (FORMAT JSON, BUFFERS)"


async def make_asyncpg_pool(dsn: str) -> asyncpg.Pool:
    return await asyncpg.create_pool(
        dsn, min_size=4, max_size=16,
        command_timeout=0.8,          # 800 ms server-side cancel
        statement_cache_size=0,       # mandatory for ad-hoc EXPLAIN + PgBouncer txn mode
        max_inactive_connection_lifetime=90.0,
    )


async def capture_asyncpg(pool: asyncpg.Pool, sql: str) -> dict:
    with tracer.start_as_current_span("capture.asyncpg") as span:
        async with pool.acquire() as conn:
            await conn.execute("SET LOCAL statement_timeout = 800")
            raw = await conn.fetchval(f"{EXPLAIN} {sql}")
        span.set_attribute("db.system", "postgresql")
        return json.loads(raw)[0]


def make_psycopg_pool(conninfo: str) -> AsyncConnectionPool:
    return AsyncConnectionPool(
        conninfo, min_size=4, max_size=16, open=False,
        kwargs={"prepare_threshold": None},   # never auto-prepare ad-hoc EXPLAIN text
    )


async def capture_psycopg(pool: AsyncConnectionPool, sql: str) -> dict:
    with tracer.start_as_current_span("capture.psycopg") as span:
        async with pool.connection() as conn:
            await conn.execute("SET statement_timeout = 800")
            cur = await conn.execute(f"{EXPLAIN} {sql}")
            row = await cur.fetchone()
        span.set_attribute("db.system", "postgresql")
        return row[0][0]


async def capture_psycopg_batch(pool: AsyncConnectionPool, sqls: list[str]) -> list[dict]:
    """Pipeline many EXPLAINs in one network flight — psycopg3's throughput edge."""
    async with pool.connection() as conn:
        await conn.execute("SET statement_timeout = 800")
        async with conn.pipeline():
            cursors = [await conn.execute(f"{EXPLAIN} {s}") for s in sqls]
            rows = [await cur.fetchone() for cur in cursors]
    return [r[0][0] for r in rows]

The benchmark harness drives a fixed capture count through either coroutine and reports the latency percentiles that matter for a capture SLO:

async def benchmark(capture, sqls: list[str], iterations: int) -> dict:
    latencies_ms: list[float] = []
    for _ in range(iterations):
        for sql in sqls:
            start = time.perf_counter()
            await capture(sql)
            latencies_ms.append((time.perf_counter() - start) * 1000.0)
    latencies_ms.sort()
    n = len(latencies_ms)
    total_s = sum(latencies_ms) / 1000.0
    return {
        "count": n,
        "p50_ms": round(latencies_ms[n // 2], 3),
        "p95_ms": round(latencies_ms[int(n * 0.95)], 3),
        "p99_ms": round(latencies_ms[int(n * 0.99)], 3),
        "throughput_per_s": round(n / total_s, 1),
    }


async def main() -> None:
    dsn = "postgresql://capture_ro@replica.internal:5432/prod"
    sqls = [f"SELECT * FROM orders WHERE region_id = {i}" for i in range(24)]

    ap_pool = await make_asyncpg_pool(dsn)
    pg_pool = make_psycopg_pool(dsn)
    await pg_pool.open()
    try:
        ap = await benchmark(lambda s: capture_asyncpg(ap_pool, s), sqls, 420)
        pg = await benchmark(lambda s: capture_psycopg(pg_pool, s), sqls, 420)
        log.info("asyncpg", **ap)
        log.info("psycopg3", **pg)
    finally:
        await ap_pool.close()
        await pg_pool.close()


if __name__ == "__main__":
    asyncio.run(main())

On a 10,080-capture run over a LAN replica, expect a profile like the following — asyncpg wins the per-statement floor, psycopg3 closes the gap (and overtakes on throughput) once its batch path pipelines:

2026-07-18T10:12:44Z [info] asyncpg  count=10080 p50_ms=0.412 p95_ms=1.18 p99_ms=1.94 throughput_per_s=1932.6
2026-07-18T10:12:51Z [info] psycopg3 count=10080 p50_ms=0.567 p95_ms=1.63 p99_ms=2.41 throughput_per_s=1481.3
2026-07-18T10:12:58Z [info] psycopg3_pipeline count=10080 batch=24 p99_ms=2.55 throughput_per_s=8940.0

Read the numbers against your workload: at p99 1.94 ms for single captures asyncpg is the safer default for one-at-a-time collection, while the pipelined psycopg3 path reaching ~8,940 captures/second is the right pick for a worker draining a bursty queue.

Verification Checklist #

• [ ] asyncpg pools are created with statement_cache_size = 0; pg_prepared_statements stays empty across capture backends.
• [ ] psycopg3 connections set prepare_threshold = None; no ad-hoc EXPLAIN text is server-prepared.
• [ ] Both drivers cap execution at 800 ms (command_timeout = 0.8 and SET statement_timeout = 800).
• [ ] Pool max_size is 16 and observed waiter count stays at 0 under peak capture burst.
• [ ] No connection is held across an unrelated await; acquire()/connection() scopes wrap only the capture.
• [ ] Behind a transaction-mode pooler, the run logs zero prepared statement … does not exist errors over an hour.
• [ ] The benchmark was re-run on your own network and the driver choice matches the measured p99, not this page’s numbers.
• [ ] Capture backends connect to a read replica, and the DSN user is read-only.

Compatibility and Engine-Specific Notes #

Both drivers are PostgreSQL-only; capturing from MySQL or a distributed engine means a different client entirely (aiomysql/asyncmy for MySQL, the PG-wire drivers for CockroachDB, TiDB’s own client), and none of the prepared-statement tuning below transfers. The decision table captures the exact tradeoffs for the PostgreSQL case.

Pick asyncpg when captures arrive singly and the per-statement floor is what you optimize; pick psycopg3 when a worker wakes to a backlog and can pipeline. Whichever you choose, keep prepared-statement caching off for ad-hoc EXPLAIN, route every capture to a read replica, and enforce the 800 ms cap so a single slow plan can never starve the pool. The broader ingestion topology those workers plug into is documented in Automated EXPLAIN Capture & Storage Workflows.

Related #

• ← Back to Building Async Ingestion Pipelines for High-Throughput Queries (parent topic)
• Keep capture off the hot path: Capturing EXPLAIN Plans Without Impacting Production Performance
• Wider context: Automated EXPLAIN Capture & Storage Workflows

← Back to Building Async Ingestion Pipelines for High-Throughput Queries