Continuous Queries: Automating Time-Series Aggregation

Why it exists

Raw time-series data, especially from IoT devices or application metrics, can be incredibly high-volume. Querying this raw data directly for high-level trends, like daily averages for a dashboard, is inefficient and slow. Continuous queries solve this by pre-aggregating or "rolling up" the data into a new, smaller time series as it arrives, making subsequent analysis much faster.

The mental model

Think of a continuous query as a standing order you give your database. Instead of you manually running a GROUP BY query every hour to get the average temperature, you tell the database: "Every hour, run this aggregation on the last hour's data and save the result over there." It's a "set it and forget it" approach to data summarization that runs inside the database itself.

How it works

You define a continuous query using a CREATE CQ statement. This statement includes a unique ID, the query logic itself (SELECT, FROM, GROUP BY), and a destination series (INTO). The most critical part is the RESAMPLE clause, which controls the execution schedule and the time window of the data being queried. The EVERY parameter sets the execution frequency (e.g., every hour). The RANGE parameter defines the slice of time to query, relative to the execution time (e.g., now() minus 60 minutes to now()). The database scheduler then takes over, automatically executing this query at the specified interval and writing the result into the target time series. The system manages the time boundaries, which is why users cannot specify their own time filters in the WHERE clause.

When to use it

Use continuous queries for creating downsampled views of high-frequency data. This is essential for performance in monitoring dashboards, long-term trend analysis, and any scenario where you repeatedly query aggregated views of raw data. It's a core pattern for managing data lifecycle in time-series databases, moving from high-granularity raw data to lower-granularity summary data over time.

When not to use it

Avoid continuous queries for one-off data analysis or for queries that require complex, non-time-based filtering that changes frequently. It's not a replacement for ad-hoc querying. If you need real-time, per-event processing with complex logic, a more general stream processing engine like Flink or Spark Streaming might be a better fit than a database-centric CQ.

One canonical example

To create a new series temp_hourly_avg that stores the hourly average temperature from a raw series device1.temperature, you would create a continuous query. The query would be configured to run every hour (EVERY 1h). Each run would select the average temperature from the last hour (RANGE 60m) and insert that single value, timestamped, into the temp_hourly_avg series. This transforms millions of raw data points into just 24 summary points per day.