Small Files and File Formats

Scenario

Example company: a streaming job writes clickstream events to object storage as JSON files, one file per partition every 10 seconds, partitioned by date/hour/country. Analysts query them with a SQL engine over the data lake.

What you see in production

• Data volume grows 5% a month, but query time for "yesterday's clicks" grows from 20 seconds to almost 4 minutes.
• The engine spends most of its time listing and opening files: yesterday alone has more than 300,000 files, most under 200 KB.
• Storage API request costs exceed storage costs.

The naive code

# every 10 seconds, for every (hour, country) partition that received events
write_json(f"s3://lake/clicks/date={d}/hour={h}/country={c}/part-{uuid}.json", batch)

Reproduce it

The model compares a day of clickstream data stored as small JSON files with the same data compacted into large columnar (Parquet) files.

day_bytes_json = 60 * 10**9            # 60 GB of JSON per day
parquet_ratio = 0.15                   # columnar + compression is often 5-10x smaller
countries, hours = 40, 24
writes_per_hour = 360                  # one file every 10 seconds per partition

per_file_overhead_s = 0.02             # open + read footer/metadata per file, single worker
scan_mb_per_s = 200                    # read throughput per worker
workers = 32

def query_seconds(n_files, total_bytes):
    return (n_files * per_file_overhead_s + total_bytes / 1e6 / scan_mb_per_s) / workers

small_files = countries * hours * writes_per_hour
compacted_files = int(day_bytes_json * parquet_ratio / (256 * 10**6)) + 1   # ~256 MB files
query_columns_fraction = 0.1           # the query needs 3 of 30 columns

print(f"small JSON files: {small_files:>7} files, ~{query_seconds(small_files, day_bytes_json):6.0f} s per query")
cols = day_bytes_json * parquet_ratio * query_columns_fraction
print(f"compacted Parquet: {compacted_files:>6} files, ~{query_seconds(compacted_files, cols):6.1f} s per query")

small JSON files:  345600 files, ~   225 s per query
compacted Parquet:     36 files, ~   0.2 s per query

Root cause

Two layout choices compounded:

1. Too many small files. Each file costs a listing entry, an open and metadata reads, so per-file overhead dominates when files are tiny. Over-partitioning (date, hour and country) multiplied the file count.
2. Row-oriented text format. JSON must be parsed in full and can't skip unneeded columns.

The fix

• Store analytical data as columnar, compressed files (Parquet or ORC).
• Aim for large files, commonly 128 MB to 1 GB, by buffering writes or running compaction jobs that merge small files periodically.
• Partition coarsely (usually by date) and cluster or sort within files by other filter columns, instead of partitioning by every attribute.
• Use a table format (Iceberg, Delta Lake, Hudi) that tracks files in metadata, supports compaction and avoids slow directory listings.
• Keep raw JSON only in a short-lived landing zone if you need it for replay.

Trade-offs

• Compaction costs compute and adds delay before data is in its final layout.
• Streaming sinks must trade freshness (frequent small writes) against file size; compaction reconciles both.

Detect it in production

• Track files per partition and average file size for large tables.
• Watch query planning time and storage request counts.

Key points

• Per-file overhead makes many small files slow and expensive.
• Use columnar formats, files of hundreds of MB, coarse partitions and compaction.
• Table formats manage files and compaction for you.