DuckDB Spatial vs PostGIS for Analytics

The choice between DuckDB and PostGIS is not a benchmark question — it is a question of who owns the data, because one engine lives inside your Python process and reads files it does not control, while the other is a server that owns durable state on behalf of many clients. This guide is for anyone sizing an analytical spatial stack and unsure which side a workload belongs on; it sits under DuckDB Spatial Analytics in Spatial Analysis & Advanced Query Techniques, and assumes you have already met the server-side workflow in PostGIS Integration with Python.

Why This Approach / What Goes Wrong

DuckDB is an embedded columnar engine: it starts when your script starts, holds no daemon, and treats a GeoParquet file — local or on object storage — as a table it can scan without importing anything. PostGIS is PostgreSQL with a spatial type system bolted into a row-store that was designed around durability, multi-version concurrency control, and a write-ahead log. Every practical difference between them falls out of those two sentences. DuckDB is fast because it never has to make a scan safe against a concurrent writer. PostGIS is safe because it never stops paying for that guarantee.

The two failure modes are mirror images. The first is standing up a database for a question you will ask once: provisioning Postgres, running ogr2ogr for two hours to load a 200-million-row export, building a GiST index for another hour, and then issuing a single aggregate that DuckDB would have answered directly off the Parquet files in minutes with no ingest at all. The second is the reverse — treating DuckDB as an application backend. A DuckDB database file is held by one process at a time; the moment a second worker tries to open it, or a web tier needs a connection pool, or someone needs a transaction they can roll back, the embedded model stops being a shortcut and becomes an outage.

Embedded engine reading object storage versus a shared server owning durable state Two architectures side by side. On the left, DuckDB with the spatial extension runs inside a single Python process and reads GeoParquet directly from object storage using HTTP range requests, with no ingest step and no daemon; only that one process may write the database file. On the right, three clients — a notebook, a web API and an ETL job — all connect to one PostgreSQL and PostGIS server, which maintains a GiST index and multi-version concurrency control with a write-ahead log over a durable heap and index files on disk, serving many concurrent readers and writers. Same SQL, opposite architectures DuckDB · embedded in your process one Python process · no daemon DuckDB + spatial columnar · vectorized · GEOS predicates range reads · zero ingest s3://city/parcels/*.parquet GeoParquet · row-group bbox statistics storage you only read one writer at a time · nothing to operate PostGIS · a server other people share notebook web API ETL job PostgreSQL + PostGIS GiST index filter → refine MVCC + WAL many writers heap + indexes on disk durable · transactional ingest first · then everyone queries it
DuckDB borrows files it never owns; PostGIS owns state on behalf of clients it never sees — every other difference follows from that.

Cost of arrival is the number most teams under-weight. DuckDB's arrival cost is one pip install and zero seconds of loading: the first query reads the Parquet directly. PostGIS charges you a server, a load, an index build, and an ANALYZE before the first useful query runs — and charges it again every time the upstream export changes. That cost is worth paying exactly when more than one consumer will benefit from it.

Prerequisites

pip install "duckdb>=1.1" "geopandas>=1.0" "shapely>=2.0" \
            "sqlalchemy>=2.0" "psycopg[binary]>=3.1" "geoalchemy2>=0.14"
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=gis --name gisdb postgis/postgis:16-3.4

Step-by-Step Implementation

The honest way to choose is to run the same question through both engines and watch where the time goes. The steps below do that on one dataset — a folder of GeoParquet parcel exports — and end at the pattern most production teams settle on.

1. Price the cost of arriving at a first answer.

DuckDB needs no load step, so the first query is also the whole setup. Time it end to end, including extension load, so the comparison is fair.

import time
import duckdb

con = duckdb.connect("analysis.duckdb")   # a file, so tables and R-trees persist
con.install_extension("spatial")
con.load_extension("spatial")

started = time.perf_counter()
matched = con.sql("""
    SELECT count(*)
    FROM 'parcels/*.parquet'
    WHERE ST_Intersects(geom, ST_MakeEnvelope(7.60, 45.00, 7.80, 45.10))
""").fetchone()[0]
print(f"{matched:,} parcels in {time.perf_counter() - started:.1f}s (ingest: none)")
# 418,203 parcels in 6.4s (ingest: none)

The PostGIS equivalent cannot start with the query. It starts with a load, and on hundreds of millions of rows the loader matters: GeoDataFrame.to_postgis sends rows through Python and is the slowest option by an order of magnitude, so drive the bulk path with ogr2ogr and build the index afterwards rather than maintaining it during the load.

# Bulk load first, index second — maintaining a GiST index during ingest is far slower
ogr2ogr -f PostgreSQL PG:"dbname=gisdb user=postgres password=gis host=localhost" \
        parcels/ -nln parcels -lco GEOMETRY_NAME=geom -lco SPATIAL_INDEX=NONE \
        -lco FID=parcel_pk -a_srs EPSG:4326 -progress

psql "postgresql://postgres:gis@localhost/gisdb" -c \
  "CREATE INDEX parcels_gix ON parcels USING GIST (geom); ANALYZE parcels;"

2. Compare what each engine's index actually accelerates.

This is the difference that surprises people. PostGIS's GiST index is a durable structure maintained on every write, consulted by the planner for both single-window lookups and table-to-table joins. DuckDB's R-tree is an in-database index over a materialised table, and it accelerates predicates against a constant geometry — it does not turn a spatial join between two tables into an indexed join. On raw Parquet scans there is no R-tree at all; the pruning comes from row-group bounding-box statistics in the file footer, which is a different mechanism with different limits, covered in Querying GeoParquet with DuckDB Spatial.

# An R-tree needs a real table — you cannot index a Parquet glob in place
con.execute("""
    CREATE OR REPLACE TABLE parcels AS
    SELECT parcel_id, land_use, geom FROM 'parcels/*.parquet'
""")
con.execute("CREATE INDEX parcels_rtree ON parcels USING RTREE (geom)")

con.sql("""
    EXPLAIN SELECT count(*) FROM parcels
    WHERE ST_Within(geom, ST_MakeEnvelope(7.60, 45.00, 7.80, 45.10))
""").show()
# The plan contains RTREE_INDEX_SCAN — the constant window is what makes it usable

The PostGIS side reads the same way, with one detail that has no DuckDB counterpart: ST_MakeEnvelope takes an SRID as its fifth argument, and it must match the column's SRID or the query errors instead of quietly returning nothing.

EXPLAIN ANALYZE
SELECT count(*) FROM parcels
WHERE ST_Intersects(geom, ST_MakeEnvelope(7.60, 45.00, 7.80, 45.10, 4326));
-- Index Scan using parcels_gix on parcels  (actual rows=418203 ...)
--   Index Cond: (geom && '0103...'::geometry)
Row-group pruning and the R-tree compared with a GiST index descent On the left, DuckDB scanning GeoParquet: five row groups are shown, of which only two overlap the query window and are read while three are skipped using bounding-box statistics stored in the file. A note explains that an explicit R-tree index requires a materialised table, helps predicates against a constant window, and does not accelerate table-to-table spatial joins. On the right, PostGIS descends a GiST tree from a root bounding box through child nodes, prunes two of three branches, reaches leaf rows, and passes the surviving candidates to an exact GEOS predicate in the refine step. A banner states that DuckDB prunes with file metadata while PostGIS prunes with an index it maintains on every write. Where each engine's speed actually comes from DuckDB: file statistics do the pruning bbox RG 1 bbox RG 2 bbox RG 3 bbox RG 4 bbox RG 5 two row groups read, three skipped — no index involved CREATE INDEX ... USING RTREE (geom) helps: predicates against a constant window no help: table-to-table spatial joins needs a materialised table, not a Parquet glob PostGIS: GiST descends a maintained tree root bbox pruned overlaps pruned rows rows candidates → exact GEOS predicate (refine) the index only filters boxes; correctness comes from refine DuckDB prunes with metadata it finds in the file · PostGIS prunes with an index it pays to maintain on every write
Both engines run a cheap box filter before an exact GEOS test — they differ in whether that filter is a durable index or statistics that happen to be in the file.

3. Test the concurrency assumption before you design around it.

DuckDB takes a file lock. One process may hold a database file read-write; while it does, no other process can open it at all, not even read-only. Inside a single process you can open many connections and DuckDB will run them across its thread pool, but that is parallelism, not multi-tenancy.

import duckdb

writer = duckdb.connect("analysis.duckdb")            # this process holds the lock
reader = duckdb.connect("analysis.duckdb", read_only=True)   # from a *second* process:
# duckdb.IOException: Could not set lock on file "analysis.duckdb":
#   Conflicting lock is held in <pid> ...

PostgreSQL's answer is a connection pool and MVCC: readers never block writers, writers never block readers, and a rolled-back edit leaves no trace. That is the property a web map tier or an editing application needs, and it is the one property DuckDB deliberately does not offer.

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg://postgres:gis@localhost:5432/gisdb",
    pool_size=10, max_overflow=20, pool_pre_ping=True,
)

4. Run the join that decides most arguments.

Joining two very large layers is where the columnar engine earns its place. Give DuckDB a memory ceiling and a spill directory and it will hash- and nested-loop its way through inputs far larger than RAM, on one machine, with no load step.

con.execute("SET memory_limit='16GB'")
con.execute("SET temp_directory='/var/tmp/duckdb'")   # large joins spill here

zone_counts = con.sql("""
    SELECT z.zone_id, count(*) AS ping_count
    FROM 'zones.parquet' z
    JOIN 'gps_pings/*.parquet' p ON ST_Contains(z.geom, p.geom)
    GROUP BY z.zone_id
    ORDER BY ping_count DESC
""").df()
print(zone_counts.head())

PostGIS runs the same statement — the function names are identical because both sit on GEOS — but only after both layers are resident and indexed, and its planner will lean on the GiST index for the inner side rather than scanning it. That makes PostGIS strong when one side is small and selective, and awkward when the job is a single full pass over two exports you will delete tomorrow.

How the result reaches Python differs just as much as how it is computed. DuckDB shares an address space with the interpreter, so .df() and .arrow() hand back columnar buffers with no serialisation across a socket, and the traffic runs both ways: a pandas or GeoPandas object already in scope can be queried by name without being copied into the engine first. A PostGIS result, by contrast, is encoded by the server, pushed through a socket, and decoded row by row by the driver before gpd.read_postgis rebuilds geometry from WKB — a fixed per-row cost that is irrelevant for a thousand rows and dominant for fifty million.

import pandas as pd

# A local DataFrame is queryable by name — no copy, no load step
sensor_sites = pd.DataFrame({"site_id": [11, 12], "lon": [7.68, 7.71], "lat": [45.06, 45.07]})
con.sql("""
    SELECT s.site_id, count(*) AS parcels_within_500m
    FROM sensor_sites s
    JOIN parcels p
      ON ST_DWithin(ST_Transform(ST_Point(s.lon, s.lat), 'EPSG:4326', 'EPSG:32632',
                                 always_xy := true),
                    ST_Transform(p.geom, 'EPSG:4326', 'EPSG:32632', always_xy := true),
                    500)
    GROUP BY s.site_id
""").show()
Which engine fits which spatial workload A matrix of five workloads scored for DuckDB and PostGIS. Ad-hoc scan of a 200 million row GeoParquet export: DuckDB strong because it reads in place with no ingest, PostGIS poor because hours of loading and index building come first. Repeat windowed lookups over one big file: both strong, DuckDB through row-group pruning and an R-tree, PostGIS through GiST filter and refine. Dozens of concurrent map clients: DuckDB poor because one process holds the file lock, PostGIS strong with a connection pool. Frequent edits that must roll back: DuckDB poor, PostGIS strong through multi-version concurrency control and the write-ahead log. One-off join of two hundred-million-row exports: DuckDB strong because it spills to disk, PostGIS workable but requires staging and memory tuning. Fit by workload, not by benchmark Workload DuckDB PostGIS Ad-hoc scan of a 200 M-row export Strong reads it in place, no ingest Poor hours of load + index first Repeat windowed lookups, one file Strong bbox pruning, then R-tree Strong GiST filter, then refine Dozens of concurrent map clients Poor one process holds the file Strong connection pool, shared cache Frequent edits that must roll back Poor single-writer, no pooling Strong MVCC + write-ahead log One-off join of two huge exports Strong spills to disk on one box Workable needs staging + work_mem strong fit workable with tuning wrong tool
Nothing in this table is about raw speed — every row is decided by ownership of state and by whether an ingest step pays for itself.

5. Check function coverage before you port a query.

DuckDB's ST_* family mirrors PostGIS naming closely enough that most analytical SQL moves across unchanged, but the tail is thinner. The geography type for spheroidal measurement, PostGIS Raster, the topology schema, pgRouting, and anything that depends on triggers or constraints simply do not exist in DuckDB. Rather than trusting a list, interrogate the catalogue in the version you actually have installed.

available = con.sql("""
    SELECT DISTINCT function_name
    FROM duckdb_functions()
    WHERE function_name ILIKE 'st_%'
    ORDER BY function_name
""").df()["function_name"].tolist()

for wanted in ("ST_DWithin", "ST_Union_Agg", "ST_Subdivide", "ST_ClusterDBSCAN"):
    print(f"{wanted:18s} {'present' if wanted.lower() in [f.lower() for f in available] else 'MISSING'}")

Where a function is missing, the pragmatic move is usually not to switch engines but to split the work: aggregate in DuckDB, then run the specialised step in PostGIS or in Python. Density-based grouping, for instance, is better handled by the tooling in Spatial Clustering Algorithms than by forcing it into whichever engine you started in.

6. Adopt the hybrid deliberately, not by accident.

The pattern that survives contact with production is PostGIS as the system of record and DuckDB as the analysis surface over exports from it. DuckDB's postgres extension makes the export a single query: postgres_query ships raw SQL to the server, so PostGIS functions run server-side and only WKB comes back over the wire.

con.install_extension("postgres")
con.load_extension("postgres")
con.execute("ATTACH 'dbname=gisdb user=postgres password=gis host=localhost' "
            "AS pg (TYPE POSTGRES, READ_ONLY)")

con.execute("""
    CREATE OR REPLACE TABLE parcels_snapshot AS
    SELECT parcel_id, land_use, ST_GeomFromWKB(wkb) AS geom
    FROM postgres_query('pg', $$
        SELECT parcel_id, land_use, ST_AsBinary(geom) AS wkb
        FROM parcels
        WHERE updated_at >= now() - interval '1 day'
    $$)
""")

# Write real GeoParquet — the GDAL writer records the CRS in the file metadata
con.execute("""
    COPY parcels_snapshot TO 'parcels_snapshot.parquet'
    (FORMAT GDAL, DRIVER 'Parquet', SRS 'EPSG:4326')
""")

Keep every metric calculation in a projected CRS on whichever side you run it. DuckDB's ST_Transform takes explicit source and target authority strings and follows the authority's declared axis order, so pass always_xy := true when your EPSG:4326 data is stored longitude-first — the same trap PyProj sets, described in Coordinate Systems with PyProj. Use the local UTM zone, never Web Mercator, for area or distance.

con.sql("""
    SELECT land_use,
           sum(ST_Area(ST_Transform(geom, 'EPSG:4326', 'EPSG:32632',
                                    always_xy := true))) AS area_m2
    FROM parcels_snapshot
    GROUP BY land_use
    ORDER BY area_m2 DESC
""").show()
Decision tree for choosing DuckDB, PostGIS, or the hybrid Three questions asked in order. First, must several processes read and write the data at once? If yes, choose PostGIS as a shared, indexed, transactional store. If no, ask whether the data already lives as GeoParquet on disk or object storage; if yes, use DuckDB and query the files in place. If no, ask whether other people will query the same data again later; if yes, load it into PostGIS, and if no, export it once to GeoParquet and analyse it with DuckDB. A banner at the bottom shows the common hybrid: PostGIS is the system of record, a scheduled export writes GeoParquet, and DuckDB runs the ad-hoc analysis over that export. Three questions, then the hybrid Must several processes write it at once? yes PostGIS shared · indexed · transactional no Is it already GeoParquet on disk or S3? yes DuckDB query the files in place no Will anyone else query this data later? yes Load it into PostGIS the ingest pays for itself no Export once, analyse in DuckDB one-off question, then discard The hybrid most teams land on PostGIS (system of record) → scheduled GeoParquet export → DuckDB (ad-hoc analysis)
Answer the ownership question first; the hybrid at the bottom is what you get when the honest answer is "both, for different reasons".

Verification

The hybrid is only trustworthy if both engines agree on the same predicate over the same rows. Because DuckDB's spatial extension and PostGIS both evaluate predicates through GEOS, an identical window over identical geometry must return an identical count — any discrepancy means the snapshot is stale, the SRID assumption is wrong, or the export dropped rows.

from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://postgres:gis@localhost:5432/gisdb")
window = (7.60, 45.00, 7.80, 45.10)

with engine.connect() as pg:
    srids = pg.execute(text("SELECT DISTINCT ST_SRID(geom) FROM parcels")).scalars().all()
    assert srids == [4326], f"Mixed or unexpected SRIDs in PostGIS: {srids}"
    pg_count = pg.execute(
        text("SELECT count(*) FROM parcels "
             "WHERE ST_Intersects(geom, ST_MakeEnvelope(:a, :b, :c, :d, 4326))"),
        dict(zip("abcd", window)),
    ).scalar()

duck_count = con.execute(
    "SELECT count(*) FROM parcels_snapshot "
    "WHERE ST_Intersects(geom, ST_MakeEnvelope(?, ?, ?, ?))", list(window)
).fetchone()[0]

print(f"PostGIS: {pg_count:,}   DuckDB: {duck_count:,}")
# PostGIS: 418,203   DuckDB: 418,203
assert duck_count == pg_count, "Snapshot drifted from the system of record"

Edge Cases & Debugging

Frequently Asked Questions

Can DuckDB replace PostGIS as a web map backend? Not as the storage layer. A tile or feature API needs many simultaneous connections and, usually, writes — and a DuckDB file admits one read-write process at a time. What DuckDB does well in that architecture is precompute: run the heavy aggregation over GeoParquet, write the result as a small table or a tile pipeline input, and let PostGIS or a static tile store serve it.

Is DuckDB actually faster than PostGIS? For full-table analytical passes — count, aggregate, dissolve, join two large exports — usually yes, often by a wide margin, because the columnar layout reads only the columns you asked for and never pays MVCC overhead. For a small, highly selective indexed lookup returning a handful of rows, a warm PostGIS with a GiST index is competitive and sometimes faster. The larger factor is almost always whether the ingest step is counted in the comparison.

Does the CRS survive a move between the two engines? Only if you carry it deliberately. PostGIS stores an SRID with every geometry and enforces it; DuckDB's GEOMETRY is bare coordinates with no CRS attached, and WKB carries none either. Record the CRS when you export (the GDAL Parquet writer stores it in the file metadata), and pass it explicitly when rebuilding a GeoDataFrame — the hand-off mechanics are in Querying GeoParquet with DuckDB Spatial.

Can I query PostGIS from DuckDB without exporting anything? Yes — ATTACH ... (TYPE POSTGRES) plus postgres_query runs your SQL on the server and streams the result back, which is ideal for incremental snapshots. It is not a substitute for an export when the analysis scans hundreds of millions of rows, because every row still crosses the network; in that case materialise once to GeoParquet and let DuckDB scan locally.

Related