Segment Fan-Out Ceiling

A user-facing analytics endpoint serves per-account aggregates. At 120 queries per second p99 is 300 ms. At 240 it is 1.4 seconds, and CPU on every node sits at 55%. There is no obvious bottleneck: no disk saturation, no network ceiling, no slow upstream.

Each query fans out to every segment the filters cannot prune, and each segment costs a fixed amount regardless of how many rows survive: open it, consult the index, scan, produce a partial result, merge. With 6000 segments on a node, one query is 6000 small units of work, and when the matching row count is small the fixed cost is the whole cost.

Why it matters

This is the capacity model that surprises teams migrating from a warehouse. In a warehouse, cost scales with bytes scanned and concurrency is modest. In a store built for user-facing analytics, concurrency is the product dimension - every end user issuing a query is a query - and the limit is set by the layout of the data rather than by its size.

CPU at 55% is the diagnostic signature. The machine is not short of cycles; it is short of scheduling slots in a bounded per-query thread pool, and the latency is queue delay, which rises superlinearly as utilisation of that pool approaches one.

Implementation patterns

• Partition the table on the dimension that appears in every query's filter and route by it. A query then touches one partition's segments instead of all of them, so 32 partitions cuts per-query work by roughly 32× and raises the ceiling by about the same factor.
• Size segments deliberately. Hundreds of megabytes per segment rather than a few, so the fixed cost is amortised over more rows. A store with 6000 segments per node is usually carrying segments far smaller than its own guidance suggests.
• Run compaction or segment merge as a scheduled service, not as housekeeping, because real-time ingestion creates small segments continuously and the ceiling drifts down without anyone changing a query.
• Prune on time first. A tight time filter is the cheapest fan-out reduction available and the one most dashboards omit by defaulting to "all time".
• Measure segments scanned per query as a first-class metric beside latency. It is the leading indicator; latency is the lagging one.

Industry example

The model is explicit in the documentation of the scan-based real-time stores built for this workload. Apache Pinot documents partitioned routing and a recommended segment size precisely because the fan-out term dominates its user-facing workloads in production, and Apache Druid's segment-size recommendations exist for the same reason. The two systems disagree about much and agree about this.

Failure scenarios

• A launch that doubles users and quadruples p99 while every infrastructure dashboard looks healthy.
• The thread-pool remedy: an engineer sees 55% CPU, raises the pool size, and makes the tail worse through context switching without touching the fan-out.
• Silent decay as retention grows: segments accumulate, fan-out rises month by month, and the ceiling falls without a single deployment.
• A partition key chosen for one query shape, after which cross-partition queries fan out across partitions as well as segments and are slower than before the change.

Trade-offs

Partitioning pins a key for the table's life and makes any query without that key worse. Larger segments cut fixed cost and raise the granularity at which data can be refreshed or dropped, which matters for retention and for upserts. Replicas raise total throughput linearly but leave per-query work unchanged, so they buy the ceiling at full hardware price. Precomputing answers into a key-value store removes fan-out entirely and removes the ability to answer a question nobody anticipated, which is usually the reason the store was chosen.

When not to use it

The ceiling is not the constraint for an internal dashboard with a dozen analysts: at single-digit queries per second, a store with heavy fan-out is perfectly comfortable and the right answer is often the warehouse that already exists. It also does not apply when queries routinely match millions of rows, because then the scan dominates the fixed cost and the model reverts to bytes scanned. Check the ratio of rows matched to segments touched before optimising anything.

Interview question

Q: Query rate doubles, p99 quadruples, CPU is at 55% and the data volume has not changed. Tell me what you would look at and what you would change, in order.

What a strong answer covers: segments scanned per query as the first metric; the per-segment fixed cost and the bounded thread pool as the mechanism; why CPU headroom is misleading; partitioned routing as the structural fix and the key it pins; segment merge as the alternative when no stable key exists; why replicas and bigger thread pools do not move the ceiling; and the condition under which precomputation becomes right.

Quick check

Quiz: Why does a segment-scan store slow down as concurrency rises while CPU stays near half? Each query queues 6000 small units against a bounded thread pool - the latency is queue delay rather than compute.

Flashcard: Which single metric predicts a scan store's concurrency ceiling? Segments scanned per query, which partitioned routing and larger segments both reduce.