Why can an Elasticsearch terms aggregation report wrong doc_count values on a multi-shard index?

## Two phases, and where the truth is lost A search that carries a `terms` aggregation is broadcast to one copy of every shard. Each shard computes the aggregation over *its own* documents and returns a partial result. The coordinating node then reduces those partials into the final response. Nothing about that is unusual — the problem is what the shard is allowed to send. Sending every distinct term with its count would make the aggregation as expensive as shipping the field itself, so each shard sorts its terms by the requested order and returns only the top `shard_size` of them. That truncation is the entire source of the inaccuracy. If term `X` is the 3rd most common term globally but ranks 900th on shard B because shard B holds a different slice of traffic, and `shard_size` is 40, then shard B contributes **zero** to `X`'s count. The merged number is wrong, and it is wrong in one direction: counts can only be too low, never too high. ## The two bookkeeping fields Elasticsearch does not hide this. Two fields in the response describe it, and interviewers love the distinction: - **`sum_other_doc_count`** — how many matching documents belong to terms that were *not returned* to you. It is about the tail you did not see. It is exact, and it is not an error measure. - **`doc_count_error_upper_bound`** — an upper bound on how far the *returned* counts might be from the truth, computed from the count of the last term each shard returned: any term a shard omitted must have had at most that many documents on that shard. If the value is `0`, the returned counts are exact. Elasticsearch can only compute this bound when the aggregation is ordered by descending doc count; ordered by term or by a sub-aggregation, the bound is not meaningful. Adding `"show_term_doc_count_error": true` also emits a per-bucket error, so you can see which specific bucket is untrustworthy rather than a single global bound. ## When counts are exact - **One shard.** A single-shard index sees everything, so there is no merge and no truncation; the error bound is 0. - **Routing alignment.** If documents are routed so that every document sharing the aggregated term lands on the same shard — for example routing by `customer_id` and aggregating on `customer_id` — then each term is fully counted on exactly one shard, and merging cannot lose anything. - **Low cardinality.** If the field has fewer distinct values than `shard_size`, no shard ever truncates. ## The dials, and what they cost `shard_size` is the number of candidate terms each shard returns. It defaults to a value derived from `size` — roughly `size * 1.5 + 10` — which is deliberately a little larger than `size` so the merge has slack. Raising it is the direct accuracy fix: more candidates per shard means fewer silently dropped contributions. The cost is real but bounded — more per-shard sorting, more network payload, more memory on the coordinating node during the reduce — and it is far cheaper than raising `size` itself, because `shard_size` affects only the intermediate stage, not the returned buckets. A practical recipe: keep `size` at what the user actually needs, push `shard_size` up until `doc_count_error_upper_bound` drops to 0 or to a level you can defend, and check `sum_other_doc_count` to see whether the tail matters at all. ## Ordering by a sub-aggregation makes it worse `{"order": {"avg_price": "desc"}}` asks each shard to pick its local candidates by a metric computed from local documents only. A term whose global average is high but whose local sample on one shard is unremarkable never becomes a candidate there, and its documents are lost from the merge — so both the ordering *and* the counts can be wrong, and Elasticsearch cannot even bound the error. Treat sub-aggregation ordering as approximate by construction, and if the ranking must be right, either enlarge `shard_size` substantially or compute over a candidate set you have already narrowed with a filter. ## When approximation is not acceptable If you need exhaustive, exact grouping — a reconciliation report, a billing rollup — stop using `terms` for it. The `composite` aggregation walks *all* buckets in composite-key order with `after_key` paging, so nothing is truncated; you pay by streaming every bucket rather than getting a cheap top-N. Alternatively pre-aggregate: a rollup or a transform that materialises per-key totals into their own index turns an approximate query into an exact lookup. ## The interview shape The question is usually posed as a bug report: "the dashboard's top-10 customers don't match the finance export." The expected answer names the per-shard truncation, points at `doc_count_error_upper_bound` and `sum_other_doc_count` as evidence, offers `shard_size` as the tuning knob and routing or `composite` as the structural fixes — and, crucially, does not claim the counts are randomly wrong. They are systematically *under*-counted.