⚙️ How-To DuckDB & analytic SQL Performance & scale DuckDB for Spatial Data · step 15 of 20 Updated

How to Benchmark DuckDB Against GeoPandas Honestly

Problem statement

Benchmarks between GIS engines are usually wrong in the same few ways, and all of them favour whichever engine the author preferred:

  • The file read is inside one timer and outside the other. DuckDB reading Parquet in place looks instant beside GeoPandas' explicit read_file, when in reality the read is happening — just later.
  • The page cache is warm for the second run. Whichever engine goes second wins.
  • Only time is measured. The 15.5× memory difference measured on a real join is invisible in a stopwatch.
  • The results are never compared. A CRS mismatch in DuckDB returns zero rows without an error, so an "8× speed-up" can be an empty result.

A benchmark that fixes those four produces numbers you can act on — and the honest ones do not all point the same way. Measured on the same machine and the same data:

join                                  DuckDB              GeoPandas
7,342 pts × 4,596 polys            0.168 s               0.104 s
2,241,395 pts × 51 polys      4.27 s / 227 MB     15.48 s / 988 MB
13,464,017 pts × 4,596 polys 61.77 s / 305 MB   117.53 s / 4,739 MB

Quick answer

Time the whole task, measure memory, run each engine in its own process, and assert the results match:

import subprocess
import sys


def benchmark(engine, script, *args):
    """Each engine in a clean process: no shared caches, real peak memory."""
    result = subprocess.run(
        [sys.executable, script, engine, *map(str, args)],
        capture_output=True, text=True, check=True)
    return json.loads(result.stdout.strip().splitlines()[-1])


duck = benchmark("duck", "bench.py", points, polygons)
gpd_ = benchmark("gpd", "bench.py", points, polygons)

assert duck["result"] == gpd_["result"], (
    f"results differ: {duck['result']} vs {gpd_['result']} — "
    f"check the CRS on both sides before comparing speed")

print(f"duckdb    {duck['secs']:6.2f}s  {duck['peak_mb']:7,.0f} MB")
print(f"geopandas {gpd_['secs']:6.2f}s  {gpd_['peak_mb']:7,.0f} MB")
Triage table of four benchmarking mistakes and their fixes.
Compare the outputs before comparing the times.

Step-by-step solution

1. Define the task, not the operation

Benchmark "count points per polygon from these files", not "sjoin". The task includes reading the data, because that is what a real workflow does and because the two engines do it at different points.

DuckDB reads inside the query; GeoPandas reads before it. Timing only the join hides half of GeoPandas' work and all of DuckDB's.

2. Run each engine in its own process

Peak resident memory is process-wide. Running both engines in one process reports the maximum of the two, attributed to whichever ran second.

A subprocess per engine gives a clean ru_maxrss, prevents warm caches inside a library from carrying over, and removes any chance of one import affecting the other.

3. Report memory as well as time

Time differences are often modest; memory differences are frequently decisive. The measured global join was 1.9× faster in DuckDB and used 15.5× less memory — 305 MB against 4,739 MB.

That second number is what decides whether the job runs on a laptop at all. A benchmark that omits it is measuring the less important axis.

4. Control the cache deliberately

Two policies, both defensible, and the important thing is to state which one you used:

  • Warm — run once to warm the page cache, then measure. Reflects a repeated interactive workload.
  • Cold — drop caches between runs (needs root on Linux) or use a file larger than RAM. Reflects a first run.

Also turn off DuckDB's own cache when measuring remote reads: set enable_external_file_cache = false, or the second query reads nothing and appears miraculous.

5. Assert that the results agree

This is the check that catches the embarrassing benchmark. A CRS mismatch in DuckDB returns zero rows with no error, and zero rows is very fast.

Compare the actual outputs: the matched-pair count, the group count, the top few groups. Measured on the global join, both engines returned 12,942,217 pairs across 251 groups with the same top three — which is what makes the timing comparison meaningful.

6. Take the best of several runs, and say how many

Wall-clock timings on a shared machine are noisy. Take the minimum of three to five runs — the minimum is the least contaminated by other load — and report the run count alongside.

Report the machine too: core count, RAM, and whether the data was on SSD or a network mount. Numbers without a machine are not reproducible.

Grid of four things a benchmark should report and why each matters.
State whether the cache was warm. Both choices are valid; silence is not.

Code examples

Example 1 — the benchmark script, one engine per invocation 

#!/usr/bin/env python
"""bench.py <engine> <points.parquet> <polygons.shp>"""
import json
import resource
import sys
import time


def run_duckdb(points, polygons):
    import duckdb
    con = duckdb.connect()
    con.execute("install spatial; load spatial;")
    con.execute("set enable_progress_bar = false")
    rows = con.execute(f"""
        select a.name, count(*) as n
        from read_parquet('{points}') p
        join st_read('{polygons}') a
          on st_intersects(st_point(p.lon, p.lat), a.geom)
        group by 1 order by n desc
    """).fetchall()
    return {"groups": len(rows), "total": sum(r[1] for r in rows),
            "top": [list(r) for r in rows[:3]]}


def run_geopandas(points, polygons):
    import duckdb
    import geopandas as gpd
    frame = duckdb.connect().execute(
        f"select lon, lat from read_parquet('{points}')").df()
    areas = gpd.read_file(polygons)
    pts = gpd.GeoDataFrame(frame,
                           geometry=gpd.points_from_xy(frame.lon, frame.lat),
                           crs=areas.crs)
    joined = gpd.sjoin(pts, areas, predicate="intersects")
    counts = joined.groupby("name").size().sort_values(ascending=False)
    return {"groups": len(counts), "total": int(counts.sum()),
            "top": [[k, int(v)] for k, v in list(counts.items())[:3]]}


if __name__ == "__main__":
    engine, points, polygons = sys.argv[1], sys.argv[2], sys.argv[3]
    started = time.perf_counter()
    result = run_duckdb(points, polygons) if engine == "duck" \
        else run_geopandas(points, polygons)
    elapsed = time.perf_counter() - started
    peak_mb = resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1024
    print(json.dumps({"engine": engine, "secs": round(elapsed, 2),
                      "peak_mb": round(peak_mb, 1), "result": result}))

Note that the GeoPandas path uses DuckDB to read the Parquet file. That is deliberate: it isolates the join difference from the Parquet-reading difference, and it is what a real GeoPandas user would do with a large Parquet input.

Example 2 — the runner, with repeats and assertions 

import json
import statistics
import subprocess
import sys


def bench(engine, script, points, polygons, repeats=3):
    runs = []
    for _ in range(repeats):
        out = subprocess.run([sys.executable, script, engine, points, polygons],
                             capture_output=True, text=True, check=True)
        runs.append(json.loads(out.stdout.strip().splitlines()[-1]))
    return {
        "engine": engine,
        "best_secs": min(r["secs"] for r in runs),
        "median_secs": statistics.median(r["secs"] for r in runs),
        "peak_mb": max(r["peak_mb"] for r in runs),
        "result": runs[0]["result"],
        "runs": repeats,
    }


def compare(script, points, polygons, repeats=3):
    duck = bench("duck", script, points, polygons, repeats)
    gpd_ = bench("gpd", script, points, polygons, repeats)

    assert duck["result"]["total"] == gpd_["result"]["total"], (
        f"engines disagree: {duck['result']['total']:,} vs "
        f"{gpd_['result']['total']:,} matched pairs. Check the CRS on both sides.")

    print(f"{'engine':10} {'best':>8} {'median':>8} {'peak MB':>9}  ({repeats} runs)")
    for row in (duck, gpd_):
        print(f"{row['engine']:10} {row['best_secs']:8.2f} "
              f"{row['median_secs']:8.2f} {row['peak_mb']:9,.0f}")
    print(f"\nspeed  {gpd_['best_secs'] / duck['best_secs']:.1f}× "
          f"memory {gpd_['peak_mb'] / duck['peak_mb']:.1f}×  in DuckDB's favour")

Example 3 — a size sweep, because the answer changes with scale 

def scale_sweep(script, points_parquet, polygons, sizes=(10_000, 100_000,
                                                         1_000_000, 10_000_000)):
    """The crossover is the interesting result, not any single number."""
    import duckdb
    import tempfile
    import os

    con = duckdb.connect()
    print(f"{'rows':>12} {'duckdb':>9} {'geopandas':>10} {'winner':>10}")
    for n in sizes:
        with tempfile.NamedTemporaryFile(suffix=".parquet", delete=False) as handle:
            subset = handle.name
        con.execute(f"""copy (select * from read_parquet('{points_parquet}')
                              limit {n}) to '{subset}' (format parquet)""")
        duck = bench("duck", script, subset, polygons, repeats=3)
        gpd_ = bench("gpd", script, subset, polygons, repeats=3)
        winner = "duckdb" if duck["best_secs"] < gpd_["best_secs"] else "geopandas"
        print(f"{n:12,} {duck['best_secs']:9.2f} {gpd_['best_secs']:10.2f} {winner:>10}")
        os.unlink(subset)

Measured, the crossover sits between a few thousand and a few hundred thousand rows: at 7,342 points GeoPandas won (0.104 s against 0.168 s), and at 2.2 million DuckDB won decisively (4.27 s against 15.48 s).

Explanation

Why the file read belongs inside the timer

DuckDB's whole design point is that it reads lazily inside the query. Excluding the read from its timer measures nothing, and excluding GeoPandas' explicit read measures a different task.

Timing the complete task — files in, answer out — is the only comparison that corresponds to what a user experiences. It also means the benchmark automatically credits DuckDB for column pruning, which is a real advantage that a join-only timer hides.

Why memory is the more decisive axis

The measured global join was 1.9× faster in DuckDB and used a fifteenth of the memory. On a machine with 8 GB of RAM the GeoPandas version simply does not run, which is a qualitative difference that no speed ratio expresses.

Memory also determines what else can run at the same time. A pipeline stage that peaks at 4.7 GB cannot be run four-way parallel on a 16 GB machine; one that peaks at 305 MB can.

Why the equality assertion matters more than any timing

A DuckDB spatial join across mismatched coordinate systems returns zero rows and no error. Zero rows is extremely fast.

Every benchmark should therefore compare outputs before comparing times. In the measured runs both engines produced 12,942,217 matched pairs across 251 groups with identical leaders — and that agreement is what licenses the timing comparison. Without it, the fast number could be measuring nothing at all.

Why the crossover, not the ratio, is the useful output

"DuckDB is 2× faster" is not actionable, because it is true at one size and false at another. The measured runs show GeoPandas winning at 7,342 points and losing by 3.6× at 2.2 million.

The number worth extracting from a benchmark is where your workload sits relative to that crossover. Below it, use whichever library is more convenient; above it, the choice starts to determine whether the job completes.

Bar chart of the speed ratio between the two engines at three data sizes.
Below the crossover, use whichever library is more convenient.

Edge cases or notes

  • Report the machine. Core count, RAM and storage type change every number here.
  • Take the minimum of several runs, not the mean; it is least polluted by other load.
  • ru_maxrss is kilobytes on Linux and bytes on macOS. Normalise it.
  • Disable DuckDB's external file cache when measuring remote reads.
  • A subprocess per engine is the only reliable way to attribute peak memory.
  • Warm the cache or state that you did not. Both are valid; silence is not.
  • Check thread counts. DuckDB uses all cores by default; GeoPandas is largely single-threaded.
  • Compare the outputs first. A fast wrong answer is the most common benchmark result.

FAQ

How should I time a GIS engine comparison?

Time the whole task — files in, answer out — in a separate process per engine, take the minimum of several runs, and record the machine.

Should I measure memory too?

Yes, and it is often the more important number. The measured global join was 1.9× faster in DuckDB and used 15.5× less memory: 305 MB against 4,739 MB.

Why do I need a separate process per engine?

Peak resident memory is process-wide, so running both in one process attributes the maximum to whichever ran second.

What is the most common benchmark mistake?

Not comparing the results. A DuckDB join across mismatched CRSs returns zero rows with no error, and zero rows is very fast.

Is DuckDB always faster than GeoPandas?

No. On 7,342 points against 4,596 polygons GeoPandas was faster — 0.104 s against 0.168 s. The crossover is somewhere in the low hundreds of thousands of rows.

Should the cache be warm or cold?

Either, as long as you say which. Warm reflects repeated interactive use; cold reflects a first run. Silence about it makes the numbers uninterpretable.