Choosing a PostGIS Driver: psycopg2 vs psycopg3

Picking the PostgreSQL DBAPI driver is one of the earliest and stickiest decisions in the broader SQLAlchemy and GeoAlchemy integration workflows, because the driver sets the ceiling for how efficiently Well-Known Binary geometry moves between PostGIS and Python. On a maritime AIS backend that ingests millions of ais_positions points per day and streams port_zones polygons to a FastAPI frontend, the choice between the mature psycopg2 and the rewritten psycopg (version 3) governs async support, binary transport, server-side cursor semantics, and pipeline batching. This guide compares the two drivers on the axes that actually affect spatial throughput, then gives you a decision matrix and the configuration to commit to either one.

Prerequisites and infrastructure validation

Both drivers require the same PostGIS server; the decision lives entirely in the Python layer. Confirm your stack before committing to a dialect:

• PostgreSQL 14+ with PostGIS 3.3+ — verify with SELECT PostGIS_Full_Version();
• SQLAlchemy 2.0.15+ (the psycopg dialect matured across the early 2.0 point releases)
• geoalchemy2 0.14+
• For psycopg2: psycopg2-binary 2.9+
• For psycopg (v3): psycopg[binary,pool] 3.1+
• shapely 2.0+ for any client-side geometry manipulation

The two drivers can coexist in the same virtualenv, which is what makes an incremental migration feasible. Verify both import cleanly and report their versions:

import psycopg2
import psycopg  # psycopg 3

print("psycopg2:", psycopg2.__version__)
print("psycopg :", psycopg.__version__)

On the database side, confirm the geometry columns you will be reading actually carry an explicit SRID, because a mismatched or zero SRID produces the same failure on either driver:

-- Verify the AIS schema is SRID-clean before benchmarking drivers
SELECT f_table_name, f_geometry_column, srid, type
FROM   geometry_columns
WHERE  f_table_name IN ('ais_positions', 'port_zones')
ORDER  BY f_table_name;
-- Expect: ais_positions | geom | 4326 | POINT
--         port_zones    | geom | 4326 | POLYGON

The two dialect URLs

SQLAlchemy selects the driver purely from the URL prefix, and this is the single line that changes when you switch. The legacy driver uses postgresql+psycopg2; the rewritten driver registers under the bare name psycopg (there is no psycopg3 dialect string):

from sqlalchemy import create_engine

# psycopg2 — the historical default
engine_v2 = create_engine(
    "postgresql+psycopg2://ais:secret@db.internal:5432/ais_tracking"
)

# psycopg 3 — same driver family, new dialect name
engine_v3 = create_engine(
    "postgresql+psycopg://ais:secret@db.internal:5432/ais_tracking"
)

Because the URL prefix is the only compile-time coupling, GeoAlchemy2 model definitions, Geometry(geometry_type="POINT", srid=4326) columns, and every func.ST_* wrapper stay byte-for-byte identical. That property is what the companion walkthrough on migrating spatial code from psycopg2 to psycopg3 relies on to keep the ORM layer untouched while swapping the transport underneath it.

Decision matrix

The following table scores the two drivers on the dimensions that matter for a PostGIS workload. Weight the rows by your own traffic shape — an append-heavy ingestion service cares most about pipeline mode and binary WKB, whereas a read-mostly tile API cares most about server-side cursors and pooling.

The headline reading: psycopg2 is a safe, boring choice for an established synchronous service, while psycopg (v3) is the forward-looking default whenever async, binary WKB transport, or pipeline batching would move the needle.

Core comparison workflow

The steps below walk the same AIS operations through both drivers so you can see exactly where behaviour diverges.

Step 1: Reading geometry — text hex vs binary WKB

When you fetch an ais_positions.geom value through GeoAlchemy2, the ORM hands you a WKBElement in both cases. What differs is what crossed the socket. Under psycopg2 the geometry arrives as a hex string (0101000020E6100000...) that GeoAlchemy2 unhexlifies. Under psycopg with the binary format requested, the raw bytes arrive directly:

from sqlalchemy import select
from sqlalchemy.orm import Session
from geoalchemy2.shape import to_shape
from models import AisPosition  # Geometry(geometry_type="POINT", srid=4326)

def latest_fix(engine, mmsi: int):
    with Session(engine) as session:
        stmt = (
            select(AisPosition)
            .where(AisPosition.mmsi == mmsi)
            .order_by(AisPosition.recorded_at.desc())
            .limit(1)
        )
        pos = session.scalars(stmt).first()
        point = to_shape(pos.geom)  # WKBElement -> shapely Point, SRID 4326
        return point.x, point.y     # (lon, lat)

This function is driver-agnostic. To force psycopg to use the binary result format for a raw query — where the saving on large port_zones polygons is most visible — request it explicitly:

import psycopg

def dump_zone_wkb(dsn: str, zone_id: int) -> bytes:
    with psycopg.connect(dsn) as conn:
        with conn.cursor(binary=True) as cur:  # binary result format
            cur.execute(
                "SELECT ST_AsBinary(geom) FROM port_zones WHERE id = %s",
                (zone_id,),
            )
            return cur.fetchone()[0]  # raw WKB, no hex decode step

Step 2: Server-side cursors for large result sets

Both drivers support server-side (named) cursors, which SQLAlchemy exposes through stream_results and yield_per. This is essential when exporting a day of ais_positions — millions of points must not be materialised in Python at once:

from sqlalchemy import select

def stream_track(engine, mmsi: int, batch: int = 2000):
    stmt = (
        select(AisPosition.recorded_at, AisPosition.geom)
        .where(AisPosition.mmsi == mmsi)
        .order_by(AisPosition.recorded_at)
        .execution_options(stream_results=True, yield_per=batch)
    )
    with engine.connect() as conn:
        for row in conn.execute(stmt):
            yield row.recorded_at, to_shape(row.geom)

The divergence appears with async: only psycopg can back an AsyncSession server-side cursor. The full async streaming pattern lives in async streaming of large geometry result sets, and it is simply not expressible on psycopg2.

Step 3: Bulk inserts — execute_values vs pipeline and COPY

The psycopg2 idiom for high-throughput geometry inserts was psycopg2.extras.execute_values, which builds one multi-row VALUES statement:

from psycopg2.extras import execute_values
import psycopg2

def bulk_positions_v2(dsn: str, rows: list[tuple]):
    # rows: (mmsi, epoch_seconds, lon, lat)
    with psycopg2.connect(dsn) as conn:
        with conn.cursor() as cur:
            execute_values(
                cur,
                "INSERT INTO ais_positions (mmsi, recorded_at, geom) VALUES %s",
                rows,
                template="(%s, to_timestamp(%s), ST_SetSRID(ST_MakePoint(%s, %s), 4326))",
                page_size=1000,
            )
        conn.commit()

psycopg removed execute_values; the sanctioned replacements are an optimised executemany() and, for the highest throughput, pipeline mode, which pushes many statements before reading any result and so amortises network latency across the whole batch:

import psycopg

def bulk_positions_v3(dsn: str, rows: list[tuple]):
    sql = (
        "INSERT INTO ais_positions (mmsi, recorded_at, geom) "
        "VALUES (%s, to_timestamp(%s), ST_SetSRID(ST_MakePoint(%s, %s), 4326))"
    )
    with psycopg.connect(dsn) as conn:
        with conn.pipeline():          # batch round-trips
            with conn.cursor() as cur:
                cur.executemany(sql, rows)
        conn.commit()

For the fastest possible path, psycopg’s first-class COPY support streams WKB straight into the table, which is covered as a migration target in the step-by-step migration guide.

Step 4: Connection pooling

psycopg2 has no pool of its own, so you rely on SQLAlchemy’s QueuePool (the default) or an external PgBouncer. psycopg ships psycopg_pool, but inside a SQLAlchemy application you normally keep using SQLAlchemy’s pool for consistency:

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg://ais:secret@db.internal:5432/ais_tracking",
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,   # survive network blips between AIS batches
    pool_recycle=1800,
)

The pooling knobs are identical across dialects because they live in sqlalchemy.pool, not in the driver. If you front the database with PgBouncer in transaction-pooling mode, disable server-side prepared statements on psycopg with ?prepare_threshold=0 on the URL to avoid prepared-statement name collisions.

Step 5: Async engines — the decisive split

This is the axis where the two drivers stop being interchangeable. psycopg2 has no async support whatsoever, so create_async_engine("postgresql+psycopg2://...") raises immediately. psycopg (v3) drives both a synchronous and an asynchronous SQLAlchemy engine from the same package, and it is the only mainstream driver that combines async server-side cursors with GeoAlchemy2 geometry decoding. For an async AIS API that streams ais_positions to connected clients, the async engine looks like this:

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy import select

async_engine = create_async_engine(
    "postgresql+psycopg://ais:secret@db.internal:5432/ais_tracking",
    pool_size=5,
    max_overflow=10,
    pool_pre_ping=True,
)
AsyncSessionLocal = async_sessionmaker(async_engine, expire_on_commit=False)

async def recent_fixes(mmsi: int):
    stmt = (
        select(AisPosition.recorded_at, AisPosition.geom)
        .where(AisPosition.mmsi == mmsi)
        .order_by(AisPosition.recorded_at.desc())
        .limit(100)
    )
    async with AsyncSessionLocal() as session:
        result = await session.execute(stmt)
        return [(t, to_shape(g)) for t, g in result.all()]

The identical model classes and func.ST_* expressions that ran on the sync psycopg2 engine run unchanged here — only the URL and the async/await scaffolding differ. Choosing asyncpg instead would force you to abandon GeoAlchemy2 geometry decoding and select ST_AsBinary or ST_AsGeoJSON manually, because asyncpg does not register the PostGIS geometry OID. That single fact makes psycopg the pragmatic default for any async spatial service.

Step 6: Pipeline mode for write-heavy ingestion

Pipeline mode is a psycopg-only capability with no psycopg2 counterpart. It lets the driver send a batch of statements to the server before reading any of their results, so the round-trip latency is paid once for the whole batch rather than once per statement. On an AIS feed that inserts thousands of positions per second, this is the difference between being network-latency-bound and being server-throughput-bound. The insert helper from Step 3 already used conn.pipeline(); the same primitive also batches mixed read/write sequences, such as inserting a position and immediately checking which port_zones polygon contains it, without stalling on each intermediate result. Because pipeline mode changes only the raw-driver call sites and not the ORM, it slots cleanly into an otherwise GeoAlchemy2-managed application.

Performance considerations

Driver overhead is only a slice of total query time; for spatial work the PostGIS execution plan usually dominates. Reach for EXPLAIN (ANALYZE, BUFFERS) before blaming the driver:

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.mmsi, ST_AsBinary(p.geom)
FROM   ais_positions p
JOIN   port_zones z ON ST_Contains(z.geom, p.geom)
WHERE  z.name = 'Rotterdam Maasvlakte'
  AND  p.recorded_at >= now() - interval '1 hour';

If that plan shows Index Scan using ais_positions_geom_gist, the query is index-bound and both drivers will perform the same. The driver only becomes the bottleneck once you are shipping wide rows or many of them. Two measurable effects favour psycopg:

• Binary WKB decode. A port_zones multipolygon of tens of kilobytes crosses as hex text under psycopg2 (two characters per byte, then an unhexlify), versus raw bytes under psycopg binary mode. On large-geometry result sets this trims both bandwidth and CPU.
• Round-trip amortisation. Pipeline mode turns N insert round-trips into effectively one flush. For a 10,000-row AIS batch across a 1 ms network link, that is the difference between ~10 s of latency and a fraction of it.

For a single indexed point lookup, by contrast, the two drivers are statistically indistinguishable — the WKB payload is ~25 bytes and one round-trip dominates either way.

Common failure modes and fixes

Wrong dialect string

Symptom: sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.psycopg3.

Fix: The dialect for the version-3 driver is psycopg, not psycopg3. Use postgresql+psycopg://.... The psycopg3 name never appears in a SQLAlchemy URL.

Prepared-statement clash behind PgBouncer

Symptom: intermittent DuplicatePreparedStatement errors under psycopg when connecting through PgBouncer in transaction mode.

Fix: psycopg 3 auto-prepares frequently used statements; PgBouncer transaction pooling breaks that assumption. Append ?prepare_threshold=0 to the URL, or point the app at a session-pooling port.

Missing async driver

Symptom: InvalidRequestError: The asyncio extension requires an async driver when calling create_async_engine("postgresql+psycopg2://...").

Fix: psycopg2 cannot drive async. Switch the URL to postgresql+psycopg (which supports both sync and async) or to postgresql+asyncpg. Only psycopg gives you one driver for both worlds while still decoding PostGIS geometry through GeoAlchemy2.

execute_values missing after upgrade

Symptom: ImportError: cannot import name 'execute_values' from 'psycopg'.

Fix: That helper was a psycopg2.extras function with no psycopg 3 equivalent. Replace it with cursor.executemany() inside a conn.pipeline() block, or a cursor.copy() stream for the bulk path.

Verification

After committing to a driver, assert the transport behaves as intended. This check confirms the engine round-trips a known point and rebuilds it losslessly through GeoAlchemy2 on either dialect:

from sqlalchemy import text
from geoalchemy2.shape import to_shape

def verify_driver(engine) -> dict:
    checks = {}
    with engine.connect() as conn:
        checks["driver"] = engine.dialect.driver  # 'psycopg2' or 'psycopg'
        checks["postgis"] = conn.execute(
            text("SELECT PostGIS_Lib_Version()")
        ).scalar()
        # Round-trip a point through the wire and back to shapely
        wkb = conn.execute(
            text(
                "SELECT ST_AsBinary(ST_SetSRID(ST_MakePoint(4.4792, 51.9080), 4326))"
            )
        ).scalar()
    from shapely import wkb as shp_wkb
    pt = shp_wkb.loads(bytes(wkb))
    checks["roundtrip_lon"] = round(pt.x, 4)  # 4.4792 — Port of Rotterdam
    checks["roundtrip_lat"] = round(pt.y, 4)  # 51.9080
    return checks

A healthy result reports the expected driver string, PostGIS 3.3+, and the exact coordinates you sent. If the coordinates come back swapped or shifted, an SRID or axis-order problem is masquerading as a driver problem — recheck the geometry_columns metadata from the prerequisites section before changing anything else.

Frequently Asked Questions

Does GeoAlchemy2 work identically on psycopg2 and psycopg3?

Yes. GeoAlchemy2 operates above the DBAPI layer, so it decodes WKBElement values the same way regardless of driver. The observable difference is transport: psycopg 3 can request the binary result format, which delivers raw WKB bytes without a hex round-trip, whereas psycopg2 receives geometry as a hex-encoded string that GeoAlchemy2 unhexlifies before building the WKBElement.

Which driver should I use for an async FastAPI vessel-tracking service?

Use psycopg 3, whose SQLAlchemy dialect is postgresql+psycopg. It exposes a single codebase for both sync and async engines, supports async server-side cursors for streaming large AIS position exports, and adds pipeline mode to cut round-trips on batched inserts. psycopg2 has no async support at all, so an async stack would otherwise force asyncpg, which does not natively decode PostGIS geometry OIDs.

Is psycopg3 always faster than psycopg2 for spatial queries?

No. For a single indexed point lookup the two are within noise of each other because PostGIS execution time dominates the driver overhead. psycopg 3 pulls ahead on wide result sets of large geometries, where its binary result format avoids hex decoding, and on batched writes, where pipeline mode collapses many network round-trips into one flush.

Related Topics

• SQLAlchemy & GeoAlchemy Integration Workflows — parent overview of the full ORM-to-PostGIS stack
• Migrating Spatial Code From psycopg2 to psycopg3 — the step-by-step port that keeps GeoAlchemy2 untouched
• Session Management for Spatial Data — pooling, isolation, and cursor lifecycle that both drivers plug into
• Model Mapping with GeoAlchemy2 — column definitions and SRID enforcement shared across drivers