A user-facing analytics endpoint serves per-account filtered aggregates from a segment-scan store holding 90 days in about 6000 segments per node. At 120 queries per second p99 is 300 ms. At 240 queries per second p99 is 1.4 s while CPU sits at 55%. Which change raises the concurrency ceiling?
Show the full answer Hide the answer
The deciding property
In a store of this family a query fans out to every segment that the time filter does not prune, and each segment costs a fixed amount regardless of how many rows survive the filter: open it, check the index, scan, produce a partial result, merge. With 6000 segments per node, one query is 6000 small units of work, and the per-segment fixed cost dominates whenever the matching row count is small - which is exactly what a per-account filter produces.
Doubling concurrency therefore doubles the number of units in flight against a bounded pool of threads. The queue in front of that pool is where the latency went. CPU at 55% is the signature: the machine is not short of cycles, it is short of scheduling slots and spending them on segment overhead rather than on rows.
Partitioning the table on account id and routing by that partition means a query touches the segments of one partition rather than all of them. With, say, 32 partitions the per-query work falls by roughly a factor of 32, and the concurrency ceiling rises by about the same factor.
Why the other options fail
- Add replicas. Replicas raise total capacity linearly and leave per-query work unchanged, so the ceiling moves only as fast as you buy hardware. It is the right answer when the per-query cost is already minimal and you simply need more of them; here it pays full price for a problem that partitioning solves for free.
- Increase the thread pool. This is the instinct that CPU at 55% means there is headroom. More threads than cores adds context switching and makes the tail worse, and it does nothing about the 6000 units of work per query. Thread pool sizing is a remedy for an under-used machine, not for an over-fanned query.
- Precompute every answer into a key-value store. Right when the query set is closed - one fixed dashboard per account - and wrong here, because the endpoint accepts filters, and the number of precomputed answers is the product of every filter's cardinality. It also throws away the ability to answer a new question without a backfill.
What would flip the decision
| If this changes | Choose | Because |
|---|---|---|
| Queries stop carrying an account filter | Replicas | There is nothing to partition on and fan-out is inherent |
| The filter set is fixed and small | Precomputation | Fan-out goes to zero and the store becomes a lookup |
| Segment count per node is already low | Thread pool and replicas | The per-segment overhead is no longer the dominant term |
When this is the wrong answer
Partitioning the table pins the partition key for the table's life and makes any query that does not carry that key worse, because it now fans out across partitions as well as segments. If more than a small minority of traffic queries across accounts, merge segments instead: fewer, larger segments cut the fixed cost per query without constraining the key. Target segment sizes in the hundreds of megabytes rather than a few megabytes, which is where a store with 6000 segments per node usually sits.