# Rollups Source: https://www.hotdata.dev/docs/rollups Site index: https://www.hotdata.dev/llms.txt 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](/docs/api-reference/rollups). ## 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 ```text 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_column` must be a UTC timestamp. `bucket_width_secs` is the finest time grouping the rollup serves. - `dimensions` are the columns queries group or filter by. - `filter` is optional, and keeps only the rows that match it, such as `"status <> 'error'"`. The rollup builds in the background. Poll [Get a rollup](/docs/api-reference/rollups#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. ```sql 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](/docs/api-reference/rollups#rebuild-a-rollup). **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](/docs/api-reference/rollups#set-a-rollup-s-window). **SQL `DELETE` and `UPDATE` are refused.** Change rows with a keyed load instead (see [Load modes](/docs/push-data#load-modes)), whose key includes the time column. > **Forks and dropped tables** — A fork does not copy a database's rollups. Dropping a table drops its rollups.