Continuous Aggregates

Continuous aggregates in TimescaleDB are materialized views designed specifically for time-series data. They automatically maintain pre-computed aggregations of data from a source hypertable, dramatically improving query performance for analytical workloads. Unlike standard materialized views, continuous aggregates refresh incrementally, only processing new data since the last refresh rather than recalculating the entire aggregate.

Creating a Continuous Aggregate

To create a continuous aggregate using Data Annotations, apply the [ContinuousAggregate] attribute to the entity class. This attribute requires specification of the materialized view name, parent hypertable name, and time bucketing configuration.

Use the [TimeBucket] attribute to configure the time bucketing function that groups time-series data into fixed intervals, and the [Aggregate] attribute on properties to define aggregation functions.

See also: CREATE MATERIALIZED VIEW (Continuous Aggregate)

Basic Configuration

using CmdScale.EntityFrameworkCore.TimescaleDB.Abstractions;
using CmdScale.EntityFrameworkCore.TimescaleDB.Configuration.ContinuousAggregate;
using Microsoft.EntityFrameworkCore;

[Keyless]
[ContinuousAggregate(
    MaterializedViewName = "trade_hourly_stats",
    ParentName = nameof(Trade))]
[TimeBucket("1 hour", nameof(Trade.Timestamp))]
public class TradeAggregate
{
    [Aggregate(EAggregateFunction.Avg, nameof(Trade.Price))]
    public decimal AveragePrice { get; set; }
}

public class Trade
{
    public DateTime Timestamp { get; set; }
    public decimal Price { get; set; }
    public string Ticker { get; set; } = string.Empty;
}

⚠️ Note: Continuous aggregate entities must be marked with [Keyless] since they represent views, not tables.

Aggregate Functions

The [Aggregate] attribute defines which aggregation function to apply to a source column and maps the result to the aggregate entity property.

Supported Aggregate Functions

The following aggregate functions are available through the EAggregateFunction enum:

• Avg: Calculate the average value
• Sum: Calculate the sum of values
• Min: Find the minimum value
• Max: Find the maximum value
• Count: Count the number of rows
• First: Get the first value in the time window
• Last: Get the last value in the time window

Defining Multiple Aggregations

using CmdScale.EntityFrameworkCore.TimescaleDB.Abstractions;
using CmdScale.EntityFrameworkCore.TimescaleDB.Configuration.ContinuousAggregate;
using Microsoft.EntityFrameworkCore;

[Keyless]
[ContinuousAggregate(
    MaterializedViewName = "trade_hourly_stats",
    ParentName = nameof(Trade))]
[TimeBucket("1 hour", nameof(Trade.Timestamp))]
public class TradeAggregate
{
    [Aggregate(EAggregateFunction.Avg, nameof(Trade.Price))]
    public decimal AveragePrice { get; set; }

    [Aggregate(EAggregateFunction.Max, nameof(Trade.Price))]
    public decimal MaxPrice { get; set; }

    [Aggregate(EAggregateFunction.Min, nameof(Trade.Price))]
    public decimal MinPrice { get; set; }

    [Aggregate(EAggregateFunction.Sum, nameof(Trade.Size))]
    public decimal TotalVolume { get; set; }

    [Aggregate(EAggregateFunction.Count, "*")]
    public int TradeCount { get; set; }
}

⚠️ Note: For COUNT(*), use the wildcard "*" as the source column.

Time Bucketing Configuration

The [TimeBucket] attribute configures how time-series data is grouped into fixed intervals.

TimeBucket Properties

• BucketWidth (required): The time interval for bucketing (e.g., "1 hour", "15 minutes", "1 day")
• SourceColumn (required): The name of the time column in the source hypertable
• GroupBy (optional): Whether to include the time bucket in the GROUP BY clause (default: true)

[Keyless]
[ContinuousAggregate(
    MaterializedViewName = "sensor_daily_stats",
    ParentName = nameof(SensorReading))]
[TimeBucket("1 day", nameof(SensorReading.Timestamp), GroupBy = true)]
public class SensorDailyAggregate
{
    [Aggregate(EAggregateFunction.Avg, nameof(SensorReading.Temperature))]
    public double AverageTemperature { get; set; }
}

Configuration Options

The [ContinuousAggregate] attribute provides several configuration properties:

MaterializedViewName

The name of the materialized view created in the database (required):

[ContinuousAggregate(MaterializedViewName = "hourly_metrics", ParentName = nameof(Metric))]

ParentName

The name of the source hypertable entity (required):

[ContinuousAggregate(MaterializedViewName = "trade_stats", ParentName = nameof(Trade))]

ChunkInterval

The chunk interval for the continuous aggregate's underlying materialized hypertable. Defaults to 10 times the parent hypertable's chunk interval if not specified:

[ContinuousAggregate(
    MaterializedViewName = "trade_stats",
    ParentName = nameof(Trade),
    ChunkInterval = "30 days")]

WithNoData

By default, continuous aggregates are populated with data when created. Set to true to create an empty aggregate that will be populated on the first refresh:

[ContinuousAggregate(
    MaterializedViewName = "trade_stats",
    ParentName = nameof(Trade),
    WithNoData = true)]

CreateGroupIndexes

Controls whether indexes are automatically created on GROUP BY columns. Enabled by default:

[ContinuousAggregate(
    MaterializedViewName = "trade_stats",
    ParentName = nameof(Trade),
    CreateGroupIndexes = true)]

MaterializedOnly

By default, queries combine materialized data with recent unmaterialized data from the source hypertable. Set to true to return only pre-computed materialized data:

[ContinuousAggregate(
    MaterializedViewName = "trade_stats",
    ParentName = nameof(Trade),
    MaterializedOnly = true)]

Where

Apply filtering conditions to the source hypertable before aggregation:

[ContinuousAggregate(
    MaterializedViewName = "valid_sensor_readings",
    ParentName = nameof(SensorReading),
    Where = "\"temperature\" > -50 AND \"humidity\" >= 0")]

⚠️ Note: The WHERE clause should be a valid SQL expression without the "WHERE" keyword. Use double quotes for column identifiers if needed.

Complete Example

using CmdScale.EntityFrameworkCore.TimescaleDB.Abstractions;
using CmdScale.EntityFrameworkCore.TimescaleDB.Configuration.ContinuousAggregate;
using Microsoft.EntityFrameworkCore;

// Source hypertable
[Hypertable(nameof(Timestamp), ChunkTimeInterval = "7 days")]
[PrimaryKey(nameof(Ticker), nameof(Timestamp))]
public class Trade
{
    public DateTime Timestamp { get; set; }
    public string Ticker { get; set; } = string.Empty;
    public string Exchange { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Size { get; set; }
}

// Continuous aggregate with comprehensive configuration
[Keyless]
[ContinuousAggregate(
    MaterializedViewName = "trade_hourly_stats",
    ParentName = nameof(Trade),
    ChunkInterval = "30 days",
    WithNoData = false,
    CreateGroupIndexes = true,
    MaterializedOnly = false,
    Where = "\"price\" > 0 AND \"size\" > 0")]
[TimeBucket("1 hour", nameof(Trade.Timestamp), GroupBy = true)]
public class TradeHourlyAggregate
{
    [Aggregate(EAggregateFunction.Avg, nameof(Trade.Price))]
    public decimal AveragePrice { get; set; }

    [Aggregate(EAggregateFunction.Max, nameof(Trade.Price))]
    public decimal MaxPrice { get; set; }

    [Aggregate(EAggregateFunction.Min, nameof(Trade.Price))]
    public decimal MinPrice { get; set; }

    [Aggregate(EAggregateFunction.Sum, nameof(Trade.Size))]
    public decimal TotalVolume { get; set; }

    [Aggregate(EAggregateFunction.Count, "*")]
    public int TradeCount { get; set; }

    [Aggregate(EAggregateFunction.First, nameof(Trade.Price))]
    public decimal OpeningPrice { get; set; }

    [Aggregate(EAggregateFunction.Last, nameof(Trade.Price))]
    public decimal ClosingPrice { get; set; }
}

Grouping by Additional Columns

Data Annotations do not support GROUP BY configuration beyond the time bucket. For grouping by additional columns from the source hypertable, use the Fluent API approach.

Important Notes

• Continuous aggregate entities must be marked with [Keyless].
• The source entity specified in ParentName must be a TimescaleDB hypertable.
• The time bucket width determines aggregation granularity (e.g., "1 hour", "1 day", "15 minutes").
• All aggregate properties must have the [Aggregate] attribute with appropriate function and source column.
• Use nameof() to reference source entity properties for type safety.
• Refresh policies must be configured separately to keep the aggregate up-to-date.

Common Use Cases

Hourly Metrics Dashboard

Pre-compute hourly statistics for real-time dashboards without querying raw data:

[Keyless]
[ContinuousAggregate(
    MaterializedViewName = "sensor_metrics_hourly",
    ParentName = nameof(SensorReading))]
[TimeBucket("1 hour", nameof(SensorReading.Timestamp))]
public class SensorMetricsHourly
{
    [Aggregate(EAggregateFunction.Avg, nameof(SensorReading.Temperature))]
    public double AvgTemperature { get; set; }

    [Aggregate(EAggregateFunction.Max, nameof(SensorReading.Temperature))]
    public double MaxTemperature { get; set; }

    [Aggregate(EAggregateFunction.Min, nameof(SensorReading.Temperature))]
    public double MinTemperature { get; set; }
}

Daily Rollups

Create daily summaries for long-term trend analysis:

[Keyless]
[ContinuousAggregate(
    MaterializedViewName = "orders_daily",
    ParentName = nameof(OrderEvent))]
[TimeBucket("1 day", nameof(OrderEvent.OrderDate))]
public class DailySummary
{
    [Aggregate(EAggregateFunction.Sum, nameof(OrderEvent.Amount))]
    public decimal TotalRevenue { get; set; }

    [Aggregate(EAggregateFunction.Count, nameof(OrderEvent.OrderId))]
    public int OrderCount { get; set; }
}

Downsampling High-Frequency Data

Reduce storage and improve query performance for high-frequency sensor data:

[Keyless]
[ContinuousAggregate(
    MaterializedViewName = "sensor_per_minute",
    ParentName = nameof(SensorReading))]
[TimeBucket("1 minute", nameof(SensorReading.Timestamp))]
public class SensorMinute
{
    [Aggregate(EAggregateFunction.First, nameof(SensorReading.Value))]
    public double FirstValue { get; set; }

    [Aggregate(EAggregateFunction.Last, nameof(SensorReading.Value))]
    public double LastValue { get; set; }

    [Aggregate(EAggregateFunction.Avg, nameof(SensorReading.Value))]
    public double AvgValue { get; set; }
}

Weather Data Analysis

Track daily weather statistics with quality filtering:

[Keyless]
[ContinuousAggregate(
    MaterializedViewName = "weather_daily",
    ParentName = nameof(WeatherReading),
    Where = "\"temperature\" > -50 AND \"humidity\" >= 0 AND \"humidity\" <= 100")]
[TimeBucket("1 day", nameof(WeatherReading.Time))]
public class WeatherDaily
{
    [Aggregate(EAggregateFunction.Avg, nameof(WeatherReading.Temperature))]
    public double AvgTemperature { get; set; }

    [Aggregate(EAggregateFunction.Max, nameof(WeatherReading.Temperature))]
    public double MaxTemperature { get; set; }

    [Aggregate(EAggregateFunction.Min, nameof(WeatherReading.Temperature))]
    public double MinTemperature { get; set; }

    [Aggregate(EAggregateFunction.Avg, nameof(WeatherReading.Humidity))]
    public double AvgHumidity { get; set; }

    [Aggregate(EAggregateFunction.Count, "*")]
    public int ReadingCount { get; set; }
}