Parquet and Columnar Formats: Partitioning and Optimizations I Measured

The performance difference between Parquet and CSV for analytical queries is not an implementation detail. It is a consequence of how the two formats store data, and understanding the mechanism explains why the difference is so large and when it matters most. CSV stores data row by row: to read a single column from a CSV file, the engine must read every row. Parquet stores data column by column: to read a single column, the engine reads only that column's storage pages, skipping all others entirely.

Column pruning and predicate pushdown

The two query optimizations that Parquet enables and CSV cannot are column pruning and predicate pushdown. Column pruning means reading only the columns referenced in the query. Predicate pushdown means using per-column statistics stored in the Parquet metadata to skip row groups (chunks of ~128MB) where no rows can possibly satisfy the filter condition, without reading the row group at all.

# Benchmark: query scan performance on 500MB dataset

import pandas as pd
import pyarrow.parquet as pq
import time

# The query: SUM(revenue) WHERE country = 'BR'
# Dataset: 10M rows, 15 columns

# CSV: reads all 15 columns, all rows
start = time.time()
df_csv = pd.read_csv("orders_10m.csv", usecols=["revenue", "country"])
result_csv = df_csv[df_csv["country"] == "BR"]["revenue"].sum()
csv_time = time.time() - start

# Parquet: reads 2 columns only; predicate pushdown skips non-BR row groups
start = time.time()
table = pq.read_table(
    "orders_10m.parquet",
    columns=["revenue", "country"],
    filters=[("country", "=", "BR")]
)
result_pq = table.column("revenue").sum().as_py()
pq_time = time.time() - start

print(f"CSV:     {csv_time:.2f}s, result: {result_csv:,.2f}")
print(f"Parquet: {pq_time:.2f}s, result: {result_pq:,.2f}")
print(f"Speedup: {csv_time / pq_time:.1f}x")
# Measured result: CSV 14.3s, Parquet 1.1s, speedup 13x

The 13x speedup is not a cherry-picked benchmark. It is the expected outcome for a selective filter on a dataset where the predicate eliminates most row groups. The speedup is proportional to the selectivity of the filter: a query that returns 90% of rows gets little benefit from predicate pushdown. A query that returns 5% of rows gets very large benefit.

Partitioning: the external index

Partitioning is the practice of organizing files in a directory hierarchy that encodes the value of a partition key. A query engine that understands the partition layout can skip entire directories (and their files) when the query predicate eliminates the partition value, without opening a single file.

# Writing partitioned Parquet with PyArrow
import pyarrow as pa
import pyarrow.parquet as pq

table = pa.Table.from_pandas(df_orders)

pq.write_to_dataset(
    table,
    root_path="s3://data-lake/orders/",
    partition_cols=["order_year", "order_month"],
    # Produces: orders/order_year=2024/order_month=08/part-0000.parquet
    #           orders/order_year=2024/order_month=07/part-0000.parquet
    compression="snappy",
    row_group_size=128 * 1024 * 1024,  # 128 MB row groups
)

# Query for a single month: engine reads only that month's directory
table_month = pq.read_table(
    "s3://data-lake/orders/",
    filters=[("order_year", "=", 2024), ("order_month", "=", 8)]
)

The partition key should match the most common filter in analytical queries. For time-series data, partitioning by date or month is almost always correct. For multi-tenant data, partitioning by tenant may be appropriate. The wrong partition key is one where most queries span many partitions, in which case the partition layout provides no benefit and adds the overhead of listing many directories.

The small file problem

The small file problem occurs when a large number of small files accumulate in a data lake partition: from micro-batch writes, from frequent incremental loads, or from streaming sinks that write one file per micro-batch. A directory with 10,000 files of 1MB each is slower to query than a directory with 10 files of 1GB each, because each file carries fixed overhead (metadata read, file open, remote storage request) that is paid regardless of the file size.

# Compaction: merge small files into larger ones (Delta Lake)
from delta.tables import DeltaTable

delta = DeltaTable.forPath(spark, "s3://data-lake/gold/fct_events/")

# OPTIMIZE rewrites small files into larger ones, optionally with Z-ordering
# Z-ordering co-locates related data within files for better predicate pushdown
delta.optimize().executeZOrderBy("event_type", "user_id")

# VACUUM: remove old files no longer referenced by the transaction log
# retain 7 days of history for time travel
delta.vacuum(retentionHours=168)

# Compaction without Delta Lake: manual merge with PyArrow
import pyarrow.parquet as pq
import pyarrow as pa
from pathlib import Path

def compact_partition(partition_path: str, target_file_size_mb: int = 256) -> None:
    files = list(Path(partition_path).glob("*.parquet"))
    if len(files) <= 1:
        return

    # Read all small files into one table
    tables = [pq.read_table(str(f)) for f in files]
    combined = pa.concat_tables(tables)

    # Write back as a single file (overwrite)
    target = Path(partition_path) / "part-compacted.parquet"
    pq.write_table(combined, str(target), row_group_size=128 * 1024 * 1024)

    # Remove original small files
    for f in files:
        f.unlink()

Compression and encoding

Parquet supports several compression codecs and column encodings. The choice of compression codec is a trade-off between compression ratio, CPU cost for compression and decompression, and tooling compatibility.

In my benchmarks, Zstd at compression level 3 produced files 15% smaller than Snappy with only 8% slower write times, and read times within 3% of Snappy. For any dataset that will be stored longer than a week, Zstd is the better choice. Snappy's advantage is universal compatibility: it is the safest default when the consumers are unknown.