psycopg2 vs asyncpg for Extension Automation Scripts #

A decision guide for choosing the PostgreSQL driver behind your extension-automation scripts — weighing psycopg2’s synchronous simplicity and autocommit control against asyncpg’s fleet-wide concurrency, specifically for ALTER EXTENSION and catalog work.

Up: ALTER EXTENSION Automation — this page is the driver-selection companion to that automation, under the Automated Execution & Rollback Workflows area of the site.

Context & When This Applies #

Both drivers can run every statement an extension pipeline needs, so the choice is about shape, not capability. psycopg2 (or its successor psycopg 3 in sync mode) is a synchronous, battle-tested driver whose explicit autocommit and transaction control map cleanly onto the “run this one ALTER EXTENSION correctly” problem. asyncpg is an asynchronous driver built for high-concurrency I/O, which shines when you are upgrading many databases across a fleet and want them to proceed in parallel without a thread per connection. This guide applies to Python 3.8+ automation on PostgreSQL 12–17; the decision usually comes down to whether your unit of work is one careful transition or hundreds of concurrent ones.

Getting it wrong is rarely fatal but costs you either way: asyncpg for a single sequential upgrade adds an event-loop and cancellation-semantics burden you do not need; psycopg2 for a 500-node fleet sweep serializes work that could have overlapped, stretching the window. The safest default for most extension work is psycopg2 for its transaction clarity, reaching for asyncpg only when fleet concurrency is the bottleneck.

Concept: Autocommit Ergonomics vs Concurrency Model #

The two properties that actually matter for extension work are autocommit ergonomics and concurrency model. Some extension operations must run outside a transaction block — TimescaleDB’s ALTER EXTENSION ... UPDATE spawns background workers and errors inside BEGIN — so precise control over autocommit is not a nicety. psycopg2 exposes this directly as conn.autocommit = True, and its synchronous flow makes “this statement runs standalone, that block runs in a transaction with a savepoint” easy to read and reason about. asyncpg is autocommit-by-default (statements outside an explicit async with conn.transaction() commit immediately), which is convenient but means the transaction boundary is implicit unless you opt in.

The concurrency model is the other axis. psycopg2 is one-connection-one-thread: upgrading 500 databases means a thread pool or sequential loop. asyncpg multiplexes many connections on one event loop, so 500 concurrent upgrades cost 500 coroutines, not 500 threads — a decisive advantage when the work is I/O-bound fan-out rather than CPU. But that power comes with async cancellation semantics: a cancelled coroutine mid-ALTER EXTENSION interacts with the non-transactional steps catalogued in ALTER EXTENSION Automation in ways you must handle deliberately. The idempotent orchestration layer is what makes either driver’s retries safe.

Runnable Implementation #

The same guarded update in both drivers, so the trade-off is concrete. Both set autocommit correctly for a TimescaleDB-style non-transactional update.

# --- psycopg2: synchronous, explicit autocommit ---
import psycopg2


def update_extension_sync(dsn: str, name: str, target: str) -> str:
    conn = psycopg2.connect(dsn)
    conn.autocommit = True                      # required: no transaction block
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT extversion FROM pg_extension WHERE extname = %s",
                        (name,))
            row = cur.fetchone()
            if row and row[0] == target:
                return "noop"
            cur.execute(f'ALTER EXTENSION "{name}" UPDATE TO %s', (target,))
        return "updated"
    finally:
        conn.close()

# --- asyncpg: one coroutine per database, run many concurrently ---
import asyncio
import asyncpg


async def update_extension_async(dsn: str, name: str, target: str) -> str:
    conn = await asyncpg.connect(dsn)           # autocommit by default
    try:
        installed = await conn.fetchval(
            "SELECT extversion FROM pg_extension WHERE extname = $1", name)
        if installed == target:
            return "noop"
        # Run standalone (no transaction) so a bgworker-spawning update succeeds.
        await conn.execute(f'ALTER EXTENSION "{name}" UPDATE TO $1', target)
        return "updated"
    finally:
        await conn.close()


async def sweep(dsns: list[str], name: str, target: str) -> list[str]:
    # 500 databases upgraded concurrently on one event loop.
    return await asyncio.gather(
        *(update_extension_async(d, name, target) for d in dsns))

Whichever you choose, guard the update on the installed version (idempotency) and keep a restore point via Snapshot & Point-in-Time Recovery.

Expected Output & Verification #

Both return the same per-database verdict — "noop" or "updated" — so the calling pipeline is driver-agnostic. Verify the result the same way regardless of driver:

-- Confirm the observed version matches the intended target post-run.
SELECT extname, extversion FROM pg_extension WHERE extname = 'timescaledb';

For an asyncpg sweep, also confirm concurrency actually helped: the wall-clock for 500 databases should approach the slowest single upgrade, not their sum. If it does not, the bottleneck is a shared resource (the pooler, disk) rather than connection I/O, and the async model buys nothing — a signal to reconsider per Threshold Tuning for Downtime Windows.

Edge Cases & Gotchas #

Neither driver may wrap a non-transactional update in a transaction. A TimescaleDB update inside BEGIN fails identically in both:

ERROR:  ALTER EXTENSION ... UPDATE cannot run inside a transaction block

With psycopg2, set conn.autocommit = True. With asyncpg, run the statement outside any async with conn.transaction() block, since asyncpg would otherwise wrap it when you opt into transactions.

asyncpg cancellation can strand a non-transactional step. If a coroutine running ALTER EXTENSION is cancelled (a timeout, a gather sibling failing) after a background-worker registration commits, the catalog is ahead of the pipeline’s belief. Handle cancellation explicitly and re-run the idempotent orchestrator to converge.

psycopg2’s default is a transaction, not autocommit. Forgetting conn.autocommit = True silently opens a transaction, so a non-transactional update fails and an ordinary one holds a lock longer than expected. Set autocommit deliberately per operation.

Connection limits bite the async sweep first. 500 concurrent asyncpg connections can exhaust max_connections or the pooler. Bound concurrency with a semaphore rather than an unbounded gather, and route through the connection pooler the way Threshold Tuning for Downtime Windows accounts for drain time.

Related Pages #

• Up: ALTER EXTENSION Automation
• Wrapping ALTER EXTENSION UPDATE in Transactional Savepoints
• Idempotent Migration Orchestration
• Threshold Tuning for Downtime Windows
• Snapshot & Point-in-Time Recovery