Query and Aggregation

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

// 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

// 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.

// 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.

// 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.