Solving the gaps-and-islands problem incrementally in ClickHouse materialized views, which normally only see one insert block at a time. The approach seeds each block's window computation with the stored current state from a ReplacingMergeTree, then walks the combined (seed ++ block) sequence using array functions to detect state transitions. The post covers the full schema (current-state table, closed-intervals table, two MVs, a union view), three silent traps (column name collision, intra-block transitions missed by naive state-diff, firing order myths), join key alignment for performance, and the hard constraint that every event for a given entity must land on the same shard and replica. A comparison with refreshable MVs is included.
Table of contents
The problemThe batch answer, and why it does not fit an MVThe patternFirst cut: a simple state-diff MV (and why it is not enough)Moving the window function into the MVOrder-key pushdown in the seed joinMyth-buster: firing order does not matter hereThe real limiter: one shard and replica per entityOperational gotchasWhen to reach for a refreshable MV insteadTakeaways345 Impressions