cancel
Showing results for 
Search instead for 
Did you mean: 
Community Articles
Dive into a collaborative space where members like YOU can exchange knowledge, tips, and best practices. Join the conversation today and unlock a wealth of collective wisdom to enhance your experience and drive success.
cancel
Showing results for 
Search instead for 
Did you mean: 

The Small Files Problem, and What Predictive Optimization Does About It

SrinuB
New Contributor III

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

blog9_small_files.png

Layer What it does Runs

Optimized writesRebalances data before it is written so each file is largerDuring the write
Auto compactionCombines small files within partitions after a write succeeds, only files not already compactedOn the writing cluster, right after the write
OPTIMIZERewrites small files into larger onesScheduled, 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

  1. Is the table a Unity Catalog managed table? External tables are not covered
  2. Is predictive optimization enabled for its catalog or schema? Check with DESCRIBE ... EXTENDED
  3. Does the operations history show recent OPTIMIZE runs for it?
  4. Is anything overriding the defaults, such as delta.targetFileSize or a legacy auto compaction setting? Remove what you do not need
  5. Are you calling coalesce(n) or repartition(n) just before a write? The docs advise avoiding that with optimized writes
  6. 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

  1. Small files cost per file, not per byte. Many small appends are the usual cause
  2. Optimized writes, auto compaction and OPTIMIZE work at different moments
  3. Managed tables tune file size for you. Do not set a target size unless you have a reason
  4. 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
0 REPLIES 0