Why do columnar engines target data files of hundreds of megabytes rather than a few megabytes?

## The two failure modes Data-file sizing in a columnar engine is not a single-direction rule of thumb. Both extremes have concrete, explainable costs, and a good interview answer names both. ### Too small Every file carries fixed overhead that is independent of how many rows it holds: - **Metadata.** The footer describes every column: encoding, offsets, sizes, per-column min/max statistics, sometimes null counts and dictionaries. In a file with a few hundred rows, this can exceed the data payload. - **Catalog pressure.** The table's file list is itself data the planner must read. Millions of entries make query planning slow before scanning even starts. - **I/O round trips.** On object storage a read has a latency floor per request. Fetching 2 MB costs nearly as much wall-clock as fetching 100 MB when latency dominates transfer; ten thousand small requests are hugely more expensive than a hundred large ones for the same bytes. Where the store charges per request, this also shows on the bill. - **Lost compression.** Dictionary, run-length and delta encodings amortise their own overhead across many values. Small blocks compress badly, so small files store the same logical data in more bytes. - **Scheduling overhead.** Engines assign work per file or per chunk; a tiny file still costs a task to schedule, a thread to run and a result to collect. ### Too large - **Parallelism collapses.** The unit of parallel scan work is bounded by file (or sub-file chunk) granularity. If a 1 TB table is eight files and the cluster has two hundred workers, most workers have nothing to do. Rule of thumb: you want comfortably more independent chunks than you have parallel workers, so stragglers can be balanced. - **Coarser skipping.** Per-file statistics summarise the whole file. A wider range in a file's min/max makes it less likely the file can be eliminated, so scans read more bytes than a smaller-file layout would. - **Expensive mutations.** Because replacement happens at file granularity, a delete or update of a handful of rows rewrites an entire file. Bigger files mean a bigger rewrite for the same logical change. - **Writer memory and failure cost.** A writer buffers and encodes a file before publishing it; larger targets need more memory, and a failed write throws away more work. ## Where the balance lands Engines and table formats converge on a target measured in tens to a few hundred megabytes **compressed**, with internal sub-divisions (row groups, blocks, granules) that give finer-grained parallelism and statistics inside a single file. That layering is the real answer to the tension: the file is sized for the storage layer and the metadata layer, while the sub-file chunk is sized for the scan and skipping layer. Compressed size matters more than row count for the storage side; row count matters more for the encoding and vectorized-read side. In practice you tune batches by both — flush at N megabytes or M rows, whichever trips first. ## Achieving the target Two mechanisms, and both are usually needed: 1. **At write time**, batch. Buffer rows until the batch would produce a file near the target, then commit. This is the cheap path — bytes are written once. 2. **After the fact**, compact. Background or scheduled merges read many small files and write fewer large ones. This is the expensive path — the same bytes are written at least twice — but it is the only remedy for a stream whose natural batch is small, and for tables whose partitioning produces uneven file sizes. A related trap: over-partitioning. Splitting a table into very fine partitions caps the amount of data any single file can hold. A table partitioned by hour and by tenant across thousands of tenants may physically be unable to produce large files, because each partition receives only a trickle. When a small-file problem resists compaction, the partitioning scheme is often the real cause. ## Signals in production Average bytes per file, file count per partition, and the ratio of planning time to execution time are the metrics that expose this. When planning time grows as a share of total query time and the average file is a few megabytes, you have a sizing problem — not a compute problem, and adding workers will not fix it.