Benchmarking spatial adapters with a dbt harness

This page builds a small dbt project whose only purpose is to answer “which engine should we use for this workload” with numbers from your own data: five representative spatial operations, run identically on every candidate, with correctness checked alongside speed.

When to use this approach

• The adapter decision is contested and the arguments are about general reputation rather than your data. A day of benchmarking settles it better than a week of discussion.
• A workload changed materially. An engine chosen for point-in-polygon work may be the wrong one now that the heaviest query is a nearest-neighbour search.
• You are considering a migration. Benchmark before porting, because the port is the expensive part and the benchmark is the cheap part. The decision framework is in choosing the right spatial adapter.

Prerequisites

• Two or more configured targets in one profiles.yml, so the same project runs against each.
• A representative data sample — the same rows everywhere, loaded the same way.
• Dispatched macros for any function whose name differs, per cross-engine UDF portability.
• Somewhere to record results; the run-history table from monitoring spatial model run times in dbt works unchanged.

Step-by-step instructions

1. Pick five queries that represent the real workload

A benchmark of one query measures one query. Five is enough to cover the shapes that behave differently, and few enough to keep the harness maintainable.

Verify the mix matches your workload rather than a textbook. If your pipeline never dissolves, replace bench_03 with whatever it does instead — the point is representativeness, not comparability with anyone else’s numbers.

2. Write each benchmark as one model, portable across targets

-- models/bench/bench_01_point_in_polygon.sql
{{ config(materialized = 'table', tags = ['bench']) }}

select
    p.ping_id,
    z.zone_id
from {{ ref('bench_pings') }} as p
join {{ ref('bench_zones') }} as z
  on {{ st_intersects('p.geom', 'z.geom') }}

-- models/bench/bench_02_nearest_neighbour.sql
{{ config(materialized = 'table', tags = ['bench']) }}

select
    p.ping_id,
    n.depot_id,
    n.distance_m
from {{ ref('bench_pings') }} as p
cross join lateral (
    select d.depot_id, {{ st_distance_m('p.geom', 'd.geom') }} as distance_m
    from {{ ref('bench_depots') }} as d
    order by {{ knn_operator('p.geom', 'd.geom') }}
    limit 3
) as n

The knn_operator macro is where engines diverge most: PostGIS has the <-> distance operator backed by a GiST index, and most other engines have nothing equivalent, so their implementation is an ordered scan. That difference is the benchmark’s most valuable finding rather than an inconvenience to hide.

Verify every benchmark compiles on every target before running any of them:

for t in postgres_bench duckdb_bench bigquery_bench; do
  dbt compile --select tag:bench --target "$t" >/dev/null && echo "$t compiles"
done

3. Run each target the same way, three times

#!/usr/bin/env bash
# scripts/run_benchmark.sh
for target in postgres_bench duckdb_bench bigquery_bench; do
  for run in 1 2 3; do
    dbt build --select tag:bench --target "$target" --full-refresh
    python scripts/load_artifacts.py \
      --run-results target/run_results.json \
      --manifest target/manifest.json \
      --table ops.bench_history \
      --label "${target}-run${run}"
  done
done

Three runs, full refresh each time, and the median reported. A single run measures cache state as much as engine capability; the first run against a cold cache is usually the outlier.

Verify the runs are comparable by checking input row counts on each target — a benchmark against different data is not a benchmark.

select target_name, count(*) from ops.bench_input_counts group by 1;

4. Check correctness, not only speed

-- tests/assert_bench_results_agree.sql
with pg as (select ping_id, zone_id from {{ ref('bench_01_point_in_polygon') }}),
     ref_ as (select ping_id, zone_id from {{ source('bench', 'expected_point_in_polygon') }})
select 'missing' as side, * from (select * from ref_ except select * from pg) a
union all
select 'extra', * from (select * from pg except select * from ref_) b

An engine that is twice as fast and disagrees on 0.1 per cent of rows has not won; it has raised a question about spherical versus planar edges, tie-breaking on boundaries, or validity handling. Answer that question before comparing times.

Verify the disagreement count is zero or explained. A handful of boundary rows on a geodesic engine is expected and should be written down as such rather than discovered later.

5. Write down the decision and its expiry

A benchmark result is true for a data size, an engine version and a workload mix. Record all three, so the next person knows when it stopped being evidence.

# models/bench/schema.yml
models:
  - name: bench_01_point_in_polygon
    description: >
      Benchmark run 2026-08-11. 42M pings × 18k subdivided zones.
      PostGIS 3.4.2 median 41 s; DuckDB 0.10.2 median 96 s; warehouse median 154 s.
      Results identical across engines. Decision: PostGIS for the nightly join.
      Re-run when ping volume doubles or any engine has a major upgrade.

Configuration reference

Gotchas & edge cases

• Warm caches flatter whichever engine ran last. Restart the service or clear the cache between runs where you can, and always report the median of three.
• A cloud warehouse’s first query pays for cluster start. Exclude it or note it; comparing a warm PostGIS against a cold warehouse is not a comparison.
• Sample size changes the ranking. Engines that lose on small data can win on large, because fixed overheads amortise. Benchmark near production scale or state the caveat prominently.
• --full-refresh on a cloud engine can be expensive. Set a byte ceiling before running the harness, not after.
• Do not benchmark what you will not run. A dissolve benchmark on a pipeline that never dissolves adds a number that will eventually be used to justify a decision it has nothing to do with.

FAQ

Related

• Choosing the Right Spatial Adapter — the decision this informs.
• PostGIS vs DuckDB Spatial for CI Pipelines — the same comparison for a narrower purpose.
• Monitoring Spatial Model Run Times in dbt — the artifact capture this harness reuses.

Up: Part of Choosing the Right Spatial Adapter.