Home › Learn › How reads and writes work › 3 · Layout
Delta Lake and Spark · from zero to advanced
Layout: small files, min and max, columns and compression
Why the way files are organised decides how fast reads are: the small file problem measured, overlapping min and max ranges, columnar storage and compression with real chunk sizes, and OPTIMIZE, Z-order, liquid clustering and partitioning.
The small file problem
The same rows in 400 files instead of 20
Nothing about the data changed: same rows, same columns. Only the number of files did. Every file has its own footer to read and its own task to schedule, so many tiny files cost more than a few right-sized ones.
Measured on both layouts of the same 200,000 rows.
- Where small files come from: frequent small appends, streaming micro-batches, too many shuffle partitions at write time, and partitioning by a column with too many values.
- The fix is compaction:
OPTIMIZErewrites many small files into fewer big ones (the write-path page shows a real one: 20 files removed, 1 added).
Columns and compression
Why columnar files shrink, with real chunk sizes
Each column chunk is compressed on its own, and a chunk holds values of one type with many repeats. That is why column files shrink so well. The sizes below come from the footer of one real file.
Raw vs compressed size per column chunk. Few distinct values means big savings.
- country has 10 values: 5,135 → 375 bytes (14×). The footer lists the encoding
PLAIN_DICTIONARY: store each distinct value once, then small numbers that point to it. - payload is random text, so it barely shrinks (1.09×) and is the biggest chunk on disk (405 KB). That is why selecting only two columns read just 25% of the bytes.
- Compression and skipping are one idea: a column with few or ordered values is both smaller and easier to skip.
Organising files
Arrange the files so filters cross fewer of them
Three arrangements of the same data. Only the first is free; the others need a write or OPTIMIZE.
| Technique | What it does | Good for | Watch out |
|---|---|---|---|
| Partitioning | One folder per value of a column | Low-cardinality columns you always filter on (country, date) | Too many values makes many tiny files |
| Z-order | Sorts data along several columns so similar values share files | High-cardinality columns in filters | Not combinable with liquid clustering; skip columns without statistics |
| Liquid clustering (Databricks) | Clusters by up to four keys, adjusts without rewriting everything | Replaces partitioning and Z-order for new tables (Databricks Runtime 15.4 LTS and later) | Keys must have statistics, so be within the first 32 columns |
| OPTIMIZE | Compacts small files, and applies Z-order or clustering | Fixing small files | Costs compute; predictive optimization can run it for managed tables |
user_id (with a small target file size, so it produced 28 files) turned the random-column query from 20 of 20 files into 1 of 28, and from 12.0 MB read to 607 KB.CLUSTER BY table and running OPTIMIZE on open-source Delta 3.2.0 raised an error, so the liquid clustering rows are from the Databricks docs only. Z-order, which does the same job in the same family, was measured.File size
How big should a file be
The Databricks docs say tables under 2.56 TB get a 256 MB target, growing linearly to 1 GB between 2.56 TB and 10 TB, and 1 GB above that. Unity Catalog managed tables tune this automatically. For others you can set delta.targetFileSize.
| Table size | Target file | Files (from the docs) |
|---|---|---|
| 1 TB | 256 MB | about 4,096 |
| 10 TB | 1 GB | about 24,437 |
| 100 TB | 1 GB | about 114,437 |
- Auto compaction (
delta.autoOptimize.autoCompact) compacts small files after a write; optimized write (delta.autoOptimize.optimizeWrite) shuffles before writing so files come out a good size. - Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE for Unity Catalog managed tables, and is on by default, per the docs.
What to do
Layout checklist
| Do | Verdict |
|---|---|
| Keep files near the target size; compact small ones | Pursue |
| Cluster or Z-order by the columns you filter and join on | Pursue |
| Partition only on low-cardinality columns | Pursue |
| Partition by a high-cardinality column such as user_id | Drop |
| Leave thousands of tiny files and rely on a bigger cluster | Drop |
FAQ
Questions people ask
What is the small file problem?
Many tiny files mean many footers to read, many tasks to schedule and, on cloud storage, many requests. The same rows in 400 files instead of 20 took about 1.6 times the bytes on disk and opened 20 files instead of 1 for a one-day filter in the local test.
Why do columns compress better than rows?
A column chunk holds values of one type with many repeats, so dictionary and run-length encoding work well. In the real file a 10-value column shrank about 14 times, while random text barely shrank.
Z-order or liquid clustering?
On Databricks the docs recommend liquid clustering for new tables and say it replaces partitioning and Z-order. Z-order is still the older option and cannot be combined with liquid clustering. Only Z-order was measured here.