DuckDB Spatial Analytical SQL for GIS

Chunking Large GeoParquet Writes

Writing a two-hundred-million-row spatial result to a single GeoParquet file materializes every row group in memory before the first byte lands, so the write OOMs long before the query that produced it ever did. This walkthrough sits under batch processing pipelines and turns that all-or-nothing flush into a chunked, memory-bounded write: PARTITION_BY splits the output into hive-partitioned files, a bounded ROW_GROUP_SIZE caps how much geometry the writer buffers at once, geometry is validated before the flush so no chunk aborts mid-stream, and a re-read step confirms the partitions are the predicate-pushdown-friendly layout you meant to produce.

Root-Cause Analysis: why large GeoParquet writes fail

A COPY (SELECT ...) TO is not a trickle to disk — the Parquet writer accumulates a full row group of encoded values, geometry included, before it flushes that group and starts the next. Geometry is the expensive column: each polygon serializes to a variable-length WKB blob, and a row group of a hundred thousand dense polygons can hold hundreds of megabytes of encoder state on its own. Large GeoParquet writes fail along four axes, and each maps to one of the steps below:

  • Unbounded row-group buffering. With the default row-group size, a wide geometry column buffers far more encoder state than an interactive memory_limit expects, and the write spikes past the ceiling with no spill relief. Cap it explicitly with ROW_GROUP_SIZE.
  • A single monolithic output file. One out.parquet for the whole dataset forces the writer to hold partition state for every region at once and produces a file no reader can prune. Split it with PARTITION_BY into a hive-partitioned tree.
  • A poison geometry mid-flush. One invalid ring or unclosed polygon makes the WKB encoder — or a pre-write ST_ transform — raise partway through a row group, discarding the whole in-flight chunk. Repair before the write, not after the crash.
  • A layout that defeats later reads. Partitions written without stable statistics or a sane row-group granularity force every downstream query to scan the whole tree, erasing the pushdown that made GeoParquet worth choosing over Shapefile in the first place.

The fix is a four-step discipline that keeps per-chunk memory bounded on write and keeps the output prunable on read. The steps below follow the same order.

Four-step memory-safe GeoParquet write pipeline in DuckDB A left-to-right pipeline of four numbered stages. Step 1, Guardrails, sets memory_limit and a fast temp_directory so an over-budget write spills instead of failing. Step 2, Validate, repairs geometry with ST_MakeValid and filters on ST_IsValid so no chunk aborts mid-flush. Step 3, COPY TO, writes with PARTITION_BY on a natural key and a bounded ROW_GROUP_SIZE into a hive-partitioned tree of region directories. Step 4, Verify, re-reads the partitions to confirm feature counts, a single CRS, and row-group statistics that keep reads prunable. An annotation underneath notes that per-chunk memory is capped by the row-group size while the partition tree keeps later reads prunable. CAP PER-CHUNK MEMORY ON WRITE → KEEP PARTITIONS PRUNABLE ON READ 1 · GUARDRAILS memory_limit temp_directory (NVMe) spill, don't fail 2 · VALIDATE ST_MakeValid(geom) filter ST_IsValid no mid-flush abort 3 · COPY TO PARTITION_BY (region) ROW_GROUP_SIZE n bounded per chunk 4 · VERIFY counts & CRS re-read partitions prunable layout ROW_GROUP_SIZE caps per-chunk memory on write; the partition tree keeps later reads prunable.

Step 1 — Set a memory ceiling and a fast spill target

The write inherits the same connection guardrails as any batch job, but the setting that matters most here is the spill path: when a row group’s encoder state approaches memory_limit, DuckDB spills to temp_directory rather than aborting the COPY. Point it at fast local storage or the spill turns into an I/O cliff.

import duckdb

def open_writer_connection(spill_dir: str = "/data/duckdb_spill") -> duckdb.DuckDBPyConnection:
    con = duckdb.connect(":memory:")
    con.execute("INSTALL spatial; LOAD spatial;")

    # Engine parallelism. Each writer thread buffers its own row group, so peak
    # write memory scales with threads — lower this before lowering memory_limit.
    con.execute("SET threads = 8;")

    # Hard ceiling for the write's buffered encoder state. On breach DuckDB spills
    # to temp_directory instead of OOM-killing the COPY mid-flush.
    con.execute("SET memory_limit = '12GB';")

    # Spill target — MUST be local NVMe. A spilling geometry write on a network
    # mount degrades throughput 10-50x versus local storage.
    con.execute(f"SET temp_directory = '{spill_dir}';")

    # Cap the spill so one oversized partition can't fill the disk and take the
    # whole node down with it.
    con.execute("SET max_temp_directory_size = '50GB';")

    # Drops the row-order barrier so partitioned writes parallelize. Trade-off:
    # output row order within a partition is undefined — sort on read if needed.
    con.execute("SET preserve_insertion_order = false;")
    return con

The spill mechanics differ between an in-memory and a persisted database, and the same buffer-manager behaviour governs how a large raster or geometry write pressures memory — the boundaries are laid out in memory limits for large raster data. Confirm the preconditions before the first COPY:

Step 2 — Validate and repair geometry before the write

A single invalid geometry raises inside the WKB encoder or a pre-write ST_ transform and discards the whole in-flight row group, so gate the geometry before it reaches COPY. Repair deterministically with ST_MakeValid and drop anything still unrepairable to a side relation rather than letting it abort the write.

-- Quarantine offenders first so you can inspect them, not just discard them.
CREATE TABLE parcels_bad AS
SELECT * FROM parcels WHERE NOT ST_IsValid(geom);

-- Materialize a write-safe relation: repaired geometry, single CRS assumed.
CREATE TABLE parcels_clean AS
SELECT
    parcel_id,
    region,
    ST_MakeValid(geom) AS geom     -- closes rings, fixes self-intersections
FROM parcels
WHERE ST_IsValid(geom)
   OR ST_IsValid(ST_MakeValid(geom));  -- keep rows repair can actually fix

Validation before a chunked write is the same guard that keeps a partition from aborting two hours into a run, and it is cheap relative to a failed flush. If more than a few percent of rows fail ST_IsValid, treat it as a data-source problem upstream rather than repairing row-by-row on every write — the topology errors otherwise cascade into wrong areas and empty joins downstream.

Step 3 — Write with PARTITION_BY and a bounded ROW_GROUP_SIZE

Now issue the chunked write. PARTITION_BY splits the output into a hive-partitioned directory tree — one subtree per key value — so no single file holds the whole dataset, and ROW_GROUP_SIZE caps how many rows the writer buffers before flushing a group, which is what bounds per-chunk memory for the wide geometry column.

-- Chunked GeoParquet write: hive partitions cap file size, ROW_GROUP_SIZE caps
-- the encoder's buffered memory per flush.
COPY (
    SELECT parcel_id, region, geom      -- geom serializes to WKB in the Parquet file
    FROM parcels_clean
) TO 'out/parcels'
(
    FORMAT PARQUET,
    PARTITION_BY (region),              -- writes out/parcels/region=<value>/*.parquet
    ROW_GROUP_SIZE 100_000,             -- rows per group: smaller = lower write memory
    COMPRESSION 'zstd',                 -- best ratio for WKB blobs; trades CPU for size
    OVERWRITE_OR_IGNORE true            -- idempotent re-run: safe to overwrite a partition
);

Three knobs carry the memory and read-performance trade-off, and they interact:

Setting Lower value Higher value Guidance
ROW_GROUP_SIZE less write memory, more row groups, finer read pushdown more write memory, fewer groups, coarser pushdown Start at 100k for dense polygons; drop toward 50k if the write spills
PARTITION_BY cardinality fewer, larger files many small files (metadata overhead) Partition on a key with tens-to-hundreds of values, not millions
COMPRESSION faster write (snappy) smaller files (zstd) zstd for archival GeoParquet; snappy when write latency dominates

The row-group boundary is also the unit of predicate pushdown on read: DuckDB keeps min/max statistics per row group, so a bounded, sorted row group lets a later query skip groups whose bounding coordinates fall outside a filter — the columnar advantage that makes GeoParquet parsing fast. To make those statistics sharp, cluster the write by geometry locality so nearby rows share a row group:

-- Order by a Z-ish key so each row group's coordinate range is tight, which
-- sharpens the min/max statistics readers use to skip groups.
COPY (
    SELECT parcel_id, region, geom
    FROM parcels_clean
    ORDER BY ST_XMin(geom), ST_YMin(geom)   -- spatial locality within each partition
) TO 'out/parcels'
(FORMAT PARQUET, PARTITION_BY (region), ROW_GROUP_SIZE 100_000, COMPRESSION 'zstd', OVERWRITE_OR_IGNORE true);

For a dataset too large to write in one COPY even with partitioning, drive one partition per call from the checkpointed loop in batch processing pipelines, so a crash costs one partition’s write rather than the whole tree. Each call writes to its own key subtree and is independently restartable.

Step 4 — Verify feature counts, CRS, and the row-group layout

A write that succeeds silently can still be wrong — a dropped CRS, a lost partition, or a row-group layout that defeats pushdown. Re-read the partitions and assert the invariants before declaring the dataset done. Hive discovery lets you read the whole tree back with the partition column reconstructed from the path.

-- Count features per partition and confirm nothing was dropped in the write.
SELECT region, COUNT(*) AS feature_count
FROM read_parquet('out/parcels/**/*.parquet', hive_partitioning = true)
GROUP BY region
ORDER BY region;

-- Assert a single CRS across every written partition — a silent mix is the
-- classic cause of wrong areas and empty joins downstream.
SELECT DISTINCT ST_SRID(geom) AS srid
FROM read_parquet('out/parcels/**/*.parquet', hive_partitioning = true);

CRS drift is silent and corrupting — no error is raised when partitions disagree on units — so this assertion belongs in the pipeline, not just in a one-off check; the reprojection rules are in the CRS mapping and transformations reference. Confirm pushdown actually works by filtering on the partition key and reading the plan: a hive-partitioned read should prune whole subtrees rather than scanning every file.

-- The plan should show only the region=north subtree being scanned, not all partitions.
EXPLAIN
SELECT parcel_id, geom
FROM read_parquet('out/parcels/**/*.parquet', hive_partitioning = true)
WHERE region = 'north';

Read the plan for two things: a partition filter that lists only the matching subtree (proof the hive layout prunes), and a row-group count far below the total (proof the ROW_GROUP_SIZE statistics let the reader skip groups). If the plan scans every partition, the filter is not on the partition column; if it reads every row group, the write was not clustered by geometry locality and the min/max statistics are too loose to help. Once verified, the partitioned dataset re-reads cleanly into downstream steps — including the zero-copy handoff described in DuckDB to GeoPandas sync — and its per-row-group statistics behave like a coarse pre-index that a later R-tree index refines.

See also

Up: Batch Processing Pipelines · Python & DuckDB Integration Workflows