Designing a Reproducible Spatial Benchmark Harness

This guide builds a benchmark harness whose results can be reproduced, compared across months and defended in a review — which requires pinning far more than the engine version.

Context and prerequisites

Most spatial benchmarks are not wrong so much as unrepeatable: the layout was not recorded, the queries differed subtly between engines, the caches were warm for one run and cold for another. This recipe uses Python to orchestrate DuckDB, Trino and Spark with Sedona; the design principles are in engine benchmarking and selection, and the layout that must be pinned in spatial partitioning schemes.

What has to be pinned

The third artefact is where spatial benchmarks most often lose their validity. Function names and semantics differ between engines — one includes touching geometries in an intersection, another may not; distance functions differ in their units — so two queries that look equivalent can answer different questions. Proving the result sets match is the only way to know, and it should be a gate rather than a spot check.

Complete working solution

import hashlib, json, time
from dataclasses import dataclass, asdict

@dataclass
class RunResult:
    engine: str
    query_id: str
    cache: str            # "cold" or "warm"
    wall_ms: float
    rows: int
    result_hash: str
    bytes_scanned: int | None
    files_scanned: int | None

def result_fingerprint(rows) -> str:
    """Order-independent hash so engines returning different orders still match."""
    h = hashlib.sha256()
    for line in sorted("|".join(map(str, r)) for r in rows):
        h.update(line.encode())
    return h.hexdigest()[:16]

def run_query(engine, query_id: str, sql: str, cache: str) -> RunResult:
    if cache == "cold":
        engine.drop_caches()
    start = time.perf_counter()
    rows = engine.execute(sql)
    wall = (time.perf_counter() - start) * 1000
    stats = engine.last_query_stats()
    return RunResult(engine.name, query_id, cache, wall, len(rows),
                     result_fingerprint(rows),
                     stats.get("bytes_scanned"), stats.get("files_scanned"))

def benchmark(engines, queries: dict[str, dict[str, str]], repeats: int = 3):
    results, fingerprints = [], {}
    for qid, per_engine_sql in queries.items():
        for engine in engines:
            sql = per_engine_sql[engine.name]
            for cache in ("cold", "warm"):
                runs = [run_query(engine, qid, sql, cache) for _ in range(repeats)]
                runs.sort(key=lambda r: r.wall_ms)
                results.append(runs[len(runs) // 2])          # the median run
            fingerprints.setdefault(qid, {})[engine.name] = runs[0].result_hash

    for qid, per_engine in fingerprints.items():
        distinct = set(per_engine.values())
        assert len(distinct) == 1, (
            f"query {qid}: engines disagree on the result — {per_engine}")
    return results

Step-by-step walkthrough

1. Fingerprint results order-independently. Engines return rows in different orders, so a naive hash of the concatenated output differs for identical result sets. Sorting the row strings before hashing removes the false difference while keeping real ones.

2. Assert agreement before reporting timings. A benchmark comparing engines that return different answers is measuring nothing. Making the agreement check a hard assertion means a semantic difference stops the run rather than appearing as a suspiciously fast engine.

3. Take the median of several runs. Cluster contention, storage variability and JIT warm-up all add noise. Three runs and the median is enough to be stable without making the harness slow.

4. Report cold and warm separately. They answer different questions — cold measures the layout, warm measures the engine — and averaging them produces a number that answers neither.

5. Capture bytes and files scanned where available. Wall time tells you which engine was faster on the day; bytes scanned tells you why, and survives a change of hardware.

Common errors and fixes

Reporting the result

The fourth section is what makes the report reusable. Stating “we chose Trino; had DuckDB been within 20% on the twenty-user concurrent mix, we would have chosen it” turns a future re-evaluation into a single measurement rather than a repeat of the whole exercise — and it makes the reasoning auditable by someone who was not in the room.

Publish per-query results rather than an aggregate. A geometric mean hides the case that matters, which is usually the one where an engine was an order of magnitude slower rather than the several where it was slightly faster.

Keeping the harness honest over time

Re-run the benchmark on a schedule rather than only when a decision is due, and store the results in the same metrics table the rest of the platform’s observability uses. Engine releases change performance, the data grows, and a decision made eighteen months ago against versions nobody runs any more is not evidence.

The scheduled run also catches something a one-off cannot: a regression in the platform’s own layout. If the same query against the same pinned dataset slows by a factor of three, the engine did not change — the table did, and the harness has just found a layout regression that no other check was looking for.

Designing the Query Set

The queries are the part of the harness that most determines whether its conclusion transfers to production, and a set assembled from intuition rarely does.

The fourth shape is the one that reverses conclusions. An in-process engine that wins the first three convincingly can lose the fourth badly, because concurrency is where its architecture differs most from a coordinated one. A benchmark that omits it is not a simplification of the production question; it is a different question.

Derive the query set from the query log rather than from imagination. The ten shapes that consume the most total time on the current platform are the ten that matter, and they are usually less exotic than a hand-designed set — mostly small windows and simple aggregations, which is itself a useful finding.

Interpreting the Numbers

A benchmark produces a table; turning it into a decision needs a few interpretive habits.

Compare bytes scanned before wall time. Two engines reading the same bytes at different speeds is an engine difference; two engines reading different bytes is a pushdown difference, and the second is usually larger and more actionable.

Treat differences under twenty percent as noise unless the harness has demonstrated tighter repeatability. Spatial workloads on shared infrastructure vary enough that a smaller apparent difference rarely survives a re-run.

Weight the query shapes by their production frequency rather than treating them equally. An engine ten percent slower on the shape that runs a thousand times a day matters more than one twice as slow on the shape that runs weekly.

And check the failure behaviour, not only the success timings. An engine that answers the concurrent mix quickly but fails one query in fifty under load is worse than a slower one that never fails, and a benchmark reporting only successful runs will not show it.

Cost of the Harness

A reproducible harness is more work than an ad-hoc timing script, and the difference is worth quantifying because it is smaller than it appears.

The generator, the layout DDL and the query files are written once and are mostly things the platform needs anyway — a representative dataset and a set of canonical queries are useful for testing and for onboarding as well as for benchmarking. The orchestration is a couple of hundred lines. The result fingerprinting is twenty.

The ongoing cost is the scheduled run, which for a moderate dataset is minutes of compute and can happen overnight. Against that, the harness answers “is the platform still performing as it did” continuously rather than only when somebody asks, and it catches layout regressions that no other check looks for.

The case where it is not worth building is a one-off decision on a platform that will not be re-evaluated — a single engine choice, made once, never revisited. Those are rarer than they seem: engine versions change, data grows, and the question comes back. Building the harness the first time the question is asked means the second time costs an afternoon rather than a fortnight. And an afternoon is short enough that the question gets answered with evidence rather than with the loudest opinion in the room. For the design principles the harness implements — which dimensions to vary, and why a single-query benchmark misleads on a concurrent platform — see engine benchmarking and selection. Read that page first if the harness is being built to settle a live disagreement, because the design decisions it covers determine whether the numbers will be believed. Credibility is the harness’s real output. A fast engine nobody believes is slower, in practice, than a slower one everybody trusts — because the first gets re-litigated every quarter and the second gets built on.