--- title: 'Query and Aggregation' source: 'https://academia.sh/en/courses/devops-observability/query-and-aggregation' course: 'Observability and Operations' language: en updated: '2026-09-13T05:08:22+00:00' license: 'CC BY-SA 4.0' objectives: - 'Answers a question with four aggregations and shows the answer changing with the aggregation' - 'Counts which measure pre-aggregation preserves exactly, which it distorts, and which it erases entirely' - 'Measures query cost by records scanned and runs the duration measurement through a band check' --- # Query and Aggregation The same question is answered with four aggregations, and the answer is shown to change: the average hides, the percentile shows, the total misleads, and the count and the rate point to different services. The detail pre-aggregation loses irrecoverably and the query cost it buys back are counted in the same table. The previous lesson measured which breakdown the series set stores. But no question is answered by the series itself: a series is hundreds of thousands of numbers, and the answer must be a single number. **Aggregation** steps in between — average, percentile, max, total — and this lesson shows that this step is not settled: from the same data, for the same question, different aggregations produce **different answers**. The second measure is pre-aggregation. Storing a minute-level summary instead of the raw measurement drops query cost by two orders of magnitude, and erases a detail irrecoverably in return. This lesson's measure is counting both sides in the same table. The measurement is again on the **regional measurement network** and is **fictional**; no real traffic is run, what gets counted is a **model** produced with `node`. **ML13.** The request rate and latency profile of the four services are fictional and written in advance. **ML14.** Measurements are produced with our own generator, seed `20260902`; 90,000 requests in a one-hour window. **ML15.** Six percent of the billing service's requests wait on a lock and take 950–1100 ms of extra latency; the other three services have a smooth distribution. A fictional 90-second degradation is also added to the validator (+900 ms), and the event-free profile values are taken as the threshold baseline. **ML16.** Pre-aggregation keeps four fields per minute: count, total, max, and the minute's p95 value. **ML17.** The overall p95 computed from the summary is the request-count-weighted average of the minute p95s — the assumption that percentiles can be combined is applied deliberately. **ML18.** Duration measurement depends on the environment; only the result of the band check is printed to the output, not the raw measurement. ## The Raw Measurement Set ```js // measurements.mjs — one hour of request measurements produced with our own generator; profiles are fictional. export const makeRng = (t) => () => (t = (t * 1103515245 + 12345) % 2147483648) / 2147483648; export const WINDOW = 3600; export const PROFILE = { // service: [req/s, base, spread, tail share, tail ms, error rate] "reading-collector": [12, 150, 110, 0, 0, 0.012], validator: [8, 90, 40, 0, 0, 0.006], billing: [3, 40, 30, 0.06, 950, 0.035], "work-order": [2, 200, 40, 0, 0, 0.009], }; export const measurements = (seed = 20260902) => { const rand = makeRng(seed), o = {}; for (const [service, [rate, base, spread, tailShare, tailMs, errorRate]] of Object.entries(PROFILE)) { const d = []; for (let sec = 0; sec < WINDOW; sec++) for (let i = 0; i < rate; i++) { const queued = rand() < tailShare; d.push({ sec, ms: Math.round(base + rand() * spread + (queued ? tailMs + rand() * 150 : 0)), error: rand() < errorRate }); } o[service] = d; } return o; }; export const percentile = (a, p) => { const s = [...a].sort((x, y) => x - y); return s[Math.min(s.length - 1, Math.ceil(p * s.length) - 1)]; }; export const maxName = (o) => Object.entries(o).sort((a, b) => b[1] - a[1])[0][0]; ``` The profiles are not distinguished by their average alone. The reading collector is high-volume with a smooth distribution; billing is low-volume and has a tail — six percent of its requests finish above one second. These two shapes are the two latency patterns most commonly encountered in real operations, and which aggregation sees the difference between them is not obvious from the start. ## The Same Question, Four Aggregations ```js // query.mjs — the same question is answered with different aggregations; the cost of pre-aggregation is counted. import { PROFILE, WINDOW, measurements, percentile, maxName } from "./measurements.mjs"; const raw = measurements(); const services = Object.keys(PROFILE); const totalRequests = services.reduce((t, s) => t + raw[s].length, 0); console.log(`one hour of measurement (fictional model): ${totalRequests.toLocaleString("en-US")} requests, ${services.length} services`); console.log("\nQ1 'which service slowed down' — answer by aggregation"); console.log("aggregation " + services.map((s, i) => s.padStart([19, 11, 9, 12][i])).join("") + " answer"); const AGGREGATIONS = [["average ms", (d) => d.reduce((t, x) => t + x.ms, 0) / d.length], ["p95 ms", (d) => percentile(d.map((x) => x.ms), 0.95)], ["max ms", (d) => Math.max(...d.map((x) => x.ms))], ["total s", (d) => d.reduce((t, x) => t + x.ms, 0) / 1000]]; for (const [name, f] of AGGREGATIONS) { const v = Object.fromEntries(services.map((s) => [s, f(raw[s])])); console.log(`${name.padEnd(14)}` + services.map((s, i) => v[s].toFixed(0).padStart([19, 11, 9, 12][i])).join("") + ` ${maxName(v)}`); } console.log("\nQ10 'which error code rose' — count or rate"); const errorCount = Object.fromEntries(services.map((s) => [s, raw[s].filter((x) => x.error).length])); const errorRate = Object.fromEntries(services.map((s) => [s, 100 * errorCount[s] / raw[s].length])); console.log("measure " + services.map((s, i) => s.padStart([19, 11, 9, 12][i])).join("") + " answer"); console.log("error count " + services.map((s, i) => String(errorCount[s]).padStart([19, 11, 9, 12][i])).join("") + ` ${maxName(errorCount)}`); console.log("error rate % " + services.map((s, i) => errorRate[s].toFixed(2).padStart([19, 11, 9, 12][i])).join("") + ` ${maxName(errorRate)}`); ``` ``` one hour of measurement (fictional model): 90,000 requests, 4 services Q1 'which service slowed down' — answer by aggregation aggregation reading-collector validator billing work-order answer average ms 204 110 125 220 work-order p95 ms 255 128 1042 238 billing max ms 260 130 1166 240 billing total s 8834 3162 1350 1582 reading-collector Q10 'which error code rose' — count or rate measure reading-collector validator billing work-order answer error count 523 168 428 66 reading-collector error rate % 1.21 0.58 3.96 0.92 billing ``` One question, three separate aggregations that produce four different answers. The numbers all belong to the same 90,000 requests, and none of them is wrong. **The average hides.** Billing's average is 125 milliseconds, the second lowest of the four services. The six percent slice waiting in the tail pulls the average up by only seventy milliseconds, because the average divides extreme values across the whole volume. A dashboard that looks at the average never shows billing at all. **The percentile shows.** The same service's p95 is 1042 milliseconds — between four and eight times the others. The tail cannot hide in the p95, because the six percent slice is above the five percent threshold. If one request in twenty crosses one second, that shows up only in the percentile. **The total misleads.** Looking at the total duration, the answer is the reading collector: 8834 seconds. But what this number says is not latency, it is **volume** — the reading collector takes 43,200 requests an hour, billing takes 10,800. The total measures workload when what is wanted is speed. It is correct for a capacity question and misleading for a latency question. The error table sets up the same split a second time. Error **count** points to the reading collector (523), error **rate** points to billing (3.96%). Both are correct, and they answer two different questions: "which service produces the most errors" versus "which service is the most broken." When the on-call person is shown the wrong one, the investigation starts in the logs of the service whose error rate is three times lower. ## The Query Window Does the Same Thing Aggregation is not only which function is applied, it is also **which interval** it is applied to. The same average measures different things when applied to a minute versus an hour. The block below places a short degradation on the validator and asks the same two aggregations across four windows. ```js // window.mjs — when the query window changes, the same question gives a different answer. import { PROFILE, WINDOW, measurements, percentile } from "./measurements.mjs"; const raw = measurements(); const EVENT = { service: "validator", start: 1500, duration: 90, extra: 900 }; // fictional degradation for (const x of raw[EVENT.service]) if (x.sec >= EVENT.start && x.sec < EVENT.start + EVENT.duration) x.ms += EVENT.extra; const d = raw[EVENT.service]; const baseline = { avg: 110, p95: 128 }; // values of the event-free profile console.log(`${EVENT.service} service has a ${EVENT.duration} s degradation of +${EVENT.extra} ms` + ` (${(100 * EVENT.duration / WINDOW).toFixed(1)}% of the hour); threshold is twice the baseline value`); console.log("query window windows highest avg ms over threshold highest p95 ms over threshold time to notice"); for (const w of [60, 300, 900, 3600]) { const windowCount = WINDOW / w, avg = [], p95 = []; for (let i = 0; i < windowCount; i++) { const g = d.filter((x) => x.sec >= i * w && x.sec < (i + 1) * w).map((x) => x.ms); avg.push(g.reduce((a, b) => a + b, 0) / g.length); p95.push(percentile(g, 0.95)); } console.log(`${String(`${w / 60} min`).padEnd(14)}${String(windowCount).padStart(9)}` + `${Math.max(...avg).toFixed(0).padStart(17)}${String(`${avg.filter((v) => v > 2 * baseline.avg).length}/${windowCount}`).padStart(16)}` + `${String(Math.max(...p95)).padStart(16)}${String(`${p95.filter((v) => v > 2 * baseline.p95).length}/${windowCount}`).padStart(16)}` + `${String(`${w / 60} min`).padStart(16)}`); } console.log(`\nrecords per resolution (one hour, four services): ` + [60, 300, 900, 3600].map((w) => `${w / 60} min ${4 * (WINDOW / w) * 4}`).join(" ")); ``` ``` validator service has a 90 s degradation of +900 ms (2.5% of the hour); threshold is twice the baseline value query window windows highest avg ms over threshold highest p95 ms over threshold time to notice 1 min 60 1009 2/60 1028 2/60 1 min 5 min 12 380 1/12 1023 1/12 5 min 15 min 4 200 0/4 1008 1/4 15 min 60 min 1 132 0/1 129 0/1 60 min records per resolution (one hour, four services): 1 min 960 5 min 192 15 min 64 60 min 16 ``` The degradation is two and a half percent of the hour, and it looks four different ways across four windows. In the one-minute window, both the average and the percentile rise above one thousand milliseconds, and two windows cross the threshold. At five minutes the average drops to 380 but is still above the threshold. **At fifteen minutes the average dies**: the highest value is 200 milliseconds, which stays below the two-hundred-twenty threshold — no window is flagged. In the same window the percentile still sees 1008 milliseconds and flags one window. At one hour both vanish: 132 milliseconds for the average, 129 for the percentile — no trace of the event remains. The record loss and its cost sit in the same line. One-minute resolution holds 960 records an hour, one-hour resolution holds 16 — a sixty-fold difference. Time to notice is the window itself: a window cannot be queried before it closes, so fifteen-minute resolution means a fifteen-minute delay at best. If the degradation lasts ninety seconds, that fifteen minutes falls entirely after the event has already ended. ## The Two Faces of Pre-Aggregation Storing the raw measurement per request is expensive. The common response is to keep a minute-level summary: count, total, max, and the minute's p95 value for every minute. The block below actually produces the summary and asks the same questions of both stores. ```js // preagg.mjs — the detail pre-aggregation loses and the query cost it buys back. import { PROFILE, measurements, percentile } from "./measurements.mjs"; const raw = measurements(), services = Object.keys(PROFILE); const summary = {}; // minute summary: count, total, max, minute p95 for (const s of services) { summary[s] = Array.from({ length: 60 }, (_, d) => { const g = raw[s].filter((x) => Math.floor(x.sec / 60) === d).map((x) => x.ms); return { count: g.length, total: g.reduce((a, b) => a + b, 0), max: Math.max(...g), p95: percentile(g, 0.95) }; }); } const rawAll = services.flatMap((s) => raw[s].map((x) => x.ms)); const rawRecords = rawAll.length, summaryRecords = services.length * 60 * 4; const rawP95 = percentile(rawAll, 0.95); const summaryP95 = services.reduce((t, s) => t + summary[s].reduce((a, d) => a + d.p95 * d.count, 0), 0) / rawRecords; const rawAvg = rawAll.reduce((a, b) => a + b, 0) / rawRecords; const summaryAvg = services.reduce((t, s) => t + summary[s].reduce((a, d) => a + d.total, 0), 0) / rawRecords; const deviation = (o, h) => `${(100 * Math.abs(o - h) / h).toFixed(1)}%`; console.log(`raw ${rawRecords.toLocaleString("en-US")} records, minute summary ${summaryRecords} records` + ` (${(rawRecords / summaryRecords).toFixed(0)}x smaller)`); console.log("\nquestion raw answer summary answer deviation"); const ROWS = [["overall average ms", rawAvg.toFixed(1), summaryAvg.toFixed(1), deviation(summaryAvg, rawAvg)], ["overall p95 ms", String(rawP95), summaryP95.toFixed(1), deviation(summaryP95, rawP95)], ["max ms", String(Math.max(...rawAll)), String(Math.max(...services.flatMap((s) => summary[s].map((d) => d.max)))), "0.0%"], ["requests over 1 s", String(rawAll.filter((x) => x > 1000).length), "cannot be computed", "-"]]; for (const [a, h, o, s] of ROWS) console.log(`${a.padEnd(26)}${h.padStart(12)}${o.padStart(21)}${s.padStart(11)}`); // query cost: the same p95 query is run against both stores REPEATS times const REPEATS = 40; const expectedRatio = rawRecords / summaryRecords; const measure = (f) => { const b = process.hrtime.bigint(); for (let i = 0; i < REPEATS; i++) f(); return Number(process.hrtime.bigint() - b); }; const rawTime = measure(() => services.map((s) => percentile(raw[s].map((x) => x.ms), 0.95))); const summaryTime = measure(() => services.map((s) => percentile(summary[s].map((d) => d.p95), 0.95))); const measuredRatio = rawTime / summaryTime; console.log(`\np95 query run ${REPEATS} times`); console.log(` records scanned raw ${(rawRecords * REPEATS).toLocaleString("en-US")}` + ` summary ${(summaryRecords * REPEATS).toLocaleString("en-US")} expected ratio ${expectedRatio.toFixed(0)}x`); console.log(` is the measured time ratio between 5 and 20000x: ${measuredRatio > 5 && measuredRatio < 20000 ? "yes" : "no"}`); ``` ``` raw 90,000 records, minute summary 960 records (94x smaller) question raw answer summary answer deviation overall average ms 165.9 165.9 0.0% overall p95 ms 251 288.1 14.8% max ms 1166 1166 0.0% requests over 1 s 729 cannot be computed - p95 query run 40 times records scanned raw 3,600,000 summary 38,400 expected ratio 94x is the measured time ratio between 5 and 20000x: yes ``` The table lays three separate behaviors side by side. The average is **preserved exactly**: the deviation is zero, because the average can be reconstructed from the total and the count — the sum of the minute totals is the hour's total. The max is also preserved, for the same reason: the max of the maxes is the real max. The percentile is not preserved. The overall p95 is 251 in the raw data and 288 milliseconds from the summary; the deviation is 14.8%. The reason is one sentence: **the average of percentiles is not a percentile.** The minute p95 values are the tail of each minute's own distribution; weighting them by request count and summing them does not give the tail of the hour's distribution. The direction is not a coincidence, either — in this data set the summary skews upward, because billing's tail pulls every minute's p95 up, and those minutes are not diluted in the total. In a different data set the same method could skew downward; the only thing that can be predicted is that a deviation exists. The last line is a total loss. The raw answer to "how many requests crossed one second" is 729; the summary has no answer, and not even an approximate one, because the count of requests above the threshold sits in none of the summary's four fields. The detail has been erased, and it cannot be recovered. Nothing makes this visible, either: when this query is written against the summary, the store does not raise an error, it just becomes clear that the query cannot be written. The gain side is in the same table too. The same p95 query scans 3.6 million records in the raw store, 38,400 in the summary store — **94 times** fewer. Duration measurement depends on the environment and is not printed here; whether the measured ratio matches the expected order of magnitude is run through a band check, and the result is written as `yes`. The decision is a collision between two numbers: a ninety-four-times cheaper query, a percentile skewed by fifteen percent, and a threshold question that is completely lost. The summary is close to free for dashboards that ask for the average and the max; it is the wrong foundation for alert rules that ask for the percentile and the threshold. ## Summary - The same question gives three different answers across four aggregations: the average points to `work-order`, the p95 and max point to `billing`, the total points to `reading-collector`. None is wrong; each measures something different. - The average hides because it divides the extreme slice across the volume (billing 125 ms), the percentile shows it (1042 ms); the total measures volume instead of latency. - Error count and error rate point to different services: 523 errors for `reading-collector`, a 3.96% rate for `billing`. - The query window is also an aggregation: a 90-second degradation shows up in both the average and the percentile at a 1-minute window, only in the percentile at 15 minutes, and in neither at 60 minutes. As resolution drops from 960 records to 16, time to notice rises from 1 minute to 60 minutes. - Pre-aggregation preserves the average and the max exactly (0.0%), skews the p95 by 14.8%, and erases the count of over-threshold requests entirely — 729 requests cannot be computed from the summary. - The same query scans 3.6 million records in the raw store, 38,400 in the summary store: 94 times cheaper. The gain and the loss are two faces of the same decision. ## Next Step Up to this point every answer was a number: how many milliseconds, how many requests, how many errors. But no number in the table answers the question of **why**. It is known that billing's p95 is 1042 milliseconds; it is not known which lock, which query, or which region's reading it was waiting on. That information does not live in a metric, it lives in the text the process writes itself. The next lesson takes on the log stream and counts, layer by layer, how many lines of that text are lost as it passes through the collection pipeline.