Tuning Vector Size for Spatial Aggregates

DuckDB processes data in fixed 2,048-row column vectors, and the number is a compile-time constant (STANDARD_VECTOR_SIZE) you cannot change with SET — so “tuning vector size” for a spatial aggregate really means controlling everything around that fixed lane: thread and memory budgets, morsel and row-group sizing, and above all keeping variable-length geometry from bloating the vector’s blob heap and destroying cache locality. This walkthrough sits under vectorized aggregations and targets the specific failure where an aggregate that should stream in a single columnar pass instead thrashes memory because each vector is dragging multi-kilobyte WKB payloads through the CPU cache.

Root-Cause Analysis of Poor Vectorization #

A spatial aggregate that under-vectorizes rarely errors; it just runs several times slower than the row count justifies. The causes cluster into four failure modes, and only one of them is about the vector width itself — which you cannot touch — so the diagnosis matters.

• Oversized variable-length geometry per lane. A vector holds 2,048 values, but a GEOMETRY value is a variable-length WKB blob stored in a separate heap that the vector’s fixed-width slots merely point into. When each geometry is a dense multipolygon of thousands of vertices, that heap balloons to tens of megabytes per chunk, evicting the fixed-width aggregate state from cache. The SIMD-friendly kernels still run, but every access chases a pointer into cold memory. This is the dominant cause and it is a data shape problem, not a settings problem.
• Implicit per-row fallback. A scalar Python UDF, or an implicit GEOMETRY → VARCHAR cast inside the aggregate, drops the engine off the batched kernel onto row-at-a-time evaluation. The vector is still 2,048 wide; it is simply no longer processed as a batch.
• Thread and morsel starvation. DuckDB’s parallelism is morsel-driven: the scan is chopped into morsels (row-group-sized units) handed to worker threads. Too few threads, or a single monster row group, leaves cores idle while one thread grinds through an oversized morsel. This under-utilizes the vectorized pipeline without changing any vector.
• Memory pressure forcing spill mid-aggregate. When the hash table plus the geometry heap exceed memory_limit, the aggregate spills, and every spilled chunk re-serializes its geometry to disk and back. The streaming pass becomes an I/O cliff.

The through-line: you tune the inputs and budgets around a fixed 2,048-row vector, never the vector. Everything below is a lever on those inputs.

Deterministic Configuration #

The controllable knobs are threads, memory, spill, and row order. None of them resizes the vector; together they decide how efficiently the fixed vector streams.

INSTALL spatial; LOAD spatial;

-- Match physical cores so morsels distribute one-per-core. Hyperthread siblings
-- share SIMD units, so oversubscription slows the batched geometry kernels
-- rather than speeding them; the vector width is unaffected either way.
SET threads = 8;

-- Ceiling for the hash table PLUS the variable-length geometry heap the vectors
-- point into. Too low → spill that re-serializes WKB; too high on a shared host
-- → OS OOM-kill of the whole process.
SET memory_limit = '12GB';

-- Stream, don't buffer: releasing insertion order lets aggregate morsels retire
-- out of order and keeps the pipeline moving instead of holding chunks for sort.
SET preserve_insertion_order = false;

-- Spill target on fast local NVMe. A spilling aggregate re-serializes each
-- vector's geometry heap here, so a network mount turns a spill into a stall.
SET temp_directory = '/mnt/nvme/duckdb_spill';

Confirm the preconditions before running a heavy aggregate:

• Geometry is native GEOMETRY (WKB-backed) so the vector points at a compact cached representation, not a re-parsed text column — the storage difference is dissected in ST_Geometry vs WKB storage.
• Grouping keys are scalar (integers, hashes), so the aggregate’s fixed-width state stays cache-resident alongside the geometry heap.
• Files are written with a sane row-group size so morsels are neither tiny nor monstrous (covered next).
• memory_limit exceeds the widest partition’s geometry-heap footprint, or a spill directory absorbs the overflow.

Optimized Execution Pattern #

The single highest-leverage move is shrinking what each vector lane carries. If the aggregate only needs a coordinate, a cell key, or a simplified outline, derive that before the heavy geometry ever enters the vector. Compare:

-- ANTI-PATTERN: full dense multipolygons ride every vector into the aggregate.
-- Each 2,048-row chunk drags a huge WKB heap through cache; ST_Union then
-- balloons the working set. Correct, but memory-thrashing and slow.
SELECT region_id, ST_Union(geom) AS dissolved
FROM parcels           -- geom = multipolygons, thousands of vertices each
GROUP BY region_id;

-- OPTIMIZED: reduce precision first so the vector's geometry heap shrinks, and
-- group on a scalar key so the fixed-width aggregate state stays cache-resident.
WITH thinned AS (
  SELECT
    region_id,                                    -- scalar grouping key
    ST_ReducePrecision(geom, 0.001) AS geom       -- fewer vertices per lane
  FROM parcels
)
SELECT region_id, ST_Union(geom) AS dissolved
FROM thinned
GROUP BY region_id;

The behavioral change is that each vector now carries thinner blobs, so more useful rows fit in cache per chunk and the aggregate streams instead of thrashing. Where the aggregate does not need topology at all, reducing geometry to a scalar first is even cheaper — snap to a grid cell and let the vector carry two integers rather than a polygon, exactly the reduction the parent vectorized aggregations reference recommends.

Row-group size on write is the other side of the same coin: it sets the morsel granularity that feeds those vectors. A row group is a multiple of the vector width, and the default of 122,880 rows (sixty vectors) is right for most workloads — but a wide geometry column benefits from smaller groups so no single morsel holds an unmanageable heap.

-- Smaller row groups when the geometry column is wide, so each morsel's heap
-- stays cache-friendly and parallelism stays balanced across threads.
COPY (SELECT * FROM parcels)
TO 'parcels.parquet'
(FORMAT PARQUET, ROW_GROUP_SIZE 61_440);   -- 30 vectors per group vs the 60 default

Diagnostic Queries & Plan Validation #

Reading the plan is the only reliable way to confirm the aggregate stayed on the batched kernel and streamed rather than spilled.

-- Measured plan with per-operator timing and any spill indicator.
EXPLAIN ANALYZE
SELECT region_id, COUNT(*), ST_Collect(geom)
FROM thinned
GROUP BY region_id;

Assert three things in the output:

1. No row-at-a-time fallback. A ROW_EXECUTION node or a scalar ST_ function evaluated inside the aggregation phase means the batched kernel was bypassed — usually a Python UDF or an implicit GEOMETRY → VARCHAR cast. Replace it with a native ST_ equivalent so the 2,048-row vector is processed as a batch again.
2. No spill on the group. A non-empty spill or temporary-file indicator on the HASH_GROUP_BY means the geometry heap plus hash table exceeded memory_limit. Reduce precision, coarsen the group, or raise the ceiling.
3. Balanced thread utilization. If one thread dominates operator_timing, a single oversized row group is starving the others — rewrite the source with a smaller ROW_GROUP_SIZE.

For Python-driven aggregation over results too large to materialize at once, pull them as bounded Arrow record batches so no single fetch holds the whole geometry heap in memory. Each batch is itself a run of full-width vectors, so streaming preserves the SIMD advantage:

import duckdb

con = duckdb.connect("parcels.duckdb")
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("SET threads = 8; SET memory_limit = '12GB';")

# Stream the aggregate in bounded batches instead of one giant fetch, so the
# client never holds the entire geometry heap resident at once.
reader = con.execute("""
    SELECT region_id, COUNT(*) AS n, ST_AsWKB(ST_Collect(geom)) AS wkb
    FROM thinned
    GROUP BY region_id
""").fetch_record_batch(100_000)   # rows per Arrow batch

for batch in reader:
    handle(batch)   # process one bounded chunk, then release it

Whether the aggregate fits in memory at all, and how spill is sized when it does not, is governed by the in-memory vs disk storage boundaries; the batch-streaming pattern above composes with the orchestration in batch processing pipelines.

Geometry Validation & Fallback Routing #

Invalid geometry is not just a correctness hazard — a self-intersecting polygon can inflate the vertex count that a ST_Union drags through each vector, worsening the exact cache pressure this page is about. Validate, then simplify, before the aggregate:

-- Repair offenders so the aggregate isn't fed pathological vertex counts.
UPDATE parcels SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);

-- Optional: simplify with a tolerance to further shrink the per-vector heap
-- when exact boundaries are not required downstream.
SELECT region_id, ST_Union(ST_SimplifyPreserveTopology(geom, 0.5)) AS dissolved
FROM parcels
GROUP BY region_id;

When a single group still overflows memory despite thinning, fall back to chunked execution: aggregate coarse sub-groups, then combine their partial results. This keeps every vector’s heap bounded regardless of how skewed the group sizes are:

-- Two-level fold: collect within sub-groups first (cheap, streams), then union
-- the far smaller set of partials. Bounds the per-vector heap on skewed data.
WITH partials AS (
  SELECT region_id, sub, ST_Collect(geom) AS g
  FROM (SELECT region_id, hash(id) % 16 AS sub, geom FROM parcels)
  GROUP BY region_id, sub
)
SELECT region_id, ST_Union(g) AS dissolved
FROM partials
GROUP BY region_id;

Related #

See also

• Vectorized aggregations — the streaming aggregate model and scalar-key discipline this tuning guide refines.
• ST_Geometry vs WKB storage — why the column type decides how compact each vector’s geometry heap is.
• In-memory vs disk storage — the memory ceiling and spill behavior that decide whether an aggregate streams.

Up: Vectorized Aggregations · Modern Spatial SQL Query Patterns