A platform's analytical queries slow steadily over months with no change in data volume per day. What is happening?

What is happening

Small file accumulation. Streaming or frequent-batch ingestion writes many small files. Each is cheap to write and expensive to read: per-file open cost, metadata to list and plan over, and columnar statistics that are useless because each file covers a tiny range.

Query time becomes dominated by file handling rather than by data volume — which is why it degrades with elapsed time rather than with data size.

The contributing factors

• Over-partitioning, which multiplies files by partition count.
• Frequent micro-batches, each producing at least one file per partition.
• No compaction, so the count only grows.
• Deletes accumulating in merge-on-read formats, adding read-time work until compaction applies them.
• Metadata growth, which in table formats becomes its own planning cost.

The remedies

1. Scheduled compaction, rewriting many small files into fewer large ones sized for the engine — typically in the hundreds of megabytes. This is routine maintenance, not an optimisation, and treating it as optional is the usual cause.

2. Fewer, larger write batches where latency permits, trading freshness for file size.

3. Coarser partitioning, since the pruning benefit rarely justifies a partition explosion.

4. Clustering within partitions, which delivers skipping without adding files.

5. Metadata compaction in table formats, which is a separate operation and equally necessary.

The trade to state

Compaction costs compute and rewrites data, competing with queries and ingestion for resources. It must be scheduled and resourced deliberately, and it must not run against the same capacity as latency-sensitive workloads — which is the same workload-isolation principle that applies to backfills.

The design property that prevents it

Separate the write path's latency requirement from the read path's layout requirement. Ingest in small batches for freshness, and compact asynchronously into a read-optimised layout.

Systems that write directly into their final layout must choose one or the other, and usually choose freshness and then wonder why queries slowed.