Data Compression
  1. Home
  2. Compression, Chunking & Spatial Indexing
  3. Row Group Sizing Strategies for Parquet
  4. Row Group Size and Read Amplification in Object Storage
Section: Compression, Chunking & Spatial Indexing: The Foundation of Cloud-Native Geospatial Storage 11 min read

Row Group Size and Read Amplification in Object Storage

Read amplification is bytes fetched divided by bytes returned, and row group size is the single writer-side setting that moves it most. A selective query against a well-laid-out dataset should sit under about 5; a factor of 100 means the engine is reading a hundred times what it returns and paying for all of it. This page makes the metric concrete and shows how to attribute the excess, extending row group sizing strategies for Parquet.

Quick Reference

Row group size Selective query amplification Full scan throughput Footer overhead Primary Use Case
8 MB 2–3 Poor — request-bound High Point lookups on tiny result sets
32 MB 3–5 Fair Moderate Highly selective spatial windows
128 MB 5–9 Good Low The general-purpose default
512 MB 18–30 Excellent Very low Full-table scans and ETL reads
1 GB+ 40+ Excellent Negligible Write-once, always-scanned archives

Where the Excess Bytes Come From

Amplification has three independent sources, and confusing them leads to tuning the wrong thing.

Granularity. A row group is the smallest unit an engine can skip. If a query matches 200 rows inside a 128 MB group, the engine reads the relevant column chunks of the whole group. Smaller groups reduce this waste proportionally.

Ordering. Skipping only works when a group’s statistics rule it out. On a file written in insertion order, every group’s bounding box spans the dataset, so nothing is skipped and the engine reads everything regardless of group size. This source of amplification is unbounded and is not fixed by resizing.

Overhead. Footers, page headers, dictionary pages, and the fixed cost of each range request all get fetched and none appear in the result. Smaller groups increase this term, which is why shrinking row groups eventually makes amplification worse.

Amplification is a sum of two opposing terms, with a minimum between them Read amplification plotted against row group size for a selective query. A granularity component rises steadily with row group size, because a larger group wastes more of itself on a small match. An overhead component falls with row group size, because fewer groups mean less footer and per-request cost. Their sum forms a U-shaped curve with a minimum in the thirty-two to one-hundred-and-twenty-eight megabyte range. A separate horizontal line shows the amplification of an unsorted file, which is high everywhere and unaffected by row group size. Read amplification for a selective spatial query overhead footers, requests granularity wasted rows per group minimum 32–128 MB unsorted file — flat, high, unaffected by row group size 8 MB 32 MB 128 MB 512 MB row group size → amplification → Tuning row group size on an unsorted file moves along the dashed horizontal line and achieves nothing.

Measuring It

python
# Requires: duckdb>=1.0, pyarrow>=16  (Python 3.10+)
from __future__ import annotations

import json
from dataclasses import dataclass

import duckdb
import pyarrow.parquet as pq


@dataclass(frozen=True)
class Amplification:
    bytes_fetched: int
    bytes_returned: int
    row_groups_read: int
    row_groups_total: int

    @property
    def factor(self) -> float:
        return self.bytes_fetched / max(self.bytes_returned, 1)

    @property
    def skip_rate(self) -> float:
        return 1 - (self.row_groups_read / max(self.row_groups_total, 1))

    def diagnose(self) -> str:
        if self.skip_rate < 0.5:
            return (
                "fewer than half the row groups were skipped — the file is "
                "probably not spatially sorted; re-sort before resizing"
            )
        if self.factor > 20:
            return "good skipping but high amplification — try smaller row groups"
        if self.factor < 3:
            return "excellent; check that full scans have not become request-bound"
        return "healthy for a selective query"


def measure(path: str, sql: str) -> Amplification:
    """Read amplification for one real query against one real file."""
    meta = pq.read_metadata(path)
    con = duckdb.connect()
    try:
        con.execute("PRAGMA enable_profiling='json'")
        con.execute("PRAGMA profiling_output='/tmp/profile.json'")
        result = con.execute(sql).arrow()
        profile = json.load(open("/tmp/profile.json", encoding="utf-8"))
    except duckdb.Error as exc:
        raise RuntimeError(f"profiling query failed: {exc}") from exc
    finally:
        con.close()

    fetched = _sum_key(profile, "bytes_scanned") or _sum_key(profile, "bytes_read")
    if not fetched:
        raise RuntimeError("engine did not report bytes scanned — cannot measure")

    return Amplification(
        bytes_fetched=int(fetched),
        bytes_returned=int(result.nbytes),
        row_groups_read=int(_sum_key(profile, "row_groups_read") or meta.num_row_groups),
        row_groups_total=meta.num_row_groups,
    )


def _sum_key(node: dict, key: str) -> int:
    """Depth-first sum of a numeric key across a profiling tree."""
    total = int(node.get(key, 0) or 0)
    for child in node.get("children", []) or []:
        total += _sum_key(child, key)
    return total

Validation

Measure the same query against the same data written at several row group sizes. The curve is what tells you where the minimum is for your selectivity; a published default cannot.

bash
# Write the same table at four sizes, then measure one representative query
for RG in 8 32 128 512; do
  python3 -c "
import pyarrow.parquet as pq
t = pq.read_table('parcels.source.parquet')
pq.write_table(t, 'rg${RG}.parquet', row_group_size=int(${RG} * 1e6 / 180),
               compression='zstd', compression_level=3, write_statistics=True)"
done

Expected shape: amplification falls from 8 MB to a minimum somewhere between 32 MB and 128 MB, then rises steadily. If the curve is flat and high at every size, the file is not spatially sorted and no row group size will help — go and fix the space-filling-curve ordering first.

What a query fetches versus what it returns Two bars for the same query. At five hundred and twelve megabyte row groups the engine fetches four hundred and ten megabytes to return four megabytes, an amplification factor of one hundred and two. At sixty-four megabyte row groups it fetches thirty-one megabytes to return the same four megabytes, a factor of eight. In both bars the returned portion is drawn to scale as a thin sliver at one end, making the wasted proportion visually obvious. A note observes that the wasted bytes are billed identically to the useful ones. Same query, same data, two row group sizes 512 MB row groups 410 MB fetched · 4 MB returned · amplification 102× 64 MB row groups 31 MB fetched · 4 MB returned · amplification 8× The dark sliver at the left of each bar is what the query actually needed. On a per-terabyte-scanned engine, the pale remainder is billed at exactly the same rate. Why amplification must be measured on a selective query The same file measured two ways. Against a full scan, amplification is close to one by definition, because the query wants everything the engine reads, and the number carries no information about layout. Against a selective window query, amplification exposes exactly how much the layout is costing. Tuning against the first number produces no improvement in the second. The same file, two measurements Full scan amplification 1.03 — tells you nothing Selective window amplification 66 — tells you the layout is wrong returned Solid = bytes the query returned. Outline = bytes the engine fetched to produce them.

Edge Cases and Caveats

Amplification measured on a full scan. A full scan has an amplification of roughly 1 by definition and tells you nothing about layout. Always measure on the selective queries users actually run; if you do not know what those are, that is the first thing to find out.

Mixing workloads in one file. A dataset serving both selective point queries and nightly full scans cannot have one optimal row group size. Either accept a compromise around 128 MB or write the data twice, exactly as the Zarr dual-store argument concludes for raster.

Row groups smaller than the compression window. Below about 16 MB, each column chunk gives the compressor too little context and ratios fall measurably — so the storage bill rises while the query bill falls. Include compressed size in the comparison, not just amplification, before adopting a small row group.

Frequently Asked Questions

What is read amplification and how is it measured?

It is the ratio of bytes fetched from storage to bytes the query actually needed. A query returning 4 MB of results that fetched 400 MB has an amplification factor of 100. It is measured from the engine’s own profiling output rather than estimated, because the fetched figure includes footers, page headers, and any column chunk the engine read speculatively — all of which you pay for and none of which appear in the result.

Does a smaller row group always reduce read amplification?

It reduces the granularity term and increases the overhead term, so there is a minimum rather than a monotonic improvement. Smaller groups mean a selective query wastes less of each group it reads, but they also mean more groups, more footer metadata, more per-chunk request overhead, and worse compression because each chunk has less context. Below roughly 32 MB on cloud object storage the overhead usually wins.

How much amplification is acceptable?

For a selective spatial query against a well-laid-out dataset, under about 5 is good and under 10 is workable. Above 50 something structural is wrong — usually that the file was written without a spatial sort, so the covering statistics cannot skip anything and the engine reads groups it will discard. Resizing row groups will not fix that; re-sorting will.

← Back to Row Group Sizing Strategies for Parquet