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.

20 files of 10,000 rows400 files of 500 rows (same data)20 files400 filesBytes on disk11.3 MB17.6 MBTasks for SELECT *415Bytes read, SELECT *11.6 MB21.2 MBFiles for a one-day filter120Time, SELECT *0.34 s0.58 s

Measured on both layouts of the same 200,000 rows.

Measured. The 400-file layout used 17.6 MB on disk against 11.3 MB for 20 files (1.6× more, because per-file overhead and weaker compression add up), needed 15 tasks instead of 4, and a one-day filter opened 20 files instead of 1. On a laptop disk the time difference is small (0.58 s vs 0.34 s); on cloud storage each file is a separate request, which is likely to make it worse. That last point is general knowledge, not measured here.
  • 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: OPTIMIZE rewrites 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.

Per column chunk, one 10,000-row file: raw size (outline) vs on disk after compression (solid)event_id80,037 → 40,066 B (2.0×)user_id80,037 → 42,348 B (1.9×)amount80,037 → 48,227 B (1.7×)payload (random text)440,037 → 404,576 B (1.1×)country (10 values)5,135 → 375 B (13.7×)f01 (few values)3,857 → 278 B (13.9×)late_code (20 values)60 → 64 B (0.9×)Whole file: 894,844 bytes raw → 562,960 bytes on disk (1.6×).

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

As loadeduser_id = 12345 crosses all 20 filesAfter Z-ORDER by user_idcrosses 1 of 28 files (measured)Partitioned by countrycountry = 'IN' opens one folderHorizontal axis: event_date. Vertical axis: user_id. The dashed line is the filter. Every box it crosses has to be read.

Three arrangements of the same data. Only the first is free; the others need a write or OPTIMIZE.

TechniqueWhat it doesGood forWatch out
PartitioningOne folder per value of a columnLow-cardinality columns you always filter on (country, date)Too many values makes many tiny files
Z-orderSorts data along several columns so similar values share filesHigh-cardinality columns in filtersNot combinable with liquid clustering; skip columns without statistics
Liquid clustering (Databricks)Clusters by up to four keys, adjusts without rewriting everythingReplaces 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
OPTIMIZECompacts small files, and applies Z-order or clusteringFixing small filesCosts compute; predictive optimization can run it for managed tables
Measured. Z-ordering the main table by 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.
Not measured: I could not run liquid clustering locally. Creating a 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 sizeTarget fileFiles (from the docs)
1 TB256 MBabout 4,096
10 TB1 GBabout 24,437
100 TB1 GBabout 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

DoVerdict
Keep files near the target size; compact small onesPursue
Cluster or Z-order by the columns you filter and join onPursue
Partition only on low-cardinality columnsPursue
Partition by a high-cardinality column such as user_idDrop
Leave thousands of tiny files and rely on a bigger clusterDrop

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.

← 2 · Read path4 · Joins →