#Tuning Row Group Size for Cloud Query Performance

For cloud-native geospatial workloads, target row groups between 128 MB and 512 MB uncompressed, with 100k–500k rows per group as a practical baseline. This range amortises object storage request overhead, preserves columnar pruning efficiency, and prevents memory exhaustion during spatial joins. Always pair explicit row group sizing with write_statistics=True and spatial pre-sorting (Z-ordering or Hilbert curves) to maximise predicate pushdown. If geometry complexity varies widely, cap row groups at 128 MB and rely on quadtree-based spatial partitioning to limit scan scope. This page sits under the broader treatment of row group sizing for Parquet; start there if you have not yet chosen a baseline group size.

#Quick Reference: Size by Geometry Type

#Why Row Group Boundaries Drive Cloud I/O Cost

Cloud object stores (S3, GCS, ADLS) optimise for sequential throughput but penalise excessive API calls. Every row group boundary translates to a separate GET request when a query engine evaluates column statistics. The Parquet file footer encodes per-column min and max statistics for each row group; the engine reads those statistics first and fetches the row group data only if the statistics indicate a match. This makes group count a first-order cost driver — not just file size.

Oversized groups — above 1 GB — force engines to download and decompress massive column chunks even when spatial filters match only a small fraction of rows. Undersized groups, below 32 MB, multiply request counts, saturating network bandwidth and inflating per-request API costs before predicate evaluation can offset the overhead. The diagram below shows how group size shifts where the bottleneck sits in the I/O path from storage to the query engine.

The sweet spot balances three factors: HTTP request amortisation, column chunk locality, and in-memory decompression overhead. When you align group boundaries with spatial extents — by pre-sorting via space-filling curves — engines such as DuckDB, Trino, or Spark can skip irrelevant groups at the metadata layer, reducing both egress costs and compute time. This interaction between layout and statistics is the core thesis of row group sizing strategies for Parquet.

#Geospatial-Specific Sizing Rules

Geospatial datasets introduce unique constraints. GEOMETRY and GEOGRAPHY columns are highly variable: a simple lat/lon point occupies roughly 16 bytes as WKB, while a detailed coastline polygon can exceed 50 KB. Default Parquet writers ignore this skew and produce row groups that trigger memory spikes during deserialisation. Apply the following rules when configuring writers:

• Point / LineString data: 256–512 MB groups work well. High row density keeps statistics tight and minimises request overhead.
• Polygon / MultiPolygon data: Cap at 128–256 MB. Large geometries inflate row sizes quickly; smaller groups prevent OOM errors during join operations and keep bounding-box statistics accurate.
• Mixed geometry tiers: Split datasets by geometry complexity, or tune data_page_size alongside row groups. Keep spatial indexes (min/max bounding boxes) accurate by writing with write_statistics=True.
• Cloud throttling mitigation: If your storage backend returns 503 SlowDown errors, reduce row group size to 64–128 MB and increase parallel reader threads. This distributes I/O across more concurrent connections, smoothing throughput.

Spatial predicate pruning relies entirely on column statistics. If your writer skips min/max stats or uses aggressive dictionary encoding on high-cardinality geometry columns, engines fall back to full scans. The interaction between encoding choices and statistics coverage is covered in depth in dictionary encoding for categorical GIS attributes. Always validate statistics coverage before production deployment.

#Spatial Sorting and Statistics Alignment

Row group sizing alone cannot compensate for poor data layout. Spatial filters like ST_Intersects or ST_Contains evaluate bounding boxes against Parquet column statistics. If your dataset is written in insertion order, min/max geometry bounds span entire continents, rendering row group skipping useless regardless of group size.

Pre-sort data using space-filling curves before writing:

• Z-ordering interleaves X/Y coordinates into a single sortable integer, clustering nearby geometries into the same row group.
• Hilbert curves preserve locality better for irregular grids but require more CPU during ingestion.

When geometries are sorted, row group min/max bounding boxes shrink dramatically. A 256 MB group containing only Pacific Northwest coastlines will be skipped entirely when filtering for the Gulf of Mexico. This metadata-level pruning is the primary mechanism behind cloud query efficiency. The diagram below contrasts the two layouts for the same four row groups under one query window.

Sorted groups also expose more repetitive coordinate runs, improving ZSTD compression levels by 10–20% at the same compression setting. The two optimisations compound: tighter groups improve statistics; better statistics improve skip rates; sorted coordinates improve compression ratios. For the per-data-type level choice, see optimal ZSTD levels for vector vs raster data.

#Production Implementation

Correct configuration depends on your stack. The patterns below are production-ready for PyArrow and DuckDB, the two most common writers in Python-based geospatial pipelines.

PyArrow (pyarrow >= 12.0)

# pyarrow>=12.0  geopandas>=0.13  zstandard>=0.21
import pyarrow as pa
import pyarrow.parquet as pq
import geopandas as gpd


def write_spatial_parquet(
    gdf: gpd.GeoDataFrame,
    output_path: str,
    row_group_size: int = 200_000,
    compression_level: int = 3,
) -> None:
    """
    Write a spatially sorted GeoDataFrame to Parquet with cloud-optimised row groups.

    Pre-condition: gdf must be sorted by a space-filling curve key before calling.
    Targets ~128–256 MB per group for polygon-dominant data at this row count.

    Args:
        gdf: Non-empty GeoDataFrame. Must not contain CRS=None geometry.
        output_path: Destination path — local or s3://bucket/key.parquet.
        row_group_size: Row count per group. Lower for MultiPolygon-heavy data,
                        higher for point data. Adjust after profiling byte sizes.
        compression_level: ZSTD level 1–22. Level 3 balances ratio and read speed.

    Raises:
        ValueError: If gdf is empty or CRS is undefined.
    """
    if gdf.empty:
        raise ValueError("GeoDataFrame is empty — nothing to write.")
    if gdf.crs is None:
        raise ValueError("GeoDataFrame has no CRS defined. Reproject before writing.")

    table: pa.Table = pa.Table.from_pandas(gdf, preserve_index=False)

    pq.write_table(
        table,
        output_path,
        row_group_size=row_group_size,
        data_page_size=16 * 1024 * 1024,   # 16 MB pages for better compression
        write_statistics=True,              # Critical: enables predicate pushdown
        compression="zstd",
        compression_level=compression_level,
    )

DuckDB with spatial extension (DuckDB >= 0.9)

DuckDB’s spatial extension provides ST_Hilbert for Hilbert-curve ordering of geometries. Use it to pre-sort rows before writing:

-- Run once per session:
-- INSTALL spatial; INSTALL httpfs;
LOAD spatial;

-- Write spatially sorted Parquet with explicit row group target
COPY (
    SELECT *
    FROM   spatial_table
    ORDER BY ST_Hilbert(
        ST_Centroid(geometry),
        ST_Extent(geometry) OVER ()
    )
) TO 's3://bucket/spatial_data.parquet' (
    FORMAT      PARQUET,
    ROW_GROUP_SIZE 250000,
    COMPRESSION ZSTD
);

If ST_Hilbert is not available in your DuckDB version, sort by a numeric Z-order key computed from centroid X/Y coordinates instead.

#Validation and Verification

Inspect metadata after every write to confirm that sizing and statistics coverage are within bounds:

# pyarrow>=12.0
import pyarrow.parquet as pq


def validate_cloud_parquet(
    file_path: str,
    expected_min_mb: float = 64.0,
    expected_max_mb: float = 512.0,
) -> dict:
    """
    Validate row group sizes and statistics coverage in a written Parquet file.

    Returns a summary dict suitable for logging or pipeline alerting.
    Raises ValueError if any row group falls outside the expected byte range.
    """
    meta = pq.read_metadata(file_path)
    rg_sizes_mb = [
        meta.row_group(i).total_byte_size / (1024 ** 2)
        for i in range(meta.num_row_groups)
    ]
    columns_with_stats = sum(
        1
        for i in range(meta.num_row_groups)
        for j in range(meta.row_group(i).num_columns)
        if meta.row_group(i).column(j).is_stats_set
    )

    for idx, size_mb in enumerate(rg_sizes_mb):
        if not (expected_min_mb <= size_mb <= expected_max_mb):
            raise ValueError(
                f"Row group {idx} is {size_mb:.1f} MB — outside "
                f"[{expected_min_mb}, {expected_max_mb}] MB target."
            )

    return {
        "num_row_groups": meta.num_row_groups,
        "total_rows": meta.num_rows,
        "compressed_mb_min": round(min(rg_sizes_mb), 2),
        "compressed_mb_max": round(max(rg_sizes_mb), 2),
        "compressed_mb_mean": round(sum(rg_sizes_mb) / len(rg_sizes_mb), 2),
        "columns_with_statistics": columns_with_stats,
    }

A healthy spatial filter should skip more than 80% of row groups at the metadata layer. Monitor query execution plans for Rows Read vs Rows Returned ratios; a ratio above 5:1 signals that bounding-box statistics are too broad — usually because the dataset was not sorted before writing.

Engine-specific settings that must accompany correct row group sizes:

• Spark: spark.sql.parquet.filterPushdown=true (enabled by default in Spark 3+)
• Trino: hive.parquet.filter-pushdown=true
• Athena: no additional configuration needed — predicate pushdown is always enabled for Parquet

#Edge Cases and Caveats

Very large or complex polygon datasets. When individual WKB geometries exceed 50 KB (dense coastlines, detailed administrative boundaries at national scale), a row group target of 128 MB may produce only 2,000–3,000 rows per group. That is acceptable for correctness, but it inflates the Parquet file footer metadata. If you have more than a few hundred row groups in a single file, consider splitting the dataset into multiple files by spatial tile instead of reducing the row group size further.

Streaming ingestion vs batch writes. Row group size tuning assumes a known dataset written in a single pass. Streaming writers that append rows incrementally — common in IoT or real-time telemetry pipelines — cannot control row group size precisely. They tend to produce many small groups, especially during low-throughput periods. For these workloads, write small interim files and compact them periodically into correctly sized Parquet files using a background job.

Mixed CRS sources in the same file. If you are combining features from multiple coordinate reference systems before writing — for example, merging EPSG:4326 and EPSG:3857 datasets — the WKB coordinate ranges vary wildly between rows. Min/max bounding-box statistics become meaningless across CRS boundaries, defeating predicate pushdown regardless of row group size. Always reproject all features to a single CRS before applying spatial sorting and writing. This prerequisite is part of the broader Compression, Chunking & Spatial Indexing pipeline.

#Frequently Asked Questions

Why does row group size affect cloud query cost on S3?

Each row group boundary is a separate GET request when a query engine reads column statistics. Too many small groups multiply request counts and inflate API costs; too few large groups force engines to fetch and decompress more data than spatial filters actually need, raising both egress cost and memory pressure.

What row group size should I use for polygon-heavy geospatial datasets on S3?

Cap polygon or MultiPolygon datasets at 128–256 MB uncompressed per row group. Large WKB-encoded geometries inflate row sizes unpredictably; smaller group limits prevent memory exhaustion during spatial joins and keep bounding-box statistics tight enough for effective predicate pushdown.

Does spatial sorting before writing actually reduce query cost?

Yes, significantly. When rows are sorted by a space-filling curve (Z-order or Hilbert), each row group’s min/max bounding box covers only a small geographic area. A spatial filter targeting a distant region can skip the entire row group at the metadata layer, eliminating the GET request and decompression work entirely.

Should I use different row group sizes for Athena vs DuckDB?

Yes. Athena benefits from 128–256 MB groups that amortise S3 GET overhead across more rows. DuckDB loads entire row groups into memory before applying predicates, so 32–64 MB groups reduce memory pressure and improve parallelism on workstation-class hardware. Write Parquet at the cloud-engine target and let DuckDB re-partition if needed for local analysis.

#Related

• Row Group Sizing Strategies for Parquet
• ZSTD Compression Levels for Geospatial Data
• Optimal ZSTD Levels for Vector vs Raster Data
• Dictionary Encoding for Categorical GIS Attributes
• Spatial Partitioning with Quadtree Indexes

← Back to Row Group Sizing Strategies for Parquet