Migrating to DuckDB Spatial from PostGIS and GeoPandas

Migrating spatial analytics to DuckDB means trading a server you connect to and a dataframe you load into RAM for an embedded, vectorized engine that runs inside your own process — and this reference, part of the DuckDB Spatial and analytical SQL knowledge base, is the map for data engineers and GIS teams making that move deliberately rather than by trial and error. It covers why analytical spatial workloads leave PostGIS (a shared server built on a row-store heap and GiST indexes) and GeoPandas (single-threaded, bounded by physical memory) for DuckDB’s columnar execution, and it names the three translation concerns that break migrations when they are ignored: function names and semantics, index behaviour, and coordinate-reference handling. The guides beneath it — PostGIS to DuckDB migration, GeoPandas to DuckDB migration, and performance crossover benchmarks — take each source stack in turn; this page frames the mental model that unifies all three.

Both source stacks converge on the same engine, but they arrive through different translation concerns: PostGIS migration is mostly about functions, indexes, and SQL semantics, while GeoPandas migration is mostly about moving from per-row Python objects to columnar Arrow buffers.

Execution Model & Core Concepts #

The reason these migrations are worth doing is a fundamental difference in execution model, and understanding it is what keeps you from porting a slow pattern faithfully into a fast engine. PostGIS is a row-store: the executor pulls one tuple at a time up through the plan, and for a spatial predicate it decodes each geometry, calls into GEOS, and moves on. That model is excellent for transactional workloads — inserting a parcel, updating one road segment, serving a single feature to a web map — because it touches exactly the rows a request needs and nothing more. GeoPandas sits at the opposite extreme of the same per-object idea: every geometry is a Python shapely object, operations map over a GeoSeries one element at a time (vectorized in NumPy for coordinate math, but object-by-object for topology), and the entire frame must fit in RAM because there is no spill, no pruning, and no query planner.

DuckDB replaces both with a vectorized, columnar analytical engine. Geometry is stored as a contiguous buffer of Well-Known Binary (WKB) values, and operators process fixed-size column chunks — 2,048 values per vector by default — so a spatial filter strides across thousands of geometries and hands only the survivors to an exact-topology kernel. The mechanics of that storage layout and the two-stage filter are the subject of the DuckDB Spatial architecture reference, and the difference between the GEOMETRY type and raw WKB is worked through in ST_Geometry vs WKB. The practical consequence for a migrating team is that the fastest PostGIS query and the fastest DuckDB query for the same question can have completely different shapes.

Three mental-model shifts carry most of the migration:

• Server to embedded. There is no daemon, no connection pool that a hundred clients share, no pg_hba.conf. DuckDB is a library linked into your process; a “connection” is an object in your address space. This removes an entire operational surface (no server to patch, tune, or scale) and removes an entire safety net (no server to absorb a runaway query away from your application).
• Per-row to vectorized. A predicate that PostGIS evaluates one tuple at a time, DuckDB evaluates over whole vectors with SIMD-friendly kernels. Patterns that were fine because the row-store only touched a handful of rows — a correlated subquery, a scalar function in the SELECT list, a per-feature Python call — become the bottleneck when the same logic runs across a full columnar scan. The modern spatial SQL query patterns reference catalogues the shapes that vectorize cleanly.
• SRID-tagged geometry to externally-tracked CRS. This is the sharpest semantic break. A PostGIS geometry(Point,4326) column carries its spatial-reference identifier in the type, and functions consult it. DuckDB’s GEOMETRY type does not durably tag geometry with an SRID — ST_SRID typically returns 0 — so the coordinate reference system becomes metadata you track yourself, in a column, a table comment, or your pipeline’s contract. The full handling model is in how DuckDB Spatial handles coordinate systems.

That last point deserves emphasis because it silently changes behaviour rather than raising an error. In PostGIS, ST_Distance between two 4326 geometries returns degrees and between two 3857 geometries returns metres, because the type carries the CRS. In DuckDB every distance is computed in whatever units the coordinates happen to be in, with no CRS to consult and no automatic reprojection — the engine will happily subtract longitudes as if they were metres. Migration correctness therefore depends on treating CRS as an explicit contract, not an attribute the engine will enforce for you.

Configuration Reference #

A migration workbench session — the DuckDB process you use to pull data out of PostGIS, reshape it, and validate it against the old system — wants a configuration tuned for bulk movement and heavy validation queries, not for a latency-sensitive service. Set these explicitly at connection time; the defaults are conservative and assume a general-analytics profile rather than a geometry-heavy bulk load.

INSTALL spatial;          -- one-time download of the spatial extension into the local cache
LOAD spatial;             -- per-session; required before any ST_ function resolves
INSTALL postgres;         -- the postgres scanner, so the workbench can read PostGIS directly
LOAD postgres;

-- Match physical cores. Topology kernels and R-tree builds are SIMD- and bandwidth-bound,
-- so hyperthread siblings contend for the same units — oversubscribing slows the load.
SET threads = 8;

-- Ceiling for the largest operator's working set during bulk conversion. Too low → the
-- conversion spills mid-flight; too high on the box hosting Postgres → you starve the server.
SET memory_limit = '10GB';

-- Drop insertion-order guarantees so bulk scans and R-tree builds parallelize. Re-impose
-- order with an explicit ORDER BY on the final validated output only.
SET preserve_insertion_order = false;

-- Spill target for over-budget conversions. Point at fast local NVMe, never a network mount,
-- or a spilling ST_Union / large join during validation becomes an I/O cliff.
SET temp_directory = '/var/lib/duckdb/spill';

-- Cap spill so a runaway validation query cannot fill the disk the source database lives on.
SET max_temp_directory_size = '40GB';

Trade-off: on a migration you often run the DuckDB workbench on the same host as the PostGIS server you are draining. In that case memory_limit and threads are not just about DuckDB’s own performance — they are the blast radius you impose on the live database. Size them so a full-table scan through the postgres scanner cannot evict the server’s shared buffers or saturate every core. When the source is remote, you can be more generous, and the persistent-file-versus-memory decision analyzed in in-memory vs disk storage applies directly to whether the workbench should stage into a .duckdb file or stay in RAM.

Confirm the extension build once per session so a planner difference after an upgrade is attributable:

-- Verify the spatial extension is loaded and note the version for reproducibility.
SELECT extension_name, installed, install_mode
FROM duckdb_extensions()
WHERE extension_name IN ('spatial', 'postgres');

For a reproducible workbench you can persist all of the above in a config file so every migration session starts identically, as shown in the DuckDB Spatial CLI setup walkthrough.

Data-Transfer Paths #

There are two families of source, and each has a preferred transfer path. Getting geometry across the boundary in a native, columnar form — never as re-parsed WKT text — is what makes the target queries fast, so transfer design is part of migration design, not a separate chore.

PostGIS to DuckDB: via the scanner or via GeoParquet #

The direct path attaches the PostGIS database and reads it as if it were a set of DuckDB tables. The one wrinkle is geometry: the scanner surfaces a PostGIS geometry column as its binary encoding, so wrap it back into a native GEOMETRY with ST_GeomFromWKB on the DuckDB side.

-- Attach the live PostGIS database read-only for the duration of the migration.
ATTACH 'dbname=gis host=127.0.0.1 user=readonly' AS pg (TYPE POSTGRES, READ_ONLY);

-- Emit WKB from Postgres, decode to native GEOMETRY in DuckDB, and keep the source
-- SRID as an explicit column because the DuckDB GEOMETRY will not carry it.
CREATE TABLE parcels AS
SELECT
    parcel_id,
    region_id,
    ST_GeomFromWKB(geom_wkb) AS geom,   -- geom_wkb comes across as a BLOB
    4326                    AS srid_src  -- record the CRS you know the source is in
FROM postgres_query('pg', 'SELECT parcel_id, region_id, ST_AsBinary(geom) AS geom_wkb, region_id FROM parcels');

For very large tables, or when you want a reusable, engine-neutral artifact, route through GeoParquet instead. A one-time COPY out of the workbench produces a columnar file with per-row-group statistics that every downstream reader can prune against — the encoding and its advantage over legacy formats are covered in GeoParquet parsing and the GeoParquet vs Shapefile performance comparison.

-- Materialize the converted table to Parquet with geometry stored as WKB.
-- Downstream scans then prune row groups before decoding a single geometry.
COPY (SELECT parcel_id, region_id, geom FROM parcels)
  TO 'parcels.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);

GeoPandas to DuckDB: via Arrow, not via WKT #

A GeoDataFrame already lives in memory; the goal is to hand it to DuckDB without a serialization round-trip. The wrong way is to stringify geometry to WKT and re-parse it. The right way is to move it as WKB inside an Arrow buffer, which DuckDB registers zero-copy and decodes once. This handoff is the whole subject of the GeoPandas to DuckDB migration guide and the Arrow mechanics behind it are detailed in the Python and DuckDB integration workflows reference.

import duckdb
import geopandas as gpd

gdf = gpd.read_file("parcels.gpkg")          # single-threaded, whole file into RAM
crs_epsg = gdf.crs.to_epsg()                 # capture the CRS BEFORE you lose the tag

# Move geometry as WKB in a plain column; DuckDB scans the frame without copying.
gdf["geom_wkb"] = gdf.geometry.to_wkb()
frame = gdf.drop(columns="geometry")

con = duckdb.connect()
con.execute("INSTALL spatial; LOAD spatial;")
con.execute(
    """
    CREATE TABLE parcels AS
    SELECT * EXCLUDE (geom_wkb),
           ST_GeomFromWKB(geom_wkb) AS geom   -- decode once, at load, into native GEOMETRY
    FROM frame
    """
)

The pattern to internalize is that the CRS lives on the GeoDataFrame and evaporates the moment geometry becomes WKB — capture gdf.crs before the conversion and carry it as data, because DuckDB will not reconstruct it for you.

Query Planning & Optimization #

Every migrated query should be validated against EXPLAIN and EXPLAIN ANALYZE, and reading the DuckDB plan is where PostGIS intuition helps most, because the concepts map even though the vocabulary does not. In Postgres you look for an Index Scan using ... gist feeding a Nested Loop, and you fear a Seq Scan under a spatial predicate. In DuckDB you look for the bounding-box predicate being served by an R-tree scan feeding a HASH_JOIN, and you fear a NESTED_LOOP_JOIN carrying the full ST_Intersects predicate — the signature of a two-stage filter that never engaged.

-- DuckDB: confirm the bbox stage is exposed and the join is not a nested loop.
EXPLAIN ANALYZE
SELECT p.id, b.name
FROM points p
JOIN boundaries b
  ON b.geom && ST_Point(p.lon, p.lat)             -- stage 1: MBR overlap (R-tree servable)
WHERE ST_Contains(b.geom, ST_Point(p.lon, p.lat)); -- stage 2: exact topology on survivors

The correspondence between the two planners is worth holding in your head during migration:

Two planning differences catch teams off guard. First, DuckDB does not use an R-tree the way Postgres uses GiST for an ordered nearest-neighbour scan — there is no index-backed ORDER BY geom <-> point LIMIT k operator; a k-nearest search is expressed as a bounded ST_DWithin window, as worked through in the range and kNN search guide. Second, the R-tree only helps when the && predicate is exposed against the bare indexed column; wrap that column in a function and DuckDB, like Postgres, falls back to a full scan. The index internals and this exposure rule are detailed in R-tree spatial indexing internals, and the specifics of porting a GiST index are covered in migrating GiST indexes to R-tree.

Capture the plan as JSON when you want a machine-readable baseline to diff old-versus-new behaviour across a migration:

-- Machine-readable plan for regression tracking during the migration.
EXPLAIN (FORMAT JSON)
SELECT COUNT(*) FROM points p JOIN boundaries b ON b.geom && ST_Point(p.lon, p.lat);

Production Deployment Boundaries #

Deploying a migrated workload changes the security and isolation model as much as it changes performance, and pretending otherwise is the most common way a migration surprises an operations team. PostGIS gives you GRANT/REVOKE, roles, row-level security, per-connection resource limits enforced by the server, and a clear network boundary. DuckDB has none of these. It is a library; there is no server-side authorization layer, no GRANT, no roles, no row-level security. Access control lives entirely in the surrounding application and filesystem: whoever can read the .duckdb file or the Parquet objects can read every row, and whoever can open a writable connection can change anything.

The isolation model is likewise different. DuckDB uses a single-writer, multiple-reader concurrency model within a process and across processes on the same file, not the MVCC-with-many-concurrent-writers model of Postgres. Long-running analytical readers coexist fine; concurrent writers do not, and a migration that assumed a busy write path will need to funnel writes through one owner. The connection and concurrency implications of running this inside a service are the subject of the connection and concurrency management guide.

Because there is no server to absorb a bad query, resource boundaries are the host’s boundaries:

• Memory. A single memory_limit is a global ceiling for the connection. A runaway ST_Union competes directly with the application that embeds DuckDB — size the limit to the worst-case geometry materialization, not the average, and give distinct tenants distinct connections rather than trusting one ceiling.
• CPU. Topology kernels are SIMD-heavy and CPU-bound. Setting threads above the physical core count slows index builds and topology evaluation because hyperthread siblings fight over the same vector units; on a shared host, cap threads below the core count to leave headroom for co-resident services.
• Storage and file descriptors. The spill volume must be fast local storage, and wide multi-file Parquet scans need a high file-descriptor limit — both are host settings the old PostGIS server used to own on your behalf.

Failure Modes & Diagnostics #

Migrations fail quietly far more often than they fail loudly, and the dangerous failures all come from a source-system assumption that DuckDB does not share. Each has a detection query.

CRS assumption drift (silent wrong answers). This is the number-one migration bug. A query that returned metres in PostGIS returns degrees in DuckDB because the geometry no longer carries an SRID and no reprojection happened. The result looks plausible — a distance, an area — and is geometrically nonsense.

-- Diagnostic: DuckDB geometry usually reports SRID 0, so you cannot trust the type.
-- Assert the coordinate range instead: metric CRS values are large, degrees are within ±180.
SELECT
    min(ST_X(geom)) AS min_x, max(ST_X(geom)) AS max_x,
    min(ST_Y(geom)) AS min_y, max(ST_Y(geom)) AS max_y
FROM parcels;   -- values inside [-180,180] mean degrees — a metre threshold is meaningless

Remediate by projecting once, explicitly, with ST_Transform, and by carrying the CRS as data throughout the pipeline. The full transformation model and its cost live in CRS mapping and transformations, and the Python-side version of this bug — a GeoDataFrame losing its crs on round-trip — is dissected in fixing CRS drift in GeoDataFrame conversion.

GiST-versus-RTREE behavioural gaps (silent slowdown). A GiST index in PostGIS is consulted automatically by the planner for &&, ST_Intersects, and nearest-neighbour operators. DuckDB’s R-tree is narrower: you must create it explicitly, it is used only when the && predicate is exposed against the bare column, and it does not power an ordered kNN scan. A migrated query that “had an index” in Postgres may run without one in DuckDB and degrade to a full scan.

-- Diagnostic: confirm the R-tree actually exists before trusting a migrated plan.
SELECT index_name, table_name, is_unique
FROM duckdb_indexes()
WHERE table_name = 'boundaries';
-- If empty, the GiST index did not migrate: CREATE INDEX ... USING RTREE (geom);

Function-name and semantics mismatches (errors or wrong results). Most ST_ names are shared, but not all, and the semantics behind a shared name can differ. ST_Union is both a binary function and an aggregate; the argument order of ST_Contains versus ST_Within is easy to invert during a port; DuckDB has no geography type, so spheroidal-distance queries that relied on PostGIS geography need an explicit reprojection or a different function; and helpers such as ST_MakeEnvelope or ST_DWithin’s spheroid option may not have identical signatures. The systematic mapping is the entire content of translating PostGIS functions to DuckDB, and the GeoPandas equivalent — replacing gpd.sjoin with a DuckDB spatial join — is covered in replacing GeoPandas sjoin with DuckDB.

Invalid geometry surviving the transfer (downstream corruption). PostGIS may have tolerated, or silently repaired, geometry that DuckDB’s GEOS kernels reject during a union or intersection. Validate at the boundary rather than discovering it three transforms later:

-- Quarantine invalid geometry immediately after transfer, before any set operation.
SELECT parcel_id, geom
FROM parcels
WHERE NOT ST_IsValid(geom);
-- Repair deterministically: UPDATE parcels SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);

Treat all four as gates in the migration itself: assert the coordinate range, assert the index exists, diff the function results against the source system on a sample, and quarantine invalid geometry — before you cut any workload over. The performance question of whether the cutover pays off, and at what dataset size it does, is quantified in the performance crossover benchmarks guide.

Related #

See also

• PostGIS to DuckDB migration — the server-to-embedded port in full, including function translation and GiST-to-R-tree.
• GeoPandas to DuckDB migration — moving from in-memory dataframes to columnar SQL, including replacing sjoin.
• Performance crossover benchmarks — where DuckDB wins or loses versus PostGIS and GeoPandas, and the crossover points.
• DuckDB Spatial architecture and fundamentals — storage layout, R-tree indexing internals, and CRS handling.
• Modern spatial SQL query patterns and Python and DuckDB integration workflows — the target query shapes and orchestration your migrated workload lands in.

Up: DuckDB Spatial & Modern Analytical SQL for GIS