Time zone bucketing, distinct-count accuracy and shard-approximate terms

GET orders/_search
{
  "size": 0,
  "query": {"range": {"order_date": {
      "gte": "2024-03-01T00:00:00+01:00",
      "lt":  "2024-04-01T00:00:00+02:00"}}},
  "aggs": {
    "per_day": {
      "date_histogram": {
        "field": "order_date",
        "calendar_interval": "1d",
        "time_zone": "Europe/Berlin",
        "min_doc_count": 0
      },
      "aggs": {"revenue": {"sum": {"field": "total"}}}
    }
  }
}

What time_zone does: shifts where each bucket boundary falls, so a day means 00:00–24:00 local. Without it, a "day" is 00:00–24:00 UTC, which is why two hours of orders land in the wrong bucket for Berlin. Each bucket's key_as_string is rendered in that zone.

What it does not do:

• It does not move the underlying data - dates are stored as epoch millis (UTC) regardless.
• It does not affect the query range. gte/lt are absolute instants; you must supply the right offsets yourself. Note the range above uses +01:00 in March and +02:00 in April precisely because Berlin is CET before and CEST after the last Sunday of March.
• Prefer Europe/Berlin over a fixed +02:00: a fixed offset is wrong for half the year and the error silently changes at DST. Only use a numeric offset when the business definition really is a fixed offset.
• calendar_interval: 1d respects DST (a local day may be 23 or 25 hours); fixed_interval: 24h does not. Choose deliberately - for finance daily totals you want calendar_interval.

cardinality is HyperLogLog++: an estimate, deterministic for a fixed index/shard layout and query, but biased with a bounded error. With precision_threshold at its default (3000) and ~2.4M distinct values, a few percent of error is expected - 2.28M vs 2.41M is exactly that. Raising precision_threshold to 40000 shrinks the error but never eliminates it, and costs memory per shard proportional to the threshold.

Options, worst-to-best for this use case:

1. cardinality with precision_threshold: 40000 - cheap, still approximate. Acceptable for a trend line, not for a number that must reconcile with billing.
2. cardinality with a partition-free composite pagination and a client-side count - exact, but you must page through (potentially) millions of buckets:

   GET orders/_search
   {"size": 0,
    "aggs": {"buyers": {"composite": {"size": 1000, "sources": [
        {"c": {"terms": {"field": "customer_id"}}}]}}}}
   

   Loop on after_key. Exact and shard-safe (composite is the one agg that pages deterministically), but expensive: for millions of keys this is thousands of round trips.
3. Precomputed daily rollups: a nightly job writing distinct-buyer-per-day into a small aggregate index, summed/merged for the week. Fast and exact by definition, wrong only if the definition of "distinct per week" is not "sum of distinct per day" - it is not, for buyers who buy on two days. So this is only valid if the business accepts the definition; otherwise you must store a per-week structure such as a map/bitmap, or exact per-day sets and union them.

State the trade-off explicitly rather than silently raising the threshold: approximate is fine for dashboards, exact requires either paging or a precomputed structure.

Why the category is missing. A terms aggregation is computed per shard and merged. Each shard returns its own local top-shard_size list; a shard that holds only a fraction of a category's documents may rank it below its local cut-off and never report it. With size: 10, shard_size defaults to size * 1.5 + 10 = 25 - and with 12 shards the merged result can omit a genuinely large category. Ordering by a sub-aggregation (revenue) makes this much worse, because shard-level ordering by an average/sum is even less stable than by count. doc_count_error_upper_bound: 412 is the engine telling you how wrong the counts may be.

Fixes:

"aggs": {"by_cat": {
  "terms": {"field": "category", "size": 10, "shard_size": 200,
            "order": {"revenue": "desc"}},
  "aggs": {"revenue": {"sum": {"field": "total"}}}
}}

• Raise shard_size (rule of thumb: several × size) to shrink the error bound; confirm the bound drops to an acceptable value rather than assuming it does.
• For an exact top-N by revenue, do a two-pass approach: run a composite aggregation paging all categories and their revenue sums, sort client-side. Exact, more expensive, and the right call only for a small category cardinality.
• For a large category cardinality where exactness matters, an alternative is to size high per shard with shard_min_doc_count to suppress noise - but that still leaves a bound, so read doc_count_error_upper_bound and sum_other_doc_count and surface them rather than presenting the numbers as exact.

null averages in empty hours. With date_histogram and min_doc_count: 0, empty buckets are still returned, and avg over zero documents yields {"value": null}. That is correct behaviour, not a bug. Options:

• Keep the buckets and render gaps client-side (best - it distinguishes "no orders" from "zero revenue").
• Use "min_doc_count": 1 to drop empty buckets, accepting a hole in the time axis.
• If the chart needs a continuous series, use a pipeline aggregation with gap_policy: "skip" (e.g. avg_bucket over a finer histogram) rather than faking a 0.0, which would corrupt the average.
• avg's missing parameter applies to documents where the field is absent - it does not fill empty buckets. Do not confuse the two.