Zone maps and clustering

Measure your machineNeeds Python with PyArrow

Problem

A zone map keeps the minimum and maximum of each column for each block of rows, and a query skips blocks whose range cannot match. It is a lightweight index whose power comes entirely from how the data is ordered: sorted by the column, each block covers a narrow slice; in random order, each block covers almost everything.

Write a Parquet file of 50 million timestamped rows in random order, and another with the same rows sorted by timestamp. Run a one-hour time-range query on each and count the row groups read, from the row-group statistics.

The question. How many row groups does a one-hour query read in each file?

Predict

In the random-order file, how many of the 50 row groups does a one-hour query read?

1 or 2A fewMost or all

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.

zone-maps-and-clustering.py44 linesDownload

View the code

zone-maps-and-clustering.py

# /// script
# requires-python = ">=3.10"
# dependencies = ["duckdb", "pyarrow"]
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""Zone maps and clustering.

50 million timestamped rows written to Parquet in random order and sorted by
time; a one-hour range query on each: how many row groups do their min/max
statistics let a reader skip?
"""
import argparse, datetime, json, os, tempfile
import duckdb
import pyarrow.parquet as pq

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--rows", type=int, default=50_000_000, help="Rows.")
p.add_argument("--row-group", type=int, default=1_000_000, help="Rows per row group.")
p.add_argument("--dir", default=os.path.join(tempfile.gettempdir(), "zone-maps-indexing"), help="Where the files go.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()
import atexit, shutil
atexit.register(lambda: shutil.rmtree(args.dir, ignore_errors=True) if os.path.isdir(args.dir) else os.path.exists(args.dir) and os.remove(args.dir))   # leave nothing behind
os.makedirs(args.dir, exist_ok=True)
con = duckdb.connect()
con.execute(f"CREATE TABLE t AS SELECT TIMESTAMP '2025-01-01' + INTERVAL (i * 2) SECOND AS ts, i AS id FROM range({args.rows}) r(i)")
paths = {"random order": f"{args.dir}/random.parquet", "sorted by time": f"{args.dir}/sorted.parquet"}
con.execute(f"COPY (SELECT * FROM t ORDER BY random()) TO '{paths['random order']}' (ROW_GROUP_SIZE {args.row_group})")
con.execute(f"COPY (SELECT * FROM t ORDER BY ts) TO '{paths['sorted by time']}' (ROW_GROUP_SIZE {args.row_group})")
lo, hi = con.execute("SELECT min(ts), max(ts) FROM t").fetchone()
q0 = lo + (hi - lo) / 2; q1 = q0 + datetime.timedelta(hours=1)
rows = []
for label, path in paths.items():
    md = pq.ParquetFile(path).metadata
    st = [md.row_group(i).column(0).statistics for i in range(md.num_row_groups)]
    read = sum(1 for s in st if not (s.max < q0 or s.min > q1))
    rows.append({"file": label, "row_groups": md.num_row_groups, "read": read})
    print(f"{label:<16} one-hour query reads {read} of {md.num_row_groups} row groups")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"files": 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 zone-maps-and-clustering.py
uv run zone-maps-and-clustering.py --help     # every knob
```

Knobs
Knob · default	What it changes
--rows50,000,000	Rows.
--row-group1,000,000	Rows per row group.
--diros.path.join(tempfile.gettempdir(), 'zone-maps-indexing')	Where the files go.

Our run

One-hour query

One-hour query
File	Row groups read	Row groups
random order	50	50
sorted by time	1	50

Reference runApple M2 · 8 cores · 8 GB

Output · uv run zone-maps-and-clustering.py

random order     one-hour query reads 50 of 50 row groups
sorted by time   one-hour query reads 1 of 50 row groups

macOS 26.6.2 · Python 3.13.3 · duckdb 1.5.6 · pyarrow 26.0.0 · run 2026-10-20

What we found

The random file had to read 50 of 50 row groups and the sorted file 1, as expected.

Read it

The index is the same in both files; only the order differs. A zone map does nothing for data written in arbitrary order, which is why table formats sort or cluster on the columns people filter by when they write or compact.