DuckDB Spatial Analytical SQL for GIS

Row-Group Sizing for Spatial Scans

The default row-group size is a row count, and a row count is a bad proxy for size the moment the payload is geometry — this walkthrough, part of the partitioning and file layout reference, covers how to derive the right row count from the bytes your geometries actually occupy, why the same setting behaves differently on a point layer and a coastline layer, and how to confirm from the file itself that the groups came out the size you intended.

Root-Cause Analysis: why one setting cannot serve both

A row group is the unit at which Parquet writes statistics and at which a reader decides to skip. Its cost appears twice, in opposite directions, and the geometry column is what makes the two costs diverge.

  • On the write, the group is a buffer. Every writer thread holds one whole row group in memory before flushing it. A group of 122,880 points is a few megabytes; the same group of dense multipolygons can be several gigabytes, and with six threads that is the peak memory of the write. This is the failure that stops a load completing.
  • On the read, the group is the granularity of skipping. A predicate can eliminate a group only in its entirety, so larger groups mean coarser pruning — a file of one group prunes nothing at all, however good its statistics are. This is the failure that makes a query slow without ever erroring.
  • The two pull in the same direction only by accident. Smaller groups reduce the write buffer and sharpen pruning, which suggests going small; what pushes back is footer overhead, shorter sequential reads, and per-group decompression setup. The optimum is a byte size, not a row count.
  • The default was chosen for fixed-width columns. For integers and dates, rows are a fine proxy for bytes, and the default lands in a sensible byte range. Geometry breaks that proxy by three or four orders of magnitude depending on the layer.

The distinguishing question is what a single row costs. Once the average serialised geometry size is known, the row count follows from the byte target arithmetically.

The default row count across four layers, in bytes At 122,880 rows a point layer yields a 4 MB group, simple polygons 98 MB, municipal boundaries 2.7 GB and coastlines about 170 GB. LAYER AVG BYTES / GEOMETRY GROUP SIZE AT THE DEFAULT points ~32 B 4 MB — too small, footer-heavy simple polygons ~800 B 98 MB — close to ideal municipal boundaries ~22 KB 2.7 GB — spills, prunes coarsely coastlines ~1.4 MB ~170 GB — cannot be written

Identical row count in all four rows. Four orders of magnitude between the first and the last.

Deterministic Configuration

The write is where the sizing decision bites, and its peak is the thread count times the group buffer.

INSTALL spatial; LOAD spatial;

-- Peak write memory ≈ threads × row_group_size × avg_bytes_per_row.
-- Lower threads before raising memory_limit: it reduces the peak rather
-- than accommodating it.
SET memory_limit = '12GB';
SET threads = 4;

SET temp_directory = '/var/tmp/duckdb_write';
SET max_temp_directory_size = '60GB';

Optimized Execution Pattern

The pattern is to measure first and derive the row count, rather than accepting a default whose assumptions do not hold for this column.

-- ANTI-PATTERN: the default, on a layer where a row is 22 KB.
COPY parcels TO 'parcels.parquet' (FORMAT PARQUET);   -- ~2.7 GB per group
-- PATTERN: measure the payload, then derive the row count from a byte target.
WITH sizing AS (
    SELECT avg(octet_length(ST_AsWKB(geometry))) AS avg_bytes FROM parcels
)
SELECT
    avg_bytes,
    (128 * 1024 * 1024 / avg_bytes)::BIGINT AS rows_for_128mb
FROM sizing;
-- → e.g. avg_bytes = 22000 → about 6,100 rows per group

COPY (
    SELECT * FROM parcels ORDER BY region, land_use
) TO 'parcels.parquet' (FORMAT PARQUET, ROW_GROUP_SIZE 6100);

The ORDER BY is not incidental here. Row-group size decides how finely a predicate can prune; write order decides whether it can prune at all. Halving the group size on a randomly ordered file doubles the number of groups that each still span the full value range, which is twice as much metadata for exactly the same amount of skipping — none.

Diagnostic Queries & Plan Validation

The written file reports its own layout, so verification is a query rather than an inference.

-- What did the writer actually produce? One row per row group, with its
-- row count and compressed size. Compare against the target.
SELECT row_group_id,
       row_group_num_rows                       AS rows,
       sum(total_compressed_size) / 1024 / 1024 AS mb
FROM parquet_metadata('parcels.parquet')
GROUP BY row_group_id, row_group_num_rows
ORDER BY row_group_id
LIMIT 20;

Diagnostic — a file with one row group: the most damaging outcome and the easiest to miss, because the file is valid, readable and fast to write.

-- One group means statistics can never prune. Any file that matters should
-- have tens of groups at least.
SELECT count(DISTINCT row_group_id) AS row_groups
FROM parquet_metadata('parcels.parquet');
Reading a row-group census One group means no pruning; wildly varying groups mean skew or no sort; tens of groups near the target is healthy; thousands of tiny groups means footer overhead. WHAT THE CENSUS SHOWS WHAT IT MEANS exactly one row group statistics can never prune — rewrite it groups varying wildly in size skew, or geometry of very uneven density tens of groups near the target healthy — this is what to aim for thousands of tiny groups footer overhead now dominates the read Three costs pulling in two directions Write buffer and pruning granularity favour smaller groups; footer overhead and per-group setup favour larger ones; the band between them is 64 to 256 MB. COST AS GROUPS GET LARGER ARGUES FOR write buffer grows linearly smaller groups pruning granularity coarsens smaller groups footer + per-group setup shrinks per byte larger groups The band where none dominates is roughly 64–256 MB of compressed data per group. Below it the metadata wins; above it the buffer does.

Two votes for small, one for large, and the winner depends on which one is currently hurting.

Geometry Validation & Fallback Routing

When a layer contains geometries whose sizes differ by orders of magnitude — a national layer holding both small urban parcels and one enormous national-park boundary — a single row count cannot be right for all of it. The fallback is to split the write by size class rather than to compromise on one setting.

-- Two writes, two settings, one logical dataset. The heavy tail gets small
-- groups so the buffer stays bounded; the bulk gets larger ones so the
-- footer stays proportionate.
COPY (SELECT * FROM parcels WHERE octet_length(ST_AsWKB(geometry)) <  50000 ORDER BY region)
  TO 'parcels/size=small' (FORMAT PARQUET, ROW_GROUP_SIZE 40000);

COPY (SELECT * FROM parcels WHERE octet_length(ST_AsWKB(geometry)) >= 50000 ORDER BY region)
  TO 'parcels/size=large' (FORMAT PARQUET, ROW_GROUP_SIZE 500);

Reading both back is a single glob, so the split is invisible to consumers. What it buys is that neither the write buffer nor the footer is sized by the wrong half of the distribution.

Why the read side and the write side disagree

The uncomfortable part of this setting is that the two costs it controls do not have the same shape. Write-side cost is a hard ceiling: exceed it and the job fails, loudly, at a specific moment. Read-side cost is a gradient: a group that is twice as large as it should be does not fail anything, it just reads twice as much as it needed to, on every query, forever.

That asymmetry biases the decision, and correctly so. A setting that is slightly too small wastes some footer space and shortens sequential reads, both of which are measurable and modest. A setting that is too large risks a write that cannot complete on the largest partition in the dataset — which is the partition you will discover last, three hours into a national load. When in doubt, size against the heaviest partition rather than the average one and accept a slightly small group everywhere else.

-- Size against the tail, not the mean. This reports the byte cost of a group
-- at the current setting for the heaviest partition, which is the one that
-- decides whether the write completes at all.
SELECT region,
       count(*)                                            AS rows,
       avg(octet_length(ST_AsWKB(geometry)))               AS avg_bytes,
       30000 * avg(octet_length(ST_AsWKB(geometry))) / 1e6 AS mb_per_group
FROM parcels
GROUP BY region
ORDER BY mb_per_group DESC
LIMIT 5;

Frequently Asked Questions

What row-group size should I use for geometry?

Derive it: divide a byte target of roughly 64–256 MB by the average serialised geometry size for that layer. On dense boundaries that often lands in the low thousands of rows rather than the default’s 122,880. Copying a number from another dataset is how a setting that worked for points ends up unwritable for polygons.

Why does my write run out of memory when the source table fits fine?

Because a row group is buffered whole before it is flushed, and every writer thread holds one. Peak write memory is roughly threads times group size times bytes per row, which on dense geometry can be tens of gigabytes even though the table itself is comfortable. Lower threads first — it reduces the peak rather than accommodating it.

Do smaller row groups always prune better?

They prune more finely, but only if the data was sorted on the filter column. On a randomly ordered file every group spans the full value range whatever its size, so halving the group size doubles the metadata and skips nothing extra. Sort order is the prerequisite; group size is the granularity.

How do I check what a file actually contains?

parquet_metadata reports one row per column chunk with its row group, row count and compressed size, so a single grouped query gives you the census. It is worth running after any change to the write, because a mistyped setting produces a file that is valid, fast to write, and useless to query.

Up: Partitioning and File Layout