Diagnose a small-files explosion

At a glance

Learning objectives

• Recognize the small-files signature from file sizes and planning time
• Query metadata tables to quantify fragmentation
• Fix the layout, not just the symptoms

What you need

• A table whose queries spend more time planning than scanning
• Metadata-table access (Iceberg files, Delta history)
• An ingestion window you can schedule compaction around

Steps

1. Confirm the symptoms: tens of thousands of tiny files, query planning and listing dominating query time, metadata operations (OPTIMIZE/REPAIR) very slow. (from runbook-small-files-explosion )
2. List file sizes; avg < 32 MB and a growing count is the signature. (from runbook-small-files-explosion )
3. For Iceberg/Delta, query the metadata tables (`files`, `history`). (from runbook-small-files-explosion )
4. Compact to a 128-256 MB target; rewrite manifests if huge. (from runbook-small-files-explosion )
5. Reduce partition granularity if the layout over-partitions. (from runbook-small-files-explosion )
6. Auto-compaction on the table (Delta) or a scheduled maintenance job; partition by date, not by hour/user, unless access patterns demand it. (from runbook-small-files-explosion )

Check yourself

• The metadata query shows an average file size under 32 MB before the fix and inside the 128-256 MB target after compaction.
• Planning time no longer dominates — the same query's planning phase shrinks below scan time after compaction and layout coarsening.

Worked example

SELECT count(*), avg(file_size_in_bytes)/1e6 FROM db.tbl.files;

OPTIMIZE db.tbl ZORDER BY (user_id);  -- Delta
CALL catalog.system.rewrite_data_files('db.tbl');  -- Iceberg

Fix:
- Compact to a 128-256 MB target; rewrite manifests if huge.
- Reduce partition granularity if the layout over-partitions.
- Schedule compaction after the ingestion window, never during it.

Prevention:
- Auto-compaction on the table (Delta) or a scheduled maintenance job.
- Partition by date, not by hour/user, unless access patterns demand it.

Sourced from

• Small files explosion
• Compaction & File-Size Management

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• Compact after the ingestion window, never during it.
• ZORDER rewrites the whole table — only worth it on a real query predicate.
• Over-partitioning is often the root cause; coarsening the layout beats compacting harder.

Production considerations

• Enable Delta auto-compaction or schedule Iceberg rewrite_data_files.
• Target 128-256 MB files and alert on file-count growth.

Diagram

small files diagnosis
   |
   |- planning dominates query time --> list file sizes
   |
   |- avg < 32 MB + growing count --> metadata tables (files/history)
   |
   |- compact to 128-256 MB --> rewrite manifests if huge
   |
   |- over-partitioned? --> coarsen layout (date, not hour/user)
   |
    |- schedule compaction after ingestion window --> prevent regrowth

Registry stats