Rollups
A rollup keeps a table pre-aggregated by time. This page covers declaring one, the queries it answers, and how it changes writes to the table; for every endpoint and field, see the Rollups API reference.
How rollups work
A rollup holds one row per time bucket and combination of the columns you choose, with the aggregates you declare.
- Queries are not rewritten by hand. An aggregate query the rollup can answer reads it instead of the table.
- Rows the rollup has not absorbed yet are read from the table, so answers include the latest data.
- Answers are the same as reading the table, except percentiles, which are approximate.
Rollups suit dashboards and reports that aggregate a large, time-stamped table over days or weeks.
Declaring a rollup
POST /v1/databases/{database_id}/rollups
{ "schema_name": "public", "table_name": "requests",
"time_column": "ts", "bucket_width_secs": 3600,
"dimensions": ["region", "status"],
"measures": [{ "kind": "count" },
{ "kind": "numeric", "column": "bytes" },
{ "kind": "approx_percentile", "column": "latency_ms" }] }
# => { "rollup": { "id": "rlup…", "status": "building", … },
# "job_id": "jobs…" }
time_columnmust be a UTC timestamp.bucket_width_secsis the finest time grouping the rollup serves.dimensionsare the columns queries group or filter by.filteris optional, and keeps only the rows that match it, such as"status <> 'error'".
The rollup builds in the background. Poll Get a rollup, or the job, until status is ready.
| Measure | Answers |
|---|---|
count | count(*), or count(column) when it names a column |
numeric | count, sum, avg, min and max of the column |
sum, min, max | That aggregate of the column |
approx_percentile | approx_percentile_cont and approx_median of the column |
A rollup cannot be changed. To change one, drop it and declare it again, which builds it from the start.
Querying
Query the table as usual. The query response, and the query run, carry a rollup field:
| Value | Meaning |
|---|---|
used | A rollup answered the query. |
not_matched | A rollup is ready, but cannot answer this query. |
not_ready | The rollup is building, needs a rebuild, or its build failed. |
unavailable | The rollup was skipped for this run. The same query later can use it. |
The field is absent when no table the query reads has a rollup.
Which queries a rollup answers
Aggregates come from its measures. min and max of a floating-point column are always read from the table.
Grouping is by its dimensions and by time. Group time with date_trunc or date_bin, at the rollup's bucket width or a multiple of it.
Conditions are on its dimensions or its time column.
A filtered rollup needs its filter in the query, as declared. An equivalent condition written differently does not match.
Percentiles must use the rollup's digest size. Call approx_percentile_cont or approx_median with the declared centroids, 100 unless set.
SELECT date_trunc('day', ts) AS day, region,
count(*), avg(bytes), approx_percentile_cont(latency_ms, 0.95)
FROM public.requests
WHERE ts >= now() - INTERVAL '30 days'
GROUP BY 1, 2
Writes to a table with rollups
Appends are always accepted. So are replaces, which set the table's rollups to needs_rebuild; queries read the table until you rebuild them.
Rows older than the window cannot be changed or deleted. The window is window_secs, one hour by default, and is shared by every rollup on the table. Such a write returns a 409 with the code ROLLUP_COVERAGE_EDIT; retrying it does not help. To change the window, see Set a rollup's window.
SQL DELETE and UPDATE are refused. Change rows with a keyed load instead (see Load modes), whose key includes the time column.