Python asyncpg Database — Core Concepts

What asyncpg Is

asyncpg is a high-performance, asyncio-native PostgreSQL client library for Python. It was created by Yury Selivanov (also behind uvloop) and is used by projects like EdgeDB, Tortoise ORM, and many FastAPI applications.

Key differentiators:

• Pure binary protocol — communicates with PostgreSQL using the binary wire protocol, not text
• Asyncio native — built from scratch for async/await, not a wrapper around a sync driver
• C-level performance — critical paths are written in Cython

Basic Usage

Single Connection

import asyncpg

async def main():
    conn = await asyncpg.connect(
        host='localhost',
        database='mydb',
        user='user',
        password='pass'
    )
    
    # Fetch multiple rows
    rows = await conn.fetch("SELECT id, name FROM users WHERE active = $1", True)
    for row in rows:
        print(row['id'], row['name'])  # Rows are dict-like Record objects
    
    # Fetch one row
    row = await conn.fetchrow("SELECT count(*) FROM users")
    
    # Fetch one value
    count = await conn.fetchval("SELECT count(*) FROM users")
    
    await conn.close()

Note the $1, $2 parameter syntax — asyncpg uses PostgreSQL’s native parameter format, not %s or ?.

Connection Pools

For production use, always use a pool:

async def main():
    pool = await asyncpg.create_pool(
        dsn='postgresql://user:pass@localhost/mydb',
        min_size=5,
        max_size=20
    )
    
    async with pool.acquire() as conn:
        users = await conn.fetch("SELECT * FROM users")
    
    await pool.close()

The pool manages connection lifecycle — creating new connections when needed, reusing idle ones, and enforcing limits.

Prepared Statements

asyncpg can prepare statements for repeated execution:

async with pool.acquire() as conn:
    stmt = await conn.prepare("SELECT * FROM users WHERE department = $1")
    
    # Execute the same query with different parameters
    engineering = await stmt.fetch('engineering')
    marketing = await stmt.fetch('marketing')

Prepared statements are parsed and planned once by PostgreSQL, saving overhead on repeated queries.

Transactions

async with pool.acquire() as conn:
    async with conn.transaction():
        await conn.execute(
            "INSERT INTO orders (user_id, amount) VALUES ($1, $2)",
            user_id, amount
        )
        await conn.execute(
            "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
            amount, user_id
        )
    # Transaction commits automatically; rolls back on exception

For savepoints (nested transactions):

async with conn.transaction():
    await conn.execute("INSERT INTO log (msg) VALUES ($1)", "start")
    
    try:
        async with conn.transaction():  # Creates a savepoint
            await conn.execute("INSERT INTO risky_table VALUES ($1)", data)
    except asyncpg.UniqueViolationError:
        pass  # Savepoint rolled back, outer transaction continues
    
    await conn.execute("INSERT INTO log (msg) VALUES ($1)", "end")

Type Mapping

asyncpg automatically converts between PostgreSQL and Python types:

PostgreSQL	Python
integer, bigint	int
real, double precision	float
text, varchar	str
boolean	bool
timestamp	datetime.datetime
date	datetime.date
jsonb	dict / list
uuid	uuid.UUID
array	list

For custom types, register codecs:

await conn.set_type_codec(
    'geometry',
    encoder=encode_geometry,
    decoder=decode_geometry,
    schema='public'
)

asyncpg vs psycopg (sync)

Feature	asyncpg	psycopg2/3
Async support	Native	psycopg3 has async mode
Protocol	Binary	Text (default)
Speed (single query)	Fast	Moderate
Speed (high concurrency)	Much faster	Limited by threads
ORM integration	Tortoise, encode/databases	SQLAlchemy, Django
Ecosystem	Growing	Mature

Common Misconception

“asyncpg is always faster than psycopg2.” For a single query on a single connection, the speed difference is modest. asyncpg’s advantage appears under concurrency — when dozens or hundreds of queries are in flight simultaneously. A FastAPI endpoint serving 1,000 concurrent requests benefits enormously; a batch script running queries one at a time may see only marginal gains.

One thing to remember: asyncpg excels at high-concurrency PostgreSQL access — its binary protocol and native async design make it the fastest choice for web servers and applications that juggle many simultaneous database conversations. Use connection pools, use $1 parameter syntax, and let transactions handle your consistency needs.