Lakehouse Table Optimization: When to Re-Optimize

Re-optimize a lakehouse table after data changes when the number of small files or the level of data skew degrades query performance beyond an acceptable threshold—typically after a large batch load, frequent incremental inserts, or a major update/delete operation. You enter the current table statistics (file count, size, and partition layout) and the calculator below estimates the potential gain from compaction, so you can decide if a re-optimization is worth the compute cost.

Formula

The core calculation is straightforward: optimal file count = total data size (in MB) ÷ target file size (in MB). The reduction factor is your current file count divided by that optimal count. The estimated speedup is a conservative square-root scaling, reflecting that query time often scales with the number of files read, but not perfectly linearly due to parallelism and metadata overhead.

Variables:

• f – current number of files in the table (or partition).
• s – total logical size of the data, in GB.
• t – desired file size after compaction, typically 128–512 MB for Parquet.
• ratio – how many times fewer files you could have after compaction.
• gain – rough estimate of query speedup, capped at 10x for realism.

This is a heuristic, not a precise benchmark. Real gains depend on your query engine, cluster size, and file format.

Worked Example

Using the defaults: a table has 1,000 files totaling 100 GB. You set a target file size of 128 MB.

1. Convert total size to MB: 100 GB × 1024 = 102,400 MB.
2. Compute optimal file count: 102,400 ÷ 128 = 800 files.
3. Reduction factor: 1,000 ÷ 800 = 1.25x.
4. Estimated speedup: sqrt(1.25) ≈ 1.12x.

So with only 1.25x fewer files, the query speedup is modest. If you had 10,000 files for the same data, the factor would be 12.5x and the estimated speedup about 3.5x—a clear signal to re-optimize.

Why File Count Matters in Lakehouses

Unlike a traditional database that manages storage internally, a lakehouse table is just a directory of files (usually Parquet, ORC, or Delta format) on object storage like S3 or ADLS. Every query opens a set of files, reads metadata, and scans data. The more files, the more overhead:

• Metadata operations – listing files and reading footers can dominate for small files.
• Task scheduling – Spark or Trino spawns tasks per file; too many tiny tasks wastes cluster resources.
• Predicate pushdown – with many small files, statistics per file are less useful, so more data is read.

Conversely, too few huge files reduce parallelism and make it hard to split work across nodes. The sweet spot is usually 128–512 MB per file, but it depends on your engine and cluster.

What Counts as a “Data Change” That Triggers Re-Optimization

Not every write needs immediate compaction. Common triggers:

• Bulk loads – a one-time ingestion of many small files (e.g., from Kafka or a data dump).
• Incremental inserts – frequent small appends (every few minutes) that create a new file each time.
• Update/delete operations – in Delta Lake or Iceberg, these create new files and tombstone old ones, leaving many small files.
• Partition changes – repartitioning or adding new partitions with uneven data distribution.

A good rule of thumb: if your file count is more than 3–5x the optimal count (from the formula), re-optimization is likely worthwhile. If it’s less than 2x, the compute cost may exceed the query benefit.

How to Re-Optimize: Compaction and Clustering

Different lakehouse formats have built-in tools:

• Delta Lake – OPTIMIZE command with ZORDER BY for data skipping.
• Apache Iceberg – rewrite_data_files procedure, with optional sort_order.
• Apache Hudi – run_clustering or inline compaction during writes.

These operations read the existing files, merge them into larger ones, and rewrite the table metadata atomically. They don’t block reads, but they do consume compute. You can run them on a schedule (e.g., nightly) or after each major batch job.

Common Mistakes and Misconceptions

Mistake: Re-optimizing too often. Running compaction after every micro-batch can cost more than it saves. Set a threshold based on file count or data volume change.

Mistake: Ignoring partition skew. If one partition has 10,000 small files and another has 10 large files, optimizing only the skewed partition is more efficient than the whole table.

Misconception: “More files = always bad.” For very small tables (<1 GB), a few hundred files may be fine. Also, highly selective queries that use partition pruning may not suffer much from many files in unqueried partitions.

Mistake: Using target file size too small. 64 MB might be fine for a small cluster, but 256 MB often works better for larger ones. Test with your workload.

Costs and Trade-offs of Re-Optimization

Compaction rewrites data, which costs storage I/O and compute. For a 1 TB table, rewriting 10% of it daily might be acceptable; rewriting 100% every hour is not. Also, every rewrite creates a new version of the table, increasing metadata history (in Delta and Iceberg). You need to expire old snapshots periodically to avoid unbounded metadata growth.

On the benefit side, re-optimization can cut query time by 2–10x in file-heavy workloads, and reduces the number of API calls to object storage, which can lower costs. The net effect is usually positive if you target the right tables at the right time.

When NOT to Re-Optimize

Skip re-optimization when:

• The table is rarely queried (e.g., cold archival data).
• The data is append-only and queries always filter by a recent partition, so old partitions are never read.
• Your cluster is small and the rewrite time exceeds the query savings over the next week.
• The table is already well-sized (file count within 2x of optimal).

In these cases, leaving the files as-is is cheaper and simpler.

Frequently Asked Questions

How often should I run OPTIMIZE on a Delta table?

It depends on write frequency. For streaming jobs that append every minute, run compaction every few hours or when the file count exceeds a threshold (e.g., 1000 files per partition). For batch jobs, run it after each load if the load creates many small files. There’s no universal schedule; monitor query times and file counts.

Does re-optimization affect data correctness or ACID guarantees?

No. Compaction in Delta, Iceberg, and Hudi is atomic and transactional. Readers see either the old or new version, never a mix. The data content is unchanged; only the physical layout is rewritten.

What is the best target file size for my lakehouse table?

Start with 128 MB for Parquet, then test 256 MB and 512 MB. Larger files reduce metadata overhead but may hurt parallelism on small clusters. If your queries are latency-sensitive and your cluster has many cores, larger files (256–512 MB) often work better.

Can I re-optimize only a subset of partitions?

Yes. Delta’s OPTIMIZE accepts a partition predicate, and Iceberg’s rewrite_data_files supports where clauses. This is useful for skewed tables where only a few partitions are problematic.

Why does my query still run slow after compaction?

Compaction fixes file size, but not other issues like poor sorting, missing statistics, or bad join strategies. Check if your table needs Z-ORDERING or sorting by frequently filtered columns, and verify that your query engine is using file statistics for pruning.

Does re-optimization make my table bigger on storage?

Temporarily, yes—the old files remain until the snapshot expires. But after cleanup, the logical size is the same. In fact, compaction can reduce storage if many small files had high overhead (e.g., Parquet footer per file).

Related

• Windows 11 Black Screen of Death: Causes & Fixes
• Hot Work Gear Set Guide for Arknights Endfield
• Time Synchronization Failed? Fix It Now

in f | Current file count | files | 1000 | 1
in s | Total data size | GB | 100 | 0.1
in t | Target file size | MB | 128 | 16
out ratio | File count reduction factor | x | f / max(1, round(s * 1024 / t)) | 1
out gain | Estimated query speedup | x | min(10, max(1, sqrt(ratio))) | 1
out opt | Optimal file count | files | max(1, round(s * 1024 / t)) | 0