In this lesson, you will learn how to align your physical file storage configuration with the specific architectural expectations of compute engines like Spark, Trino, and Presto to maximize query performance.
While columnar formats like Parquet and ORC are universally efficient, different compute engines "consume" data using different strategies. If your file layout doesn’t match the engine's internal execution model, you end up wasting resources on unnecessary I/O or failing to leverage vectorized processing.
Matching Engine Needs
Most modern engines, such as Spark or Trino, perform best when data is stored in "balanced" files. For example, Spark’s execution model relies heavily on partitioning and task parallelism. If you have millions of tiny files, Spark will spend more time managing metadata and task scheduling than actually reading data. Conversely, if you have massive files, you risk losing the benefits of fine-grained predicate pushdown and partition pruning.
To align these, you must tune your row group size (in Parquet) or stripe size (in ORC) to reflect the memory profile of your executors.
Tuning for Vectorization
Compute engines use vectorized reading to process batches of rows at once, which is significantly faster than row-by-row processing. If your file layout creates row groups that are too small, the engine cannot fill its memory buffers efficiently, breaking the "vectorization pipeline."
Consider this example of configuring a Parquet writer for Spark:
-- Aligning row group size to typical memory buffer expectations
SET spark.sql.parquet.rowGroupSizeBytes = 134217728; -- 128MB
By setting this value to 128MB, you ensure that every row group is large enough to keep the CPU cache and vectorized execution paths saturated, while staying small enough to prevent memory pressure or OOM (Out of Memory) errors during shuffle operations.
Why this matters
When your file layout is misaligned—such as having very wide rows (thousands of columns) stored in a way that doesn't respect the engine's block-read size—you effectively bypass the benefits of Columnar Projection. The engine is forced to read data it doesn't need, nullifying the work you did with Zone Maps and Bloom Filters.
Key Takeaway: To maximize performance, align your row group or stripe sizes with your compute engine’s memory buffer limits to ensure efficient vectorized reads and prevent metadata overhead.