DuckDB vs PostGIS vs GeoPandas Performance

Choosing between DuckDB, PostGIS, and GeoPandas for a spatial workload is not a question of which engine is fastest in the abstract — it is a question of where the crossover falls for your dataset size, join selectivity, cardinality, and concurrency, and this guide, part of migrating to DuckDB Spatial, quantifies those crossovers so a migration decision rests on measured behaviour rather than folklore. GeoPandas wins on tiny, interactive frames and loses catastrophically at the memory wall; PostGIS wins on concurrent transactional access and single-row lookups; DuckDB wins on single-node analytical scans that would spill a dataframe and saturate a row-store. The disciplined way to find your own crossover is a fair benchmark — warm cache, identical CRS, identical predicate, plans confirmed with EXPLAIN ANALYZE — and the narrowest, most reproducible version of that experiment lives in the DuckDB vs PostGIS spatial join benchmarks walkthrough beneath this page.

Runtime Configuration & Memory Guardrails #

A benchmark is only meaningful if every engine is configured to its strengths and measured under the same rules. Misconfiguration is the single largest source of dishonest spatial benchmarks — a DuckDB run pinned to one thread, or a PostGIS run with work_mem starved and no GiST index, tells you nothing. Set the DuckDB side up explicitly and record the exact settings alongside the numbers.

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

-- Match physical cores so the vectorized engine can actually parallelize the scan.
-- Pinning to 1 thread to "be fair" to a row-store instead measures a crippled DuckDB.
SET threads = 8;

-- Ceiling for the benchmarked query's working set. Set it high enough that the
-- measured query does NOT spill — a spilling run measures your disk, not the engine.
SET memory_limit = '12GB';

-- Drop insertion-order guarantees so scans and R-tree builds parallelize freely.
-- Re-impose ordering only on a final result you actually compare for correctness.
SET preserve_insertion_order = false;

-- Quiet the progress bar so its render cost does not contaminate EXPLAIN ANALYZE timings.
SET enable_progress_bar = false;

-- Spill target used only for the deliberately-oversized runs that probe the memory wall.
SET temp_directory = '/var/lib/duckdb/spill';

Before recording a single number, confirm the benchmark is measuring the engines and not an accidental asymmetry:

• Both engines read the same CRS — reproject once up front so neither pays a hidden ST_Transform the other avoids.
• Both engines run the same predicate with the same tolerance (ST_Intersects, or ST_DWithin with an identical distance in the same units).
• PostGIS has a GiST index and DuckDB has an R-tree index on the matching column, or you are honestly benchmarking two full scans.
• Every measured query is run warm: execute it once to fill the OS page cache and each engine’s buffers, then time the subsequent runs.
• You capture EXPLAIN ANALYZE for each engine and confirm the intended index/join actually engaged.

Trade-off: raising memory_limit keeps the measured query in memory but hides the memory-wall behaviour that is often the whole point of the comparison. Run two configurations deliberately — one generously sized to measure peak throughput, and one constrained to probe where each engine spills or OOMs. The durability and spill mechanics behind the constrained run are analyzed in in-memory vs disk storage; GeoPandas has no equivalent knob, which is exactly why it falls off a cliff rather than degrading gracefully.

Benchmark Methodology & The Crossover Curve #

The mental picture that organizes every spatial-engine comparison is a throughput-versus-size curve. Plot the three engines against a growing dataset and the lines cross: GeoPandas leads while everything fits comfortably in RAM, then collapses; PostGIS holds a steady, moderate line; DuckDB starts with a little fixed overhead and then dominates as the columnar scan amortizes across more data — until, at very large sizes, its single-node line also bends under I/O.

The curve is schematic — your exact crossover shifts with hardware and query shape — but its shape is robust: GeoPandas leads then dies at the memory wall, PostGIS is steady, DuckDB overtakes once the scan amortizes and holds the lead into the tens of millions of rows.

Four variables move the crossover, and a fair methodology varies them one at a time:

• Dataset size. The dominant axis. Below roughly $10^5$ rows, per-query overhead (DuckDB extension load, planner cost) and GeoPandas’s in-memory locality can leave the dataframe ahead. Above $10^6$ rows the vectorized scan is almost always fastest on a single node, and GeoPandas is approaching its ceiling. The exact number depends on geometry complexity and RAM.
• Join selectivity. A highly selective spatial join — few candidate pairs survive the bounding-box stage — favours an indexed engine (PostGIS GiST or DuckDB R-tree) because the exact-topology stage runs on almost nothing. A low-selectivity join, where most pairs survive, favours DuckDB’s vectorized exact stage, because the work is dominated by the topology kernels rather than the lookup. The spatial joins and proximity filters reference details how selectivity is controlled by predicate placement.
• Cardinality (group count). Aggregations that collapse millions of rows into few groups play directly to DuckDB’s hash aggregate, as covered in vectorized aggregations. High group cardinality — nearly as many groups as rows — narrows DuckDB’s lead because the hash table grows and cache locality falls.
• Concurrency. This is where the ranking inverts. PostGIS is built for many concurrent connections each touching a few rows; DuckDB is a single-writer analytical engine optimized for one heavy query at a time. If your workload is a hundred simultaneous single-feature lookups, PostGIS wins decisively and no amount of columnar throughput changes that.

The honest methodology is therefore not “run the query and time it” but “run the query warm, on identical inputs, with the intended index engaged, and report the plan alongside the wall-clock.” Everything downstream depends on those four conditions holding.

Measurement protocol #

A repeatable protocol keeps the numbers comparable across engines and across time. Fix the hardware and report it, because a crossover measured on a laptop with two cores and 8 GB of RAM lands at a very different dataset size than the same experiment on a 32-core server with fast NVMe. Pin the thread count explicitly on both engines so neither is accidentally handicapped, disable turbo-frequency scaling if you can, and run on an otherwise-idle machine so co-tenant load does not leak into the timings. For each measured query, take the median of at least five warm runs rather than a single sample — spatial joins have enough per-run variance from cache state and memory allocation that one measurement is noise, not signal.

Report a small, fixed set of fields for every run so a reader can reconstruct the comparison: engine and version, dataset size and geometry complexity, CRS, the exact predicate and tolerance, whether an index was present and used, thread count, memory ceiling, warm-run median, peak memory, and whether the run spilled. A benchmark that omits any of these is not reproducible, and a crossover claim that cannot be reproduced is an opinion. The point of the discipline is not ceremony — it is that the four crossover variables interact, and only a fully-specified run lets you attribute a shift to the variable you actually changed rather than to an uncontrolled one.

Execution Plan Validation #

Wall-clock time without a plan is a rumour. For every benchmarked query, capture the plan on both engines and confirm the intended access path engaged — a “DuckDB is 40× faster” result usually collapses into “the PostGIS run had no usable GiST index” or “the DuckDB run never spilled while PostGIS did” once you read the plans.

-- DuckDB: measured plan with per-operator timing and peak memory.
EXPLAIN ANALYZE
SELECT p.id, b.zone_id
FROM points p
JOIN zones b
  ON b.geom && ST_Point(p.lon, p.lat)             -- want: R-tree-servable bbox stage
WHERE ST_Intersects(b.geom, ST_Point(p.lon, p.lat));

What to assert in the DuckDB output before trusting a number:

• Join operator. A HASH_JOIN or spatial/range join driven by && is healthy. A NESTED_LOOP_JOIN carrying the full ST_Intersects predicate means the two-stage filter never engaged and you are timing an $O(N \times M)$ worst case, not the engine’s real capability.
• No unintended spill. A non-empty spill indicator on the peak-throughput run means the result reflects your NVMe, not DuckDB — raise memory_limit and re-run, or move it to the deliberately-constrained configuration.
• Actual vs estimated rows. Drift beyond ~30% at the join node signals skewed geometry density or stale statistics; the plan you validated is not the plan that will run on the next data version.

The mirror-image check on PostGIS is that the plan shows an Index Scan using ... gist feeding the join rather than a Seq Scan, and that work_mem was large enough that the sort or hash did not spill to pgsql_tmp. Only when both plans show their intended access path is the comparison fair. The detailed, side-by-side plan reading for the canonical join case is worked through in the DuckDB vs PostGIS spatial join benchmarks guide.

Performance Trade-offs #

The following ranges are representative of a single modern multi-core node with an SSD; treat them as a starting hypothesis to confirm against your own data, not as guarantees. “Rel. throughput” is normalized to the fastest engine for that workload at 1×.

Two conclusions generalize. First, DuckDB’s advantage widens with size until it hits its own I/O ceiling, and the win is largest on aggregations and low-selectivity joins where the work is genuinely columnar — the vectorized aggregations and spatial joins and proximity filters references show why. Second, the cases DuckDB loses are the cases it was never designed for: high-concurrency transactional access and durable single-feature edits, which remain PostGIS’s home ground. A migration that moves the analytical read path to DuckDB while leaving the transactional write path on PostGIS is a common, defensible endpoint rather than a failure to commit.

The table also hides a subtlety worth stating: the ratios move with geometry complexity, not just row count. Ten thousand thousand-vertex polygons behave nothing like ten thousand quadrilaterals, because the exact-topology stage scales with vertex count while the bounding-box stage does not. DuckDB’s lead is largest when the exact stage dominates — dense, high-vertex geometry where SIMD kernels over columnar coordinate buffers pull furthest ahead of per-tuple evaluation — and narrows when the geometry is simple enough that the join is bound by index lookup rather than topology. When you report a crossover, report the geometry complexity alongside the row count, or the number is only half a result.

Edge Cases & Anti-Patterns #

Cold-cache asymmetry (fake speedups). Timing the first run of each query compares whichever engine’s files were already in the OS page cache. Always warm both engines before timing, and prefer the median of several warm runs over a single measurement.

-- Warm-up protocol: run once to fill caches, discard, then time subsequent runs.
SELECT count(*) FROM points p JOIN zones b ON b.geom && ST_Point(p.lon, p.lat);  -- warm
-- ... now start timing ...

Different CRS on each side (invalid comparison). If DuckDB scans degree-space geometry while PostGIS scans a projected copy, one engine pays a reprojection the other skips and the predicate means different things. Reproject both to one CRS before the benchmark; the units rules are in CRS mapping and transformations.

Missing index on one side (straw-man). The most common dishonest result is DuckDB with an R-tree against PostGIS without a GiST index, or the reverse. Confirm both indexes exist and both plans use them before publishing a ratio.

-- DuckDB: prove the R-tree is registered before you trust the join timing.
SELECT index_name, table_name FROM duckdb_indexes() WHERE table_name = 'zones';

Latency-versus-throughput confusion. A single-row lookup measures latency; a full-table analytical scan measures throughput. Reporting DuckDB’s scan throughput as if it settled the single-feature-lookup question — or PostGIS’s low lookup latency as if it settled the aggregation question — compares different axes. State which one the benchmark measures.

Ignoring the memory wall by never scaling up. Benchmarking only at sizes where everything fits in RAM hides GeoPandas’s defining failure mode. Include at least one run large enough to push GeoPandas toward OOM, because that boundary is frequently the actual reason a team migrates.

Query Regression Analysis #

A one-off benchmark rots the moment the data or a dependency changes; wire the comparison into a harness that captures timings and plans so a regression surfaces in CI rather than in production. Time each engine on identical warm inputs, record the median, and assert both that DuckDB stayed within its expected envelope and that its plan still shows the two-stage filter. This harness extends naturally into the orchestration described in Python and DuckDB integration workflows.

import duckdb
import statistics
import time

con = duckdb.connect()
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("SET threads = 8; SET memory_limit = '12GB'; SET enable_progress_bar = false;")

QUERY = """
SELECT count(*)
FROM points p
JOIN zones b ON b.geom && ST_Point(p.lon, p.lat)
WHERE ST_Intersects(b.geom, ST_Point(p.lon, p.lat))
"""

def timed_median(sql, warmups=1, runs=5):
    for _ in range(warmups):          # fill OS page cache and DuckDB buffers first
        con.execute(sql).fetchall()
    samples = []
    for _ in range(runs):
        t0 = time.perf_counter()
        con.execute(sql).fetchall()
        samples.append(time.perf_counter() - t0)
    return statistics.median(samples)

# Guard 1: timing envelope. Fail CI if the warm median regresses past a budget.
duck_median = timed_median(QUERY)
assert duck_median < 0.75, f"DuckDB spatial join regressed: {duck_median:.3f}s"

# Guard 2: plan shape. A nested-loop join means the two-stage filter silently broke.
plan = con.execute(f"EXPLAIN ANALYZE {QUERY}").fetchall()
plan_text = "\n".join(row[1] for row in plan)
assert "NESTED_LOOP_JOIN" not in plan_text, "Two-stage filter defeated: nested loop appeared"

Track three signals across builds: the warm median (a latency budget), the join operator (a shift to nested-loop is a hard regression), and peak memory or spill (early warning that the query crossed into the constrained regime). Run the same harness against a PostGIS connection with psycopg and the EXPLAIN (ANALYZE, BUFFERS) output to keep the cross-engine ratio honest over time. The narrowly-scoped, fully reproducible version of this harness for the spatial-join case — with representative numbers and the R-tree-versus-GiST plan comparison — is the DuckDB vs PostGIS spatial join benchmarks walkthrough.

Related #

See also

• DuckDB vs PostGIS spatial join benchmarks — the reproducible join experiment with representative numbers and side-by-side plans.
• Vectorized aggregations — why DuckDB’s lead is widest on columnar aggregates.
• Spatial joins and proximity filters — how selectivity and predicate placement move the crossover.
• In-memory vs disk storage — the spill behaviour that decides where each engine hits its wall.

Up: Migrating to DuckDB Spatial from PostGIS and GeoPandas