Switch the default async Postgres driver from asyncpg to psycopg3

[Dev-iL]

Important

This issue originally tracked "provide PgBouncer-safe defaults for the async metadata engine (asyncpg).". However, discussion on #67800 reached a cleaner conclusion: rather than hardening asyncpg, switch the default async Postgres driver to psycopg3 (postgresql+psycopg), which is PgBouncer-safe out of the box. This issue is re-scoped accordingly.

Why psycopg3 as the default

• asyncpg was selected as the async driver only because Airflow was pinned to SQLAlchemy 1.4. That constraint is gone (airflow-core now requires sqlalchemy[asyncio]>=2.0.48), and psycopg3 serves both sync and async from a single driver.
• Airflow recommends PgBouncer for all production Postgres (see airflow-core/docs/howto/set-up-database.rst). asyncpg uses named server-side prepared statements that break under transaction-mode PgBouncer; the only safe asyncpg configuration disables prepared-statement caching, which removes most of asyncpg's advantage and still requires per-deployment tuning. psycopg3 is safe with zero configuration.
• A safe default beats a documentation note that operators miss.

Performance context

Driver benchmark (https://github.com/Dev-iL/pgbench, localhost, concurrency 10): asyncpg keeps an edge on row-returning queries even with prepared statements disabled (e.g. pg_type 1466 vs 764 qps), but on the trivial round-trip closest to single-row OLTP the gap is ~12% (9945 vs 8836 qps), and psycopg3-async has markedly lower latency variance. Two caveats on reading those numbers:

• It is a tight localhost loop with no network hop, which isolates and maximizes raw driver overhead; behind a real network / PgBouncer hop the per-query delta becomes a small fraction of total latency.
• The harness runs async drivers as coroutines on one event loop and sync drivers via a thread pool, so sync-vs-async rows are not directly comparable. asyncpg vs psycopg3-async (both async) is the clean comparison, and the one that matters for the default.

For the high-QPS single-row OLTP routes being migrated (heartbeat, #67800), psycopg3 is a sound default.

Bulk caveat: in the same localhost benchmark psycopg3-async was ~30x slower on batch INSERT and ~3x on COPY. Today's async routes are single-row OLTP, so this does not affect the switch — but bulk-write paths must be validated (and may warrant per-route asyncpg) before they migrate to async (#67799).

Work

1. Change the default async driver mapping in airflow-core/src/airflow/settings.py (AIO_LIBS_MAPPING["postgresql"] from asyncpg to psycopg). This affects only the postgresql+... async URL derivation; the aiosqlite / aiomysql mappings are unchanged.
2. Packaging: psycopg3 (psycopg[binary]) currently ships only as an opt-in extra in providers/postgres, while asyncpg is a hard dependency. Make psycopg3 available by default and downgrade asyncpg to the opt-in extra. (Decide hard-dep vs. extra.)
3. Keep asyncpg fully supported and documented as a throughput opt-in: sql_alchemy_conn_async = postgresql+asyncpg://..., with the PgBouncer-safe connect_args recipe added in Use async DB session for Execution API task-instance heartbeat #67800 — {"statement_cache_size": 0, "prepared_statement_cache_size": 0}, both in connect_args (verified against PG14: the default leaves named __asyncpg_stmt__ statements on the backend connection, the recipe leaves none).
4. Update the docs added in Use async DB session for Execution API task-instance heartbeat #67800 to present psycopg3 as the safe default and asyncpg as the opt-in.
5. Validate against a real transaction-mode PgBouncer in front of Postgres.

Acceptance criteria

• The default async Postgres engine works behind transaction-mode PgBouncer with no extra configuration.
• The driver-mapping change is Postgres-only; SQLite / MySQL async engines are unaffected.
• asyncpg remains usable via an explicit sql_alchemy_conn_async, documented with the PgBouncer-safe recipe.
• Deployment docs reflect the final behavior.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[maheshsv]

I will work on this

[Dev-iL]

@maheshsv Please note that the objective of this issue was reframed from reconfiguring asyncpg to completely switching out the default driver.