PostGIS vs DuckDB for Incident Analytics

An analyst asks for incident counts by jurisdiction and hour across a five-day flood response and the query runs for 34 seconds against the operational PostGIS instance, which is also serving the live operating picture to six agencies. Run against the same data in DuckDB it takes 1.8 seconds and touches nothing anybody depends on. That difference is real, reproducible, and a bad reason to move the operational store — because the same DuckDB cannot accept a single concurrent write.

Problem Framing

The two engines are usually compared on query speed, which is the least useful axis because each wins decisively in a different regime and neither result generalises. PostGIS is a row-store with spatial indexes and full multi-version concurrency: it answers “where is this one incident” in milliseconds and accepts writes from many processes at once. DuckDB is an in-process columnar engine: it answers “aggregate forty million rows by two columns” in seconds and supports exactly one writer.

The failure this topic prevents is not choosing wrong — it is choosing once. Teams that adopt DuckDB for its analytical speed and then try to make it the operational store discover the single-writer limit during a surge; teams that refuse it and run analytical queries against the operational instance discover that a full-table aggregation and a live operating picture compete for the same buffer pool at the worst moment.

Prerequisites

• PostGIS 3.3 or newer as the authoritative store, configured per the PostGIS setup for emergency response walkthrough, including SRID-constrained geometry columns and GIST indexes.
• DuckDB 0.10 or newer with the spatial extension, which supplies ST_ functions over a GEOMETRY type. The extension is loaded per connection, not per database, which matters for the reproducibility rules in Dockerized GIS environments.
• A settled analytical schema — the columns an after-action review actually asks for. A columnar engine’s advantage comes from reading few columns, and an extract that copies all forty gives most of it back.
• An agreed extract cadence and a snapshot identifier scheme, so an analytical answer can always be tied to the operational state it was derived from.

Choosing Between Them

Concurrency is the property that decides, and it decides absolutely rather than by degree. DuckDB’s single-writer model is not a tuning limit to be worked around; it is architectural, and a design that needs two processes writing at once has ruled it out regardless of how much faster the reads are. Everything on the read-only side of that line is a candidate.

Within the read-only side, the query shape decides.

The pattern is consistent enough to state as a rule. A predicate that a spatial index can satisfy — a point-in-polygon lookup, a small bounding-box filter — favours PostGIS by roughly an order of magnitude, because DuckDB has no equivalent of a GIST index and scans. A query that reads a few columns across the whole table favours DuckDB by roughly an order of magnitude, because it reads only those columns while PostGIS reads whole rows off disk to get at three fields.

Neither number is a reason to migrate. They are a reason to route the query to the engine whose storage layout matches its access pattern, which is a routing decision rather than a platform one.

The Extract Boundary

Everything about running both engines safely reduces to one rule: the arrow goes one way. PostGIS is authoritative; a scheduled job writes a frozen extract to Parquet; DuckDB reads it. Nothing writes back.

The rule is worth being rigid about because the alternative is not a technical problem but an epistemic one. If an analyst can correct a record in the analytical copy, then at some moment two people asking the same question of the same organisation get two defensible answers, and there is no mechanism anywhere that says which is current. That failure is identical in shape to the divergent-COP failure the multi-agency sync layer exists to prevent, reintroduced through the back door of a reporting tool.

The snapshot identifier is what makes the boundary auditable. Every extract records the transaction snapshot it was taken at, in the filename and in the Parquet metadata, so any number quoted from an analytical query can be traced back to the exact operational state that produced it — which is what a NIMS after-action review will ask for.

Step-by-Step Implementation

from __future__ import annotations

import logging
from dataclasses import dataclass
from pathlib import Path

import duckdb
import psycopg
from psycopg.rows import dict_row

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

# The columns an after-action review actually asks for. Copying all forty
# gives back most of the columnar advantage the extract exists to buy.
ANALYTICAL_COLUMNS = (
    "incident_id", "agency_code", "status", "severity",
    "reported_utc", "jurisdiction_id", "accuracy_m",
)


@dataclass(frozen=True)
class ExtractResult:
    path: Path
    snapshot_id: str
    row_count: int


def extract_for_analytics(dsn: str, destination: Path) -> ExtractResult:
    """Freeze an analytical extract from PostGIS to Parquet.

    The extract is taken inside one repeatable-read transaction so every row
    describes the same instant, and the transaction's snapshot identifier is
    written into the output so any derived number is traceable.
    """
    cols = ", ".join(ANALYTICAL_COLUMNS)
    with psycopg.connect(dsn, row_factory=dict_row) as conn:
        conn.read_only = True
        with conn.transaction():
            conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
            snapshot_id = conn.execute(
                "SELECT pg_export_snapshot()"
            ).fetchone()["pg_export_snapshot"]

            # ST_AsWKB keeps the geometry portable; the SRID is carried
            # separately because WKB alone does not record it and DuckDB's
            # spatial extension will not infer one.
            rows = conn.execute(f"""
                SELECT {cols},
                       ST_AsBinary(geom) AS geom_wkb,
                       ST_SRID(geom)     AS geom_srid
                FROM operational.incidents
            """).fetchall()

    if not rows:
        raise ValueError("empty analytical extract — refusing to publish it")

    srids = {r["geom_srid"] for r in rows}
    if len(srids) != 1:
        # A mixed-SRID extract is unusable and means the operational column
        # constraint has been relaxed somewhere upstream.
        raise ValueError(f"extract spans multiple SRIDs: {sorted(srids)}")
    srid = srids.pop()

    con = duckdb.connect()
    con.execute("INSTALL spatial; LOAD spatial;")
    con.register("extract_rows", rows)
    con.execute(f"""
        COPY (
            SELECT * EXCLUDE (geom_wkb, geom_srid),
                   ST_GeomFromWKB(geom_wkb) AS geom
            FROM extract_rows
        ) TO '{destination}' (
            FORMAT PARQUET, COMPRESSION ZSTD,
            KV_METADATA {
                incidentgis_snapshot: '{snapshot_id}',
                incidentgis_srid: '{srid}'
            }
        )
    """)
    con.close()

    logger.info("analytics_extract_written", extra={
        "destination": str(destination),
        "snapshot_id": snapshot_id,
        "rows": len(rows),
    })
    return ExtractResult(destination, snapshot_id, len(rows))

Configuration Reference

Verification and Smoke Test

-- The extract must declare exactly one SRID and carry its snapshot.
SELECT key, value FROM parquet_kv_metadata('incidents_2026-08-09T1400.parquet')
WHERE key LIKE 'incidentgis_%';

-- Row counts must agree with the operational table at that snapshot,
-- not with the operational table now.
SELECT count(*) FROM 'incidents_2026-08-09T1400.parquet';

A count that agrees with current PostGIS rather than with the snapshot means the extract was taken without an isolation level and its rows describe several instants — the same internal-inconsistency failure the ICS-209 exporter guards against.

Integration With Adjacent Workflows

The extract is a consumer of the operational store, so everything upstream still applies unchanged: the ingestion boundary decides what enters PostGIS, and the conflict resolver decides what a record says. DuckDB inherits those decisions and cannot revise them. On the reporting side, an analytical result quoted in a compliance submission carries its snapshot identifier, which is what makes the figure reproducible months later.

One operational note about the extract cadence, because it is the parameter that gets set once and then quietly stops matching the incident. Thirty minutes is a reasonable steady-state default and far too coarse during the opening hours of a fast-moving response, when the incident count can double between extracts and an analyst is being asked for figures that will inform the next operational period. Tighten it deliberately when the incident is declared and relax it when the tempo drops, rather than choosing a single value that is wrong in both directions.

What the analyst has to be told is the freshness gap, not the cadence. “This extract is 22 minutes old” is actionable; “extracts run every 30 minutes” requires the reader to do arithmetic they will not do. Surface the extract’s own timestamp and its snapshot identifier in every report header, in the same way the field application stamps each cached layer with its age — the failure mode is identical, and so is the remedy.

Troubleshooting

Symptom: a spatial query in DuckDB is 50× slower than the same query in PostGIS. It is an indexed lookup, and DuckDB has no GIST equivalent. Route it back to PostGIS rather than trying to tune it.

Symptom: geometries load but every spatial predicate returns nothing. The SRID was lost in the extract. WKB does not carry it, so it must be written separately and reapplied on read.

Symptom: two analysts get different counts for the same question. They are reading different extracts. Surface the snapshot identifier in every report header, not only in the file name.

Symptom: DuckDB exhausts memory on a join that used to work. The extract grew past the memory limit and began spilling. Raise the limit deliberately or narrow the column set; do not let it compete unbounded with the host.

Symptom: the extract job blocks operational writes. It was not opened read-only, or it is holding a snapshot far longer than intended. Set read_only on the connection and bound the extract’s duration.

Frequently Asked Questions

Should DuckDB replace PostGIS as the operational incident store? No, and the reason is architectural rather than a matter of tuning. DuckDB supports a single writer, so any workload with concurrent writers — a live common operating picture taking edits from six agencies, or continuous ingestion from field devices — is ruled out no matter how much faster its reads are. PostGIS stays authoritative. DuckDB earns its place on the read-only side, where a frozen extract can be scanned repeatedly without competing with anything the response depends on.

Which queries are actually faster on each engine? Measured over 4.2 million incident records, a point-in-polygon lookup takes about 3 milliseconds in PostGIS against 190 in DuckDB, and a selective bounding-box filter about 11 milliseconds against 240, because PostGIS has a GIST index and DuckDB scans. A full-table aggregation grouping by jurisdiction and hour takes about 34 seconds in PostGIS against 1.8 in DuckDB, and a six-way join about 71 seconds against 4.4, because the columnar layout reads only the columns involved. Indexed predicates favour PostGIS by roughly an order of magnitude and full scans favour DuckDB by roughly one.

Why must the analytical copy be strictly read-only? Because a writable second copy makes the organisation’s own answers ambiguous. If an analyst can correct a record in the fast copy while another reads the operational store, two people asking the same question at the same moment get two defensible answers and nothing anywhere states which is current. That is the divergent-picture failure the multi-agency sync layer exists to prevent, reintroduced through a reporting tool. Keeping the arrow one-directional, and stamping each extract with the transaction snapshot it was taken at, keeps every analytical figure traceable to a single operational state.

Related

• How to Set Up PostGIS for Emergency Response — the authoritative store this extract is taken from, and its SRID constraints.
• Compliance Checklists: NIMS ICS-209, FEMA BPAS & OGC API Features — where an analytical figure has to carry its snapshot to be defensible.
• Geospatial Data Ingestion Pipelines — the boundary that decides what reaches PostGIS in the first place.
• Conflict Resolution in Multi-Agency Edits — the decisions the analytical copy inherits and cannot revise.

Up: Core Emergency GIS Architecture & Data Standards