TTL on MV

[3322bfr]

If we have a query like this one

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_ch_ext_table
INTO ch_ext_table
AS
WITH s1 AS (
  SELECT
    window_start as t,
    raw:brand as brand,
    raw:car as car,
    count(*) as counts
  FROM
    tumble(events, raw:time::datetime64, 1h)
  GROUP BY
    window_start,
    brand,
    car
)
SELECT
  window_start AS time,
  brand,
  sum(counts) AS total,
  count(*) AS distinct_cars
FROM
  tumble(s1, t, 1h)
WHERE
  counts > 10
GROUP BY
  window_start,
  brand
EMIT 
  ON UPDATE

Where we consume events from external stream events (redpanda) and we store results in a clickhouse table, what happens with memory usage for the CTE stream s1? Is it deleting events older than 1 hour? In my local test, memory keeps increasing up to the point where it gets evicted all at once usually in between 6 and 12 hours after continuously running.
The behavior is different if the CTE query is not using windowing (tumble, hop)?

[chenziliang]

tumble window aggregation will accumulate all of the aggregation states in-memory in this case until the window is "closed", then all is the states will be removed from memory and prepare for next window aggregation.

Internally, proton will track timestamp progressing to decide when a window is closed.

[chenziliang]

Global aggregation on the other hand is different which means it will track all aggregation states with no retention. If using pure in-memory aggregations and if the cardinality is growing indefinitely, the memory will blow up; if the aggregation key is a fixed set, it will be fine on the other hand.

In next proton 3.0 release which we are actively working on, it will support hybrid aggregation which spill states to disk, and can support very large amount of unique key aggregation without blowing memory.