title_wip

Tip

If you expect reads to always hit the rollup, you can create a **second materialized view** that writes *finalized* numbers to a “plain” `MergeTree` table at the same 1h grain.
States give more flexibility while finalized numbers give slightly simpler reads.

Filter on fields in the primary key for best performance

## Filter on fields in the primary key for best performance[​](#filtering-performance "Direct link to Filter on fields in the primary key for best performance")

You can use the `EXPLAIN` command to see how the index is used to prune data:

```
EXPLAIN indexes=1
SELECT *
FROM events_rollup_1h
WHERE bucket_start BETWEEN now() - INTERVAL 3 DAY AND now()
  AND country = 'US';
```

```
        ┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
    1.  │ Expression ((Project names + Projection))                                                                                          │
    2.  │   Expression                                                                                                                       │
    3.  │     ReadFromMergeTree (default.events_rollup_1h)                                                                                   │
    4.  │     Indexes:                                                                                                                       │
    5.  │       MinMax                                                                                                                       │
    6.  │         Keys:                                                                                                                      │
    7.  │           bucket_start                                                                                                             │
    8.  │         Condition: and((bucket_start in (-Inf, 1758550242]), (bucket_start in [1758291042, +Inf)))                                 │
    9.  │         Parts: 1/1                                                                                                                 │
    10. │         Granules: 1/1                                                                                                              │
    11. │       Partition                                                                                                                    │
    12. │         Keys:                                                                                                                      │
    13. │           toYYYYMM(bucket_start)                                                                                                   │
    14. │         Condition: and((toYYYYMM(bucket_start) in (-Inf, 202509]), (toYYYYMM(bucket_start) in [202509, +Inf)))                     │
    15. │         Parts: 1/1                                                                                                                 │
    16. │         Granules: 1/1                                                                                                              │
    17. │       PrimaryKey                                                                                                                   │
    18. │         Keys:                                                                                                                      │
    19. │           bucket_start                                                                                                             │
    20. │           country                                                                                                                  │
    21. │         Condition: and((country in ['US', 'US']), and((bucket_start in (-Inf, 1758550242]), (bucket_start in [1758291042, +Inf)))) │
    22. │         Parts: 1/1                                                                                                                 │
    23. │         Granules: 1/1                                                                                                              │
        └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

The query execution plan above shows three types of indexes being used:
a MinMax index, a partition index, and a primary key Index.
Each index makes use of fields specified in our primary key: `(bucket_start, country, event_type)`.
For best filtering performance you will want to make sure that your queries are making use of primary key fields to prune data.

Common variations

## Common variations[​](#common-variations "Direct link to Common variations")

- **Different grains**: add a daily rollup:

```
CREATE TABLE events_rollup_1d
(
    bucket_start Date,
    country      LowCardinality(String),
    event_type   LowCardinality(String),
    users_uniq   AggregateFunction(uniqExact, UInt64),
    value_sum    AggregateFunction(sum, Float64),
    value_avg    AggregateFunction(avg, Float64),
    events_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(bucket_start)
ORDER BY (bucket_start, country, event_type);
```

Then a second materialized view:

```
CREATE MATERIALIZED VIEW mv_events_rollup_1d
TO events_rollup_1d
AS
SELECT
    toDate(event_time) AS bucket_start,
    country,
    event_type,
    uniqExactState(user_id),
    sumState(value),
    avgState(value),
    countState()
FROM events_raw
GROUP BY ALL;
```

- **Compression**: apply codecs to big columns (example: `Codec(ZSTD(3))`) on the raw table.
- **Cost control**: push heavy retention to the raw table and keep long-lived roll-ups.
- **Backfilling**: when loading historical data, insert into `events_raw` and let the materialized view build roll-ups automatically. For existing rows, use `POPULATE` on materialized view creation if suitable, or `INSERT SELECT`.

Clean-up and retention

## Clean-up and retention[​](#clean-up-and-retention "Direct link to Clean-up and retention")

- Increase raw TTL (e.g., 30/90 days) but keep roll-ups for longer (e.g., 1 year).
- You can also use **TTL to move** old parts to cheaper storage if tiering is enabled.

Troubleshooting

## Troubleshooting[​](#troubleshooting "Direct link to Troubleshooting")

- Materialized view not updating? Check that inserts go to the **events\_raw** (not the roll-up table), and that the materialized view target is correct (`TO events_rollup_1h`).
- Slow queries? Confirm they hit the rollup (query the rollup table directly) and that the time filters align to the rollup grain.
- Backfill mismatches? Use `SYSTEM FLUSH LOGS` and check `system.query_log` / `system.parts` to confirm inserts and merges.

- [When to use this pattern](#when-to-use)- [Create the raw events table](#create-raw-events-table)- [Design the rollup (aggregated) table](#design-rollup)- [Create a materialized view that populates the rollup](#create-materialized-view-to-populate-rollup)- [Insert some sample data](#insert-some-sample-data)- [Querying the rollup](#querying-the-rollup)- [Filter on fields in the primary key for best performance](#filtering-performance)- [Common variations](#common-variations)- [Clean-up and retention](#clean-up-and-retention)- [Troubleshooting](#troubleshooting)

Was this page helpful?