Clustering payoff: sorted versus random files

Measure your machineNeeds Python with PyArrow, pandas

Problem

With U files and per-file min/max statistics, a query for one value opens every file whose range contains it. Randomly ordered files each span almost the whole domain, so the query opens all U; files sorted by the filtered column each cover a slice, so it opens one or two. The gain is close to U, and it decays as soon as new unsorted data is appended, because each new file spans everything again.

Create a table of 20 million rows with a column depot_id (500 values) and a day number. Write it as 20 Parquet files of 1 million rows, once in random order and once globally sorted by (depot_id, scan_time). Count, from the file statistics, how many files a query for one depot must open. Then append one more file of random data to the sorted folder and count again.

The question. How many files does a one-depot query open in each layout, and what does one unsorted append do?

Predict

After one file of random rows is appended to the sorted folder, how many files does a one-depot query open on average?

About 1, as beforeAbout 2Most of the 21

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

clustering-payoff-sorted-versus-random-files.py56 linesDownload

View the code

clustering-payoff-sorted-versus-random-files.py

# /// script
# requires-python = ">=3.10"
# dependencies = ["numpy", "pandas", "pyarrow"]
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""Clustering payoff: sorted against random files.

20 million rows (depot_id from 500 values, scan_time as a day number) written
as 20 Parquet files of a million rows, once in random order and once sorted
by (depot_id, scan_time). From the file statistics alone, counts the files a
query for one depot must open, averaged over depots; then appends one file of
random rows to the sorted folder and counts again.
"""
import argparse, json, os, shutil, tempfile
import numpy as np, pandas as pd, pyarrow as pa, pyarrow.parquet as pq

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--rows", type=int, default=20_000_000, help="Rows.")
p.add_argument("--files", type=int, default=20, help="Files.")
p.add_argument("--depots", type=int, default=500, help="Depots.")
p.add_argument("--seed", type=int, default=0, help="Random seed.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

rng = np.random.default_rng(args.seed); base = tempfile.mkdtemp(); n = args.rows
df = pd.DataFrame({"depot_id": rng.integers(0, args.depots, n), "scan_time": rng.integers(0, 730, n), "weight_g": rng.integers(100, 30000, n)})
per = n // args.files
def write(frame, folder):
    os.makedirs(folder, exist_ok=True)
    for i in range(args.files): pq.write_table(pa.Table.from_pandas(frame.iloc[i * per:(i + 1) * per], preserve_index=False), f"{folder}/f{i}.parquet")
def ranges(folder):
    out = []
    for name in sorted(os.listdir(folder)):
        md = pq.ParquetFile(f"{folder}/{name}").metadata
        mins = [md.row_group(g).column(0).statistics.min for g in range(md.num_row_groups)]
        maxs = [md.row_group(g).column(0).statistics.max for g in range(md.num_row_groups)]
        out.append((min(mins), max(maxs)))
    return out
def files_to_open(rs, depot): return sum(1 for lo, hi in rs if lo <= depot <= hi)
rows = []
write(df, f"{base}/random"); write(df.sort_values(["depot_id", "scan_time"]), f"{base}/sorted")
for label, folder in (("random order", "random"), ("sorted by (depot_id, scan_time)", "sorted")):
    rs = ranges(f"{base}/{folder}"); counts = [files_to_open(rs, dp) for dp in range(args.depots)]
    rows.append({"layout": label, "files": len(rs), "depot_17": files_to_open(rs, 17), "mean_files": float(np.mean(counts))})
pq.write_table(pa.Table.from_pandas(df.sample(per, random_state=1), preserve_index=False), f"{base}/sorted/f_extra.parquet")   # one unsorted append
rs = ranges(f"{base}/sorted"); counts = [files_to_open(rs, dp) for dp in range(args.depots)]
rows.append({"layout": "sorted, plus one appended random file", "files": len(rs), "depot_17": files_to_open(rs, 17), "mean_files": float(np.mean(counts))})
shutil.rmtree(base, ignore_errors=True)
summary = {"gain": rows[0]["mean_files"] / rows[1]["mean_files"]}
for r in rows: print(f"{r['layout']:<38} {r['files']} files: depot 17 opens {r['depot_17']:>2}, average over depots {r['mean_files']:5.2f}")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"summary": summary, "tables": {"layouts": rows}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.

Terminal

uv run clustering-payoff-sorted-versus-random-files.py
uv run clustering-payoff-sorted-versus-random-files.py --help     # every knob

Knobs
Knob · default	What it changes
--rows20,000,000	Rows.
--files20	Files.
--depots500	Depots.
--seed0	Random seed.

Our run

Files a one-depot query opens

Files a one-depot query opens
Layout	Files	Depot 17	Average over depots
random order	20	20	20
sorted by (depot_id, scan_time)	20	1	1.04
sorted, plus one appended random file	21	2	2.04

Reference runApple M2 · 8 cores · 8 GB

Output · uv run clustering-payoff-sorted-versus-random-files.py

random order                           20 files: depot 17 opens 20, average over depots 20.00
sorted by (depot_id, scan_time)        20 files: depot 17 opens  1, average over depots  1.04
sorted, plus one appended random file  21 files: depot 17 opens  2, average over depots  2.04

macOS 26.6.2 · Python 3.13.3 · numpy 2.5.4 · pandas 3.0.6 · pyarrow 26.0.0 · run 2026-10-20

What we found

In random order a one-depot query had to open all 20 files (on average 20); sorted, 1.04 on average, a gain of 19.3×, close to U = 20. One appended file of random rows raised that to 2.04 for every depot.

Read it

Clustering pays in proportion to the number of files, and every unclustered write erodes it. That is why sorted tables need re-clustering as part of maintenance, and why writers that can sort before writing (or route data to the right files) keep the payoff without a later rewrite.