TTL GROUP BY rollups in ClickHouse

Keeping years of history after the detail is gone

A TTL GROUP BY rule lets ClickHouse aggregate expired rows into fewer rows inside the same table instead of deleting them outright. Teams reach for it when a raw event or telemetry table grows faster than its retention policy allows, yet they still need long-range trends for capacity planning, billing reconciliation or audit questions. Typical uses include keeping per-minute request counts after the raw log lines are gone, or keeping daily cost totals per tenant after the per-event detail expires. Be aware that a rollup is one-way: once merges have run over expired parts, the detail is gone and cannot be reconstructed from the table itself.

This page covers the mechanism, the design choices, the setup steps, how to verify the result and what to confirm against the ClickHouse version you run. The design decisions come first. You choose the grouping keys, which columns are aggregated and with which functions, and which columns are kept as-is. Those choices fix what questions the history can answer later. Your team should also decide whether the rollup belongs in the same table, which depends on how much the retained aggregates must be re-aggregated across time windows.

Before you configure anything, confirm the exact TTL and GROUP BY syntax, the supported aggregate behavior and the merge semantics in the documentation for your release, and test them on a pre-production copy with representative data. Then verify the outcome by checking that expired rows collapse as expected after merges, that totals before and after the rollup match for the retained grain, and that the result holds after a restore test. The ClickHouse backup documentation is a useful reference here, since a rollup is only as safe as the backups that protect the detail before it expires.

Which rollup design fits your retention rules

This table compares four ways to keep aggregated history while removing detail: TTL GROUP BY in the source table, a materialized view writing a separate rollup table, plain TTL DELETE, and recompression of old parts. Read each column against your audit questions and query patterns, then confirm the exact behavior against the ClickHouse documentation for the version you run. The main takeaway is that the choice decides where the aggregate lives and how late or corrected data is handled.

Design	What happens to raw rows and the aggregate	Late or corrected data, storage and merge work, best fit
TTL GROUP BY in the source table	Expired rows are merged into aggregated rows during background merges, so raw detail disappears and the aggregate stays in the same table.	Merges apply the rollup only when parts combine, so timing is not exact. Late rows older than the TTL can be aggregated later, but confirm how duplicates and key ordering are handled. Suits teams that want one table and accept merge-driven timing.
Materialized view into a rollup table	The source keeps raw rows under its own TTL, while the view writes aggregates to a separate table that can have a longer retention.	Late rows still reach the rollup on insert, but corrections to source rows do not update past aggregates. Storage holds both tables and insert work runs twice. Suits dashboards and audits that query a stable summary schema.
Plain TTL DELETE with no rollup	Expired rows are removed on schedule and no aggregate is kept, so history exists only in backups or an external store.	Nothing to reconcile for late data, and merge work is limited to deletion. Lowest storage footprint, but any historical trend question needs a separate retained source. Suits data with short legal retention and no trend need.
Recompression of old parts without aggregation	Rows are kept in full, and older parts are rewritten with a heavier codec through a TTL move or recompression rule.	Detail and corrections stay intact, but storage falls only by codec gains. Rewrites add CPU and merge load on old data. Suits long audit windows where every row must remain queryable.

Designing TTL GROUP BY keys before rows expire

The GROUP BY keys and aggregate expressions are fixed when the TTL rule is written. Once ClickHouse merges a part and applies the rollup, the detail rows that could have produced a different grouping or a different aggregate are gone, so no later rule can recompute them. Treat the rule definition as a schema decision that needs the same review as a sorting key change. Confirm in the TTL documentation for your ClickHouse version how grouping columns, aggregate columns and the table's sorting key must relate, because the requirements on column order and on which columns can be grouped differ between releases.

Choose grouping columns that match the table's sorting key, starting with the time bucket and the dimensions your dashboards filter on, such as tenant, service or region. Grouping on columns the sorting key does not cover makes the rollup harder to reason about and can leave many small aggregates unmerged. For aggregates, sums, counts, minimums and maximums are straightforward to keep. Averages should be stored as a sum and a count so they can be combined later. State-based functions, such as the -State combinators used with AggregateFunction columns, let you keep intermediate results that merge correctly across parts. Distinct counts and percentiles need that same state-based approach, since a plain stored result cannot be merged into a correct distinct count or quantile later.

Before creating the rule, write down the query set the rollup must still answer, with the exact filters, groupings and time ranges each report or alert uses. For each query, check that the grouping keys and aggregate states cover it, and record the queries you are deliberately dropping for historical data. Test the rule on a copy with representative data and compare rollup output against raw-table results for the same windows. If a historical query needs a column that TTL GROUP BY discards, a materialized view that writes to a separate summary table is often the better design, because it keeps raw data and summary data on independent retention schedules.

Choosing the pattern by team and retention goal

A platform team is usually asked to keep one table inside a storage budget while the business keeps asking for longer history. The constraint is physical: disk growth, merge load and the time it takes for expired data to actually leave the disk. A TTL GROUP BY rule on the detail table is often the fit when the table is append-heavy, the aggregation keys are known in advance, and the team can accept that detail rows disappear once the rule fires. Illustrative example: a log-style table of several billion rows per day where only per-minute counts matter after a week.

Storage ceiling: agree the target size and how much headroom the merges need, not just the size of the raw data.
Aggregation keys: fix the GROUP BY columns and aggregate functions before the table goes live, since changing them later means a new table.
Merge behavior: confirm that the rollup runs during background merges and measure how long old parts take to shrink.
Sign-off: the owning team of the data must accept that detail becomes unrecoverable after the TTL fires.
Evidence to keep: disk usage over time per table, merge logs and a test on a staging table with representative volume.

Check that the rollup's keys still answer the questions the platform team expects to be asked six months from now.

An analytics team needs dashboards to keep returning results for older periods, often at a coarser grain. The constraint is query shape: panels that read daily totals for a year cannot be served from per-second detail that no longer exists. In many cases the better design is a materialized view that writes a separate rollup table as data arrives, so the dashboard reads a small table and the detail table keeps its own retention. A TTL GROUP BY rule fits when the dashboards only ever need the aggregated grain and the rollup can be defined once and kept stable.

Grain agreement: list every panel and the finest time resolution each one needs for each age of data.
Metric stability: avoid aggregates that cannot be combined later, such as averages of averages, unless the design stores sums and counts.
Backfill: decide whether history before the rollup existed must be rebuilt, which a materialized view alone will not do.
Sign-off: the analytics owner must approve the grain and the cut-over date when dashboards switch to rollup tables.
Evidence to keep: a query list with expected results, before and after comparisons on sample periods, and dashboard change records.

Confirm which dashboards break if a single column is added to the grouping keys later.

How to set up a rollup rule safely

This sequence moves a TTL GROUP BY rollup from a representative test table to production in controlled steps, with a check at each stage proving the aggregates still match the detail the business relies on. It produces a rule that has been verified, backed up and rolled out without surprises.

Confirm the ClickHouse version and changelog
The platform team checks which ClickHouse version runs in production and whether it is still covered by security updates, then reads the changelog for every release between the current version and the target. Confirm the target is a stable or LTS release that the vendor lists as supported, and note any backward-incompatible changes that touch TTL, merges or aggregate functions. The step is done when the team has a written version decision and a list of changelog items that could affect the rollup. Confirm the current support status in the ClickHouse security policy before you proceed.
Build a pre-production table with representative data
Create a pre-production cluster or a schema-identical table on a non-production node, then load a copy of real event data covering the full retention window, including late-arriving rows and any seasonal peaks. Replay the production query set against this copy so you know which periods and dimensions the business actually reads. The output is a table whose row counts by day match production for the same window, which you confirm with a count query grouped by toDate on both sides.
Define the GROUP BY keys and aggregates
Agree with the data owners which dimensions must survive the rollup, such as tenant, service or region, and which aggregates the reports need, such as sum, count, min, max or quantile states. Keep the key set narrow, because every key you keep multiplies the rolled-up row count. Write the aggregates as the exact expressions the dashboards use, so the rollup can answer them later. The step closes with a signed-off specification listing each key, each aggregate and the age at which the rule applies.
Create the rule on the pre-production table
Add the TTL GROUP BY clause to the pre-production MergeTree table, setting the sort key so the grouped columns form its prefix, and define the SET expressions that replace detail values with aggregates. Confirm the table definition with SHOW CREATE TABLE and check that the TTL expression matches the specification. The result is a table with the rule in place and no production impact.
Force or wait for merges and inspect the result
TTL GROUP BY applies during merges, so run OPTIMIZE TABLE ... FINAL on the test table or wait for background merges to process expired parts, then inspect system.parts and system.parts_columns to see which parts were rewritten. Check that old partitions now hold fewer rows per key and that the detail columns have been replaced as specified. The step is done when no expired parts still contain raw detail, which you verify with a query on the oldest partitions.
Compare rollup outputs with a reference query
Run the same reports against the rolled-up table and against an untouched copy of the detail data, then compare totals per period, per key and per aggregate. Totals such as sum of bytes and count of events should match exactly, while quantile and approximate outputs should fall within the tolerance the data owners agreed. Record any mismatch and trace it to a key, a late-arriving row or a merge that had not completed. Done means every report in the production query set returns the expected result on the rollup.
Back up before applying the rule to production
Take a BACKUP of the affected production tables before you alter them, using the backup method your team has tested, and confirm the backup completes without errors. Do not rely on replication as protection, since a rollup that runs on every replica removes detail everywhere at once. Restore the backup to a spare cluster and run the reference comparison again to confirm it can be recovered. The output is a restore-tested backup with a recorded identifier.
Roll out with a monitored merge window
Apply the TTL GROUP BY change to the production table during an agreed window, then watch merge activity, disk usage, replication queue length and query latency on the production cluster. Use system.merges, system.replication_queue and your monitoring dashboards to confirm that merges progress and replicas stay in sync. Keep the backup and rollback plan ready until the first expired partitions have merged and the production reference queries pass.

Protecting audit trails and access during rollups

A TTL GROUP BY rule removes detail rows for good once it runs, so the controls around it matter as much as the rule itself. The cards below cover what to protect before the rule takes effect, who can read what afterward, and what evidence you need when auditors ask how history was reduced.

Back up before the rule takes effect

Once a TTL GROUP BY rule merges detail rows into aggregates, the original rows cannot be reconstructed from the rollup. Replication does not help here, because a replica copies the rollup just as faithfully as it copied the detail. Take a BACKUP of the affected tables and confirm it can be restored on a spare cluster before you enable the rule. Keep the backup for as long as your retention policy requires the detail to exist.

Test the restore, not just the backup

The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed. For a rollup project, a restore test should confirm that the detail tables and the rollup tables come back with the expected row counts and that the TTL settings are present. Schedule the test on a spare cluster and record the result as part of the change ticket.

Separate detail and rollup access

Raw detail and aggregated history usually serve different readers, and the detail often holds personal data that the rollup was meant to remove. Use roles so that analysts query the rollup tables while only a small operator group can read the detail. Row policies can narrow what a role sees within a table. Follow the access control documentation for the configuration, and confirm that the default user does not keep broad rights.

Record when rules ran and what they removed

Auditors asking how data was discarded want evidence, not a configuration file that describes intent. Keep a dated record of each TTL GROUP BY rule version, the date it was applied, the tables and time windows it covered, and the row counts before and after each run. Confirm with your auditor which format of evidence they accept, and whether ClickHouse query logs can be retained long enough to support it.

Validate the rule on every replica

Replicated tables coordinate through ClickHouse Keeper, but you should not assume that a rule behaves the same way on each node. Check that the TTL definition and the merge results match across replicas after a change, and watch for parts that lag behind or fail to merge on one host. A recovered replica syncs missing data from a healthy replica, so a rule that was never applied correctly on one node can spread its state, not fix it.

Keep schema changes under version control

The GROUP BY keys, aggregate functions and TTL expressions define what history survives, so they are part of your data retention record. Store the DDL in a Git repository, review each change, and tag the commit that was applied to each environment. Exclude sensitive columns from the GROUP BY output on purpose, and document why each column was kept or dropped, so a later change cannot silently reintroduce personal data into the rollup.

Testing TTL GROUP BY rollups before production data expires

A TTL GROUP BY rule removes detail rows as soon as it runs, so the rollup logic has to be proven on data that resembles production before it is attached to a live table. Once the rule has merged expired parts, the original rows cannot be recovered from the rollup, and a wrong GROUP BY key, a missing aggregate or an incorrect SET expression becomes a permanent loss. ClickHouse's production guidance recommends a pre-production environment with representative data and queries, and that is the place to find these errors.

The backup documentation adds a warning that applies directly here: a backup that has never been test-restored will probably not restore properly when it is needed. Build the test on a spare cluster by restoring a recent backup of the source tables with RESTORE, then add the rollup rule to the restored copy. Choose a time window where detail rows are about to expire so the rule actually fires during the test. Confirm the restore procedure itself works on the same schedule as your backups, and record how long the restore took so you can plan the production change window. The guidance from ClickHouse on backups is in its backup and restore documentation.

After the rule runs on the restored copy, compare the rolled-up table with the source for the same period. Check row counts per bucket, sums and counts of the aggregated columns, and the boundary between detail and rollup data. Then run the queries your dashboards and alerts depend on against the test cluster and confirm they return the values you expect. Any mismatch should block promotion of the rule, and the test should be repeated after each change to the schema or the rule. Testing does not guarantee the production outcome, but it reduces the chance that expired data is lost without anyone noticing.

Limits and behaviors to confirm for your environment

TTL GROUP BY rollups depend on the server version, the release line you run, and how merges and replicas behave under your ingest pattern. The cards below are questions to answer from the vendor docs and from your own test results before you rely on a rollup in production.

Which versions get security updates

Check the supported version list in the ClickHouse security policy before you pick the version your rollup will run on. Versions outside that list receive no security updates, so a rollup design built on an unsupported line carries an open exposure. Ask whether your target version is on the list today and when your planned upgrade window falls. Record the answer in your change record so the decision is traceable.

Stable and LTS release cadence

Stable releases ship roughly monthly, and LTS releases ship twice a year with one year of support. Decide which line your rollup environment follows, because that choice sets how often you must retest TTL GROUP BY behavior. Ask the vendor how long a stable line receives fixes after a newer release lands, and plan your test cycle around that window rather than the release date alone.

Documented TTL GROUP BY behavior for your version

Confirm in the reference for your exact version which aggregate functions, sort key rules and TTL expressions your rollup relies on. Behavior around how aggregated columns are combined during merges is the part most likely to differ between releases. Ask the vendor docs for the rules, then reproduce each one with a test table that mirrors your schema before you write the production TTL.

Merges and late-arriving rows

Ask how rollups behave when rows arrive after a part has already been aggregated or after its TTL has passed. Rows that land in a part that has not merged yet may be combined differently than rows that arrive later, so totals can shift depending on merge timing. Test a late batch against a known total, and decide whether your reports accept that drift or whether late data needs a separate correction path.

Rollup logic across replicas and recovery

Confirm that every replica of a ReplicatedMergeTree table applies the same TTL rollup to the same parts, and that a recovered replica catches up without changing aggregated results. Ask the vendor docs how fetched parts interact with local merges during sync. Then run a test where one replica is stopped during a rollup window, restarted, and compared row by row with a healthy replica.

Backward-incompatible changes in the changelog

Read the changelog for every version between your current and target release before any upgrade, and look for entries that touch TTL, aggregate functions, merge behavior or default settings. Minor releases can include backward-incompatible changes, so a rollup that worked last quarter may need new settings or a rewritten expression. Keep a restore-tested backup of the rollup tables before you move versions.

Questions teams ask before rolling up history

What drives the cost of designing a ClickHouse rollup?

The main cost driver is how many aggregates the business needs and how many rules sit behind them. Designing a rollup means agreeing which dimensions to keep, which metrics to aggregate, and how long each grain should live. Effort rises with the number of source tables, the number of grains, the quality of existing data, and the number of query owners who must accept the new shape. One-time work covers design, the rollup table definitions, testing against representative data, and documentation. Recurring work comes from monitoring merges, reviewing schema changes and running restore tests. Ask any quote to list each rollup level, each source table, the test data plan and who signs off the results.

How long does a TTL GROUP BY rollup project take?

No duration can be stated without a written scope, because the steps that set the timeline depend on your data and owners. The biggest items are agreeing retention and grain with data and query owners, building the rollup on a pre-production cluster with representative data, and checking that aggregates match raw results within an agreed tolerance. Sign-off and any change window for production come next. A single table with clear owners moves faster than several tables with competing metrics. Ask for a milestone plan that names each gate, the people who clear it, and the evidence each gate produces.

What does a rollup project need from our own team?

The work needs three kinds of input. Data owners confirm which columns are authoritative, which retention periods the policy allows, and how deletions must be handled. Query owners list the dashboards and reports that must still work against aggregated history, along with the grain and filters they use. The team also needs a pre-production ClickHouse cluster with a copy of the data that resembles production, because the ClickHouse production FAQ recommends testing with representative data and queries before changes. Finally, a named approver should sign off the test results and the retention rule before anything runs against production tables.

Can our internal team design and run a rollup without outside help?

Yes, an internal team with ClickHouse experience can design and run a rollup, and many do. The questions to answer honestly are whether someone has run TTL and merge behavior in production, whether a restore path has been tested recently, and whether query owners will review the results. Outside help is most useful for the design review, the test plan and the first rollout, where mistakes are hardest to undo. Skill gaps usually show up in merge timing and in dashboards that break quietly. Ask any partner to show the test cases it would use, then check those cases against what your own staff already knows.

Can a TTL GROUP BY rollup be reversed after merges have run?

Not without a copy taken before the rule ran. Once a merge applies a TTL GROUP BY rule, the detail rows it replaced are gone from that part, so the only route back is a backup or a preserved source copy. The ClickHouse backup documentation notes that replication alone does not protect against human error or corruption, so a replica that already applied the rollup carries the same loss. Keep a backed-up copy of the source data for any period you may need to rebuild, verify that restore works on a spare cluster, and record the date the rule became active. Treat the rollup as a one-way step for the detail it removes.

When is a materialized view rollup worth its extra storage?

A materialized view is worth its extra storage when queries need both recent detail and aggregated history at different grains, or when aggregates must exist as soon as data arrives. It writes aggregated rows on insert, which suits dashboards that read the same summary repeatedly. TTL GROUP BY is simpler when older data only needs summarizing and the detail can go. The trade-off is that a view stores extra data alongside the raw table, and its definition must be maintained through upgrades and backfills. Ask your team to compare query latency, storage growth and the effort of backfilling the history you need before choosing between the two designs.

How does a rollup support an audit of data retention?

A rollup supports an audit by showing that detail was removed on a documented schedule and that the aggregates kept match the policy. Auditors typically look for the retention rule in version control, the date it became active, the tables and columns it covers, and evidence that merges applied it. ClickHouse system tables can help show which rows exist by age, but a rollup proves retention only when the policy and the removal evidence are kept together. Keep a change record, the rollup test results, backup retention rules and restore test logs. DeployOpen prepares these controls and evidence, but an independent auditor decides what is sufficient.

How DeployOpen scopes and delivers rollup work

DeployOpen approaches TTL GROUP BY rollups as a scoped design and delivery exercise, starting with the questions that decide whether a rollup or a materialized view fits your data. Each engagement is agreed in writing before work starts, and the rollup is tested against a pre-production copy of your own data and queries.

How we scope

Inventory which tables need aged rollups, the retention each raw dataset must meet, and the aggregates reports actually query.
Decide between TTL GROUP BY on the source table and a materialized view rollup, and record the reasoning in the scope.
Confirm with your auditors or data owners which detail may be discarded and how long rollups must be kept.

How we build

Design the rollup table, sorting key and aggregate functions, then write the TTL GROUP BY rules for each interval.
Test rollups on a pre-production cluster with representative data, checking aggregate results against raw data before expiry.
Configure recompression of older parts where the storage policy and workload justify it, and measure the effect on query time.

How we hand over

Deliver the schema, TTL expressions, verification queries and a runbook explaining how each rollup behaves over time.
Document the upgrade and rollback steps for rollup changes, including how TTL rules are altered on live tables.
Walk your operators through monitoring merges and TTL progress, and through restore tests that include rolled-up tables.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer, scoped separately from the build.
Review rollup changes when reporting needs or ClickHouse releases change, using the changelog before any upgrade.
Confirm with your team, in writing, which support hours, response expectations and responsibilities apply to your environment.

Rolling up historical data with TTL GROUP BY