PostGIS to DuckDB Spatial Migration

Moving a spatial workload off PostGIS and into DuckDB is less a data copy than a change of execution model: you trade a stateful, SRID-tagged, GiST-indexed server for an in-process columnar engine that tracks coordinate systems out-of-band and rebuilds its R-trees on demand. This guide is part of Migrating to DuckDB Spatial and walks the full end-to-end path — exporting PostGIS tables to GeoParquet, mapping the geometry model and column types, loading and indexing in DuckDB, and validating that row counts and areas survived the trip. The failure surface is specific and repeatable: SRID metadata evaporates on export, geometries that GiST happily indexed turn out invalid under ST_IsValid, and a generic geometry column carrying mixed types confuses format writers. Each of those is caught by a validation gate rather than discovered in production, and this walkthrough builds those gates as you go.

Runtime Configuration & Migration Guardrails #

A migration run holds three working sets at once — the decoded source geometry, the R-tree under construction, and the validation queries that compare source to target — so configure the session for the peak, not the average. The general-analytics defaults assume a read-mostly workload and under-provision the temp directory that a large index build spills into.

INSTALL spatial;   -- one-time download of the spatial extension into the local cache
LOAD spatial;      -- per-session; required before any ST_ function or RTREE index resolves

INSTALL postgres;  -- optional: lets DuckDB ATTACH and read the live PostGIS database
LOAD postgres;     -- only needed for the over-the-wire export route below

-- Match physical cores. R-tree construction is memory-bandwidth bound, so hyperthread
-- siblings contend for the same cache lines and slow the build instead of speeding it.
SET threads = 8;

-- Ceiling for the largest working set: decoded geometry plus the in-flight index.
-- Too low → the index build spills mid-migration; too high on a shared host → OOM-kill.
SET memory_limit = '12GB';

-- Drop row-order guarantees so the bulk load and index build parallelize. Re-impose
-- order only on the final validated output with an explicit ORDER BY.
SET preserve_insertion_order = false;

-- Spill target for the index build and any wide validation join. Point at local NVMe;
-- a network mount turns a spilling RTREE build into an I/O cliff.
SET temp_directory = '/var/lib/duckdb/spill';

Before starting a production cutover, confirm these preconditions hold:

• The source geometry column’s SRID is recorded out-of-band — DuckDB’s GEOMETRY does not persist it, so capture Find_SRID() or ST_SRID(geom) from PostGIS before you export.
• Every source table has a stable primary key so the regression harness can join source rows to target rows one-to-one.
• Invalid geometries are quarantined on the PostGIS side (GiST does not reject them) or repaired on load, as covered under Edge Cases below.
• memory_limit exceeds the uncompressed footprint of the largest table plus its R-tree, or the load is chunked.
• A local spill directory is configured so an over-budget index build degrades gracefully instead of failing outright.

Trade-off: the over-the-wire postgres extension route is convenient but serializes every geometry through the PostgreSQL protocol one buffer at a time, which is slow for large tables and holds a connection open against the live server. The file-based GeoParquet route decouples the two systems entirely and lets you re-run the DuckDB side without re-touching PostGIS — prefer it for anything beyond a few million rows.

The Geometry Model Difference #

Everything downstream depends on one structural fact: PostGIS and DuckDB store the coordinate reference system in completely different places, and the export step is where that difference bites.

In PostGIS a column is typed geometry(Point, 4326), and the SRID travels inside every geometry value — it is part of the on-disk representation, enforced by a type modifier, and readable per row with ST_SRID(geom). The server also maintains a spatial_ref_sys catalog table that resolves each SRID to a full PROJ definition. DuckDB’s GEOMETRY type is deliberately CRS-agnostic: it stores only the WKB coordinate stream, with no SRID slot. Coordinate systems are tracked externally — in a column you carry yourself, in a GeoParquet file’s crs metadata, or simply as a documented convention for the table. ST_SRID in DuckDB generally returns 0, and that is not a bug; the internal geometry representation is analyzed in the ST_Geometry versus WKB reference.

The practical consequence is that a raw WKB or plain-Parquet export drops the SRID on the floor. GeoParquet is the format that solves this: it writes the CRS into the file-level geo metadata block, so a projected layer round-trips its coordinate system even though the individual geometries no longer carry it. When you must preserve SRID through a format that has no CRS slot, carry it as an ordinary integer column and treat it as documented metadata, then reproject with ST_Transform — which in DuckDB takes an explicit source and target CRS rather than reading a stored SRID off the geometry. The mechanics are detailed in the CRS mapping and transformations reference and in the function-level translation reference.

The Migration Pipeline #

The reliable shape of a PostGIS-to-DuckDB migration is a five-stage pipeline: dump the source table, land it as GeoParquet, load it into DuckDB, build the R-tree, and validate against the source. Each stage has a single responsibility and a checkable output, so a failure localizes instead of cascading.

Stage 1–2: Exporting PostGIS tables #

There are three practical export routes, and the right one depends on table size and whether the CRS must survive automatically.

The cleanest route for most tables is ogr2ogr, which reads PostGIS directly and writes GeoParquet with the CRS embedded:

# GDAL reads the PostGIS table and writes GeoParquet with the CRS in file metadata.
# -lco GEOMETRY_ENCODING=WKB keeps the geometry as standard WKB the DuckDB reader
# resolves natively; -nlt PROMOTE_TO_MULTI normalizes a mixed single/multi column.
ogr2ogr -f Parquet parcels.parquet \
  PG:"host=db user=gis dbname=cadastre" \
  -sql "SELECT gid, parcel_ref, land_use, geom FROM parcels" \
  -lco GEOMETRY_ENCODING=WKB -nlt PROMOTE_TO_MULTI

When GDAL is not available, dump WKB straight from PostgreSQL. ST_AsEWKB preserves the SRID as an extended-WKB prefix, but most readers expect plain WKB, so pair a plain ST_AsBinary/ST_AsWKB export with an explicit SRID column you carry yourself:

-- Run inside psql on the PostGIS side. Emit plain WKB plus an explicit srid column
-- so the coordinate system is recoverable in DuckDB even without GeoParquet metadata.
COPY (
  SELECT gid,
         parcel_ref,
         ST_SRID(geom)      AS srid,      -- carried as a plain integer column
         ST_AsBinary(geom)  AS geom_wkb   -- standard WKB, no SRID prefix
  FROM parcels
) TO STDOUT WITH (FORMAT parquet);

The third route skips files entirely: attach the live database with DuckDB’s postgres extension and pull rows across the wire, converting WKB on arrival. This is the fastest to set up and the slowest to run at volume, because every geometry crosses the PostgreSQL protocol serially.

-- Read directly from the running PostGIS server. The PostGIS ST_AsWKB runs server-side;
-- DuckDB reconstructs the native GEOMETRY with ST_GeomFromWKB on arrival.
ATTACH 'host=db user=gis dbname=cadastre' AS pg (TYPE postgres, READ_ONLY);

CREATE TABLE parcels AS
SELECT gid,
       parcel_ref,
       land_use,
       ST_GeomFromWKB(geom_wkb) AS geom
FROM postgres_query('pg',
  'SELECT gid, parcel_ref, land_use, ST_AsBinary(geom) AS geom_wkb FROM parcels');

Stage 3–4: Loading and indexing in DuckDB #

GeoParquet written by GDAL carries a WKB geometry column plus the geo metadata block. DuckDB with the spatial extension loaded recognizes that block and exposes the column as native GEOMETRY; when reading plain Parquet, reconstruct it explicitly with ST_GeomFromWKB. Persist into a real table so the R-tree has a stable column to attach to — an index cannot be built over a view or a raw file scan.

-- Load GeoParquet into a persisted GEOMETRY table. Reading via ST_Read uses the
-- GDAL layer; read_parquet is faster and preferred when the geo metadata is present.
CREATE TABLE parcels AS
SELECT gid, parcel_ref, land_use, geom
FROM read_parquet('parcels.parquet');

-- If the source was plain WKB Parquet, decode the blob into native geometry instead:
-- SELECT gid, parcel_ref, srid, ST_GeomFromWKB(geom_wkb) AS geom FROM read_parquet(...);

-- Build the R-tree AFTER the bulk load, never before — a per-row-inserted index is
-- both slower to build and worse-packed than one built over the settled table.
CREATE INDEX idx_parcels_geom ON parcels USING RTREE (geom);

Building the index after the load is not a stylistic choice — DuckDB’s R-tree is bulk-loaded far more efficiently over a table that already holds all its rows, and the rebuild-after-load discipline is the whole subject of the GiST-to-R-tree migration guide and the deeper R-tree rebuild strategies reference. The GeoParquet reader’s own performance characteristics, including how it prunes row groups by bounding box, are covered under GeoParquet parsing.

Before indexing, confirm the geometry column actually landed as native GEOMETRY rather than a BLOB or VARCHAR — a mislabelled column is the most common reason a later CREATE INDEX ... USING RTREE fails outright. A one-line schema check catches it immediately:

-- The geom column must report GEOMETRY; a BLOB means the geo metadata was not
-- recognised and you still need an explicit ST_GeomFromWKB decode step.
SELECT column_name, data_type FROM duckdb_columns()
WHERE table_name = 'parcels' AND column_name = 'geom';

If the type reads BLOB, the reader treated the file as plain Parquet — re-run the load through the explicit ST_GeomFromWKB decode shown above, or read it via ST_Read('parcels.parquet'), which routes through the GDAL layer and always materializes native geometry. Either way, the index step is blocked until the column is a true GEOMETRY.

Schema & Type Mapping #

Most non-spatial columns map without surprise, but a handful of PostGIS-specific types have no DuckDB counterpart and force a decision at migration time.

The two rows that reliably cause incidents are geography and raster. A PostGIS geography column computes distances and areas on the ellipsoid; DuckDB has no geodesic type, so an ST_Area or ST_Distance that returned metres on a geography column will return degrees on the migrated planar geometry unless you project to a metric CRS first. Rasters have no home in the relational model at all and should be migrated as external files referenced by path, not pulled into a column.

Execution Plan Validation #

After loading and indexing, confirm the R-tree is actually reachable by the planner before trusting any spatial query’s performance. The check is the same two-stage predicate the point-in-polygon optimization guide relies on: a bounding-box && in the join, exact topology in the WHERE.

-- The R-tree serves the && pre-filter only if the indexed column stays bare.
EXPLAIN ANALYZE
SELECT p.gid, r.region_name
FROM parcels p
JOIN regions r ON r.geom && p.geom
WHERE ST_Intersects(r.geom, p.geom);

Read the plan and assert three things:

• An index scan, not a nested loop. A RTREE_INDEX_SCAN (or an index-driven join node) confirms the && predicate reached the R-tree. A NESTED_LOOP_JOIN over the full relation means the index was not used — verify the indexed column is bare on one side of && and that the index registered in duckdb_indexes().
• Estimated versus actual rows within ~30%. Large drift at the join node signals stale statistics or a non-selective bounding-box stage, often a symptom of the CRS mismatch described above.
• No implicit geometry cast. A CAST(geom AS VARCHAR) or a per-row ST_GeomFromText in the plan means a WKT literal leaked into the hot path; the deeper mechanics of index reachability are in the spatial indexing internals reference.

Trade-offs: What You Gain and Lose #

A migration is a deliberate exchange. Naming the losses up front prevents a mid-cutover surprise that stalls the project.

What you gain is the reason to migrate at all: vectorized columnar execution turns full-table spatial aggregations and joins from minutes into seconds, and the whole engine ships as an embedded library with no server to run. The relative crossover point — the data size and query shape at which DuckDB overtakes PostGIS — is quantified in the performance crossover benchmarks. What you lose is everything that depends on a long-running stateful server: concurrent writers, triggers, and the topology extension. If your workload is transactional, keep PostGIS; if it is analytical, the exchange is heavily in DuckDB’s favour.

Edge Cases & Anti-Patterns #

SRID silently lost on export (wrong answers, no error). A plain WKB or plain-Parquet dump strips the SRID, and DuckDB’s GEOMETRY has no slot to hold it — the geometry is simply untagged. Nothing errors, so a later reprojection that guesses the source CRS produces wrong coordinates. The fix is to record the source CRS captured from PostGIS as documented table metadata, then reproject with an explicit source and target rather than a stored SRID:

-- ST_Transform needs both CRS ends spelled out because DuckDB stores no per-geometry
-- SRID. Feed it the CRS you captured from PostGIS before the export.
CREATE TABLE parcels_m AS
SELECT gid, ST_Transform(geom, 'EPSG:4326', 'EPSG:3857') AS geom FROM parcels;

Invalid geometry that survived GiST (poisons downstream operators). PostGIS’s GiST index does not validate geometry — self-intersecting polygons and unclosed rings index and query fine, then break ST_Union, ST_Intersection, or an area sum after migration. Quarantine and repair before indexing the target:

-- Isolate offenders first, then repair deterministically before building the R-tree.
SELECT gid, ST_IsValidReason(geom) FROM parcels WHERE NOT ST_IsValid(geom);
UPDATE parcels SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);

Mixed geometry types in one column (format writer rejects the export). A generic PostGIS geometry column can hold points, lines, and polygons together. DuckDB tolerates the mix, but a strict GeoParquet or FlatGeobuf writer may demand a single geometry type per column. Normalize on the way out — -nlt PROMOTE_TO_MULTI in ogr2ogr, or split by ST_GeometryType into typed tables — and the FlatGeobuf and GeoPackage ingestion guide covers the writer-side constraints in full.

Rebuilding the index before the load settles (slow, badly packed tree). Creating the R-tree on an empty table and then bulk-inserting produces a fragmented tree with poor node fanout. Always load first, index second.

Migration Regression Analysis #

A migration is not done when the load finishes; it is done when a harness proves the target matches the source. Compare invariants that are cheap on both sides and sensitive to loss — row counts catch dropped rows, and a summed area or bounding box catches silent geometry corruption or an SRID-induced unit shift. This harness fits the orchestration patterns in Python and DuckDB integration workflows.

import duckdb
import psycopg2

TABLES = ["parcels", "regions", "roads"]
AREA_TOL = 1e-6  # relative tolerance; unit-consistent CRS assumed on both sides

pg = psycopg2.connect("host=db user=gis dbname=cadastre")
duck = duckdb.connect("cadastre.duckdb")
duck.execute("LOAD spatial;")

def pg_scalar(sql):
    with pg.cursor() as cur:
        cur.execute(sql)
        return cur.fetchone()[0]

failures = []
for t in TABLES:
    src_rows = pg_scalar(f"SELECT count(*) FROM {t}")
    dst_rows = duck.execute(f"SELECT count(*) FROM {t}").fetchone()[0]
    if src_rows != dst_rows:
        failures.append(f"{t}: row count {src_rows} -> {dst_rows}")

    # Total area is invariant across a lossless, unit-preserving migration.
    src_area = pg_scalar(f"SELECT COALESCE(SUM(ST_Area(geom)), 0) FROM {t}")
    dst_area = duck.execute(
        f"SELECT COALESCE(SUM(ST_Area(geom)), 0) FROM {t}"
    ).fetchone()[0]
    denom = max(abs(src_area), 1e-9)
    if abs(src_area - dst_area) / denom > AREA_TOL:
        failures.append(f"{t}: area drift {src_area:.4f} -> {dst_area:.4f}")

assert not failures, "Migration regressions:\n" + "\n".join(failures)
print("All tables reconciled: row counts and areas match within tolerance.")

Track these signals on every cutover run:

An area check flags the two highest-frequency migration defects at once: rows silently dropped (the sum falls) and a coordinate-unit shift from a lost SRID (the sum moves by orders of magnitude). Once both invariants hold across every table, the cutover is safe to promote.

Related #

See also

• Translating PostGIS functions to DuckDB Spatial — the function-by-function mapping, SRID-handling differences, and semantic gaps.
• Migrating PostGIS GiST indexes to R-tree — rebuild strategy and how the && two-stage filter replaces GiST’s automatic planner use.
• GeoParquet parsing and CRS mapping and transformations — the format and coordinate-system mechanics this migration depends on.
• Performance crossover benchmarks and spatial indexing internals — when DuckDB overtakes PostGIS, and the index that decides it.

Up: Migrating to DuckDB Spatial