Production Scale: Partitioning and Small File Problem

The Small File Explosion: When you ingest data continuously, you naturally create many small files. A streaming job writing every 5 minutes generates 288 batches per day. If each batch creates 10 files, you have 2,880 files daily, or 1 million files per year for a single table. Object storage handles this fine, but query planning suffers. Reading metadata for 1 million files can take 5 to 30 seconds, pushing your dashboard query p99 latency from 2 seconds to 20+ seconds. Columnar formats like Parquet also perform poorly with small files. Parquet uses row groups (typically 128 MB to 1 GB uncompressed) for efficient column scanning. A 10 MB file has minimal row groups, reducing compression ratios and increasing scan overhead. At query time, opening 1,000 small files means 1,000 S3 API calls and context switches, vs opening 10 large files. Compaction Strategy: All three formats provide compaction or optimization commands. Delta Lake has OPTIMIZE which rewrites small files into larger ones, targeting 512 MB to 1 GB per file. Iceberg and Hudi have similar compaction jobs. You typically run these on a schedule: hourly for hot partitions, daily for warm partitions, weekly for cold data. The trade off is cost. Compaction reads and rewrites data, consuming compute. For a 100 TB table with 50% daily churn, you might rewrite 50 TB per day. At 10 cents per TB scanned and written, that's 10 dollars daily. But this keeps query performance acceptable, which may save you 100 dollars in dashboard query costs and engineer time debugging slow queries. Partitioning Design: Partitioning determines how data is physically organized. A poorly designed partition scheme amplifies the small file problem. Partitioning by user_id for 10 million users means 10 million partition directories, each with potentially small files. This makes metadata operations slow and query planning expensive. Better partition schemes align with query patterns. For time series data, partition by date or hour. Queries filtering by date skip entire partitions. For multi dimensional queries, consider composite partitions like region and date, or use techniques like Z ordering (Delta) or hidden partitioning (Iceberg) where the format manages partition transforms internally. Hidden Partitioning (Iceberg): Iceberg supports partition evolution, meaning you can change partition schemes without rewriting data. If you initially partition by day but later realize hour is better, Iceberg can apply the new transform to future writes while old data remains in daily partitions. Queries work transparently because partition logic lives in metadata, not hardcoded in file paths.