Timelake

Rollups

A rollup is a table of aggregates per time bucket, such as the hourly average and maximum of each device, that Timelake keeps up to date as data arrives. Dashboards that ask for hourly values then read one row per device and hour instead of every reading.

A rollup is an ordinary Timelake table, so DuckDB and other Iceberg readers can query it directly. The Timelake reader also uses it on its own: a GROUP BY time_bucket(…) query on the raw table that the rollup matches, or a query over a time range grouped by the rollup's columns, is answered from the rollup, with the same rows the raw table would give.

Create a rollup

CREATE MATERIALIZED ROLLUP iot.readings_1h AS
SELECT device_id, time_bucket(INTERVAL '1 hour', time) AS bucket,
       avg(temp) AS avg_temp, max(temp) AS max_temp, count(*) AS n
FROM iot.readings
GROUP BY device_id, bucket
WITH (retention = '2y');
$ timelake sql "CREATE MATERIALIZED ROLLUP iot.readings_1h AS SELECT …"
+-----------------+------+
| rollup          | rows |
+-----------------+------+
| iot.readings_1h | 216  |
+-----------------+------+

The statement creates the table, computes it from the data already in iot.readings, and records it on the raw table (setting rollups). IF NOT EXISTS makes it do nothing when a table of that name exists.

The query must have this shape:

  • It reads one Timelake time-series table, with no WHERE, join, or subquery.
  • It groups by one time_bucket (or date_bin) of the table's time column, with a fixed width (seconds to days, not months) and the default origin.
  • It also groups by any of the table's series-key columns, and selects each of them under its own name.
  • Its aggregates are count(*), count(x), sum, min, max, avg, first_value(x ORDER BY time), last_value(x ORDER BY time), m4(time, x) (below), time_weighted_mean(time, x [, 'linear']), integral(time, x [, 'linear']), counter_diff(time, x), counter_rate(time, x), gap_count(time, threshold), and gap_seconds(time, threshold). Each bucket's rows give these exactly.

Outputs the query doesn't name get names like bucket, avg_temp, count, and gap_count_300s. Grouping only by series-key columns keeps a reading in the same group when a newer write replaces it (see Rows sent twice), so a rollup only ever gains rows.

WITH sets the rollup table's options:

  • retention: keep buckets this long, e.g. 2y. It is independent of the raw table's retention, so a rollup can keep history the raw table has dropped.
  • chunk_interval: partition size, hour, day, or month. Default: day for buckets under an hour, else month.
  • refresh_interval: how often inline maintenance refreshes it (see below). Default 1m; off for never.
  • Any other setting is set on the rollup table, e.g. min_snapshots = 3.

Query a rollup

Query the rollup table like any table:

SELECT device_id, bucket, avg_temp FROM iot.readings_1h
WHERE bucket >= now() - INTERVAL '7 days'

Or query the raw table. The reader answers from the rollup when:

  • the query groups by time_bucket of the rollup's width or a multiple of it (daily from an hourly rollup), and by the rollup's series-key columns or some of them,
  • every aggregate it asks for is one the rollup stores, or, for wider buckets or fewer columns, one it can be merged from: count, sum, min, max, first_value and last_value from themselves, and avg from sum or avg together with the matching count, and
  • its filters compare the time column with constants (now() included) or filter the grouping columns, including by another table: tag IN (SELECT tag FROM tags WHERE line = 'L0002') keeps the rollup's rows of those tags as it keeps the readings.

For a wider bucket or fewer columns, the rollup's rows are aggregated again: counts and sums are added, minimums and maximums taken, first and last values taken from the first and last bucket, and averages weighted by their counts. On the industrial benchmark's 30 days of readings with a 1-minute rollup, daily statistics per tag took 0.25 s instead of 2.1 s, and 6-hourly totals over all tags 0.11 s instead of 1.1 s.

The answer is exact, up to the rounding of floating-point sums added in a different order (a relative difference around 10⁻¹²). Buckets that are inside the query's time range and unchanged since the rollup's last refresh come from the rollup. The rest come from the raw table, as without a rollup: buckets that changed since the refresh, and the partial buckets at the edges of the time range. EXPLAIN shows both parts under a Union:

$ timelake sql "EXPLAIN SELECT device_id, time_bucket(INTERVAL '1 hour', time) AS hour, max(temp)
                FROM iot.readings WHERE time >= TIMESTAMP '2026-10-02 00:30:00' GROUP BY 1, 2"
…
|   Union
|     Aggregate: groupBy=[[iot.readings.device_id, date_bin(…)]], aggr=[[max(iot.readings.temp)]]
|       Filter: iot.readings.time >= TimestampMicrosecond(1790901000000000, None) AND iot.readings.time < TimestampMicrosecond(1790902800000000, None)
|         TableScan: iot.readings …
|     Projection: iot.readings_1h.device_id AS device_id, iot.readings_1h.bucket AS …, iot.readings_1h.max_temp AS …
|       Filter: iot.readings_1h.bucket >= TimestampMicrosecond(1790902800000000, None)
|         TableScan: iot.readings_1h …

Here the half hour from 00:30 comes from the raw table, and every whole hour after it from the rollup.

Time ranges without a bucket

A query grouped by some of the rollup's columns but by no time_bucket, such as each tag's average over the last day, is answered per bucket of the rollup's width the same way, then merged: averages from the buckets' sums and counts, so the rollup needs sum and count of the column for avg, and the other aggregates as for wider buckets. On the industrial benchmark with an hourly rollup, each tag's statistics over 30 days took 55 ms instead of 1.75 s, and its gaps 44 ms instead of 1.34 s.

Which rollup

When several rollups could answer, the widest goes first: it has the fewest rows. A rollup answers a time-weighted average, an integral or a counter only where it holds 32 readings or more per row on average, and m4 or the gap aggregates where it holds 4 or more: merging a bucket's stored run costs about what reading 25 to 50 readings does, so a 1-minute rollup of one-second readings answers daily averages faster than the readings (0.29 s against 1.9 s) but daily time-weighted means slower (3.6 s against 2.0 s), and an hourly rollup answers both faster.

Buckets older than the raw table's retention are never read from the rollup: the raw table has no rows there, so neither does the answer. Query the rollup table to see that history.

The setting rollup-routing turns this off for one raw table, and SET timelake.rollup_routing = false for a session. As with every setting, the session's value overrides the table's: SET timelake.rollup_routing = true routes a table that has it off. A table read without dedup (read-dedup off, by its property or the session's SET timelake.read_dedup = false) isn't routed either, since a rollup counts each reading once.

Refresh

A refresh recomputes the buckets that the raw table's new data files cover since the last refresh, and appends them to the rollup. The rollup reads with dedup on and keeps the newest copy of each bucket; its own compaction removes the older ones.

  • Inline. After an append to the raw table, maintenance refreshes each rollup whose last refresh is older than its refresh_interval (setting rollup-refresh-interval, default 1 minute), so a busy table doesn't commit to its rollups on every append.
  • Maintain. timelake maintain and Table.maintain() refresh every rollup of the table, after compaction (--no-rollups, rollups=False to skip).
  • Now. REFRESH MATERIALIZED ROLLUP iot.readings_1h brings one rollup up to date and returns the rows it took.

Late data is handled the same way: a reading for last week makes its bucket count as changed until the next refresh recomputes it. A refresh that stops halfway leaves the rollup's record of what it reflects where it was, so the next one recomputes the same buckets. If the raw table is rolled back to an earlier snapshot, its rollups aren't used until the next refresh, which rebuilds them.

First, smallest, largest and last: m4

m4(time, value) is an aggregate returning a group's first, smallest, largest and last points, as a list of {time, value} in time order without repeats (one to four points). Per time_bucket, a line drawn through every bucket's points keeps each bucket's extremes and ends:

SELECT tag, time_bucket(INTERVAL '1 hour', time) AS hour, m4(time, value) AS points
FROM iiot.readings WHERE tag = 'M00000.temperature' GROUP BY 1, 2;
-- unnest(points) gives one row per point

Ties go the same way however rows arrive: among equal times the smaller value comes first, and among equal values the earlier point. The points of several buckets merge (the m4 of their points is the m4 of their rows), so a rollup can store them, m4(points) merges stored lists, and routing answers wider buckets from a rollup's points. On the industrial benchmark (30 days, 1,000 tags), hourly points for every tag took 0.51 s from a 1-minute rollup and 2.0 s from the readings. After Apache IoTDB's time-window M4 function.

Time-weighted averages, integrals and counters

A rollup with time_weighted_mean, integral, counter_diff or counter_rate stores each bucket's run of readings: its first and last reading, the readings next to them and the time between, the area under the signal, and the counter's growth. The column holds that state, not the result: query the raw table, which routing answers from the rollup, or merge the state with the aggregate (integral(column), time_weighted_mean(column, 'linear') for a linear rollup). Runs of neighbouring buckets join exactly, the gap between them included, so wider buckets come out as they would from the readings. One stored state answers all four for its column: a rollup with time_weighted_mean(time, x) also answers integral(time, x) and the counters, and the counters from a linear one too.

Gaps

A rollup with gap_count(time, threshold) or gap_seconds(time, threshold) stores each bucket's run of reading times with its gaps for that threshold, which answers both for the same threshold, merged across buckets: a gap from one bucket's last reading to the next bucket's first counts too.

Rollups of rollups

Define every rollup on the raw table. When a table has several, each is computed from a narrower one where that one is up to date: a daily rollup next to a minute rollup reads the minute rollup's buckets instead of every reading, and the narrowest rollups are refreshed first. On the industrial benchmark (30 days, 1,000 tags), creating a daily rollup took 5.2 s from the raw readings and 1.8 s with a 1-minute rollup in place.

Drop a rollup

DROP MATERIALIZED ROLLUP [IF EXISTS] iot.readings_1h;

This removes the rollup from the raw table's rollups setting and deletes the table. Dropping the raw table leaves its rollups in place.

From Python

Lake.sql runs the same statements, and Table.maintain() reports the refresh:

lake.sql("""
    CREATE MATERIALIZED ROLLUP iot.readings_1h AS
    SELECT device_id, time_bucket(INTERVAL '1 hour', time) AS bucket, avg(temp) AS avg_temp
    FROM iot.readings GROUP BY device_id, bucket
""")
report = lake.table("iot.readings").maintain()
print(report["rollups_refreshed"], report["rollup_rows"])

Not yet

  • Percentiles.
  • Calendar buckets (months), filters in the definition, and routing in plain DuckDB.

The design is in spec 27.