Continuous Aggregates

Continuous aggregates

A continuous aggregate (cagg) is a Prisma view annotated with @timescale.continuousAggregate. The generator emits a reset-safe CREATE MATERIALIZED VIEW … WITH (timescaledb.continuous) (with WITH NO DATA, idempotent), plus an optional refresh policy.

/// @timescale.continuousAggregate(source: "SensorReading", bucket: "1 hour", timeColumn: "time", refresh: { startOffset: "1 month", endOffset: "1 hour", scheduleInterval: "1 hour" })
view SensorHourly {
  bucket   DateTime /// @timescale.bucket          (exactly one)
  deviceId Int      /// @timescale.groupBy
  avgTemp  Float    /// @timescale.aggregate(fn: "avg", column: "temperature")

  @@unique([deviceId, bucket])   // Prisma 7 disallows @@id on views
}

• source — the hypertable (or another cagg) the view reads from.
• bucket — the time_bucket interval.
• timeColumn — the source's time column.
• refresh (optional) — { startOffset, endOffset, scheduleInterval } schedules an add_continuous_aggregate_policy. Omit it for manual-refresh-only.
• Field annotations: exactly one @timescale.bucket, any number of @timescale.groupBy, and one or more @timescale.aggregate(fn, column) (avg/sum/min/max/count).

Reading a continuous aggregate

A cagg is a Prisma view, so reads are ordinary, fully-typed Prisma:

const hourly = await prisma.sensorHourly.findMany({
  where: { bucket: { gte: start }, deviceId: 1 },
  orderBy: { bucket: "desc" },
});

Refreshing

Continuous aggregates are populated by their refresh policy on a schedule; to materialize new rows immediately (e.g. right after a bulk insert, or in a test), refresh on demand:

await prisma.$timescale.refreshContinuousAggregate("SensorHourly");              // full refresh
await prisma.$timescale.refreshContinuousAggregate("SensorHourly", { start, end }); // window

See $timescale management for adding/removing the refresh policy at runtime and for the concurrent-refresh (55P03) retry behavior.

Real-time aggregation (materializedOnly)

• Omit materializedOnly for TimescaleDB's default (materialized-only on 2.18+).
• Set materializedOnly: false for real-time aggregation — the view combines materialized data with not-yet-materialized recent rows from the source.

/// @timescale.continuousAggregate(source: "SensorReading", bucket: "1 hour", timeColumn: "time", materializedOnly: false)
view SensorHourly { … }

Hierarchical continuous aggregates

A cagg's source can be another continuous aggregate — roll a fine-grained cagg up into a coarser one. The outer bucket must be a whole multiple of the inner's, and the generator emits the caggs in dependency order (inner first) so migrate reset replays cleanly.

/// @timescale.continuousAggregate(source: "SensorReading", bucket: "1 hour", timeColumn: "time")
view SensorHourly {
  bucket   DateTime /// @timescale.bucket
  deviceId Int      /// @timescale.groupBy
  avgTemp  Float    /// @timescale.aggregate(fn: "avg", column: "temperature")
  @@unique([deviceId, bucket])
}

/// Roll the hourly cagg up into a daily one (source is SensorHourly, not the hypertable):
/// @timescale.continuousAggregate(source: "SensorHourly", bucket: "1 day", timeColumn: "bucket")
view SensorDaily {
  day      DateTime /// @timescale.bucket
  deviceId Int      /// @timescale.groupBy
  avgTemp  Float    /// @timescale.aggregate(fn: "avg", column: "avgTemp")
  @@unique([deviceId, day])
}