Sorting and clustering in Iceberg: what min/max statistics can and cannot skip

• How file skipping actually works
• Seeing the statistics
• The dataset
• Unsorted: nothing is skippable
• Linear sort: 48 candidates become 1
• The secondary sort key that buys nothing
• Z-order: a different trade, not a better sort
• Keeping it sorted without rewriting
• Sorting or partitioning?
• What it costs
• Common misconceptions
• Frequently asked questions
• Conclusion
• References
• Trademarks

TL;DR

• Iceberg prunes files using per-column min/max statistics. A file is skipped only when its range cannot contain your predicate, so pruning is a property of the layout, not of the query.
• On an unsorted table, every file’s city_id range spanned almost the whole domain, so a city_id = 42 query had 48 of 48 files as candidates. Nothing was skippable.
• Sorting by that column took it to 1 of 31. The average per-file range collapsed from 499 values to 15.
• A secondary sort key buys no file-level pruning. Sorting by (city_id, amount) left the average amount range per file at the full 1000 — identical to unsorted — because amount is only ordered within each city_id.
• Z-order is not a better sort, it is a different trade. zorder(city_id, amount) made amount prunable (2 of 30) and the leading column worse than unsorted (30 of 30). Measure before assuming it is the advanced option.
• You do not have to rewrite to get this. ALTER TABLE ... WRITE ORDERED BY made plain appends land sorted: 1 of 12 candidate files instead of 16 of 16.

A read query in Iceberg is a sequence of eliminations, and the last one — skipping individual data files using the statistics held in their manifest entry — only pays off if the rows were written in an order that makes those statistics narrow. That post says so and moves on. This one measures what “narrow” is worth.

The short version is that layout, not tuning, decides how much of a table a query has to read. And the gap between a good layout and no layout is not ten percent.

How file skipping actually works

For every data file, Iceberg stores the minimum and maximum value of each column in the manifest entry that points at it. Planning a query is then arithmetic: for a predicate city_id = 42, a file whose city_id range is [100, 180] cannot contain a matching row, so it is never opened.

flowchart LR
    P["predicate<br/>city_id = 42"] --> M{"file range<br/>contains 42?"}
    M -->|no| S["skipped<br/>never opened"]
    M -->|yes| R["read<br/>and filtered"]

Two consequences follow, and they are the whole post:

• The statistic is per column, per file. It is a bounding box, not an index. It cannot express “this file contains city 42 and no others nearby”.
• A file whose range spans the whole domain is never skippable, whatever the predicate. Random insertion order produces exactly that.

Seeing the statistics

You do not have to infer any of this from timings. Iceberg exposes the numbers in the files metadata table, and readable_metrics presents them already typed:

SELECT file_path,
       readable_metrics.city_id.lower_bound,
       readable_metrics.city_id.upper_bound
FROM   ice.sortdemo.events.files;

Which makes “how many files could this query possibly need?” a query rather than a guess — count the files whose range brackets the value:

SELECT count(*) AS candidate_files
FROM   ice.sortdemo.events.files
WHERE  readable_metrics.city_id.lower_bound <= 42
  AND  readable_metrics.city_id.upper_bound >= 42;

Every number below comes from that query. It is deterministic, it does not move when the machine is busy, and it is the same decision the planner makes.

The dataset

Two million rows, city_id drawn uniformly from 500 values, written in random order — the shape you get from any ingestion that appends as data arrives. The target file size is set small so the table has enough files to be interesting on a laptop:

CREATE TABLE ice.sortdemo.events (
  id BIGINT, city_id INT, amount DOUBLE, ts TIMESTAMP)
USING iceberg
TBLPROPERTIES ('write.target-file-size-bytes'='1048576');

city_id = 42 matches 3,996 rows of the two million: 0.2% of the table. A perfect layout would read one file; a useless one reads all of them.

Unsorted: nothing is skippable

files = 48
candidate files for city_id = 42 = 48
average city_id range per file = 499 (of 500 possible)

Every file contains a near-complete spread of city_id, because the rows arrived in random order and each file is just a slice of that stream. The bounding box of each file is the whole domain, so the predicate excludes nothing. The query reads 100% of the table to return 0.2% of it, and no amount of configuration changes that.

This is the default state of any table that appends as data arrives.

Linear sort: 48 candidates become 1

rewrite_data_files with the sort strategy rewrites the table in a given order:

CALL ice.system.rewrite_data_files(
  table       => 'sortdemo.events',
  strategy    => 'sort',
  sort_order  => 'city_id ASC NULLS LAST',
  options     => map('rewrite-all','true'));

files = 31
candidate files for city_id = 42 = 1
average city_id range per file = 15.1 (of 500)

One file out of thirty-one. The mechanism is visible in the range: each file now holds about fifteen adjacent city_id values instead of all five hundred, so a single-value predicate falls inside exactly one file’s box.

Note the file count fell too, from 48 to 31 — compaction is bundled into the same operation, which is usually what you want and occasionally a surprise if you were measuring file counts for another reason.

The secondary sort key that buys nothing

The obvious next step is to sort by both columns, expecting both to become prunable. It does not work, and the reason is worth internalising:

sort_order => 'city_id ASC NULLS LAST, amount ASC NULLS LAST'

Adding amount as a secondary key changed its per-file range not at all. It is still the full domain, exactly as in the unsorted table.

That is not a bug. amount is ordered only within each city_id, and a file spans roughly sixteen different city_id values — so it contains sixteen independent runs of amount, each starting near zero and ending near the maximum. The file’s amount bounding box is therefore the whole range, and a bounding box is all the statistic can record.

A secondary sort key helps compression and row-group skipping inside a file. It does not help file-level pruning. If you need two columns prunable, linear sorting is the wrong tool.

Z-order: a different trade, not a better sort

Z-ordering interleaves the bits of several columns so that rows close in multiple dimensions end up close on disk. That is the standard answer to the problem above, so it is worth measuring rather than assuming:

sort_order => 'zorder(city_id, amount)'

Read the middle column twice. Z-ordering made the leading column worse than a linear sort by a factor of thirty, and no better than not sorting at all. What it bought was amount: from 31 candidates down to 2.

The per-column ranges say the same thing:

linear(city_id)          avg city range  15.1/500   avg amount range 1000.0/1000
zorder(city_id, amount)  avg city range 499.0/500   avg amount range   33.3/1000

So the trade is explicit. A linear sort makes one column excellent and leaves the rest untouched. A z-order spreads the benefit, and in this case spread it so unevenly that the first column got nothing.

Why so lopsided? Z-order interleaves the bits of each column’s encoded value, so a column whose encoding varies in the high-order bits dominates the resulting order. Here amount is a DOUBLE spanning a thousand values with effectively unbounded precision, and city_id is a small INT — and the ordering came out close to “sorted by amount”. What is measured above is the effect; the bit interleaving is the mechanism that produces it.

The practical rule that follows: z-order columns of comparable cardinality and type, and check the resulting ranges rather than trusting that it helped. If one column matters far more than the others, a linear sort on that column will beat z-order on it every time.

Keeping it sorted without rewriting

Everything above rewrote the table. You usually do not have to, because a table can declare the order it wants new writes to arrive in:

ALTER TABLE ice.sortdemo.events WRITE ORDERED BY city_id ASC NULLS LAST;

Writers that honour the declaration sort within the write. The same million-row append, with and without it:

One file instead of sixteen, from a declaration on the table rather than a maintenance job. The range is looser than the full rewrite achieved — 41 values per file against 15 — because each write only sorts what it is writing, not the table. That is the honest trade: cheap and continuous, versus thorough and periodic.

The two compose. Declare the order so incoming data lands roughly sorted, and run rewrite_data_files periodically to tighten what accumulated between runs.

Sorting or partitioning?

They solve the same problem at different cardinalities, and the failure mode of choosing wrong is different in each direction.

Partitioning on a high-cardinality column is the classic mistake, because each value becomes a directory and the metadata grows faster than the data. Hidden partitioning softens this by deriving partition values from a transform, but the cardinality limit remains.

Sorting has no such cliff. A sort key with a million distinct values is fine; it simply means each file covers a narrow slice. The usual answer is both: partition on the coarse column you always filter by, sort within the partition on the selective one.

What it costs

rewrite_data_files with rewrite-all reads and rewrites every file. That is a full table rewrite: the I/O of reading the table plus the I/O of writing it, and a new snapshot holding a complete new set of data files.

Three consequences worth planning for:

• The old files do not disappear. They remain referenced by older snapshots until expire_snapshots removes them, so storage goes up before it goes down — the same trap covered in table maintenance.
• It conflicts with concurrent writes. A rewrite and an append touching the same files will make one of them retry. Schedule it where writes are quiet.
• Without rewrite-all it is incremental, only rewriting files that fail the size or sort criteria, which is the right default for a recurring job.

Common misconceptions

“Sorting makes queries faster.” Sorting makes files skippable. If your predicate is not on the sort column, nothing is skipped and you paid a rewrite for nothing.

“Z-order is the better version of sort.” It is a different trade. Measured here, it was thirty times worse on the leading column and fifteen times better on the second.

“Sorting by (a, b) makes both a and b prunable.” It makes a prunable. b’s per-file range was identical to the unsorted table.

“rewrite_data_files reclaims space.” It writes new files and leaves the old ones referenced by previous snapshots. Space comes back at expire_snapshots.

“A sorted table stays sorted.” Every append after the rewrite lands unsorted unless the table declares WRITE ORDERED BY. Sortedness decays.

“More files is worse.” More, narrower files prune better than fewer, wider ones. What is bad is small files, which is a different axis — and why rewrite_data_files compacts and sorts in one pass.

Frequently asked questions

How do I know whether my table is sorted usefully? Compare the average per-file range of your filter column against its full domain, using the readable_metrics query at the top. Close to the full domain means no pruning is possible.

Does this apply to Parquet row groups too? Yes, one level down. Parquet keeps min/max per row group, so a sorted file also skips row groups inside the files it does open. That is where a secondary sort key does pay off, even though it does nothing at file level.

Does the sort order have to match the partition spec? No. They are independent, and combining them is the usual advice: partition coarsely, sort finely within the partition.

Is WRITE ORDERED BY enforced? It is a declaration that engines honour, not a constraint the format enforces. A writer that ignores it still produces a valid table — with unsorted files.

What about equality deletes and merge-on-read? Sorting applies to data files the same way regardless. Delete files carry their own statistics, and a table accumulating them has a separate problem covered in CDC into Iceberg.

Conclusion

The numbers that matter from this post are 48 → 1 and 499 → 15. A query reading 0.2% of a table read all of it because nothing about the layout let the planner rule a single file out, and one maintenance call fixed that without touching the query.

What makes the topic worth measuring rather than reading about is that the intuitive moves are wrong in specific ways. The secondary sort key looks like it should help and does nothing at file level. Z-order looks like the more advanced option and was drastically worse on the column that mattered. Both of those are visible in one query against the files metadata table, before you commit to a rewrite.

So the habit worth building is the same one that applies to skew and to tuning generally: look at the statistic the engine actually uses — here, the per-file range of your filter column — rather than at the wall clock.

References

• Iceberg configuration for write.target-file-size-bytes and the write-order properties
• Spark procedures for rewrite_data_files, its strategies and options
• Iceberg metadata tables for files, readable_metrics and the rest of the inspection surface
• SortOrder.java for how a sort order is represented in table metadata
• Life of a read query in Iceberg for where file pruning sits among the other eliminations
• Running Iceberg in production for file sizing and the jobs that have to be scheduled alongside this one

Trademarks

Apache Iceberg, Apache Spark, Apache Parquet, Apache and the Apache feather logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.