Solving “Gaps and Islands” Natively on Live Streams with Timeplus
An event tells you what happened at a particular moment. Many real-time applications need to understand what happened over an interval: how long a vehicle stayed in a region, when a device became inactive, or whether a workflow has finished.
Consider a vehicle reporting regions in this order: A → A → B → B → A. We want three separate stays, including two distinct visits to A, with timestamps marking each transition. This is the gaps-and-islands problem: grouping consecutive events into uninterrupted intervals.
But what happens when a stay begins in one insert batch and ends in another? The query needs to remember enough about earlier events to recognize the next boundary.
A Big Data Boutique article explores this challenge using ClickHouse incremental materialized views. Because these views process newly inserted blocks, the solution explicitly carries state between inserts. It combines stored current state with each incoming batch, detects transitions, and maintains separate tables for open and completed segments. The pattern also requires each entity’s events to reach the same shard and replica.
ClickHouse pipeline
Now let’s explore how Timeplus, a platform built for streaming SQL, handles this problem natively and how the approach extends to other scenarios. A Timeplus materialized view runs a continuous streaming query, with the query engine maintaining intermediate state as events arrive. Streaming SQL operators express the boundary logic, and the materialized view persists the results.
That brings us to the next question: what defines the end of an interval? A state change is one possibility, but inactivity or an explicit end event may matter just as much.
In this article, we explore four related scenarios:
State changes: detect transitions with lag() and PARTITION BY.
Periods of silence: group activity using the simple form of session().
Start and end events: define sessions with explicit conditions.
Session closure and result timing: use EMIT AFTER SESSION CLOSE to explore event-span and wall-clock limits.
All four scenarios use the same event stream, so you can compare how the rules affect both the intervals you identify and when results become available.
The Problem
To make this concrete, we set up the example problem in SQL form so we can explore and test the various solutions.
We define a stream entity_events to represent each incoming event with its current state.
Heartbeats — noisy and repetitive — are simulated with INSERT INTO. We use 9 separate INSERTs so the events look like they’re arriving from a real stream.
For each (tenant, entity), we’d like a list of intervals showing how long each state stayed, as below.
The last row for each entity is an open segment — (open) means no closing transition has arrived yet, and (now) is shorthand for the current wall-clock time (it advances as time passes).
Detect State Change
For gaps and islands, the standard trick is to compare each event with its predecessor and find where the boundary lies. Timeplus supports this natively — for both historical batch queries and streaming queries.
We use lag() and PARTITION BY to detect state changes. On a transition at time t from A → B: close the A-segment (end_time = t, next_state = B) and open a new B-segment (start_time = t, next_state = '', still open).
For the closed segments, the materialized view entity_state_segments emits a new row — a closed segment, i.e. an island — on every state change. It uses two layers of lag(), each scoped by PARTITION BY. The inner layer finds the transition points; the outer layer joins adjacent transition points into a closed segment.
The MV folds the nine heartbeat events into three closed segments, each with its from/to states.
For the open segments, we use a keyed mutable stream. The materialized view upserts the entity’s current state into the mutable stream on every transition.
SELECT * FROM table(entity_state_current) shows the current states (open segments) as below.
With both closed and open segments available, a timeline view stitches the two together with a plain UNION ALL.
Why this works: Streaming lag() crosses batches. Once you write PARTITION BY, the operator owns each key’s “previous row”, persists it via checkpoints, and resumes after restarts. There is no external state table, no array gymnastics, no cursor walk — one query, two layers of lag(). We ran the same workload with bulk-inserted blocks and with one-event-at-a-time inserts; the outputs are byte-identical because the state has nothing to do with insert-block boundaries.
Timeplus: Operator Holds the Previous Row
When to use it: Islands are defined by changes in a state value, and you need the precise segment boundaries plus the from/to states. This is the most general shape.
When it doesn’t fit: lag() is arrival-ordered. Under heavy out-of-order arrival across batches, transitions can be miscounted. When you need event-time semantics, read on.
Use Session Window to Slice Events
The lag() approach works when the island boundary is a state change. But what if the boundary is silence? When an IoT device stops sending heartbeats, there is no state change to detect — just a gap with no events. For that, Timeplus offers a different primitive: session windows.
A session window dynamically starts when the first event arrives and closes after a defined period of inactivity (the idle gap). It is ideal for modeling user sessions, bursty event streams, or workloads where data arrives in uneven bursts rather than at constant intervals. Each session is scoped to a specific grouping key, and sessions for the same key never overlap.
The boundary rule is “how long without an event counts as a break”. Below we treat each state report as a heartbeat — same nine events as before, just re-read with a different question.
The query emits two closed sessions for the same nine-event input. Treat each state report as a heartbeat. e-1 is silent for 18 minutes between 09:12 and 09:30; e-2 is silent for 19 minutes between 09:01 and 09:20. Both exceed the 15-minute idle.
The second session for e-1 (from 09:30) and the second for e-2 (from 09:20) are still open. Session windows close on event time: a session only emits once the same device’s next event pushes the watermark past window_end. That is event-time semantics working as designed, not data loss.
window_end is the last event’s time plus idle — i.e. the moment we judge the island to have ended.
How it differs from lag(): The previous lag() method emits the from/to states of each island; session() emits an aggregation per island. lag() works in arrival order; session() closes on event time and watermarks.
When to use it: User sessions, device-online sessions, and any island defined by “how long silence means a break”.
In addition, the session window also supports defining start / end conditions for the island boundary.
For example, a monitoring system streams metrics continuously. The business defines “CPU above threshold is the start of an alert, fall back below is recovery”. We want one row per alert with its duration and its peak. The island boundary is a business rule, not a silence duration.
We reuse the state stream and treat “entering A” as trigger and “leaving A” as recovery. The query uses state_id = 'A' as the condition to open a session on the first matching event, and state_id != 'A' to close it.
The bracket syntax controls which events are part of the session: [ includes the opening event, ) excludes the closing event, ] includes it. So [start, end) is the most common shape — the start event is in the session, the end event is not.
Run the same nine events through it. e-1 enters A at 09:00 (trigger) and leaves A at 09:12 (recovery); e-2 never enters A. There is only one output row: e-1 has one alert, with its three A events counted in. The B event that closes the session is excluded.
This approach, session() with conditions, fits scenarios where island boundaries are defined by business events. For example:
Car overspeed session. [speed > 50, speed < 50) opens a session on the first event with speed over 50 and closes it on the first event with speed below 50.
User login session. [action = 'login', action = 'logout'] opens a session on login and closes it on logout, including both events.
Alert process session. [event = 'trigger', event = 'recovery'] opens a session on the trigger event and closes it on recovery, including both.
Global Aggregation with EMIT AFTER SESSION CLOSE
Session windows solve the “silence closes a window” case, but they still keep state resident per window until close. For workloads with millions of in-flight keys that each only need to emit once — distributed tracing, request coalescing, batched event recognition — that state footprint is too heavy. The EMIT AFTER SESSION CLOSE policy is designed for exactly this: emit the aggregation, then drop the key.
In distributed tracing, each trace is made of spans. A trace typically completes in seconds, but millions of traces can be in flight at once, and each trace only ever needs to emit a summary once. Windows would keep state resident for every trace ever seen; here state must be released the moment a key closes.
Each key is a session (i.e. an island). The session closes when the key’s event-time span reaches MAXSPAN, when wall-clock time from creation exceeds TIMEOUT, or when an event with end_col = true arrives. The aggregation emits and the key is deleted from the hash table.
Let’s reuse the state stream as an example. We treat each entity_id as the key. The session closes and the aggregation emits when any of the following is met:
The entity’s event-time span reaches MAXSPAN.
Wall-clock time from session creation exceeds TIMEOUT.
An event with end_col = true arrives. Here we want to slice state_id = 'A' lifetimes and tag the closing event as session_end.
Once the aggregation emits, the state is released and the aggregation key is deleted from the hash table.
The output is the aggregation of the three A events for e-1: the timestamps of the first and last A event, plus the count. The closing B event is excluded because of the setting include_session_end = false.
Why state stays bounded: The setting default_hash_table = 'hybrid' lets state spill to disk, so memory usage scales with the count of currently active keys. For workloads with tens of millions of entities that each stay active for minutes, this is the only way to keep state from accumulating without bound.
When to use it: Trace / span aggregation, massive short-lived keys, and “each key emits exactly once in its lifetime” workloads.
State Lives in Timeplus Engine
So, why does this look so different in Timeplus?
In batch the problem is one window query — sort, take the previous row with lag(), filter where the state changed, done. On streams the shape looks the same, but the previous row for any given entity might be in the block that just arrived, in the one before that, or three days ago. There’s no moment when the full sorted history is sitting in front of the query. Whoever is holding onto that previous row for you is the question.
In most engines like ClickHouse, the answer is “you, with a hand-rolled state table”. You need to create a table and read it back into every insert.
A Timeplus streaming query, by contrast, is a long-running stateful operator. The state-management responsibilities live in the engine, not in your SQL:
Per-key state. After PARTITION BY or GROUP BY, each key’s “previous row”, intermediate aggregates, and window boundaries are held by the operator — independent of insert blocks.
Checkpoints. State is persisted periodically; after a restart, the operator resumes from the checkpoint. No external state table is required.
Event time. Windows advance by event time and watermarks; late and out-of-order events have defined semantics.
Two Ways to Hold the Previous Row
With those three in place, “where is the previous row” stops being a question. The remaining question is “what defines an island’s boundary” — and that choice picks the primitive.
Here’s how the four scenarios line up:
Scenario | What defines an island | Timeplus primitive |
1. Order state dwell segments | The state value changes | lag() + PARTITION BY |
2. Device-online segments | Idle for too long | session() by time gap |
3. Alert from trigger to recovery | Specific start / end conditions | session() with start/end conditions |
4. Massive short-lived traces | Event span, wall-clock timeout, or end event; state freed after the key closes | EMIT AFTER SESSION CLOSE |
Takeaway
Gaps and islands belong to a broader class of stateful event-processing problems: tracking how long a device stays in a state, grouping activity into sessions, measuring workflow duration, or detecting inactivity. Each requires context across events—and a rule for deciding when an interval ends.
Batch queries can reconstruct these intervals from stored history, but refreshing the answer means repeating work over that history. ClickHouse incremental materialized views process incoming blocks efficiently; the challenge arises when an interval crosses block boundaries. The approach discussed here bridges those boundaries with stored state, seed lookups, and consistent per-entity routing.
Timeplus makes that context part of the continuous query. The engine maintains per-key state, with checkpointing for recovery, while streaming SQL operators express transitions and session boundaries. Developers still need to choose the right ordering and timing semantics, but they can express the interval logic without implementing the seed-and-block machinery themselves.
Choose the primitive that matches your boundary rule:
State changes: lag() + PARTITION BY
Inactivity gaps: the simple form of session()
Business start/end events: session() with conditions
Session closure with span or timeout limits: EMIT AFTER SESSION CLOSE
The useful question extends beyond “How do I solve gaps and islands?” to “What context must survive between events, and what makes a result complete?” Those same questions shape session analytics, lifecycle tracking, and many other streaming applications.
Try the examples with Timeplus Proton, our open-source streaming SQL engine. Start with one boundary rule, then change it to see how the resulting intervals differ.
