Spatial Data Archival · Cold Storage

Calculating Optimal Row Group Size for Spatial Queries

Data engineers, GIS archivists, and cloud architects who tier large vector archives to object storage hit the same wall: spatial predicate queries (ST_Intersects, ST_DWithin, ST_Contains) scan far more blocks than the filter geometry should touch, and cold-storage egress bills climb accordingly. The cause is that default columnar row-group sizing targets uniform tabular analytics — it assumes near-constant per-row byte width and no spatial locality. Serialized geometry violates both assumptions: WKB payloads vary by orders of magnitude between a survey point and a coastline multipolygon, and unsorted rows scatter neighbouring features across every block. This page gives a deterministic, execution-ready procedure for calculating row-group boundaries that preserve predicate pushdown, bound min/max envelope overlap, and keep ranged-GET retrieval costs low.

Sizing Workflow

The routine moves from profiling to a validated, spatially clustered write:

Row-group sizing pipeline A left-to-right flow of five stages: profile geometry size distribution, compute R_opt, cap at 1,000,000 rows, Hilbert-cluster rows, and write plus validate statistics. The final write-and-validate stage is highlighted as the gated output. Profile geometry size distribution Compute R_opt from the formula Cap at 1,000,000 rows Hilbert-cluster rows Write + validate row-group stats

This procedure assumes the source is already a columnar archive (Parquet/GeoParquet) and that a Compression Tuning & Storage Optimization baseline — codec and compression level — is in place; row-group sizing is tuned after the codec is fixed, because the expected compression ratio feeds directly into the row-count formula below.

Step 1: Profile Geometry Payload Distribution

Serialized spatial payloads exhibit high byte-size variance. Unchecked variance forces oversized row groups, triggering full-block decompression during spatial filtering and inflating cold-storage egress.

import pyarrow.parquet as pq
import numpy as np

# Sample 10,000+ records from the target dataset
table = pq.read_table("datasets/cadastre/raw/parcels_2024.parquet",
                      columns=["geometry_wkb"])
wkb_bytes = table.column("geometry_wkb").to_pylist()
sizes = np.array([len(b) for b in wkb_bytes], dtype=np.float64)

p50, p90, p99 = np.percentile(sizes, [50, 90, 99])
g_avg = sizes.mean()
sigma_g = sizes.std()
variance_ratio = sigma_g / g_avg

print(f"G_avg: {g_avg:.0f}B | sigma_G: {sigma_g:.0f}B | ratio: {variance_ratio:.2f}")

Validation gate: if variance_ratio > 0.6, halt archival promotion. Isolate high-complexity polygons (p99 > 500 KB) into a separate tier or apply geometry simplification before grouping. High variance directly correlates with false-positive block scans during ST_Intersects evaluation, and it is also the dominant cause of poor ratios when tuning ZSTD compression for GeoParquet archives — so resolving it here pays off twice.

Step 2: Derive Target Row Count per Group

Optimal row-group size ($R_{opt}$) balances block-level I/O efficiency against spatial index granularity. Apply the deterministic formula:

$R_{opt} = \lfloor (T_{block} \times C_{ratio}) / (G_{avg} + A_{attr}) \rfloor$

Parameter definitions:

  • $T_{block}$: target compressed block size. Use 128MB for standard object storage, 256MB for deep-archive tiers.
  • $C_{ratio}$: expected compression ratio. Spatial WKB typically yields 1.8–3.2x with ZSTD; pull the exact figure for your codec from your ZSTD Level Configuration for Spatial Files baseline rather than guessing.
  • $G_{avg}$: average serialized geometry byte size (from Step 1).
  • $A_{attr}$: average serialized attribute payload per row (non-geometry columns).
target_block_mb = 128
c_ratio = 2.5
a_attr = 45  # bytes, measured from the non-geometry columns

r_opt = int((target_block_mb * 1024 * 1024 * c_ratio) / (g_avg + a_attr))

# Hard cap to prevent spatial-join materialization OOM
R_FINAL = min(r_opt, 1_000_000)
print(f"Calculated R_opt: {r_opt} | Enforced cap: {R_FINAL}")

Exceeding 1,000,000 rows per group introduces memory pressure during spatial-join materialization and increases bounding-box overlap probability. Where a single dataset spans wildly different geographic densities, split it along the same boundaries you use for spatial partitioning techniques before applying the cap, so no group straddles two partitions.

Unsorted versus Hilbert-sorted row-group envelopes Left panel: an unsorted file with four overlapping row-group envelopes spanning the full extent, so every block is scanned. Right panel: a Hilbert-sorted file whose space-filling curve packs each row group into a tight, non-overlapping quadrant, so the engine skips irrelevant blocks. Unsorted file envelopes overlap the whole extent Hilbert-sorted file tight, non-overlapping envelopes RG1 RG2 RG3 RG4 RG1 RG2 RG3 RG4 Overlapping envelopes force every block to be scanned; tight envelopes let the engine skip non-matching blocks.

Step 3: Apply Spatial Clustering Prior to Grouping

Row groups must be spatially coherent. Unsorted data scatters geographic regions across blocks, defeating min/max statistics and forcing full-block decompression. DuckDB’s ST_Hilbert function takes a geometry and a BOX_2D extent and returns a uint64 Hilbert-curve key, so the dataset extent must be computed first.

-- Step 1: compute the dataset extent
CREATE TEMPORARY TABLE dataset_extent AS
SELECT ST_Extent_Agg(geometry) AS ext FROM archive_source;

-- Step 2: sort rows along the Hilbert curve, then write
COPY (
  SELECT s.*
  FROM archive_source s, dataset_extent e
  ORDER BY ST_Hilbert(s.geometry, e.ext)
)
TO 'datasets/cadastre/cold/parcels_optimized.parquet'
(FORMAT PARQUET, ROW_GROUP_SIZE 500000, COMPRESSION ZSTD);

Sorting by a Hilbert curve aligns physical storage with spatial locality. Each row group’s min/max bounding-box envelope then tightly encloses its contents, letting the query engine skip irrelevant blocks during ST_DWithin and ST_Contains evaluations. Without this step, spatial predicate pushdown degrades to sequential full-table scans regardless of how carefully $R_{opt}$ was chosen.

Turning the Byte Target Into a Row Count

The writer takes a row count, not a byte target, so the last step of the calculation is a division — and it is the step most often done once and never revisited. Average compressed bytes per row is not a property of the schema; it is a property of the schema and the data in that partition. Dense polygon geometry with many vertices per feature can be twenty times heavier than point features under the same column layout, so a single global row count produces row groups that differ in size by an order of magnitude across an archive.

One byte target, four row counts Four dataset classes with different average compressed row sizes each converted to the row count that yields a 128 megabyte row group: sensor points at 745,000 rows, parcels at 210,000, building footprints at 122,000, and hydrology lines at 28,000. target: 128 MB compressed per row group ÷ measured bytes per row sensor points 180 B/row 745,000 rows cadastral parcels 640 B/row 210,000 rows building footprints 1.1 KB/row 122,000 rows hydrology lines 4.8 KB/row 28,000 rows — a 745,000-row setting would produce a 3.4 GB group here Bar length is bytes per row, not group size: all four rows produce the same 128 MB group. Measure bytes per row per dataset, per partition class — never inherit the number from another archive.

Derive the divisor from a written sample rather than from an estimate. Write ten thousand representative rows with the production writer configuration, divide the resulting file size by the row count, and use that. Estimating from uncompressed geometry size overstates by a factor that varies with the codec level, and estimating from the source shapefile overstates it further, because the conversion has usually dropped indexes and normalised attributes along the way.

Validation & Verification

Run these gates against the written file before promoting it to a cold tier. Expected output is annotated inline.

import pyarrow.parquet as pq

meta = pq.read_metadata("datasets/cadastre/cold/parcels_optimized.parquet")

prev = None
overlaps = 0
for i in range(meta.num_row_groups):
    rg = meta.row_group(i)
    # column 0 here is the X ordinate of the bbox; adapt to your schema
    min_x = rg.column(0).statistics.min
    max_x = rg.column(0).statistics.max
    if prev is not None:
        p_min, p_max = prev
        # 1-D overlap fraction along X as a fast proxy for envelope overlap
        inter = max(0.0, min(max_x, p_max) - max(min_x, p_min))
        union = max(max_x, p_max) - min(min_x, p_min)
        if union > 0 and inter / union > 0.10:
            overlaps += 1
    prev = (min_x, max_x)

print(f"Row groups: {meta.num_row_groups}")
print(f"Overlap violations: {overlaps}")
assert overlaps < meta.num_row_groups * 0.10, "FAIL: spatial coherence threshold breached"

Expected output on a correctly Hilbert-sorted archive:

Row groups: 84
Overlap violations: 3        # < 10% of 84 → PASS

If Overlap violations approaches the row-group count, the sort did not take effect (see Troubleshooting). Cross-check the three thresholds below directly from parquet_metadata():

Validation gate Threshold Check Failure root cause
Bounding-box overlap < 10% between adjacent envelopes compare adjacent row-group min/max envelopes insufficient clustering; Hilbert key collision or centroid skew
Block decompression ratio < 15% of blocks scanned per query blocks_scanned vs blocks_returned oversized groups; variance > 0.6 bypassed
Attribute sparsity alignment NULL/empty < 5% per group per-column null stats from metadata mixed geometry types in one group

Troubleshooting

Symptom Root cause Fix
ST_Intersects scans 100% of blocks despite a tight filter row groups unsorted; envelopes span multiple regions re-run the Hilbert sort and rewrite with write_statistics=True so min/max stats regenerate
Cold-storage retrieval cost spikes on monthly audits groups exceed ~1.2M rows; reads spill to disk enforce R_FINAL = min(R_opt, 1_000_000) and split by geographic partition first
Geometry-column compression drops below 1.2x mixed topology types (points, lines, multipolygons) in one group isolate geometry types and apply type-specific encoding per dictionary encoding for GIS attributes
Query engine ignores spatial stats entirely Parquet metadata not refreshed after the sort rewrite with write_statistics=True (PyArrow) or re-COPY through DuckDB

For cloud-native cold retrieval, align row-group boundaries with your object store’s ranged-GET request sizing (typically 8–16 MB per request) to avoid partial-object retrieval penalties, and consult the Apache Parquet file format specification for the exact metadata layout.

Frequently Asked Questions

How often should the row count be recalculated?

Whenever the inputs to the division change: a new codec level, a schema change that adds or removes columns, a coordinate-precision change, or a shift in the mix of feature types within a partition. In a stable pipeline that is roughly annual. The cheap safeguard is to assert on the actual group sizes after each write and alert when the median drifts more than about 25% from the target, which catches a changed divisor without anyone having to remember to recompute it.

Does the row count need to be identical across partitions?

No — the byte target should be identical, and the row count should be allowed to vary to hit it. Fixing the row count instead is what produces the failure in the diagram above: a uniform 200,000-row setting yields well-sized groups for parcels and multi-gigabyte groups for dense line geometry. Pass a per-dataset row count computed from that dataset’s measured bytes per row.

What if a partition holds fewer rows than the calculated row count?

Then it writes as a single, smaller row group, which is correct and needs no special handling. What does need attention is the object it lands in: many such partitions produce many small objects, and small objects in archive classes are where per-request and minimum-billable-size charges accumulate. Merging sparse neighbouring partitions before the write is the usual remedy, at the cost of slightly coarser pruning.

Related pages

Benchmarking Row-Group Size Against Spatial Predicate Pushdown Sizing Row Groups for Glacier Retrieval of GeoParquet