Arrow Interop Patterns for DuckDB Spatial

Apache Arrow is the columnar wire format that lets DuckDB Spatial and the Python geospatial stack share the same buffers without ever serializing a geometry to text, and getting the geometry-encoding boundary right is what separates a genuinely zero-copy handoff from a hidden per-row parse. This guide sits under Python & DuckDB Integration Workflows and covers how geometry crosses the Arrow C Data Interface as WKB binary or GeoArrow, how to register Arrow tables and RecordBatchReaders back into DuckDB as virtual tables, how to stream results out of an engine result set with fetch_record_batch for larger-than-memory pipelines, and where DuckDB’s native GEOMETRY type must be explicitly encoded before it can travel. The one rule that governs everything below is that DuckDB GEOMETRY is an internal type that does not exist in Arrow — it must become a BLOB of WKB (or a GeoArrow-typed column) at the boundary, and the receiver rebuilds Shapely or GeoPandas geometry on the far side.

Runtime Configuration & Memory Guardrails #

Arrow interop is deceptively cheap on paper — the whole point is to avoid copies — but the two processes sharing the address space (DuckDB’s execution buffers and Python’s Arrow allocator) contend for the same RAM. A materialized arrow() call pulls the entire result into a pyarrow.Table at once; a streaming fetch_record_batch bounds that to one batch at a time. Configure the session so neither the engine nor the Arrow buffer pool can grow without a ceiling.

import duckdb

con = duckdb.connect(":memory:")
con.execute("INSTALL spatial; LOAD spatial;")

# Hard cap on DuckDB's working set. The Arrow export allocates ON TOP of this,
# so leave headroom: an arrow() of a 4GB result needs the result buffer too.
con.execute("SET memory_limit = '8GB';")

# Match physical cores. Arrow export parallelizes per column chunk; oversubscribing
# hyperthreads contends for memory bandwidth without speeding the encode.
con.execute("SET threads = 8;")

# Drop row-order guarantees so scans and the WKB projection run out of order.
# Re-impose order with an explicit ORDER BY only on the final materialized result.
con.execute("SET preserve_insertion_order = false;")

# Spill target for over-budget operators upstream of the export. Point at local
# NVMe; a network mount turns a spilling scan into an I/O cliff before Arrow ever sees it.
con.execute("SET temp_directory = '/var/lib/duckdb/spill';")

Before wiring an Arrow handoff into a production pipeline, confirm these preconditions:

• Geometry columns are native GEOMETRY at rest and are projected to WKB (ST_AsWKB) or GeoArrow at the query boundary — never left as an implicit type that Arrow cannot represent.
• The consumer’s CRS is recorded out-of-band; Arrow WKB carries no SRID, and the receiver must re-attach it explicitly (the mechanics live in the DuckDB-to-GeoPandas sync guide).
• For results larger than a comfortable fraction of memory_limit, the pipeline streams via fetch_record_batch rather than materializing the whole table with arrow().
• Any Arrow table registered back into DuckDB has a stable schema; a geometry column arriving as BLOB stays BLOB until ST_GeomFromWKB re-hydrates it.

Trade-off: raising memory_limit lets arrow() materialize larger results in one shot, but the Arrow table is a second full copy alongside DuckDB’s own buffers — on a shared host that doubled footprint is what triggers the OOM kill. When the result approaches memory pressure, streaming batches is not an optimization, it is the only correct pattern; the batch-oriented orchestration is covered in batch processing pipelines.

The Geometry-Encoding Boundary #

Everything in Arrow interop turns on one fact: DuckDB GEOMETRY is not an Arrow type. Internally DuckDB stores geometry as its own WKB-derived blob layout, and the Arrow C Data Interface has no logical type for it. If you export a GEOMETRY column directly, DuckDB emits it as an Arrow binary/BLOB column of raw bytes that most consumers cannot interpret. To cross the boundary deterministically you choose one of two encodings and make it explicit in the SQL projection.

The two encodings differ in what the receiver has to do:

• WKB in an Arrow binary column. ST_AsWKB(geom) emits standards WKB bytes. It is universally understood — Shapely, GeoPandas, and any WKB reader can rebuild geometry from it — but the bytes are opaque to Arrow-native compute; nothing but a WKB decoder can operate on them.
• GeoArrow-encoded column. GeoArrow encodes coordinates as native Arrow nested lists (a point is a struct/fixed_size_list of doubles), so Arrow-native tools can read coordinates without a WKB parse. It is the emerging interchange standard and what GeoPandas’ to_arrow()/from_arrow() speak, but it is more rigid about geometry-type uniformity per column.

For a DuckDB export today the pragmatic default is WKB: ST_AsWKB is a single vectorized kernel, and the zero-copy Arrow handoff to GeoPandas walkthrough shows the GeoSeries.from_wkb rebuild that turns those bytes into a GeoDataFrame in one vectorized call.

Primary Execution Patterns #

There are three canonical directions across the Arrow bridge: exporting a result to a pyarrow.Table, streaming a result as a RecordBatchReader, and importing an Arrow object back into DuckDB as a virtual table. All three keep geometry as bytes on the wire and rebuild it only at the endpoints.

Materialized export with arrow() / fetch_arrow_table() #

The simplest handoff runs a query, projects geometry to WKB, and pulls the whole result into a pyarrow.Table. con.arrow() and con.fetch_arrow_table() are equivalent entry points to the same Arrow export path.

import duckdb
import pyarrow as pa
from geopandas import GeoSeries

con = duckdb.connect(":memory:")
con.execute("INSTALL spatial; LOAD spatial;")

# Project GEOMETRY -> WKB in the SQL, so the Arrow column is a portable binary
# column instead of DuckDB's opaque internal geometry layout.
tbl: pa.Table = con.execute("""
    SELECT id, name,
           ST_AsWKB(geom) AS geom_wkb   -- the only column that crosses as geometry
    FROM parcels
    WHERE ST_Area(geom) > 1000
""").arrow()

# Rebuild geometry vectorized on the Python side; CRS is re-attached explicitly
# because Arrow WKB carried no SRID across the boundary.
geometry = GeoSeries.from_wkb(tbl.column("geom_wkb").to_pandas(), crs="EPSG:3857")

The materialized form is correct whenever the result comfortably fits in RAM alongside DuckDB’s own buffers. The efficient GeoDataFrame assembly around this pattern — avoiding a second pandas copy — is detailed in converting DuckDB queries to a GeoDataFrame efficiently.

Streaming export with fetch_record_batch #

For results that exceed a safe fraction of memory_limit, fetch_record_batch(rows_per_batch) returns a pyarrow.RecordBatchReader that pulls one batch at a time. DuckDB produces each batch lazily as the reader is iterated, so the peak footprint is one batch plus the reconstructed geometry for that batch — not the whole result.

import duckdb
from geopandas import GeoSeries

con = duckdb.connect(":memory:")
con.execute("INSTALL spatial; LOAD spatial;")

# rows_per_batch bounds the per-iteration working set. Larger batches amortize the
# per-batch Python/Arrow call overhead; smaller batches cap peak memory. Tune to
# geometry width: dense polygons need far smaller batches than points.
reader = con.execute("""
    SELECT trip_id, ST_AsWKB(geom) AS geom_wkb
    FROM trajectories
""").fetch_record_batch(rows_per_batch=100_000)

for batch in reader:                     # each batch is a pyarrow.RecordBatch
    geoms = GeoSeries.from_wkb(batch.column("geom_wkb").to_pandas(), crs="EPSG:4326")
    process(geoms)                       # write a GeoParquet shard, buffer downstream, etc.

This is the larger-than-memory pattern: the reader backpressures DuckDB, so a 200M-row trajectory scan never materializes as a single table. Pairing it with chunked writes lets a pipeline process a dataset far larger than RAM, as covered in chunking large GeoParquet writes.

Importing Arrow back into DuckDB #

The reverse direction is just as important: an Arrow table already in Python (from GeoPandas’ to_arrow(), a parquet reader, or another engine) can be queried by DuckDB without a copy. DuckDB’s replacement scan lets you name the Python variable directly in FROM, and con.register gives it a stable alias.

import duckdb
import geopandas as gpd

con = duckdb.connect(":memory:")
con.execute("INSTALL spatial; LOAD spatial;")

# GeoPandas emits GeoArrow; to keep the DuckDB side simple, hand it WKB instead.
gdf = gpd.read_parquet("boundaries.parquet")
arrow_boundaries = gdf.to_wkb().to_arrow()   # geometry column now WKB bytes in Arrow

# Option A: replacement scan — DuckDB resolves the Python variable name in FROM.
con.execute("SELECT count(*) FROM arrow_boundaries").fetchall()

# Option B: register a stable virtual-table alias, then re-hydrate geometry in SQL.
con.register("boundaries_vt", arrow_boundaries)
con.execute("""
    CREATE TABLE boundaries AS
    SELECT * REPLACE (ST_GeomFromWKB(geometry) AS geometry)  -- BLOB -> native GEOMETRY
    FROM boundaries_vt
""")

The critical step is the ST_GeomFromWKB re-hydration: until you run it, the geometry column is an inert BLOB and no ST_ function or R-tree index will touch it. You can also build a relation with duckdb.from_arrow(arrow_object), which accepts both a pyarrow.Table and a RecordBatchReader and returns a DuckDB relation you can compose further — the streaming form lets DuckDB consume another engine’s output batch-by-batch. The Shapely-side equivalents of this round-trip, where individual geometries become Python objects, are contrasted in the Shapely integration guide.

GeoArrow round-trips with GeoPandas to_arrow / from_arrow #

When both ends of the pipeline understand GeoArrow, you can skip the WKB step and let coordinates travel as native Arrow lists. GeoPandas emits GeoArrow from GeoDataFrame.to_arrow() and reconstructs from GeoDataFrame.from_arrow(), and DuckDB reads the resulting Arrow object through the same replacement-scan mechanism. The difference is that the geometry column arrives coordinate-native rather than as opaque bytes, so an Arrow-native consumer can inspect it without a decode.

import duckdb
import geopandas as gpd

con = duckdb.connect(":memory:")
con.execute("INSTALL spatial; LOAD spatial;")

# GeoPandas -> GeoArrow -> DuckDB. to_arrow() emits a GeoArrow-encoded table;
# DuckDB scans it directly, though ST_ operations still need the geometry lifted.
gdf = gpd.read_parquet("regions.parquet")
arrow_geo = gdf.to_arrow()                       # GeoArrow-typed geometry column
con.register("regions_ga", arrow_geo)

# DuckDB -> GeoArrow -> GeoPandas. from_arrow rebuilds a GeoDataFrame with CRS
# preserved, because GeoArrow (unlike bare WKB) can carry CRS metadata in-band.
result = con.execute("SELECT * FROM regions_ga").arrow()
gdf_back = gpd.GeoDataFrame.from_arrow(result)

The trade-off is rigidity: GeoArrow expects a uniform geometry type per column, so a mixed-type export still favours WKB. The upside is metadata — GeoArrow’s extension type can carry the CRS in-band, closing the one gap that forces WKB pipelines to re-attach the SRID by hand. For a DuckDB-authored export today the WKB path remains the least-surprising default, and GeoArrow is the option to reach for when the whole chain is GeoArrow-aware end to end.

Execution Plan Validation #

Registering an Arrow table does not mean DuckDB scans it efficiently — you validate that with EXPLAIN. A healthy plan over a registered Arrow relation shows an ARROW_SCAN (sometimes surfaced as a TABLE_SCAN over the Arrow source) with projection and filter pushdown applied, so only the requested columns and rows are pulled across the C Data Interface.

EXPLAIN
SELECT trip_id, ST_GeomFromWKB(geom_wkb) AS geom
FROM arrow_trajectories
WHERE trip_id > 1000;

What to assert in the output:

• Projection pushdown. The ARROW_SCAN should list only trip_id and geom_wkb, not the full schema. If every column is projected, DuckDB is pulling more of the Arrow buffer than the query needs — select explicit columns rather than SELECT *.
• Filter pushdown. The trip_id > 1000 predicate should appear at the scan, not as a separate FILTER above it. A filter that fails to push down means every batch crosses the interface before being discarded.
• No hidden cast. If a CAST node wraps the geometry column, a type mismatch is forcing a per-row conversion. The geometry should arrive as BLOB and be lifted by an explicit ST_GeomFromWKB projection — never an implicit cast in a predicate.

For the export direction, use EXPLAIN ANALYZE to confirm the WKB projection is a single vectorized pass and not a row-at-a-time fallback:

-- Measured plan: the ST_AsWKB projection should be one vectorized PROJECTION node.
EXPLAIN ANALYZE
SELECT id, ST_AsWKB(geom) AS geom_wkb FROM parcels;

A ROW_EXECUTION marker or a SCALAR_FUNCTION_EVAL of ST_AsWKB inside a per-row context means the encode is bypassing SIMD kernels — usually because a scalar Python UDF or an implicit GEOMETRY → VARCHAR cast crept into the projection list. The broader plan-reading discipline for the export side carries over from vectorized aggregations.

Performance Trade-offs #

The Arrow boundary has three tuning levers — encoding, batch size, and materialized-versus-streaming — and each is a bounded trade-off. Measure against your own geometry width, but these are the typical shapes:

The single highest-leverage decision is streaming versus materializing. A materialized arrow() of a result near memory_limit is the most common cause of an Arrow-boundary OOM, because the Arrow table is a full second copy — switching to fetch_record_batch removes that copy entirely at the cost of a for loop. The second-highest is encoding: WKB keeps the export to a single vectorized ST_AsWKB kernel and defers all geometry reconstruction to the endpoint, which is exactly what makes the transfer cheap regardless of how many rows cross.

Edge Cases & Anti-Patterns #

Exporting GEOMETRY without ST_AsWKB (silent BLOB). Selecting a raw GEOMETRY column into Arrow emits DuckDB’s internal blob layout, which downstream WKB readers misparse or reject.

# Anti-pattern: geometry crosses as an opaque internal blob; from_wkb will choke.
tbl = con.execute("SELECT id, geom FROM parcels").arrow()

# Fix: make the encoding explicit so the column is standards WKB.
tbl = con.execute("SELECT id, ST_AsWKB(geom) AS geom_wkb FROM parcels").arrow()

Assuming CRS survives the crossing (silent wrong coordinates). Arrow WKB carries no SRID and no PROJ metadata. If the receiver does not re-attach the CRS, the GeoDataFrame has geometry with crs=None, and any subsequent reprojection or distance is silently in the wrong units. Record the SRID before the export and pass it to set_crs; the drift failure mode and its fix are dissected in fixing CRS drift in GeoDataFrame conversion.

-- Capture the SRID out of band before exporting, so Python can re-attach it.
SELECT DISTINCT ST_SRID(geom) AS srid FROM parcels;

Null geometry breaking the rebuild. ST_AsWKB(NULL) yields a null Arrow slot, and GeoSeries.from_wkb maps that to a missing geometry — which is usually fine, but an anti-join or a COALESCE to an empty-geometry sentinel is often what you actually want. Decide explicitly rather than letting nulls flow into a spatial predicate that treats them as non-matching.

-- Make null-geometry handling explicit at the boundary instead of downstream.
SELECT id, ST_AsWKB(COALESCE(geom, ST_GeomFromText('POINT EMPTY'))) AS geom_wkb
FROM parcels;

Chunk sizing that ignores geometry width. A rows_per_batch tuned for a point layer will blow the budget on dense multpolygons, where a single row can carry megabytes of coordinates. Size batches by bytes, not rows: for wide geometry, drop rows_per_batch by an order of magnitude so each batch stays within the per-iteration ceiling.

Registering an Arrow relation and never re-hydrating. A geometry column imported as BLOB and left that way silently disables every spatial operation — ST_Contains over it errors or returns nonsense, and CREATE INDEX ... USING RTREE refuses the column. Always follow con.register with an ST_GeomFromWKB projection before treating the column as geometry.

Query Regression Analysis #

An Arrow pipeline regresses in two quiet ways: the export projection falls back to row-at-a-time encoding, or a registered scan stops pushing projection/filter down and drags the whole buffer across the interface. Capture the plan as JSON, walk it, and assert that neither pattern appears. This harness composes with the plan-diffing approach used across async execution patterns so the capture never blocks the event loop.

import duckdb
import json

con = duckdb.connect(":memory:")
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("SET threads = 8; SET memory_limit = '8GB';")

QUERY = """
SELECT id, ST_AsWKB(geom) AS geom_wkb
FROM parcels
WHERE ST_Area(geom) > 1000
"""

# FORMAT JSON returns a machine-readable plan tree that diffs cleanly across builds.
plan = json.loads(
    con.execute(f"EXPLAIN (FORMAT JSON) {QUERY}").fetchone()[1]
)

BANNED = {"ROW_EXECUTION"}                       # per-row fallback in the WKB encode

def walk(node):
    yield node.get("name", "")
    for child in node.get("children", []):
        yield from walk(child)

found = set(walk(plan[0])) if isinstance(plan, list) else set(walk(plan))
assert not (found & BANNED), f"Arrow export regressed to row execution: {found & BANNED}"

# Guard the round-trip too: register, re-hydrate, and confirm the count survives.
import pyarrow as pa
sample = con.execute(QUERY).arrow()
con.register("roundtrip_vt", sample)
n_in = sample.num_rows
n_out = con.execute(
    "SELECT count(*) FROM roundtrip_vt WHERE ST_GeomFromWKB(geom_wkb) IS NOT NULL"
).fetchone()[0]
assert n_in == n_out, f"Round-trip lost rows: {n_in} in, {n_out} out"

Three signals are worth tracking across builds: the presence of ROW_EXECUTION in the export plan (a hard regression to per-row WKB encoding), the projected column list at the ARROW_SCAN (a widening list means projection pushdown broke), and the round-trip row count (a mismatch means null-geometry handling or a schema shift silently dropped rows).

Diagnostic thresholds to alert on:

Related #

See also

• Zero-copy Arrow handoff to GeoPandas — the narrow, fastest DuckDB→GeoPandas path built on the WKB export shown here.
• DuckDB-to-GeoPandas sync and converting DuckDB queries to a GeoDataFrame efficiently — assembling a GeoDataFrame from the Arrow table without a redundant copy.
• Shapely integration — when geometry must become individual Python objects rather than staying columnar.
• Batch processing pipelines — orchestrating the streaming fetch_record_batch path over larger-than-memory inputs.

Up: Python & DuckDB Integration Workflows