skip to content

A columnar table on object storage got tiny appends every minute for a year — why did queries slow down?

level: seniorimportance: should knowfreq 65%

answer

  1. the bytes barely grew, the file count exploded
  2. planning happens before any data is read
  3. object storage has a per-request latency floor
  4. adding workers does not fix latency-bound scans
  5. fix the writer before you compact

basics

~20 s

The table accumulated hundreds of thousands of tiny files. Planning must enumerate them all, each scan task pays a storage round trip for a few kilobytes of data, and compression is poor — so the query becomes metadata and latency bound rather than throughput bound.

solid answer

~50 s

This is the small-file problem, and it shows up as a **planning and per-file overhead** cost rather than a data-volume cost. Roughly half a million files accumulate in a year at one append per minute per writer. Query planning has to enumerate and filter that file list before any data is read, so planning time grows linearly with file count. Each file then costs a scheduled task plus at least one storage request whose latency floor dwarfs the few kilobytes it transfers, so the scan is latency bound and adding workers barely helps. Compression is also worse per file, so the same logical data occupies more bytes. Confirm it by looking at file count and average file size per partition and the ratio of planning time to execution time. Fix it by compacting existing data into target-sized files, then fixing the writer to batch — otherwise the problem regrows. Check the partition granularity too, since over-partitioning re-creates it regardless of batching.

code

text · 5 lines
text
-- per-partition file inventory that confirms the diagnosis
partition       files    total_bytes   avg_file_bytes   plan_ms   scan_ms
2026-08-19      11        2.3 GB         209 MB            35      1850
2026-08-20    1440        1.9 GB         1.3 MB           640      4900
2026-08-21    1438        2.0 GB         1.4 MB           655      5100

go deeper

for a junior

Know the symptom and the name: many tiny files make analytical queries slow even when total data is small, and the cure is batching writes and compacting what already accumulated.

for a middle

Explain where the time goes — planning over a huge file list, a storage round trip per tiny file, weak compression — and why more compute does not help a latency-bound scan.

for a senior

Demonstrate the diagnostic path: measure file count and average size per partition, compare planning to execution time, fix the writer first, then compact incrementally while watching the compute and storage cost.

for a principal

Own the prevention. Set platform-wide file-size and file-count standards with alerting, decide where buffering belongs in the ingestion architecture, and make the freshness-versus-cost trade explicit with the teams that consume the table.

## Recognising the shape of the problem The tell is that the table did not grow much in bytes, but queries got steadily slower, and throwing compute at it did not help. That combination points away from data volume and toward per-file overhead. One small append per minute is roughly 525,000 files a year from a single writer; several parallel writers multiply that, and a fine partition scheme multiplies it again. ## Where the time actually goes **Planning.** Before any bytes are scanned, the engine must obtain the current file set, read or consult each file's statistics to decide what can be skipped, and build a work assignment. All three costs are per file. With half a million files, planning alone can take seconds — and it is paid on every query, including one that ends up reading almost nothing. **Per-request latency.** On object storage every read is a network request with a latency floor. Transferring 2 KB and transferring 20 MB cost roughly the same in latency terms; only the second amortises it. A scan that issues hundreds of thousands of tiny requests spends its life waiting, not reading. Where the store bills per request, this is visible on the invoice too. **Task scheduling.** Each file becomes a unit of work: assigned, dispatched, executed, its result collected. Fixed overhead per unit swamps the microseconds of actual work in a file holding a few dozen rows. **Weaker compression and larger footprint.** Dictionary and run-length encodings amortise across values; tiny blocks compress badly. The same logical rows occupy noticeably more bytes than they would in well-sized files, so you also read more. **Poorer elimination.** Statistics work per file, but with a firehose of interleaved arrivals each tiny file may span a wide range of key values, so fewer files can be excluded than the layout suggests. ## Confirming it before you act Don't guess. Gather: - **File count and average size per partition** — the direct measurement. Averages in the kilobyte-to-single-megabyte range confirm the diagnosis. - **Planning versus execution time** — if planning is a large and growing share of total query time, the bottleneck is the file list, not the data. - **Bytes scanned versus rows returned** — helps separate a small-file problem from a pruning problem, which has a different fix and belongs to the layout, not the file sizing. - **Compaction backlog** — if the engine has a background merge service, is it keeping up, and what is it costing? ## Fixing it, in the right order 1. **Stop the bleeding at the writer.** Compacting first and leaving the writer alone means the problem returns. Introduce buffering: batch on a size or time trigger, or put a queue and a micro-batching consumer in front, or write to a landing table that is merged in bulk. 2. **Compact the existing data.** Rewrite the accumulated small files into target-sized files. Do it incrementally, oldest partitions first, so you can throttle the compute it consumes and stop safely at any point. Expect the storage bill to rise temporarily while superseded files sit in the retention window. 3. **Check the partition scheme.** If partitions are fine enough that each receives only a trickle, no batching can produce large files inside them, and compaction will have nothing to merge. Coarsening partition granularity is often the real fix. 4. **Verify with the same metrics** you used to diagnose, and add an alert on average file size or file count per partition so the regression is caught in days, not a year. ## Trade-offs to state aloud Compaction is not free: it reads and rewrites data that was already written, so it costs compute and I/O — write amplification you are choosing to pay because read cost is worse. It also competes with ingest for resources, which is why running it in a separate compute pool or during a quiet window is common. And the writer fix trades freshness for file size: data becomes visible after the batch interval instead of within seconds. If a consumer genuinely needs sub-minute freshness, the honest answer is a hybrid — a small hot region kept fresh and small, compacted aggressively into the historical body of the table. ## Weak versus strong answers A weak answer says "the table got big, scale up the cluster". A strong one separates data volume from file count, names planning and per-request latency as the actual costs, states how it would confirm the diagnosis with numbers, fixes the writer before compacting, and mentions the partitioning scheme as the frequent underlying cause.

  • Why doesn't adding more compute nodes fix this?
    The scan is latency bound, not throughput bound. Extra workers issue more concurrent tiny requests but each still waits on the same per-request latency floor, and planning — which is single-file-list work done before any parallelism — does not speed up at all. You would be paying more compute to wait in parallel.
  • How do you run compaction on a live table without disrupting queries?
    Compaction is a read-and-rewrite followed by an atomic metadata swap, so running readers keep seeing the old file set and switch at the commit. Do it partition by partition to bound the blast radius and the compute burn, run it in a separate compute pool or a quiet window so it does not compete with ingest, and expect a temporary storage increase while superseded files sit in the retention window.
  • How would you distinguish a small-file problem from a pruning problem?
    Look at bytes scanned versus data returned alongside file count and average file size. A pruning problem reads a large fraction of a well-sized table because predicates do not match the layout; a small-file problem shows modest bytes scanned but heavy planning time and huge file counts. They can coexist, and they have different fixes — layout versus file sizing.
  • What would you monitor so this never regresses silently?
    Average file size and file count per partition, tracked per table with an alert threshold, plus planning time as a share of total query time. Both are cheap to compute from table metadata and both move long before users notice slow dashboards. A yearly-scale regression is only possible when nobody is watching either number.

saying these in an interview costs you the question

  • Blames data volume when bytes barely grew
  • Recommends scaling the cluster to fix per-file overhead
  • Compacts once without fixing the writer's batching
  • Overlooks over-partitioning as the underlying cause
  • Treats compaction as free rather than paid compute

context

resolving links…