Solving the Small Files Problem in Databricks: OPTIMIZE, Auto Optimize, Goldilocks Zone & the Bin Packing Algorithm Explained

Introduction

If you’ve worked with Delta Lake long enough, you’ve definitely hit the Small Files Problem.

It starts innocently:

You ingest incremental data every hour. Each micro-batch produces a few 1 MB Parquet files. After days, your Delta table contains 10,000+ tiny files.

Query performance tanks. Your cluster struggles to scan billions of tiny files. The query planner spends more time opening files than reading data.

The Small Files Problem — Why It Matters

Imagine a Delta table like this:

• 10,000 files
• Each ~1 MB
• Query:

query = "select AVG(unit_price) as avg_price from orders_managed"
res = spark.sql(query).collect()
print(res)

Even though the total data size is ≈10 GB, the query engine must:

• Open 10,000 files
• Parse 10,000 Parquet footers
• Perform 10,000 I/O operations

This becomes a metadata bottleneck. Spark spends more time asking “what’s in this file?” than reading the file itself.

Now compare with:

• 100 files of 100 MB each
• Same total size (10 GB)

Fewer files → Less metadata → Faster scans → Lower compute cost.

This is exactly what Delta Lake’s OPTIMIZE operation fixes.

1. Manual OPTIMIZE — Your First Line of Defense

The OPTIMIZE command rewrites small files into larger ones:

%sql
DROP TABLE IF EXISTS orders_managed;

CREATE OR REPLACE TABLE orders_managed (
  order_id BIGINT,
  sku STRING,
  product_name STRING,
  product_category STRING,
  qty INT,
  unit_price DECIMAL(10,2)
)
USING DELTA
TBLPROPERTIES (
  delta.autoOptimize.optimizeWrite = false,
  delta.autoOptimize.autoCompact = false
)

from pyspark.sql import Row
from pyspark.sql.types import DecimalType
from pyspark.sql.functions import col
import time

N = 100

for i in range(N):
    row = Row(order_id=int(i+1),
              sku=f"SKU-{(i%10)+1}",
              product_name=f"Product-{(i%50)+1}",
              product_category=f"Category-{(i%5)+1}",
              qty=(i % 10) + 1,
              unit_price=round(10.0 + (i%7)*1.5, 2))

    df = spark.createDataFrame([row])

    df = df.withColumn("qty", col("qty").cast("int")).withColumn("unit_price", col("unit_price").cast(DecimalType(10,2)))

    df.write.format("delta").mode("append").saveAsTable("orders_managed")
    # optional tiny sleep to mimic real small-batch writes
    # time.sleep(0.02)

print(f"Inserted {N} tiny writes.")

%sql
select * from orders_managed where order_id = 56;

This query reads data from 1 file and prunes the rest.

query = "select AVG(unit_price) as avg_price from orders_managed"
res = spark.sql(query).collect()
print(res)

This query reads all the files which is a small files issue.

%sql
OPTIMIZE orders_managed;

After Optimize command:

query = "select AVG(unit_price) as avg_price from orders_managed"
res = spark.sql(query).collect()
print(res)

This query now reads data from only 7 files which solves small files issue by combining smaller files into medium sized files.

Under the hood, Delta Lake uses the Bin Packing Algorithm to intelligently combine files.

You can tune the target file size (possible only in classic compute):

SET spark.databricks.delta.optimize.maxFileSize = <bytes>;

Typical range for optimal file sizes: 16 MB — 1 GB (This is called the Goldilocks Zone — not too big, not too small.)

Bin Packing Algorithm — Behind the Scenes

To compact files, Delta uses a variant of the bin packing algorithm.

Imagine your table has:

100MB Bin 2
300MB Bin 2
300MB Bin 1
100MB Bin 1
600MB Bin 1
300MB Bin 2
600MB Bin 3
300MB Bin 4
300MB Bin 4
100MB Bin 4
100MB Bin 4

Goal: Pack files together without exceeding the maxFileSize (e.g., 1 GB).

Example Packing

• Bin 1 → 600MB + 300MB + 100MB = 1,000 MB
• Bin 2 → 300MB + 300MB + 100MB = 700 MB
• Bin 3 → 600MB (alone)
• Bin 4 → 300MB + 300MB. etc.

This balances:

• Scan efficiency
• Parallelism
• File system throughput

This is why OPTIMIZE is not a simple merge — it is an intelligent grouping.

2. Auto Optimize — Hands-Free Compaction

Databricks can automatically reduce small files in the background.

Two properties control it:

Triggering point (when Auto Optimize activates)

spark.databricks.delta.autoCompact.minNumFiles

This is the minimum number of small files required before Auto Optimize runs.

Example:

• If set to 10, and a microbatch produces 15 files → Auto Optimize triggers
• If set to 10, and a microbatch produces 4 files → Auto Optimize does nothing

Default = 50 files

This means:

Auto Optimize (Auto Compact) will only trigger when at least 50 small files exist in a partition. If fewer than 50 files are present, Databricks will not automatically compact them.

Why 50?

This threshold prevents unnecessary compaction work in scenarios where:

• Microbatch writes produce only a few files
• Workload is streaming-heavy
• File generation is bursty

It ensures compaction runs only when it is likely to bring real performance benefit.

Target optimized file size

spark.databricks.delta.optimize.maxFileSize = 1 GB

Auto Optimize uses the same bin packing algorithm internally and rewrites files that exceed the configured threshold of “too many small files.”

Auto Optimize Handles Two Things

1️⃣ Optimize Write — rewrites files during write 2️⃣ Auto Compact — combines small files after write

This prevents the table from accumulating thousands of tiny files over time.

The Goldilocks Zone: Finding “Just Right” File Sizes

Delta Lake works best when files are:

• Large enough to reduce metadata overhead
• Small enough to allow parallelism

Ideal range: 16 MB → 1 GB

Why?

• Files smaller than 16 MB → Too many small files = slow reads
• Files larger than 1 GB → Less parallelism = slow big queries

This balance is why compaction is critical.

Real-World Scenario

❗ Scenario: Hourly incremental loads

• Every hour, 50 new micro-partitions arrive
• Each task writes tiny 1–2 MB Parquet files
• After 90 days → table has over 100,000 files
• Query latency increases by 5–8×

Fix:

1. Enable Auto Optimize
2. Run periodic OPTIMIZE
3. Schedule weekly compaction jobs

Result:

• 100,000 files compress to ~2,000
• 70–80% faster aggregations
• Lower compute cost on interactive queries

Knowing When NOT to Optimize

Avoid running OPTIMIZE too frequently in:

• High-churn streaming tables
• Tables with extremely frequent writes
• Very large datasets where compaction is expensive

Best practice: Set up a scheduled OPTIMIZE job during low-traffic hours.

Interview Questions

Q: What is the small files problem? A: Small files cause excessive metadata overhead and slow queries because Spark must open and process thousands of Parquet files.

Q: What does OPTIMIZE do? A: It rewrites many small files into fewer large ones using a bin packing algorithm.

Q: What is the purpose of spark.databricks.delta.optimize.maxFileSize? A: It defines the target max file size (e.g., 1 GB) for compaction.

Q: How does Auto Optimize decide when to run? A: Based on the number of files exceeding:

spark.databricks.delta.autoCompact.minNumFiles

When this threshold is reached, compaction is triggered.

Q: What is the Goldilocks Zone for Delta file sizes? A: Between 16 MB and 1 GB — optimal balance of scan efficiency and parallelism.

Q: How does the bin packing algorithm improve performance? A: It distributes file sizes into bins without exceeding maxFileSize, balancing partition sizes for parallel execution and minimizing the number of files.

Q: What are the trade-offs of Auto Optimize? A:

Pros:

• Hands-free compaction
• Prevents small file accumulation

Cons:

• CPU/memory overhead during streaming
• Not ideal for massive ingestion pipelines
• Harder to control compaction timing

Q: How would you design a strategy to minimize small files in a real-time ingestion system? A: Architectural answer:

• Use fewer shuffle partitions
• Use Auto Optimize (compaction + optimize write)
• Periodic manual OPTIMIZE
• Tune microbatch size
• Consider using mergeSchema=false and merge-on-read patterns
• Avoid excessive parallelism on write clusters

Q: What happens if two large OPTIMIZE jobs run at the same time? A:

Potential issues:

1. Duplicate compaction work (idempotent but expensive)

OPTIMIZE is idempotent, meaning running it twice produces the same end result — but both jobs will redundantly scan data files, plan compaction, and rewrite files, essentially doubling the compute cost for no real benefit.

2. Heavy cluster load

Both jobs consume CPU, memory, shuffle bandwidth, and executors simultaneously, causing the cluster to become saturated and slowing down all other workloads running on it.

3. Resource contention

Parallel OPTIMIZE jobs may compete for the same underlying resources (file system bandwidth, shuffle space, metadata operations), which can lead to failures, slower progress, or increased retries.

4. Higher cost

Since both jobs perform computationally expensive rewrite operations, running them concurrently dramatically increases DBUs consumed, leading to unnecessary cloud cost spikes.

Mitigation Strategies:

1. Use job orchestration

Tools like Databricks Workflows or Airflow ensure OPTIMIZE jobs do not overlap by scheduling them sequentially or enforcing dependencies between tables or pipelines.

2. Use table-level locking

Delta Lake’s transaction protocol will prevent simultaneous writes, but explicitly designing workflows to avoid overlapping OPTIMIZE operations on the same table avoids lock waits, delays, or commit conflicts.

3. Run OPTIMIZE in off-hours

Scheduling compaction during low-traffic periods ensures that expensive file rewrites do not interfere with daytime production queries or streaming workloads, improving overall platform stability.

Q: How do small files relate to streaming workloads? A: Streaming writes often produce small files because:

• Many microbatches
• Many parallel tasks
• Low input volume per microbatch

Solution: Auto Optimize + tuned trigger intervals + proper partitioning.

Production Incident Caused by Overlapping OPTIMIZE Jobs

Context

We had a Delta Lake table in our Silver layer that received hourly incremental updates. To maintain performance, an automated nightly OPTIMIZE job compacted small files. Another team, unaware of this job, added their own weekly OPTIMIZE process targeting the same table but scheduled at slightly overlapping times.

S — Situation

In our Lakehouse environment, we had a Delta table in the Silver layer that received hourly incremental updates. To maintain performance, we scheduled a nightly OPTIMIZE job for file compaction. Separately, another team introduced a weekly OPTIMIZE job on the same table without coordination. One night, both OPTIMIZE jobs ran simultaneously, targeting the same dataset.

T — Task

My responsibility was to identify the root cause of the severe platform slowdown, mitigate the performance impact, and redesign the maintenance strategy so such conflicts wouldn’t reoccur.

A — Action

1. Investigation

• Used DESCRIBE HISTORY to identify overlapping OPTIMIZE operations.
• Analyzed cluster metrics and saw heavy CPU saturation, shuffle spill, and metadata contention.
• Confirmed both jobs attempted large rewrite operations on the same table, causing transaction log lock waiting and retries.

2. Immediate Remediation

• I terminated one of the OPTIMIZE jobs to free the cluster.
• Increased cluster resources temporarily to stabilize downstream workloads.
• Cleared the backlog in streaming jobs caused by slow file rewrites.

3. Long-Term Fix

• Centralized all maintenance tasks under Databricks Workflows, ensuring OPTIMIZE jobs on shared tables never overlap.
• Added table-level constraints and documentation for when OPTIMIZE, Auto Optimize, and Z-Ordering should run.
• Introduced monitoring and alerting for:

— concurrent maintenance jobs

— long-running OPTIMIZE commands

— abnormal DBU spikes

— Educated cross-functional teams about compaction policies and Lakehouse governance.

R — Result

• Query performance returned to normal within an hour of intervention.
• Monthly DBU waste dropped because duplicate compaction was eliminated.
• Streaming workloads recovered fully and resumed meeting SLA.
• Future OPTIMIZE conflicts were completely avoided due to orchestrated scheduling.
• As a side benefit, we standardized compaction strategy across all tables, improving overall platform stability.

This incident reinforced that Delta compaction is not purely a technical DBA task — it’s a platform governance responsibility. Even though OPTIMIZE is idempotent, running it concurrently can severely impact performance, cost, and availability. Designing controlled, orchestrated maintenance workflows became a key architectural principle in our Lakehouse environment.

Final Thoughts

The small files problem isn’t just a performance issue — it’s an architectural challenge. Understanding how OPTIMIZE, Auto Optimize, and bin packing work allows you to build fast, scalable, AI-ready data platforms.

Delta Lake gives you the tools — your job is knowing when to use each.