Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 39 ยท Retention and Downsampling

Downsampling with Aggregate Windows

Turn high-resolution time-series data into hourly or daily summaries with aggregate windows, keep min, max and count, and avoid the average-of-averages trap.

Downsampling means replacing many detailed points with a few summary points. Ten thousand one-second CPU readings in an hour become one row that says "mean 41%, max 97%, 3,600 samples". You lose the detail, but you keep the shape of the data, and you cut storage and query time by orders of magnitude.

It pairs with retention: keep raw points for a few days, keep the summaries for months or years. The building block is the aggregate window: split time into fixed slices, then reduce each slice with a function such as mean, min, max, sum, or count. You will see the real InfluxDB syntax (read-only, since no server runs here) and runnable SQL stand-ins that do the same arithmetic.

The idea in Flux and SQL

In InfluxDB 2.x you write Flux. aggregateWindow groups rows into windows and applies a function:

JavaScript
from(bucket: "raw")
  |> range(start: -24h)
  |> filter(fn: (r) => r._measurement == "cpu" and r._field == "usage")
  |> aggregateWindow(every: 1h, fn: mean, createEmpty: false)

In InfluxDB 3 you use SQL with date_bin, which snaps each timestamp down to the start of its bin:

SQL
SELECT date_bin(INTERVAL '1 hour', time) AS hour,
       host,
       avg(usage) AS mean_usage,
       max(usage) AS max_usage
FROM cpu
WHERE time >= now() - INTERVAL '24 hours'
GROUP BY hour, host
ORDER BY hour;

Both read-only snippets above express the same plan: slice the time axis, group, reduce. Flux marks each result row with the end of the window by default (timeSrc: "_stop"), whereas date_bin returns the start. Decide which you want, and use the same convention everywhere, or your charts will be shifted by one window.

flowchart LR
  A["Raw points<br/>every 10 s"] --> B["Window<br/>1 hour"]
  B --> C["mean, min, max, count"]
  C --> D["Summary bucket<br/>1 row per hour"]

Downsampling groups points into windows and writes one summary row per window.

Runnable stand-in: hourly rollups in SQL

Here is the same operation with portable SQL. The table holds 6 hours of readings, one every 5 minutes, for 2 hosts. The window start is computed by integer division on the Unix timestamp.

SQL Stand-in: roll raw points up into hourly summaries

Press Run (or Ctrl + Enter) to see the result here.

The raw table has 144 rows and the summary has 12. That is a 12 to 1 reduction here, and a 360 to 1 reduction for 10-second data rolled up hourly.

Keep more than the mean

A single average hides spikes. If a server hits 99% for two minutes, an hourly mean will barely move. Store several aggregates per window:

Aggregate Answers
mean "Typical" load
min, max Range and spikes
count How many raw points formed this row
sum Totals such as bytes or requests

Keeping count and sum matters for the next point.

The average-of-averages trap

Suppose you store hourly means and later want a daily mean. Averaging 24 hourly means gives the right answer only if every hour contained the same number of samples. If one hour lost half its points (a short outage), that hour is over-weighted. The correct way is to combine sums and counts: daily mean = sum of hourly sums divided by sum of hourly counts.

SQL Weighted mean versus mean of means

Press Run (or Ctrl + Enter) to see the result here.

The naive average is 30, while the true mean over all raw samples is about 10.9. The lesson: when you downsample, store count (or sum) next to mean so that later roll-ups stay correct. The same trap applies to percentiles, which you cannot rebuild from percentiles at all; keep a histogram or store several quantiles if you will need them.

Choosing the window size

Pick a window that matches how the data will be read:

  • Dashboards over 24 hours need roughly 100 to 1,000 points per series, so a 1 to 15 minute window is typical.
  • Capacity planning over a year works with hourly or daily windows.
  • Align windows to natural boundaries (hour, day) so that comparisons line up.
  • Windows must be a multiple of the previous level if you chain roll-ups (minute to hour to day).

Gaps and empty windows

If no points fall in a window, plain GROUP BY returns no row at all. Flux's createEmpty: true emits a row with a null value instead, and then fill() can replace nulls. Charts that must show gaps honestly should keep nulls; alert rules often prefer a deliberate "no data" state.

Common mistakes

  • Averaging averages instead of weighting by count.
  • Mixing window-start and window-end timestamps between roll-ups.
  • Rolling up before the data has fully arrived, so late points are missing from the summary (leave an offset of a minute or more).
  • Dropping count, then being unable to tell a quiet hour from a missing one.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which Flux function groups rows into fixed time windows and applies a function?

    aggregateWindow splits time into windows given by every: and reduces each window with fn:.
  2. 2. Why store count alongside mean in a summary table?

    A mean of means is wrong when windows hold different numbers of samples. Counts give the weights.
  3. 3. A one-hour mean barely moved although a server hit 99% for two minutes. What would reveal the spike?

    Min and max keep the extremes that a mean smooths away.
  4. 4. In InfluxDB 3 SQL, which function snaps timestamps to the start of a bin?

    date_bin(INTERVAL '1 hour', time) returns the start of each hour-long bin.

Summary

  • Downsampling replaces many raw points with one summary row per time window.
  • Flux uses aggregateWindow; InfluxDB 3 SQL uses date_bin with GROUP BY.
  • Store mean, min, max and count (or sum) so spikes and weights survive.
  • Never average averages; combine sums and counts.
  • Be consistent about window start versus window end.

Next: Scheduled Downsampling with Tasks