---
title: "Continuous Aggregate Refresh, Demystified: Invalidation, Lookback, and Late-Arriving Data"
published: 2026-08-14T08:00:28.000-04:00
updated: 2026-08-14T08:00:28.000-04:00
excerpt: "TimescaleDB continuous aggregates and late-arriving data: invalidation tracking, lookback windows, and the two offsets that prevent stale aggregate buckets."
tags: Continuous Aggregates, IoT
authors: Damaso Sanoja
---

> **TimescaleDB is now Tiger Data.**

Yesterday the shift dashboard reported 41,900 units across an eight-hour window. Today the same query over the same eight hours reports 43,100. Nobody shipped a code change and nobody edited a row after the fact, so why did the number move? A plant-floor historian had lost its uplink, buffered locally, and flushed overnight, landing rows that carry their original timestamps. The raw table was correct at every point along the way. The pre-computed aggregate behind the dashboard never went back for those rows, so it published one number and later replaced it with another.

Every pre-computation strategy handles a fresh row arriving at the head of the table. They diverge on the two events no evaluation demo covers: data that arrives late for a window already computed, and data that changes after the fact. This piece runs the four common strategies against exactly those events to show why each behaves the way it does, then ends where the choice gets practical: the two offsets that decide, on a TimescaleDB continuous aggregate, whether a late row is ever reconciled at all.

## The four ways to pre-compute a time-series aggregate

The industry files all of these under one label, "materialized view refresh," and the label is where the trouble starts: it implies one correctness story where there are four. Pre-computation is a bet about _when the work happens_: at write time, at schedule time, or at query time. Four designs place that bet differently.

### 1\. Scheduled full recompute

A materialized view plus cron. In vanilla Postgres, [REFRESH MATERIALIZED VIEW](https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html) rebuilds the view from scratch on every run, with no incremental path, so between runs the view is stale by construction. You pay the full rebuild every run, whether one row changed or a million did. That stays affordable exactly as long as a rebuild of the range you care about fits inside the schedule interval.

For a daily rollup over a month of data on modest hardware, it fits for a long time. For a fine-grained rollup over years of raw samples, it stops fitting well before anyone notices, because the failure arrives as a slow schedule slip and not as an error. [InfluxDB v2 tasks](https://docs.influxdata.com/influxdb/v2/process-data/common-tasks/downsample-data/) have the same shape: a scheduled Flux task reads a window of raw data and writes downsampled results, and the documented lever for lateness is an offset that delays the run rather than an invalidation layer that reaches back after it.

### 2\. Insert-triggered incremental views

[ClickHouse standard materialized views](https://clickhouse.com/docs/materialized-view/incremental-materialized-view) fire on INSERT into the source table and write the incremental result forward. Each insert carries a tiny increment, which is what makes the design attractive at ingest rates where a recompute is out of the question. It rests on a strong premise: the past never changes. Nothing in the standard view reacts to a backfill or a correction, so reconciling history becomes a manual, resource-heavy operation.

The same system offers an in-family alternative, [refreshable materialized views](https://clickhouse.com/docs/materialized-view/refreshable-materialized-view), which re-run the full query on a schedule and atomically swap the destination table. That is strategy 1 again, reached for because strategy 2's premise broke.

### 3\. Streaming dataflow engines

RisingWave and Materialize maintain a view through a [dataflow graph updated on every source change](https://risingwave.com/blog/incremental-materialized-views-complete-guide/), targeting sub-second freshness. Late data is another change event entering the graph, so there is no refresh window to reason about at all.

The tradeoff here is architectural rather than computational: a persistent streaming system running alongside your database, carrying its own consistency model and its own operational surface. If the freshness contract you have to meet is measured in seconds and your team already runs that layer comfortably, this is a defensible choice, and the rest of this piece is not an argument against it.

### 4\. Invalidation-tracked window refresh

This is what a [continuous aggregate](https://www.tigerdata.com/docs/learn/continuous-aggregates) in TimescaleDB does, and if you have not run one, the plain version is this: **you declare a query with a time bucket and a `GROUP BY`, TimescaleDB stores its results in a table it maintains for you, and a scheduled policy keeps that table current**.

The bookkeeping in between is what makes it a distinct strategy. Writes that touch already-summarized time ranges are recorded in an invalidation log, and each scheduled run re-examines a bounded window of time, recomputing only the buckets inside that window that saw activity. The work stays small and stays inside the database you already run. _In exchange, someone has to pick the right width for that window_.

Four strategies, each defensible for the workload it was designed around. What decides which one fits yours is what your data does after it first arrives.

## The test: new data, late data, changed data

Here is how the four designs answer the three events:

|  |  |  |  |  |
| --- | --- | --- | --- | --- |
| Strategy | New data at the head | Late data for a closed window | UPDATE / DELETE on aggregated rows | What it costs |
| 1. Scheduled full recompute | At next run | At next run | At next run | Full recompute, every run, whether or not anything changed |
| 2. Insert-triggered incremental | Instant | Never | Never | Tiny increment per insert |
| 3. Streaming dataflow | Instant | Instant | Instant | Continuous, in a second system you also operate |
| 4. Invalidation-tracked window refresh | At next run | At next run if inside the window, never if outside | Same rule as late data | Recompute of the window only, and only the buckets that changed |

_Four strategies against the three things that happen to time-series data._

### New data unites them; late data divides them

**New data is where they all look alike.** A row arrives at the head of the table with a current timestamp, and every one of the four picks it up on its own, with nobody intervening. What separates them here is only how long that takes: milliseconds for the two that work continuously, and up to a full schedule interval for the two that work on a timer. That is a freshness difference, and freshness is a dial you already know how to set. It is the only difference this regime exposes. Every evaluation runs here, which is exactly why the differences that matter stay hidden through the evaluation and surface in production six weeks later.

**Late data splits them.** Scheduled full recompute is right by construction: the next run recomputes everything in range without caring when any of it arrived. That correctness is prepaid on every run, including the runs where nothing was late.

An **insert-triggered view** sits at the opposite corner. The late `INSERT` does fire the trigger, but the view has no way to reach back into an aggregate it already wrote for an older timestamp, so historical corrections require a separate, resource-heavy refresh. The design traded that reach away deliberately in exchange for per-insert cheapness.

**Streaming dataflow** has no boundary to miss, because it never drew one. A late event is a change like any other, and the graph updates. What looks like a free win here is a cost moved rather than removed: the engine pays continuously to stay in a position where late data is unremarkable.

**Changed data widens the split.** An `UPDATE` or `DELETE` against rows that are already summarized is the case an insert-triggered view has no answer to at all, because no insert happened. The aggregate diverges silently until someone triggers a full refresh, and the divergence carries no timestamp to grep for. Full recompute absorbs the change for free, having never trusted its own previous output. Streaming dataflow propagates a retraction through the graph. TimescaleDB [logs UPDATE and DELETE activity as invalidations exactly the way it logs INSERTs](https://www.tigerdata.com/docs/use-timescale/latest/continuous-aggregates/about-continuous-aggregates), so a correction to an old row inherits the same boundary condition as a late insert: inside the window it is fixed, outside the window it is not.

_TimescaleDB's is worth looking at in depth, because it sits between those corners. It uses invalidation-tracked window refresh because late data is unbounded in theory and bounded in practice. Recording which buckets a write disturbed costs almost nothing. Re-examining a window wide enough to cover the lateness you actually see costs far less than recomputing all of history to catch it, and it happens inside the database you are already running. The bet is that your late-data envelope is something you can put a number on. What follows is the machinery that reads that number, and the two offsets you write it into._

## Window refresh in detail

The bookkeeping starts before any refresh runs. TimescaleDB maintains an invalidation threshold, also called the materialization watermark: a time cutoff behind the hot head of the table. Mutations landing before it are logged as invalidations, because that region has already been summarized and the summary is now suspect. Mutations landing after it need no bookkeeping at all, because nothing has been materialized there yet. The granularity of that log is worth knowing, because it explains why a bulk backfill behaves differently from a trickle of corrections: [each transaction logs the minimum and maximum timestamps of the rows it modified](https://www.tigerdata.com/docs/learn/continuous-aggregates#invalidation-engine), so one transaction spanning a wide range marks every bucket between its endpoints.

A write whose timestamp lands in an already-materialized bucket _is_ recorded in the invalidation log. Whether it ever gets re-materialized depends on the [refresh policy's window](https://www.tigerdata.com/docs/use-timescale/latest/continuous-aggregates/refresh-policies). If that bucket falls inside the window when the policy next runs, the bucket is recomputed and the number corrects itself. If it falls outside, the policy never revisits it, and the aggregate keeps reporting the pre-arrival value indefinitely. TimescaleDB's own [issue tracker](https://github.com/timescale/timescaledb/issues/6548) carries a user report of exactly this boundary condition.

The aggregate query still returns, still returns fast, and still returns a plausible number. Nothing in a default policy setup raises "this bucket is stale relative to a write that landed after I last looked at it." The overnight dashboard shift in the opening is that branch resolving the slow way, when a human eventually re-ran something wide enough to sweep the bucket in.

## Creating a continuous aggregate in TimescaleDB

Start with where the numbers live.

A continuous aggregate is a materialized view backed by [its own hypertable](https://www.tigerdata.com/docs/use-timescale/latest/continuous-aggregates/about-continuous-aggregates), holding one row per `GROUP BY` bucket plus a column per aggregate. Here is the canonical shape:

```SQL
CREATE MATERIALIZED VIEW conditions_summary_hourly
WITH (timescaledb.continuous) AS
SELECT
    device,
    time_bucket(INTERVAL '1 hour', time) AS bucket,
    avg(temperature) AS avg_temp,
    max(temperature) AS max_temp,
    min(temperature) AS min_temp
FROM conditions
GROUP BY device, bucket
WITH NO DATA;
```

Two parts of that statement are doing the declaring. `WITH (timescaledb.continuous)` is what makes this a continuous aggregate rather than an ordinary materialized view. The `time_bucket` call in the`GROUP BY` is what the continuous machinery then requires, because the bucket is the unit of work it invalidates and recomputes.

`WITH NO DATA` is a separate decision, and worth being precise about, since it is not the default. It controls the initial backfill at creation time and nothing else. The default, `WITH DATA`, computes every historical bucket the moment you run the statement, which on a large hypertable is a long blocking build at the worst possible time. `WITH NO DATA` creates the structure empty and hands the filling to the refresh policy you attach next, plus a manual `refresh_continuous_aggregate` call for whatever history you want backfilled deliberately. The pre-computation still happens either way. You are choosing when to pay for it.

A refresh run, whether triggered by the policy or by a manual call, is [two transactions](https://www.tigerdata.com/docs/learn/continuous-aggregates#materialization-engine) rather than one: the first briefly blocks writes while it determines the range to materialize and advances the threshold, and the second materializes without blocking writers, so a wide refresh does not hold one long lock.

![One refresh cycle. The two offsets are the two edges of the window in the middle box, and everything about staleness is decided by which side of them a bucket falls on.](https://assets.tigerdata.com/blog/2026/08/Creating-continuousaggregate.svg)

__One refresh cycle. The two offsets are the two edges of the window in the middle box, and everything about staleness is decided by which side of them a bucket falls on.__

Which brings the design down to two numbers you set on the policy:

```SQL
SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
    start_offset      => INTERVAL '48 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');
```

Each run refreshes the range from `now() - start_offset` to `now() - end_offset`. So [start\_offset is the lookback window](https://www.tigerdata.com/docs/build/continuous-aggregates/refresh-policies), the distance back in time the policy is willing to reconsider, and `end_offset` is a deliberate freshness floor that holds the newest, still-filling bucket out of the refresh so it is not recomputed on every run while data is still landing in it. With one-hour buckets, an `end_offset` of one hour is the smallest value that does that job.

That floor has a consequence worth handing to whoever consumes the dashboard. Worst-case publication delay is roughly `schedule_interval + end_offset`: a row lands just after a run finishes, waits a full interval for the next one, and is still held back by `end_offset` when that one goes. With the values above the composite runs to about ninety minutes, triple the schedule interval taken on its own. Hand downstream consumers the composite, because either parameter alone understates what they will see.

**Two things widen the envelope you are sizing for.** [**Hierarchical continuous aggregates**](https://www.tigerdata.com/docs/learn/continuous-aggregates/hierarchical-continuous-aggregates) **are built on top of other continuous aggregates, and each tier runs its own refresh policy against the tier below, so a correction lands at the top only after every tier's schedule has fired in turn, later than the top policy alone suggests. And a retention policy that drops raw chunks still inside your** `start_offset` **removes the source rows a late correction would have been reconciled from, so size those two against each other deliberately.**

**One feature looks like it closes the gap, and it does not.** Real-time aggregation unions the materialized buckets with a live query over the tail that has not been materialized yet, so a query can read current even between policy runs. It is genuinely useful, but it solves a different problem: it covers data _newer than the watermark_. A late write carrying an old timestamp lands in a bucket that is already behind the watermark, and if that bucket is outside every policy window, no amount of real-time aggregation surfaces it, because the live query is not looking there. The two features solve adjacent problems, and it is easy to assume the first one closed the second.

```
-- Real-time aggregation, if you want the un-materialized tail included in reads.
ALTER MATERIALIZED VIEW conditions_summary_hourly
    SET (timescaledb.materialized_only = false);
```

Note the default flipped: [in TimescaleDB v2.13 and later, real-time aggregates are disabled by default](https://www.tigerdata.com/docs/learn/continuous-aggregates), where earlier versions enabled them. A window sized correctly still tells you nothing about whether the policy that reads it is running.

## Checking that the policy is doing its job

TimescaleDB already records what you need to track the calculations. Each job's [`last_successful_finish`](https://www.tigerdata.com/docs/reference/timescaledb/informational-views/job_stats#returns), compared against its own `schedule_interval`, tells you whether it is keeping up. The extra join is there because `job_stats` reports the internal materialization hypertable rather than the name you query:

```SQL
SELECT ca.view_name,
       j.schedule_interval,
       js.last_successful_finish,
       now() - js.last_successful_finish AS since_last_success,
       js.last_run_status,
       js.total_failures
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats js
  ON j.job_id = js.job_id
JOIN timescaledb_information.continuous_aggregates ca
  ON ca.materialization_hypertable_name = js.hypertable_name
WHERE j.proc_name = 'policy_refresh_continuous_aggregate';
```

Audit your own policies this week: for each continuous aggregate, compare how far back your writes actually arrive against the `start_offset` that policy covers. Where the arrivals run wider than the window, that aggregate is already publishing numbers that will never correct themselves, and widening `start_offset` is the cheapest thing you can do about it. The [refresh-policies documentation](https://www.tigerdata.com/docs/build/continuous-aggregates/refresh-policies) carries the parameter reference, and [Tiger Cloud](https://www.tigerdata.com/cloud) is one place to try a wider window against a copy of your own data before you touch production.