Optimizing Parquet Storage

When to use

• Parquet/lake reads are slow or scan too much data.
• Files are too small (many tiny files) or too large (poor parallelism).
• Choosing compression, row-group size, or partition layout.
• Do NOT use for warehouse-native storage tuning (use the warehouse skills).

Workflow

- [ ] Target ~128MB-1GB files and ~128MB row groups
- [ ] Partition by common filter columns (low/medium cardinality)
- [ ] Sort within files by a filter column to tighten min/max pruning
- [ ] Pick compression: Snappy (speed) or ZSTD (ratio)
- [ ] Compact small files; select only needed columns

1. Right-size files and row groups. Aim for ~128MB–1GB files and ~128MB row groups so engines get efficient parallelism and pruning. Tiny files kill performance via per-file overhead.
2. Partition on filter columns of low/medium cardinality (date, region). Avoid high-cardinality partitioning (user_id) — it creates millions of tiny files.
3. Sort within files by a frequently filtered column so Parquet's per-row-group min/max stats enable predicate pushdown (data skipping).
4. Compression: Snappy for hot, latency-sensitive data; ZSTD for better ratio and cheaper storage at similar read speed.
5. Read fewer columns — the biggest columnar win is column pruning.

Patterns

Write well-sized, sorted Parquet (Spark):

(df.sort("ordered_at")                       # tighten row-group min/max for pushdown
   .repartition(1, "region")                 # control file count per partition
   .write.partitionBy("region")
   .option("compression", "zstd")
   .parquet("s3://lake/orders/"))

Predicate pushdown — filtering on the sorted/partition column lets the reader skip row groups and partitions entirely, reading far fewer bytes.

Compact small files — periodically read a partition and rewrite it into a few large files (or use a table format's compaction) to fix the small-files problem from streaming/frequent appends.

Common pitfalls

• Small-files problem — frequent small appends create thousands of tiny files; compact on a schedule.
• High-cardinality partitioning — partitioning by id/timestamp-to-the-second explodes file count; partition coarsely, sort finely.
• No sorting — unsorted files have wide row-group min/max, so pushdown skips nothing.
• SELECT * on wide tables — reads every column's bytes; project only needed columns.
• Row groups too small/large — too small loses compression/pushdown efficiency, too large hurts parallelism and memory.
• Gzip for analytics — slow to decode; prefer Snappy or ZSTD.