Comparison
Partitioning strategyvsSmall files problem
Partitioning strategy
you partitioned by customer id, there are two million customers, and now every query lists two million tiny folders.
The choice of what a table is physically split by, which sets how much data a filtered query can avoid reading. It is the highest-leverage physical decision in a warehouse and the hardest to change later, because changing it rewrites the table. The two failure modes are opposite and equally common: too coarse and nothing is pruned, too fine and you have the small files problem plus metadata that dwarfs the data.
Full entry →Small files problem
a streaming job wrote a file every ten seconds and now a single day's query opens eight thousand objects to read four hundred megabytes.
Query cost dominated by per-file overhead — listing, opening, reading a footer — rather than by the data itself. It is the standard consequence of frequent writes to object storage, and it degrades gradually enough that it is usually noticed as 'the warehouse got slower' months later. The fix is compaction, and the prevention is making write frequency a deliberate decision rather than a side effect of the streaming interval.
Full entry →