How Parquet File Layout Decides Your Query Speed

A query that reads 4 GB to answer a question about 20 MB of data is not a query problem. It is a layout problem.

Parquet gives an engine three chances to skip data before it decompresses anything: the file, the row group, and the page. Whether those chances are useful is decided when you write the file, not when you read it.

The three levels of skipping

A Parquet file is a sequence of row groups. Each row group holds a chunk of rows, stored column by column. Each column chunk carries statistics - min, max, null count - and is split into pages with statistics of their own.

When an engine plans WHERE country = 'DE', it walks down:

1. Does the file’s metadata rule it out? Skip the file.
2. Does a row group’s min/max for country exclude 'DE'? Skip the row group.
3. Does a page’s min/max exclude it? Skip the page.

Every one of those checks compares against min and max. Which means they only help when the values in a chunk are clustered. In a randomly ordered dataset, almost every row group contains both 'AD' and 'ZW', so min/max spans the whole domain and nothing is skipped.

Statistics do not make data skippable. Sort order does. Statistics only report the result.

Sort on the column you filter, not the column you join

The highest-return change in most tables is a single ORDER BY at write time on the most-filtered column.

COPY (
  SELECT * FROM events
  ORDER BY event_date, country, user_id
) TO 'events.parquet' (FORMAT parquet, ROW_GROUP_SIZE 1000000);

The order of the sort keys matters and follows the same logic as a composite index: the first key gets near-perfect pruning, the second gets good pruning within each run of the first, and by the third you are mostly getting compression benefits rather than skipping.

Pick the keys from the query log, not from the schema. The column that feels like the primary identifier is rarely the one in the WHERE clause.

Row group size is a real trade-off

The default in many writers is 128 MB, and it is a reasonable default for a scan-heavy warehouse. It is a poor one for selective lookups.

The failure mode at each end is worth naming. Too large, and a query matching 1 000 rows still reads a 512 MB row group. Too small, and the engine spends its time reading footers - on object storage, where every read is a request with latency measured in tens of milliseconds, thousands of tiny row groups will comfortably out-cost the data you skipped.

The small file problem is a metadata problem

A dataset written as 40 000 files of 3 MB each is slow for a reason that has nothing to do with its size. Before reading anything, the engine must list the directory and open every footer. On S3 that is 40 000 requests, serialised through whatever concurrency limit the client has.

Target 128 MB to 1 GB per file. Compact on a schedule if your ingest produces small files - most table formats have a OPTIMIZE or compact operation for exactly this, and running it nightly is usually the single cheapest performance win available.

Encoding is chosen for you, and you can help

Parquet picks encodings per column chunk. Dictionary encoding is used when a column has few distinct values relative to the chunk, and it is dramatically faster to filter - comparisons happen against dictionary indices rather than strings.

You influence it in two ways:

• Cardinality per row group. A column with 50 000 distinct values across the table but 400 within a sorted row group will get dictionary encoding. The same column unsorted will not.
• Type choice. Storing a timestamp as a string defeats delta encoding and doubles the bytes. Storing an enum as VARCHAR instead of a dictionary-friendly small domain does the same.

import pyarrow.parquet as pq

meta = pq.ParquetFile('events.parquet').metadata
rg = meta.row_group(0)
for i in range(rg.num_columns):
    col = rg.column(i)
    print(f'{col.path_in_schema:24} {col.encodings} {col.total_compressed_size:>12,}')

Reading the encodings back is the fastest way to find out that a column you assumed was dictionary-encoded is not.

A short diagnostic

When a Parquet-backed query is slower than it should be, check in this order:

1. Bytes scanned versus bytes returned. A ratio above ~50× means pruning is not working.
2. Number of files touched. If it is thousands, compact before tuning anything else.
3. Row group count per file. One row group in a 1 GB file means no intra-file pruning at all.
4. Sort order. Compare a row group’s min/max for your filter column against the column’s global range. If they match, the data is unsorted with respect to that filter.

Most of the time the answer is step four, and the fix is one ORDER BY in the job that writes the table.