asyncpg for Spatial Workloads

Problem Statement

An ingest service takes two hundred thousand position updates a minute and spends most of its time in the driver rather than the database. asyncpg is measurably the fastest PostgreSQL driver for Python, and it does not know what a geometry is. This page covers what it takes to use it with PostGIS properly — the codec, the pool wiring, and an honest account of what the choice costs, extending the driver comparison.

Why the Naive Approach Fails

Without a codec, geometry round-trips as text:

python
row = await conn.fetchrow("SELECT geom FROM stations LIMIT 1")
row["geom"]
# '0101000020E6100000A01A2FDD24E65E40D8817346941D4A40'

That is hex-encoded EWKB, which is correct, unhelpful, and about twice the bytes it needs to be. Every read then needs a manual bytes.fromhex(...) plus a shapely parse, and every write needs the reverse — the exact work a codec exists to do once, at the driver boundary.

What the codec removes Without a codec, a 240-byte geometry crosses the wire as 480 hex characters and is decoded by application code on every row. With a binary codec it crosses as 240 bytes and is decoded once by the driver, in C, with no per-row Python. One 240-byte polygon, two paths into Python no codec 480 hex characters over the wire + fromhex + wkb.loads per row the decoding happens in Python, once per row, on the event loop with a binary codec 240 bytes over the wire decoded by the driver, in C half the bytes, and no per-row Python on the hot path

Production-Ready Implementation

The codec is thirty lines and belongs in the pool’s initialiser so every connection gets it:

python
from __future__ import annotations

import asyncpg
from shapely import wkb
from shapely.geometry.base import BaseGeometry


async def _register_geometry(conn: asyncpg.Connection) -> None:
    """Teach this connection how to speak PostGIS geometry."""
    # The OID is assigned when the extension is installed, so look it up.
    oid_row = await conn.fetchrow(
        "SELECT oid FROM pg_type WHERE typname = 'geometry'"
    )
    if oid_row is None:                       # database without PostGIS
        return

    await conn.set_type_codec(
        "geometry",
        schema="public",
        encoder=lambda geom: wkb.dumps(geom, srid=4326, hex=False),
        decoder=lambda data: wkb.loads(bytes(data)),
        format="binary",
    )


async def make_pool(dsn: str, *, min_size: int = 4, max_size: int = 20):
    """A pool whose every connection understands geometry."""
    return await asyncpg.create_pool(
        dsn,
        min_size=min_size,
        max_size=max_size,
        init=_register_geometry,          # runs on every new connection
        command_timeout=10,
    )

With that in place, geometry behaves like any other type in both directions:

python
from shapely.geometry import Point


async def nearest_stations(pool, lon: float, lat: float, k: int = 5):
    query = """
        SELECT id, name, geom
        FROM stations
        ORDER BY geom <-> ST_SetSRID(ST_MakePoint($1, $2), 4326)
        LIMIT $3
    """
    async with pool.acquire() as conn:
        rows = await conn.fetch(query, lon, lat, k)
    # row["geom"] is already a shapely geometry
    return [(r["id"], r["name"], r["geom"].wkt) for r in rows]


async def insert_station(pool, name: str, point: Point) -> int:
    async with pool.acquire() as conn:
        return await conn.fetchval(
            "INSERT INTO stations (name, geom) VALUES ($1, $2) RETURNING id",
            name, point,                  # the codec encodes the shapely object
        )

The init callback is the part that is easy to get wrong. Registering the codec on one connection outside the pool works in a test and fails in production, because the pool hands out other connections that never saw the registration — producing an application where geometry decoding depends on which connection a request happened to get.

The trade, stated plainly asyncpg with a codec gains roughly thirty percent throughput on read-heavy geometry workloads and the fastest available COPY. It gives up GeoAlchemy2 type declarations, Alembic autogenerate for spatial columns, and the composability of ORM query construction. What you get, and what you hand back gained ~30% more rows/s on reads the fastest COPY of any driver native prepared statements a small, predictable API worth it on an ingest path given up GeoAlchemy2 column declarations Alembic autogenerate for geometry composable query construction one SQL dialect layer for tests expensive on a CRUD path The usual resolution is both: SQLAlchemy for the application model, raw asyncpg for the one hot path that needs it. Geometry read throughput by driver Rows per second reading polygon geometry: psycopg2 at 41 thousand, psycopg3 at 78 thousand, asyncpg without a codec at 82 thousand, and asyncpg with a binary codec at 104 thousand. Reading 1M polygons, rows per second psycopg2 41,000/s · hex text psycopg3 78,000/s · binary asyncpg, no codec 82,000/s · hex, decoded in Python asyncpg + codec 104,000/s The codec is worth about a quarter of asyncpg's advantage; the rest is the driver itself.

Configuration and Tuning Knobs

command_timeout on the pool bounds any single statement. asyncpg has no equivalent of a server-side statement_timeout unless you set one per session, so the client-side timeout is the practical guard.

Prepared statement caching is on by default and is one of asyncpg’s speed advantages. It also means a schema change under a long-lived pool can produce cached-plan errors; statement_cache_size=0 disables it at a real cost and is occasionally the right answer behind a connection pooler in transaction mode.

Pool sizing follows the same arithmetic as any other driver: connections per worker times workers must fit inside the server’s max_connections, with headroom.

The codec’s SRID is baked into the encoder in the example above. A codebase handling multiple SRIDs should encode from the shapely object’s own metadata or pass the SRID explicitly rather than assuming 4326 — a hard-coded SRID in a codec is a silent relabelling waiting to happen.

Verification Steps

python
import asyncio
from shapely.geometry import Point


async def check(pool) -> None:
    async with pool.acquire() as conn:
        # 1. round-trip a geometry through the codec
        original = Point(10.75, 59.91)
        returned = await conn.fetchval("SELECT $1::geometry", original)
        assert returned.equals_exact(original, 1e-9), "codec round-trip failed"

        # 2. the SRID survived
        srid = await conn.fetchval("SELECT ST_SRID($1::geometry)", original)
        assert srid == 4326, f"SRID lost in transit: {srid}"

        # 3. every pooled connection has the codec, not just this one
        results = await asyncio.gather(*[
            pool.fetchval("SELECT geom FROM stations LIMIT 1") for _ in range(20)
        ])
        assert all(not isinstance(r, str) for r in results), \
            "some connections lack the codec — check the pool init callback"

That third check is the one worth keeping in the test suite. It is the failure that only appears under concurrency, in production, on a subset of requests.

Gotchas Checklist

  • Register the codec in init, never on a single connection. A pool without it produces intermittent hex strings that look like a data problem.
  • The geometry OID differs per database. Hard-coding one from a development machine works until the first deploy to a differently-provisioned environment.
  • wkb.dumps without srid= produces SRID 0. The column’s type modifier then rejects the insert, which is the good outcome — an untyped column accepts it silently, which is not.
  • asyncpg uses $1 placeholders, not %s. Code moved from psycopg needs every query rewritten, and a missed one produces a syntax error at runtime rather than at import.
  • Mixing raw asyncpg with SQLAlchemy sessions is where the confusion lives. Pick one per module and make the boundary explicit.

Where the Async Win Actually Comes From

It is worth being precise about what asynchronous drivers do and do not accelerate, because the mistaken version of the story leads to disappointing benchmarks and unnecessary rewrites.

An async driver does not make an individual query faster. A spatial join that takes 400 milliseconds of server-side work takes 400 milliseconds whether the client waits synchronously or yields to an event loop. What changes is what the process does with that time: under asyncpg, one process can have dozens of queries in flight and spend the waiting time serving other requests, whereas a synchronous driver blocks a worker per query. For an API whose latency is dominated by database wait time, that difference is the entire throughput story.

The qualification specific to spatial work is that geometry results are not free to decode. A query returning ten thousand polygons hands back several megabytes of WKB, and parsing it into Python objects is CPU work on the event loop thread. While that parsing runs, nothing else on the loop progresses — including the other queries whose concurrency was the point. This is why an async service can show worse tail latency than a threaded one on geometry-heavy endpoints, and it is invisible in a benchmark that returns row counts instead of geometries.

Three habits keep the win intact. Return the smallest representation the client can use — ST_AsGeoJSON or ST_AsMVT computed server-side rather than WKB decoded client-side. Push aggregation into SQL so the row count crossing the wire is small. And when a genuinely large decode is unavoidable, run it in a thread or process pool rather than on the loop.

Benchmark with the payloads the production endpoint actually returns. A comparison run against SELECT 1 measures the driver’s protocol overhead and nothing that will matter later.