PostGIS vs DuckDB + GeoParquet for zoning overlay storage

You are standing up the storage layer for a system that tracks zoning overlays against a parcel fabric, and the choice of engine will decide whether a nightly impact run finishes in ninety seconds or forty minutes, whether two analysts can query the same district without blocking each other, and how much a per-release snapshot costs to keep. The two credible answers pull in opposite directions: a transactional PostGIS instance that treats overlays as live, mutable rows, or a columnar DuckDB engine reading immutable GeoParquet files from object storage. Picking the wrong one is not a catastrophe — both store polygons correctly — but it quietly taxes every query and every deploy afterward. This guide frames the decision the way you would actually make it inside the broader municipal data structures topic of the municipal zoning data architecture and compliance frameworks area: what workload each engine wins, the symptoms that should push you off one, how to migrate, and how to verify you actually gained something.

Diagnosis: which workload are you actually running?

Before comparing features, characterize the workload, because the engines optimize for different shapes and the “right” answer is entirely a function of yours. Ask three questions and answer them with numbers, not intuition.

First, read-to-write ratio. Are overlays ingested once per release and then read thousands of times (analytical), or does an editor mutate individual parcel-district assignments through the day (transactional)? A zoning-overlay store that receives a fresh county extract every quarter and otherwise only serves impact queries is 99.9% read. An editorial parcel database with staff correcting boundaries is write-heavy in bursts.

Second, concurrency model. Do you need many writers holding row locks with ACID guarantees, or many independent readers scanning the same immutable snapshot? These are not the same problem, and an engine tuned for one is mediocre at the other.

Third, scan shape. Do queries touch a handful of parcels by primary key and spatial index (point lookups, ST_Intersects against one overlay), or do they aggregate every parcel in a jurisdiction (sum affected acreage across an entire overlay change)? Index-friendly point work favors a B-tree/GiST world; full-column aggregation favors columnar scans.

A quick way to expose the shape is to instrument a representative query on a copy of the data and read the plan. In PostGIS you time it directly:

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.parcel_id, ST_Area(ST_Intersection(p.geom, o.geom)) AS overlap_m2
FROM parcels p
JOIN zoning_overlays o
  ON ST_Intersects(p.geom, o.geom)   -- GiST index candidate
WHERE o.overlay_code = 'FLD-AE'      -- one flood overlay
  AND o.valid_to IS NULL;            -- current version only

If the plan shows an Index Scan using parcels_geom_gist with a tight row estimate, PostGIS is doing what it is good at. If it shows a Seq Scan over millions of rows because the query aggregates the whole layer, you are paying transactional overhead for an analytical job — a signal worth taking seriously.

Decision flow for choosing PostGIS, DuckDB plus GeoParquet, or both for zoning overlay storage A workload profile box feeds a central decision diamond asking whether the store is write-heavy with concurrent editors. A "yes, mutable" branch goes to PostGIS with GiST indexing and ACID writes. A "no, read-mostly analytical" branch goes to DuckDB reading immutable GeoParquet snapshots from object storage. A third "both" path shows PostGIS as the transactional system of record exporting versioned GeoParquet snapshots that DuckDB queries for analytics, with a verification box confirming each engine via EXPLAIN and timing. Workload profile read:write · scan shape Write-heavy? editors mutate rows yes · mutable PostGIS GiST · ACID writes no · read-mostly DuckDB + GeoParquet columnar · object store export snapshot Verify EXPLAIN · timing
Route the storage decision from a measured workload profile, using both engines when the system of record and the analytics layer have different shapes.

The decision matrix

Score both engines against the axes that actually move a zoning-overlay workload. Ratings are relative, not absolute — DuckDB “weak” transactional writes still commit correctly, they just are not the point.

Axis PostGIS (PostgreSQL) DuckDB + GeoParquet
Transactional writes Strong — full ACID, UPDATE/DELETE on live rows, constraints Weak — files are immutable; “writes” mean rewriting a partition
Concurrent readers Good, but each connection costs a backend process and shared buffers Excellent — every reader opens the same Parquet independently, no locks
Spatial index maturity Mature — GiST/SP-GiST, ST_* predicate pushdown, decades hardened Emerging — spatial extension, R-tree via bbox, no persistent index yet
Analytical scan speed Moderate — row store, index or seq scan, per-row function overhead Fast — columnar, vectorized, reads only the projected columns
Cost Always-on server (CPU, RAM, storage, backups) Compute-on-read; storage is cheap object bytes, engine is embedded
Snapshot / versioning Manual — table copies, valid_from/valid_to, or logical dumps Native feel — a new file/partition per release is the snapshot
Ops burden Higher — tuning, VACUUM, connection pool, HA, upgrades Lower — no server; ship files, embed the engine in the job

Two rows deserve emphasis for zoning work specifically. Spatial index maturity is where PostGIS still decisively wins: a GiST index over geometry supports fast ST_Intersects, ST_DWithin, and nearest-neighbor operators that a per-parcel editorial UI leans on constantly. Deep tuning of those indexes is its own topic under spatial database indexing and performance. Snapshot/versioning is where DuckDB plus GeoParquet feels native: a quarterly overlay release is simply a new immutable file, so “the overlays as of Q1” is a path, not a temporal join.

The two spatial queries, side by side

The same overlap-area question expressed in each engine shows the ergonomic difference. PostGIS runs the query above against indexed live tables. DuckDB runs the equivalent directly over GeoParquet files, no load step:

import duckdb

con = duckdb.connect()
con.execute("INSTALL spatial; LOAD spatial;")

# Query immutable snapshots straight from object storage — no import.
result = con.execute("""
    SELECT p.parcel_id,
           ST_Area(ST_Intersection(p.geom, o.geom)) AS overlap_m2
    FROM read_parquet('s3://zoning/parcels/2026q2/*.parquet') p
    JOIN read_parquet('s3://zoning/overlays/2026q2/*.parquet') o
      ON ST_Intersects(p.geom, o.geom)     -- bbox pre-filter, then exact
    WHERE o.overlay_code = 'FLD-AE'
""").fetchdf()

print(result.head())   # returns a pandas DataFrame, ready for reporting

The DuckDB version scans only the geom and overlay_code columns it references and never touches a server; the PostGIS version rides a persistent GiST index and can update a single parcel in place. Neither is “better” — they answer the same question for different operational lives.

When to pick which — and when to use both

Pick PostGIS when the store is the transactional system of record. If staff edit parcel-to-district assignments, if you need foreign keys and check constraints enforcing that every overlay references a valid parcel, if concurrent editors must not clobber each other, or if a UI issues thousands of small indexed spatial lookups per minute, PostGIS is the correct default. Bi-temporal valid_from/valid_to columns and exclusion constraints make it a strong system of record for the mutable core of your municipal data structures.

Pick DuckDB + GeoParquet when the store is a read-mostly analytical snapshot. If overlays land as periodic county extracts and are then only queried — nightly impact runs, ad-hoc “how many parcels does this new overlay touch” analyses, dashboards — the columnar engine reading immutable files is faster, cheaper, and dramatically simpler to operate. There is no server to keep alive, snapshots are free, and every analyst gets an isolated reader. Converting incoming extracts into partitioned GeoParquet is exactly the job covered under geospatial format conversion.

Use both when your system of record and your analytics layer have different shapes — which, for most production zoning platforms, they do. Keep the authoritative, mutable overlays and parcels in PostGIS, then export a versioned GeoParquet snapshot per release into object storage and point DuckDB at it for heavy analytical scans. Transactional integrity lives in Postgres; cheap, isolated, versioned analytics live in Parquet. The export is a clean seam, and it means a runaway analytical query can never lock the editorial database.

Migrating from one to the other

If timing your workload pushed you off your first choice, migrate deliberately.

PostGIS to GeoParquet (analytical offload). Export current-version overlays to partitioned Parquet on each release. Keep the SRID explicit so the snapshot is self-describing:

import geopandas as gpd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg://gis@db/zoning")

# Pull only current-version rows; project once, write immutable snapshot.
gdf = gpd.read_postgis(
    "SELECT parcel_id, overlay_code, geom FROM zoning_overlays "
    "WHERE valid_to IS NULL",
    engine, geom_col="geom",
)
assert gdf.crs is not None, "refuse to write a snapshot with no CRS"
gdf.to_parquet("overlays_2026q2.parquet", index=False)   # embeds CRS in metadata

GeoParquet to PostGIS (promoting analytics to a live store). When an analytical dataset needs editing and constraints, load it and build the index explicitly — never rely on it existing:

gdf = gpd.read_parquet("overlays_2026q2.parquet")
gdf.to_postgis("zoning_overlays", engine, if_exists="append", index=False)
# GiST index is not automatic on to_postgis — create it or every query seq-scans.
with engine.begin() as cx:
    cx.exec_driver_sql(
        "CREATE INDEX IF NOT EXISTS overlays_geom_gist "
        "ON zoning_overlays USING GIST (geom)"
    )

The single most common migration bug is losing the CRS: GeoParquet stores it in file metadata and PostGIS stores it as an SRID, and a silent mismatch lands parcels in the wrong hemisphere. Assert the CRS on both sides of the move.

Verification: prove the engine change paid off

Do not trust a switch on faith — measure it on the real query.

  • Compare wall-clock on the representative query. Run the nightly impact aggregation on both engines against the same data and record the median of five runs. A columnar offload that does not beat the indexed PostGIS query on a full-scan aggregate means the workload was point-lookup shaped after all.
  • Read the PostGIS plan. EXPLAIN (ANALYZE, BUFFERS) should show an Index Scan (not Seq Scan) on spatial-predicate queries, and the BUFFERS line should not reveal massive heap reads. A Seq Scan on ST_Intersects means the GiST index is missing or the planner mis-estimated — the diagnostic that belongs to indexing and performance work.
  • Check DuckDB projection pushdown. Run EXPLAIN on the DuckDB query and confirm it reads only the referenced columns and prunes partitions by overlay_code; a full-file scan means partitioning or filter pushdown is not engaging.
  • Round-trip a checksum. After any migration, count rows and sum ST_Area of the geometry on both sides. Equal counts and areas within floating tolerance prove nothing was dropped or reprojected in transit.

Failure recovery

  • Analytical query is locking the editorial DB. This is the classic symptom that you are running an analytical scan on a transactional engine. Move the heavy read to a GeoParquet snapshot in DuckDB so it can never contend for locks on the live tables again.
  • DuckDB spatial join is slow on huge extracts. The spatial extension has no persistent index, so a naive cross-region join degrades. Partition the Parquet by jurisdiction and filter on the partition key first so the bbox pre-filter has less to scan; if you truly need a persistent spatial index and constant point lookups, that workload wanted PostGIS.
  • Snapshot CRS drift. If a released snapshot renders offset, a reprojection slipped in during export. Recover by re-exporting from the PostGIS system of record with the SRID asserted, and quarantine the bad file rather than patching it in place.
  • Corrupt or partial Parquet write. Because snapshots are immutable, recovery is trivial: discard the partial file and re-export. Never append into a snapshot other readers may already be reading — write to a new path and swap the pointer atomically.

Frequently asked questions

Is DuckDB a replacement for PostGIS for zoning data?

No — they solve different problems. PostGIS is a transactional system of record with mature spatial indexing and ACID writes; DuckDB plus GeoParquet is a read-mostly analytical engine over immutable files. Most production platforms keep the mutable overlays in PostGIS and offload heavy analytical scans to DuckDB snapshots, rather than choosing one exclusively.

Can DuckDB use a spatial index like PostGIS GiST?

Not a persistent one yet. The DuckDB spatial extension applies a bounding-box pre-filter on ST_Intersects-style joins, which helps, but there is no on-disk R-tree you build and reuse across sessions. For constant indexed point lookups and nearest-neighbor queries, PostGIS GiST is still the stronger choice; DuckDB shines on full-column vectorized scans instead.

How do I version zoning overlays with each approach?

In DuckDB plus GeoParquet, a version is simply a new immutable file or partition per release, so querying “overlays as of Q1” is a path selection. In PostGIS you version in-table with bi-temporal valid_from and valid_to columns or by copying tables. The Parquet approach makes snapshots essentially free; the PostGIS approach keeps them queryable alongside live edits.

Why does my ST_Intersects query seq-scan in PostGIS?

Almost always a missing or unused GiST index. Confirm a GIST index exists on the geometry column, run EXPLAIN ANALYZE to see whether the planner chooses it, and check that both geometries share an SRID — a mismatch prevents index use. Tuning this is covered under spatial database indexing and performance.

Does GeoParquet preserve CRS and field types on conversion?

Yes, when written correctly — GeoParquet stores the CRS in file-level metadata and preserves column types. The failure mode is losing it during export or a mismatched load, so assert the CRS is present on both write and read. Lossless format conversion, including CRS preservation, is its own topic in the geospatial format conversion guide.