DuckDB Spatial Analytical SQL for GIS

DuckDB vs PostGIS for Analytical Workloads

The question “which is faster” has no answer, but “which should own this analytical workload” has a short one — this walkthrough, part of the engine comparisons reference, works through the five analytical query shapes that actually occur, what changes about a query beyond its timing when it moves, and the four questions that settle ownership without needing a benchmark at all.

Root-Cause Analysis: why the comparison is so often reported wrongly

Published comparisons between these two engines disagree wildly, and the disagreements are almost entirely methodological rather than substantive.

  • One query shape is generalised. The two engines lead on different shapes, so a benchmark of a single query produces a correct result that transfers to nothing else. The five-shape table below exists precisely because no single number is available.
  • Index state differs. A PostGIS table with a years-old GiST index against a freshly loaded DuckDB table with none is a demonstration that indexes work. This alone accounts for factors of ten to a hundred.
  • Parallelism budgets differ. DuckDB takes every core by default; a Postgres backend takes one unless parallel query is configured. Comparing defaults compares hardware allocation.
  • Cache state is unreported. A cold first read against a warm repeat differs by an order of magnitude, and the two answer different operational questions.
  • Operational cost is omitted entirely. The dimension most teams actually decide on — what has to be run, backed up and upgraded — appears in almost no benchmark.

The distinguishing question is what fraction of the table the query touches. That single property predicts the winner better than dataset size, row count or anything else.

Five analytical shapes, compared Full-layer aggregations and wide joins favour DuckDB; selective filters are even; single-row lookups favour PostGIS; repeated dashboard queries favour DuckDB once loaded. QUERY SHAPE FAVOURS BY ROUGHLY full-layer aggregation DuckDB one to two orders of magnitude join across two whole layers DuckDB one order of magnitude selective filter, <1,000 rows neither within a factor of two single row by primary key PostGIS one order of magnitude repeated dashboard query DuckDB after the load amortises

Three rows for one engine, one for the other, one even. The workload picks the row.

Deterministic Configuration 

-- DuckDB configured for comparability rather than for a good result.
SET threads = 4;                          -- match what the other side actually got
SET memory_limit = '8GB';
SET temp_directory = '/var/tmp/duckdb_cmp';
SET enable_object_cache = false;          -- so cold and warm are distinguishable

-- Attach the source directly so both sides read the same rows, with no
-- export step in between to introduce a difference.
ATTACH 'postgres:dbname=gis host=db-a' AS pg (TYPE POSTGRES, READ_ONLY);

Optimized Execution Pattern

The rewrite that matters when a query moves is the two-stage predicate, because PostGIS applied it for you and DuckDB does not.

-- ANTI-PATTERN: the direct port. Correct, and it forfeits the index, because
-- the optimizer cannot decompose ST_Intersects into an envelope stage.
SELECT z.zone_id, count(*)
FROM incidents i JOIN zones z ON ST_Intersects(z.geom, i.geom)
GROUP BY z.zone_id;
-- PATTERN: the envelope test in ON, where an index can serve it, and the
-- exact predicate in WHERE, where it runs only on survivors.
SELECT z.zone_id, count(*)
FROM incidents i
JOIN zones z ON z.geom && i.geom
WHERE ST_Intersects(z.geom, i.geom)
GROUP BY z.zone_id;
What actually changes when the query moves Row storage becomes columnar, one backend becomes all cores, automatic GiST becomes explicit R-tree, WAL becomes snapshots, and roles become file permissions. DIMENSION POSTGIS DUCKDB storage model row-oriented columnar — reads only what you select parallelism one backend per connection all cores within one query index use GiST, consulted automatically R-tree, explicit, via && durability WAL, point-in-time recovery a file plus your snapshot policy authorisation roles and GRANT filesystem permissions

The first two rows are why it is faster. The last three are why it is not a replacement.

The three rows that are not about speed

The bottom three rows of that comparison are why “DuckDB is faster on analytics” is a true statement that settles nothing. Explicit indexing is a habit change with a checklist. Durability and authorisation are absences: there is no slower version of row-level security in DuckDB, there is none. A workload whose analytical half moves and whose transactional half stays is the normal outcome, and the snapshot between them is a feature rather than a compromise — nothing anyone runs on the analytical side can slow down the system of record.

Diagnostic Queries & Plan Validation 

-- Compare row counts at each stage rather than wall-clock time. If the
-- candidate counts differ between the two engines, the queries are not
-- equivalent and no timing comparison between them means anything.
EXPLAIN ANALYZE
SELECT z.zone_id, count(*)
FROM incidents i JOIN zones z ON z.geom && i.geom
WHERE ST_Intersects(z.geom, i.geom)
GROUP BY z.zone_id;

Diagnostic — are the two answering the same question? Run both and compare a count and a checksum before comparing anything else. A predicate whose argument order was inverted during the port returns a plausible number of plausible rows, and a timing comparison against it is measuring two different queries.

SELECT count(*) AS rows_out, sum(hash(zone_id, n)) AS checksum
FROM (SELECT z.zone_id, count(*) AS n
      FROM incidents i JOIN zones z ON z.geom && i.geom
      WHERE ST_Intersects(z.geom, i.geom) GROUP BY z.zone_id);
Which engine should own the analytical workload Continuous freshness favours PostGIS; periodic refresh favours DuckDB; many concurrent heavy users favour PostGIS; exploratory iteration favours DuckDB. THE DECIDING QUESTION ANSWER WHY must analysis see the latest write? PostGIS a snapshot is stale by construction is a periodic refresh acceptable? DuckDB the snapshot is also an isolation boundary many concurrent heavy users? PostGIS one memory ceiling is shared in-process exploratory and iterative? DuckDB instant startup, no server to protect

Four questions, and the first one you answer “yes” to settles it.

Geometry Validation & Fallback Routing

The one failure that is specific to this comparison is invalid geometry surfacing for the first time during the move. PostGIS may have tolerated, or silently repaired, shapes that GEOS in DuckDB rejects during an overlay — so a query that ran for years can fail on its first execution against migrated data, and the cause looks like the migration rather than like the data.

-- Gate the transferred data before comparing anything. A non-zero count here
-- explains a failure that would otherwise be blamed on the engine.
SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid,
       count(*) FILTER (WHERE geom IS NULL)         AS missing,
       count(*)                                     AS total
FROM zones;

Running both without keeping two copies

The arrangement that works in practice is not two databases holding the same data but one dataset with two readers. PostGIS remains the system of record and accepts every write; a scheduled job exports a GeoParquet snapshot; DuckDB reads that snapshot for analysis. There is one authoritative copy and one derived one, the derivation is a single scheduled statement, and the direction of dependency is unambiguous.

What that buys, beyond speed, is isolation. An analytical query that would previously have competed with transactional work for the same buffer pool and the same connection slots now runs against a file on a different machine. The worst thing a careless analyst can do is exhaust their own memory limit, and the system of record does not notice. That property is frequently worth more than the throughput difference, and it is available immediately rather than after a migration.

-- The whole interface between the two systems, as one scheduled statement.
-- Sorted on the columns analysis filters by, so the snapshot is prunable.
ATTACH 'postgres:dbname=gis host=db-a' AS pg (TYPE POSTGRES, READ_ONLY);

COPY (
    SELECT parcel_id, region, land_use, geom
    FROM pg.public.parcels
    ORDER BY region, land_use
) TO 's3://lake/parcels' (FORMAT PARQUET, PARTITION_BY (region), ROW_GROUP_SIZE 65536);

The freshness of the analytical side is then exactly the schedule of that job, which is a number someone chose rather than a property of the architecture — and if the answer is that no staleness is acceptable, that is a clear signal the query belongs on the transactional side after all.

Frequently Asked Questions

At what data size does DuckDB overtake PostGIS?

Size is the wrong axis. What predicts the winner is the fraction of the table the query touches: a query reading most of a layer favours DuckDB from quite modest sizes, and a query fetching twenty rows by key never will. Two datasets of identical size can land on opposite sides depending only on the queries run against them.

Do I have to give up my GiST indexes?

You have to recreate them, as R-trees, after the load rather than before it. And you have to reach them deliberately: DuckDB will not decompose an exact predicate into an envelope stage the way PostGIS’s planner does, so the two-stage form has to be written out. Both are habit changes rather than losses.

Can DuckDB read directly from PostGIS?

Yes, through the Postgres scanner, which needs no export step and is ideal for a first comparison because both sides genuinely read the same rows. What it is not ideal for is repeated production use, since every query re-reads the source and puts load on a server whose job is something else.

Is the comparison different for writes?

It is not a comparison at all. DuckDB permits one writer and offers no roles, no row-level security and no point-in-time recovery. Those are absences rather than slow implementations, and no analytical throughput substitutes for them, which is why the transactional half of a workload stays where it is.

How much does the snapshot boundary cost in freshness?

Whatever you set it to, and that is the point worth making explicitly: the refresh interval is a decision rather than a limitation. For most analytical workloads a daily or hourly snapshot is invisible to the question being asked. For anything that must reflect the latest write, the boundary is the wrong architecture and PostGIS should own the query.

Up: DuckDB Spatial vs PostGIS, GeoPandas and Sedona