Parquet Row-Group Bloat: Why AI Agents Pull 500MB for a 4KB Lookup

Apache Parquet files written with default 512MB unsorted row groups force analytical AI agents using DuckDB, Polars, or Trino to download 492MB from S3 or Wasabi just to retrieve a single 4KB customer record. Sorting writes by (tenant_id, event_ts), sizing row groups to 64MB, and enabling Parquet v2 PageIndex headers with split-block Bloom filters reduces S3 byte-range transfers to 184KB (-99.96% bandwidth) and cuts P95 query latency from 3,840ms to 68ms.

Modern AI data architectures frequently bypass traditional data warehouses by equipping autonomous agents with embedded SQL engines (DuckDB, DataFusion, Polars) that query Apache Parquet tables directly over S3-compatible object storage. When an enterprise remedies the small-file Iceberg lakehouse explosion by compacting thousands of tiny JSON logs into 1GB Parquet files, batch scan throughput improves immediately.

Then an AI copilot starts running high-concurrency point lookups (WHERE tenant_id = 'T-849' AND order_id = 'ORD-9921') against those compacted 1GB Parquet files. Suddenly, network interfaces saturate at 10 Gbps, DuckDB worker containers crash with Out-Of-Memory (OOM) errors, and query latency spikes past three seconds. You can't use HTTP Range requests to surgically fetch a 4KB row if your Parquet writer never wrote page-level offset indexes or sorted the data before compression.

Examine the object storage I/O and latency telemetry we recorded querying a 4.2GB Apache Parquet dataset (64 million rows across 8 files) over S3 object storage for a single-tenant agent lookup (4.2 KB matching payload):

Look at Row 1 and Row 3 in the table. In an unsorted Parquet file, a query that only needs 4.2 kilobytes of data pulls 492 megabytes across the network—a 117,142x read amplification penalty.

We diagnosed this exact bottleneck on a customer-facing telemetry agent running 20 concurrent DuckDB workers. Every time a user asked about a recent shipment, the agent ran a parameterized SQL query against S3. Because the upstream Kafka-to-Parquet sink flushed records in arrival order without sorting by tenant_id, every single 512MB row group contained tenants A through Z. DuckDB read the Parquet footer, saw that T-849 fell between 'A' and 'Z' in all eight row groups, and downloaded half a gigabyte of compressed column chunks per question.

Why Default Parquet Writers Break S3 Byte-Range Pruning

Object storage (AWS S3, Wasabi, MinIO, Cloudflare R2) doesn't have a local filesystem block cache; every byte read must travel over an HTTP GET request with a Range: bytes=start-end header. Parquet relies on a three-tier metadata hierarchy (File Footer $\rightarrow$ Row Group Column Chunk $\rightarrow$ Data Page) to decide which byte ranges to fetch, and if those statistics aren't sorted, pruning won't eliminate a single block.

1. Overlapping Min/Max Zone Maps in Unsorted Row Groups

Parquet stores min_value and max_value statistics in the file footer for each column chunk inside a row group (which defaults to 128MB–1GB or 1,000,000+ rows in Spark, PyArrow, and Pandas). If rows are appended in chronological ingestion order rather than clustered by the query predicate column (tenant_id, customer_id, device_id), every row group's [min, max] interval spans the entire key space (['0000...', 'ffff...']). Min/max pruning eliminates zero row groups.

2. Missing ColumnIndex and OffsetIndex (The Whole-Chunk Download Trap)

Even when only one row group matches, legacy Parquet writers omit the Parquet v2 PageIndex structures (ColumnIndex and OffsetIndex) or write massive 1MB data pages. Without OffsetIndex byte offsets for individual 64KB pages, the query engine cannot issue a surgical Range: bytes=917504-983040 request for Page #14; it is forced to download and decompress the entire multi-megabyte column chunk from byte zero. Worse, if those reads traverse a hyperscaler NAT gateway, they trigger the cloud storage egress fee trap.

3. High-Cardinality UUID Lookups Without Split-Block Bloom Filters

When an AI agent looks up a specific trace_id, invoice_id, or epc_code, sorting by tenant_id narrows the search to one tenant's row groups, but trace_id values remain randomly distributed within that tenant's slice. Without a Parquet split-block Bloom filter (XXH64 hash bitset stored in the header), the reader still has to scan every row group belonging to a large enterprise tenant.

Unsorted 512MB Row Groups vs. Clustered 64KB PageIndex Pruning

Transforming object storage Parquet files into sub-80ms point-lookup substrates requires enforcing physical sort order, 64MB row groups, 64KB page indexes, and Bloom filters during compaction.

Writing PageIndex & Bloom-Enabled Parquet Files in PyArrow and DuckDB

When compacting Parquet datasets for analytical AI agents or hybrid lakehouses, configure pyarrow.parquet.write_table (or your Iceberg/DuckDB compaction job) to sort by your primary tenant and time keys, cap row_group_size at 100,000 rows (~64MB), set data_page_size to 64KB, enable write_page_index=True, and attach Bloom filters to high-cardinality ID columns.

# Production Compaction Pattern: Clustered 64MB Row Groups + 64KB PageIndex + Split-Block Bloom Filters
import pyarrow as pa
import pyarrow.compute as pc
import pyarrow.parquet as pq

def write_agent_optimized_parquet(
    table: pa.Table,
    output_s3_uri: str,
    filesystem,
) -> None:
    # 1. Physically sort rows by (tenant_id, event_timestamp) to eliminate zone-map overlap
    sort_indices = pc.sort_indices(
        table,
        sort_keys=[("tenant_id", "ascending"), ("event_timestamp", "ascending")],
    )
    sorted_table = table.take(sort_indices)

    # 2. Write Parquet with 100K-row groups (~64MB), 64KB pages, PageIndex, and Bloom filters
    pq.write_table(
        sorted_table,
        where=output_s3_uri,
        filesystem=filesystem,
        compression="zstd",
        compression_level=3,
        use_dictionary=["tenant_id", "event_type", "status"],
        row_group_size=100_000,               # ~64MB row groups instead of 512MB-1GB defaults
        data_page_size=65_536,                # 64KB granular data pages for surgical S3 Range GETs
        write_page_index=True,                # Writes ColumnIndex + OffsetIndex for page-level pruning
        write_statistics=True,
        store_schema=True,
        # Enable split-block XXH64 Bloom filters (1% FPP) on high-cardinality point-lookup IDs
        use_bloom_filter=["order_id", "trace_id", "document_id"],
    )

You can verify that your existing Parquet files on S3 actually contain ColumnIndex, OffsetIndex, and non-overlapping row-group zone maps using this DuckDB diagnostic query:

-- Audit Row-Group Zone-Map Overlap & PageIndex Presence in DuckDB
SELECT
    row_group_id,
    row_group_num_rows,
    ROUND(row_group_bytes / 1048576.0, 2) AS row_group_mb,
    stats_min_value AS min_tenant_id,
    stats_max_value AS max_tenant_id,
    bloom_filter_offset IS NOT NULL AS has_bloom_filter,
    column_index_offset IS NOT NULL AS has_page_index
FROM parquet_metadata('s3://logic42-lakehouse-prd/telemetry/2026-10/*.parquet')
WHERE path_in_schema = 'tenant_id'
ORDER BY file_name, row_group_id;

Three Storage Engineering Rules for Sub-100ms S3 Agent Queries

1. Cluster Every Compaction Write by (tenant_id, timestamp): Never flush streaming Kafka or CDC batches directly into final Parquet files without sorting. Enforcing a two-key sort order during compaction guarantees disjoint [min, max] boundaries across row groups so 98%+ of the file is eliminated from the footer alone.

2. Enable write_page_index=True and Cap Data Pages at 64KB: Row-group pruning is not enough when a single row group spans 64MB to 128MB. Writing ColumnIndex and OffsetIndex headers with 64KB pages lets DuckDB, Polars, and Trino fetch only the exact 64KB byte slice matching your timestamp window over S3 HTTP Range requests.

3. Attach Split-Block Bloom Filters to UUID and Hash Columns: Min/max statistics are useless on random UUIDv4, SHA-256, or alphanumeric order IDs because every row group spans '0' to 'z'. Enabling Parquet Bloom filters (use_bloom_filter) adds ~1.2 bytes per value while pruning 99% of non-matching row groups before a single data page is downloaded.