Benchmarking Spatial Aggregations Across Both Engines

A benchmark circulated internally shows DuckDB answering the incident aggregation in 1.8 seconds against PostGIS’s 34, and by the end of the week somebody has proposed replacing the operational store. Both numbers are real. Neither is a comparison, because they were measured on a warm cache against a cold one, over different column sets, with the extract cost omitted entirely.

Root Cause and Operational Impact

Spatial benchmarks are unusually easy to run and unusually hard to run comparably. The two engines differ by roughly an order of magnitude in each direction depending on the query shape, and at least five conditions unrelated to the engines move results by more than that. A benchmark that does not pin all of them is not measuring the engines; it is measuring whichever condition happened to differ.

The operational cost of getting this wrong is not a slow query. It is a platform decision made on a number, and platform decisions in this domain are expensive to reverse — a store chosen for analytical speed that cannot accept concurrent writes is discovered during the first surge, which is the worst possible time to find out.

What each benchmark run must state to be comparable Five conditions that change a spatial aggregation benchmark by more than the engines differ from each other. Whether the operating system page cache is warm changes PostGIS by up to four times, so a cold and a warm run are not comparable. The Parquet row-group size changes DuckDB by up to three times depending on whether the query is selective or scanning. Whether a GIST index exists decides the point-in-polygon result entirely rather than influencing it. The number of concurrent sessions changes PostGIS substantially and DuckDB barely, because one is serving other work and the other is not. And the geometry vertex count changes both, since deserialisation dominates for complex polygons. A benchmark that does not state all five is not reproducible. five conditions that move the result more than the engines differ page cache warm or cold PostGIS varies up to 4× — a cold run and a warm run are different experiments Parquet row-group size DuckDB varies up to 3× — larger groups favour scans, smaller favour predicates GIST index present decides the point-in-polygon result outright rather than influencing it concurrent sessions PostGIS is also serving the operating picture; the analytical copy is serving nobody geometry vertex count deserialisation dominates for complex perimeters, on both engines

The GIST index row is the one that most often invalidates a comparison outright. A point-in-polygon benchmark run against a PostGIS table whose index was never created is not a comparison between engines; it is a comparison between an index and a scan, and it will report the two engines as roughly equal when in reality one is fifty times faster for that query.

Tiered Resolution Strategy

  1. Pin every condition before comparing anything (definitive). Cache state, row-group size, index presence, concurrency and the geometry complexity of the fixture. Record all five in the result, because a benchmark whose conditions are not stated cannot be reproduced or argued with.
  2. Benchmark query shapes, not queries. Run at least one indexed lookup, one selective predicate, one wide aggregation and one multi-way join. A single query produces a number that generalises to nothing.
  3. Vary the column count deliberately. The columnar advantage is a function of how many columns the query touches, so a benchmark at a fixed column count measures one point on a curve and reports it as a property.
  4. Include the extract cost (safe default). The DuckDB number is only meaningful alongside the snapshot and write cost that produced the file it read.
  5. Report a range, not a figure. Publish best and worst case across the conditions above, so a reader can see which decisions the result is sensitive to.
Aggregation time against the number of columns the query touches A group-by aggregation over 4.2 million incident records, run against a table of forty columns, with the query touching between three and forty of them. DuckDB starts at about 1.8 seconds for three columns and climbs steadily to about 19 seconds for all forty, because a columnar engine reads only the columns a query names. PostGIS is nearly flat at about 34 seconds throughout, because a row store reads whole rows off disk whatever the query asked for. The two cross at around twenty-six columns. The practical consequence is that the columnar advantage is a property of the query rather than of the engine, and an extract that copies every column hands most of it back. 4.2 M rows, 40-column table — the advantage is a property of the query seconds 010203040 PostGIS — flat at ~34 s, whole rows either way DuckDB — reads only the columns named they cross near 26 columns 310203040 columns the query touches An extract that copies all forty columns hands back most of the advantage it was built to buy.

Tier three deserves its own emphasis because it changes the recommendation rather than the number. At three columns DuckDB is roughly nineteen times faster; at forty it is under twice; and the two cross near twenty-six columns. Since the extract’s column set is a design choice, the engine’s advantage is partly something the team decides rather than something it measures — an extract that copies the whole table has given most of it away before any query runs.

The Cost the Benchmark Omits

The cost the benchmark usually omits A query answered from DuckDB appears to take 1.8 seconds, but the extract it reads had to be produced. Taking the snapshot from PostGIS costs about 41 seconds, writing the Parquet file about 12 seconds, and the extract is then reused by however many queries run before the next one. Amortised over a single query the true cost is about 55 seconds, worse than PostGIS answering directly in 34. Amortised over twenty queries in the same extract window it is about 4.5 seconds each, and over a hundred it approaches the raw query time. The break-even is near two queries per extract, which is why the decision depends on how many questions an analyst asks of one snapshot rather than on how fast one question is answered. the extract has to be produced — amortise it or the comparison is fiction 1 query 55 s each — worse than PostGIS 20 queries 4.5 s each 100 queries 2.3 s each — approaching the raw query time snapshot from PostGIS · 41 s write Parquet · 12 s the query itself · 1.8 s break-even is near two queries per extract — the question is how many things an analyst asks of one snapshot.

The extract is not free and it is almost never counted. Taking a consistent snapshot from PostGIS costs about 41 seconds on this dataset and writing the Parquet file about 12 more, so the first query answered from a fresh extract really cost 55 seconds — worse than asking PostGIS directly.

That inverts the usual framing. The question is not “which engine answers this query faster” but “how many questions will an analyst ask of one snapshot?” Below about two, the extract is not worth building. Above twenty it is overwhelming. Since after-action review and interactive analysis both involve asking dozens of questions of one frozen state, they sit far on the profitable side — and a one-off operational lookup sits firmly on the other, which is a useful rule to hand to whoever is choosing where to send a query.

Production Python Implementation

python 
from __future__ import annotations

import logging
import subprocess
import time
from dataclasses import dataclass, asdict, field

import duckdb
import psycopg

logger = logging.getLogger("incidentgis.benchmark")


@dataclass
class RunConditions:
    """Everything that must be stated for a result to be comparable."""
    cache_state: str            # "cold" | "warm"
    row_group_size: int
    gist_index_present: bool
    concurrent_sessions: int
    mean_vertices: float
    columns_touched: int


@dataclass
class Result:
    label: str
    engine: str
    seconds: float
    conditions: RunConditions
    samples: list[float] = field(default_factory=list)


def drop_caches() -> None:
    """Cold-cache runs need the OS page cache dropped, not just a restart.

    Requires privilege; a benchmark that cannot do this must report every run
    as warm rather than pretending the first one was cold.
    """
    subprocess.run(["sync"], check=True)
    with open("/proc/sys/vm/drop_caches", "w") as fh:
        fh.write("3")


def time_query(fn, *, repeats: int = 5) -> tuple[float, list[float]]:
    """Median of repeated runs — the mean is dominated by the first run."""
    samples = []
    for _ in range(repeats):
        start = time.perf_counter()
        fn()
        samples.append(time.perf_counter() - start)
    samples.sort()
    return samples[len(samples) // 2], samples


def benchmark_pair(pg_dsn: str, parquet_path: str, sql_pg: str, sql_duck: str,
                   *, label: str, conditions: RunConditions) -> list[Result]:
    """Run one query shape on both engines under identical stated conditions."""
    with psycopg.connect(pg_dsn) as conn:
        conn.read_only = True
        # Confirm the index actually exists rather than assuming it does — a
        # missing GIST turns an engine comparison into an index comparison.
        has_index = conn.execute("""
            SELECT count(*) > 0 FROM pg_indexes
            WHERE tablename = 'incidents' AND indexdef ILIKE '%USING gist%'
        """).fetchone()[0]
        if has_index != conditions.gist_index_present:
            raise RuntimeError(
                f"stated gist_index_present={conditions.gist_index_present} "
                f"but the database says {has_index}"
            )
        pg_seconds, pg_samples = time_query(
            lambda: conn.execute(sql_pg).fetchall()
        )

    con = duckdb.connect()
    con.execute("INSTALL spatial; LOAD spatial;")
    con.execute(f"CREATE VIEW incidents AS SELECT * FROM read_parquet('{parquet_path}')")
    duck_seconds, duck_samples = time_query(lambda: con.execute(sql_duck).fetchall())
    con.close()

    results = [
        Result(label, "postgis", pg_seconds, conditions, pg_samples),
        Result(label, "duckdb", duck_seconds, conditions, duck_samples),
    ]
    for r in results:
        logger.info("benchmark_result", extra=asdict(r))
    return results

Validation Checklist

  • Cache state is stated for every run, and cold runs actually drop the OS page cache.
  • The presence of the GIST index is asserted against the database, not assumed.
  • At least four query shapes are measured: indexed lookup, selective predicate, wide aggregation, multi-way join.
  • Column count is varied, and the result is reported as a curve rather than a point.
  • The extract’s snapshot and write cost are included and amortised over a stated query count.
  • Results are medians of repeated runs, not means, so the first cold run does not dominate.
  • Concurrent session count is stated, since one engine is also serving the operating picture.
  • The fixture’s mean vertex count is recorded, because geometry complexity moves both engines.

Edge Cases and Gotchas

  • A “cold” run that is only a restarted process. Restarting PostgreSQL clears its shared buffers and leaves the OS page cache warm, which is most of the benefit. Without dropping the page cache the run is warm and should be reported as such.
  • DuckDB reading from the OS cache after the first query. The second query against the same Parquet file is reading memory. This is realistic for an analyst asking many questions and unrealistic for a single-shot comparison; state which case is being modelled.
  • Row-group size tuned for the benchmark’s query. Large groups favour scans and small ones favour selective predicates, so it is possible to tune the extract to whichever result you wanted. Fix it once from the real workload and leave it.
  • A fixture of simple geometries. Points and boxes deserialise almost instantly and hide the vertex-count effect entirely. Use a fixture with the perimeter complexity the real archive carries, as the benchmarking guidance for the field libraries also insists.
  • Comparing against a PostGIS instance under load. If the operational instance is serving the picture during the benchmark, the result measures contention rather than capability — which is a legitimate thing to measure, and must be labelled as such.

Frequently Asked Questions

Why do PostGIS and DuckDB benchmarks disagree so wildly between teams? Because at least five conditions unrelated to the engines move results by more than the engines differ. Whether the operating system page cache is warm changes PostGIS by up to four times. Parquet row-group size changes DuckDB by up to three. Whether a GIST index exists decides an indexed lookup outright rather than influencing it. Concurrency matters for the engine that is also serving the operating picture and not for the one serving nobody. And geometry vertex count moves both, since deserialisation dominates for complex perimeters. A benchmark that does not state all five is measuring whichever condition happened to differ.

Does the columnar advantage depend on the query? Substantially. Measured on a forty-column table of 4.2 million incident records, a group-by aggregation touching three columns runs about nineteen times faster in DuckDB, one touching twenty about three times faster, and one touching all forty under twice — the two cross near twenty-six columns, because a columnar engine reads only the columns named while a row store reads whole rows regardless. Since the extract’s column set is a design decision, part of the advantage is chosen rather than measured, and an extract that copies the whole table gives most of it away before any query runs.

Should the extract’s build cost count against DuckDB? Yes, and omitting it is the most common way these comparisons mislead. Taking a consistent snapshot from PostGIS costs about 41 seconds on this dataset and writing the Parquet file about 12 more, so a single query answered from a fresh extract really costs about 55 seconds against PostGIS’s 34. Amortised over twenty queries it falls to about 4.5 seconds each and over a hundred it approaches the raw query time. Break-even is near two queries per extract, which reframes the decision as how many questions an analyst asks of one snapshot rather than how fast one question is answered.

Up: PostGIS vs DuckDB for Incident Analytics

Other guides in PostGIS vs DuckDB for Incident Analytics

Related
Handling CRS Loss in DuckDB Spatial Extracts
→
Related
Migrating Incident Analytics Queries to DuckDB
→