PostGIS vs DuckDB Spatial for CI Pipelines

This page helps you decide whether a pull-request CI job should validate your dbt spatial models against an in-process DuckDB spatial database or a running PostGIS service — and shows the hybrid that uses both.

When to use this approach

The choice is not “which engine is better” but “which engine should gate a merge”. The two are not interchangeable, and the right answer usually depends on how close your CI must sit to production behavior versus how fast it must return.

• Reach for DuckDB spatial in CI when you want sub-minute feedback on every push, no service container to provision, and no credentials to inject. It runs in-process through the DuckDB spatial extension integration, so a job spins up, builds the DAG, runs every geometry test, and tears down without a network hop.
• Reach for a PostGIS service in CI when production is PostGIS and you must catch engine-specific behavior — GiST planner decisions, GEOGRAPHY spheroidal math, post_hook index creation, or functions DuckDB does not implement identically. This is the only way to reproduce the plan the warehouse will actually choose.
• Use both — the recommended default — when you want the speed of DuckDB as a fast gate on every commit and the fidelity of PostGIS as a slower promotion check before merge. The full engine trade-off matrix lives in choosing the right spatial adapter; this page narrows it to the CI-specific decision.

If your production warehouse is neither engine — BigQuery or Snowflake — DuckDB is still an excellent fast gate for the portable subset of your logic, but a nightly build against the real warehouse remains the only trustworthy fidelity check.

Prerequisites

• dbt Core 1.7+ with both adapters installed in the CI image: dbt-duckdb for the fast lane and dbt-postgres for the fidelity lane.
• DuckDB spatial extension available offline or via the extension repository; configured through DuckDB spatial extension integration.
• A PostGIS 3.3+ service reachable from CI — as a GitHub Actions services: container or an ephemeral cloud instance. Setup mirrors setting up PostGIS with dbt.
• A single canonical SRID agreed for the project so both engines validate against the same coordinate frame; policy lives in spatial reference system management.
• Secrets wired through env_var() so the PostGIS lane reads credentials from the CI secret store, never from a committed profiles.yml.

# dbt_project.yml — shared vars both CI lanes consume
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '4326') }}"

The core comparison

The two engines differ on the five axes that actually decide a CI strategy: how long a job takes to become usable, how faithfully it reproduces production semantics, how it handles indexes, what it costs in secrets and surface area, and what it costs in money and runner minutes.

The single most important row is index behavior. A model that passes on DuckDB can still choose a sequential scan on PostGIS, because DuckDB never exercised the post_hook that builds the GiST index. That is precisely the class of regression the PostGIS lane exists to catch, and why DuckDB alone is not a sufficient gate for a PostGIS-backed production system.

Step-by-step: build the hybrid gate-then-promote CI

The pattern that gives you both speed and fidelity is a two-job workflow: DuckDB gates every commit, PostGIS validates only when a merge is imminent.

1. Define two dbt targets, one per engine

Keep both lanes reading the same models and the same env_var()-driven secrets. Only the connection block differs.

# ci/profiles.yml
dbt_geospatial:
  target: duckdb_ci
  outputs:
    duckdb_ci:
      type: duckdb
      path: "{{ env_var('DBT_DUCKDB_PATH', 'ci.duckdb') }}"
      extensions: [spatial]
    postgis_ci:
      type: postgres
      host: "{{ env_var('DBT_PG_HOST') }}"
      port: "{{ env_var('DBT_PG_PORT', '5432') | int }}"
      user: "{{ env_var('DBT_PG_USER') }}"
      password: "{{ env_var('DBT_PG_PASSWORD') }}"
      dbname: "{{ env_var('DBT_PG_DBNAME', 'gis') }}"
      schema: "{{ env_var('DBT_PG_SCHEMA', 'dbt_ci') }}"
      threads: 4

Verify both targets resolve before wiring CI:

dbt debug --target duckdb_ci    # expects "Connection test: OK"
dbt debug --target postgis_ci   # expects "Connection test: OK"

2. Make the DuckDB gate the required check

This job runs on every push. It installs one package, builds the DAG, and runs every geometry test in-process — no services: block, no secrets.

# .github/workflows/spatial-ci.yml
name: spatial CI
on: [pull_request]
jobs:
  duckdb-gate:
    runs-on: ubuntu-latest
    env:
      DBT_PROFILES_DIR: ./ci
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: "3.11" }
      - run: pip install dbt-duckdb
      - run: dbt build --target duckdb_ci   # DAG + all generic tests

Verify the gate is fast and self-contained: the job should complete without a network dependency and, on a modest project, finish in well under a minute. If it stalls, the spatial extension is being downloaded on the fly — pre-bake it into the runner image.

3. Add the PostGIS fidelity lane as a service job

This job only needs to run before merge. It brings up a PostGIS container, injects credentials from the secret store, and builds against the real planner so GiST post_hook indexes and GEOGRAPHY math are exercised exactly as in production.

  postgis-promote:
    runs-on: ubuntu-latest
    needs: duckdb-gate
    env:
      DBT_PROFILES_DIR: ./ci
      DBT_PG_HOST: localhost
      DBT_PG_USER: ${{ secrets.PG_USER }}
      DBT_PG_PASSWORD: ${{ secrets.PG_PASSWORD }}
    services:
      postgis:
        image: postgis/postgis:16-3.4
        env:
          POSTGRES_USER: ${{ secrets.PG_USER }}
          POSTGRES_PASSWORD: ${{ secrets.PG_PASSWORD }}
          POSTGRES_DB: gis
        ports: ["5432:5432"]
        options: >-
          --health-cmd "pg_isready -U ${{ secrets.PG_USER }}"
          --health-interval 5s --health-timeout 5s --health-retries 10
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: "3.11" }
      - run: pip install dbt-postgres
      - run: dbt build --target postgis_ci

Verify the fidelity lane actually exercised an index rather than silently passing on a sequential scan:

-- run inside the PostGIS lane against a proximity model
EXPLAIN ANALYZE
SELECT a.id, b.id
FROM stg_points a
JOIN stg_zones b ON ST_DWithin(a.geom, b.geom, 500);
-- expect "Index Scan using ..._gist"; a "Seq Scan" means the post_hook index did not build

4. Isolate engine differences behind macros

The hybrid only stays green on both engines if every dialect difference is routed through an adapter-aware macro rather than branched by hand in model SQL. A KNN <-> lateral join has no DuckDB equivalent, and GEOGRAPHY casting differs; dispatch on target.type so one model compiles correctly on both lanes. The construction pattern is covered in choosing the right spatial adapter and its downstream macro guides.

-- macros/spatial/dwithin_ci.sql
{% macro dwithin_ci(a, b, meters) %}
  {%- if target.type == 'duckdb' -%}
    ST_DWithin({{ a }}, {{ b }}, {{ meters }})
  {%- else -%}
    ST_DWithin({{ a }}::geography, {{ b }}::geography, {{ meters }})
  {%- endif -%}
{% endmacro %}

Verify the macro emits the right dialect on each target:

dbt compile --select fct_proximity --target duckdb_ci
dbt compile --select fct_proximity --target postgis_ci
# diff target/compiled/... — the geography cast should appear only in the PostGIS output

Configuration reference

Gotchas & edge cases

• DuckDB green is not PostGIS green. A post_hook GiST index never runs in the in-memory DuckDB lane, so an index-plan regression sails through the gate and only surfaces in the PostGIS lane. Treat the DuckDB job as a correctness-of-logic gate, not a performance gate.
• GEOGRAPHY semantics diverge. Spheroidal distance on PostGIS GEOGRAPHY will not match planar GEOMETRY results in DuckDB. If a model depends on great-circle distance, its numeric assertions belong in the PostGIS lane; the trade-off is detailed in geometry vs geography type trade-offs.
• Extension download inside the job. If DuckDB fetches the spatial extension at runtime, the “fast” lane inherits a flaky network dependency. Pre-install or vendor the extension into the CI image.
• SRID drift between lanes. Seed the same fixture SRIDs into both engines. A fixture tagged SRID 0 may pass planar checks in DuckDB and then fail a ::geography cast in PostGIS.
• Service never became ready. Without a --health-cmd, dbt connects before PostGIS finishes initializing and the job fails with a confusing connection error rather than waiting. Always gate the build step on a health check.

FAQ

Related

• Choosing the Right Spatial Adapter — the full engine trade-off matrix behind this CI decision.
• DuckDB Spatial Extension Integration — configure the in-process engine that powers the fast lane.
• Setting Up PostGIS with dbt — stand up the service the fidelity lane connects to.

Up: Part of Choosing the Right Spatial Adapter.