Liquid Clustering "wins three of four workloads", but only three were measured

Should a big table be partitioned by date, Z-ordered, or switched to Liquid Clustering? A benchmark by Kushal Kumar Vishwakarma, republished by InfoQ China on 24 October, runs all three against the same data and four kinds of query.¹ It’s worth a read for any Databricks team, as long as you check the numbers row by row.

Same 50 million rows, three ways to lay them out

The data is about 50 million synthetic retail transactions. The partitioned table splits on txn_date; ZOrder and Liquid Clustering share three keys, customer_id, region and txn_date. Each query ran five times with caching off, and the figures are medians of time and bytes read.² ZOrder weakens with every extra key;⁴ Liquid Clustering runs OPTIMIZE incrementally and lets you change keys later.³

Three query types, three different winners

• One customer (W1): Liquid Clustering takes 1.003 s, 49% faster than partitioning. ZOrder reads the least yet is 0.4 s slower, so fewer bytes isn’t the same as faster.²
• One month (W2): with the date third in the ZOrder key, ZOrder is 78% slower than plain date partitioning.¹ If the date isn’t first in your own ZOrder key, test your range queries first. Liquid Clustering reads as much as the partitioned table and is still about 37% faster.²
• Three-column filter (W3): Liquid Clustering’s one loss, nearly 5x slower and about 10x more data.¹ Partitioning lands just 7 ms behind ZOrder, so the lesson is to avoid Liquid Clustering for this shape of query.²

The fourth workload, a full scan, repeats the second one’s numbers

The article’s fourth workload is a full-table SUM grouped by region, and it reports Liquid Clustering fastest (659 ms), ZOrder slowest (1.845 s) and partitioning in between (1.038 s).¹ Those latencies and bytes read are identical, figure for figure, to W2’s one-month query, and already were in the first Medium version from 15 July.⁵ Neither the article nor the code shows W4 being run on its own, so it can’t serve as a comparison for now.²

Three layouts in one table

Databricks also recommends Liquid Clustering for new tables.³ The real answer is in your own query history, in four steps:

1. Group your queries by the columns they filter on and pick the three most frequent groups.
2. Take a sample that keeps your real hot spots and skew, and build one table per layout.
3. Run each query five times with caching off, take the median, and record both latency and bytes read from the query profile. The author’s code is on GitHub, ready to adapt.²
4. Add the two things the article skipped: a full-scan aggregation, and the compute it takes to keep each layout in shape after every day’s writes.