Optimization · Delta Lake · Why a table gets slower as it grows, and what to stop doing by hand
By @SrinuB in collaboration with @sridharplv
A table that loads every 10 minutes works fine for a month, then dashboards on it start crawling. Nothing in the SQL changed. The table now holds hundreds of thousands of tiny files, and every query pays to open each one.
This is the small files problem. The good news is that on Unity Catalog managed tables most of it is now handled for you, and the useful skill is knowing what is automatic, what is not, and how to check.
Why small files hurt
Every file a query touches costs a metadata lookup, an open and a read request, no matter how little data is in it. Frequent small appends, streaming micro-batches and heavily partitioned writes all create many small files. The data is the same, but the query spends its time on overhead instead of on reading.
Three layers, not one

Layer What it does Runs
| Optimized writes | Rebalances data before it is written so each file is larger | During the write |
| Auto compaction | Combines small files within partitions after a write succeeds, only files not already compacted | On the writing cluster, right after the write |
| OPTIMIZE | Rewrites small files into larger ones | Scheduled, or by predictive optimization |
Per the docs, optimized writes are on by default for MERGE, and for UPDATE and DELETE with subqueries. They are also on for CTAS and INSERT on SQL warehouses. Auto compaction is always on for MERGE, UPDATE and DELETE.
Let Databricks pick the file size
For Unity Catalog managed tables, the docs say manual file size tuning recommendations do not apply, because they use automatic file size tuning. The target grows with the table:
- Under 2.56 TB: 256 MB
- 2.56 TB to 10 TB: grows linearly from 256 MB to 1 GB
- Over 10 TB: 1 GB
A table with an explicit delta.targetFileSize is not autotuned, and existing files are not rewritten when the target grows. For new tables the docs say to use managed tables with default settings.
What predictive optimization adds
Predictive optimization is the part that removes the maintenance job. For Unity Catalog managed tables it runs:
- OPTIMIZE, including incremental liquid clustering and file size optimization (it does not run ZORDER)
- VACUUM, removing unreferenced files (default retention 7 days)
- ANALYZE, collecting statistics
It needs a Premium plan or above and a supported region, and it runs on serverless compute for jobs, billed under a serverless jobs SKU. It does not run on external tables.
Check it, do not assume it
Enable and inherit at the level you want:
-- See the current status
DESCRIBE CATALOG EXTENDED main;
-- Turn it on for a schema, or let it inherit from the catalog
ALTER SCHEMA main.sales ENABLE PREDICTIVE OPTIMIZATION;
ALTER TABLE main.sales.orders INHERIT PREDICTIVE OPTIMIZATION;
You need to be the owner or hold MANAGE on the object. Then see what it actually did:
SELECT *
FROM system.storage.predictive_optimization_operations_history
ORDER BY start_time DESC
LIMIT 50;
Run this and filter to your own table. If a table you expected to be optimized has no rows, find out why before you add a manual job.
A checklist
- Is the table a Unity Catalog managed table? External tables are not covered
- Is predictive optimization enabled for its catalog or schema? Check with DESCRIBE ... EXTENDED
- Does the operations history show recent OPTIMIZE runs for it?
- Is anything overriding the defaults, such as delta.targetFileSize or a legacy auto compaction setting? Remove what you do not need
- Are you calling coalesce(n) or repartition(n) just before a write? The docs advise avoiding that with optimized writes
- For tables over 1 TB, the docs still recommend scheduling OPTIMIZE
What I would do (opinion, not from the docs)
Move frequently written tables to managed tables with defaults, turn on predictive optimization at the catalog, and delete the hand-written nightly OPTIMIZE and VACUUM jobs only after the history table shows it is doing the work. Keep the manual job for external tables, which nothing automatic covers.
The takeaway
- Small files cost per file, not per byte. Many small appends are the usual cause
- Optimized writes, auto compaction and OPTIMIZE work at different moments
- Managed tables tune file size for you. Do not set a target size unless you have a reason
- Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE on managed tables, and the history table proves it
How many files does your busiest table hold, and when did it last get optimized?
References
- Predictive optimization for Unity Catalog managed tables, Databricks documentation
- Configure Delta Lake to control data file size, Databricks documentation
- Predictive optimization system table reference, Databricks documentation