Alerting on Continuous Aggregate Refresh Lag

A continuous aggregate that stops materializing rarely throws an error — its background job simply falls behind or stalls while dashboards keep serving stale rollups, so the failure is invisible until someone notices yesterday’s numbers on a “live” panel. Refresh lag makes that silence measurable: it is the distance between now() and the aggregate’s materialization watermark, and this page turns it into a deterministic alert threshold, the exact catalog query that reads the watermark per view, and a psycopg collector you can wire to your metrics pipeline.

The watermark is the timestamp up to which the aggregate is guaranteed materialized; everything after it is either missing or served on the fly. A healthy job keeps the watermark trailing now() by a small, bounded amount. When lag drifts past a threshold you derived from the refresh cadence, materialization has stopped keeping up — and that is the signal worth paging on.

Input Profiling: Metrics to Gather First

Refresh lag is only meaningful relative to how fast the aggregate is supposed to update. Before setting a threshold, profile four values per aggregate — three describe the refresh mechanics, one describes what your consumers can tolerate:

Bucket width — the time_bucket interval of the aggregate (for example 1 hour). The current bucket cannot be finalized until it closes, so the watermark always sits at least one bucket behind wall-clock time.
Schedule interval — the schedule_interval of the refresh policy. This is how long the watermark can legitimately drift between two successful runs.
Ingestion settling time — how long after a bucket closes you wait for stragglers before materializing it, expressed as the policy’s end_offset. Fleets with gateway buffering or reconnecting devices need a larger settling window than clean, real-time streams.
Dashboard SLA — the freshness your consumers actually require. A capacity-planning panel tolerates a day of lag; an operational alerting view might tolerate ten minutes. The threshold you page on must sit below this number.

The first three inputs come straight from the aggregate’s definition and its refresh policy design and scheduling. The fourth is a product decision. Together they bound both the healthy range of lag and the unacceptable range, and the gap between them is where your alert threshold lives.

Deriving a Deterministic Alert Threshold

Under healthy operation the watermark oscillates within a predictable band. Immediately after a successful refresh it sits at roughly now() − (bucket_width + settling), because the policy deliberately leaves the still-open bucket and the settling window unmaterialized. It then drifts forward until the next scheduled run, adding up to one full schedule_interval. The largest lag a healthy aggregate ever shows is therefore the sum of those three terms; add a margin for job-runtime jitter and worker contention and you have the alert threshold:

L_{alert} = w_{bucket} + t_{sched} + t_{settle} + m

where $w_{bucket}$ is the bucket width, $t_{sched}$ is the schedule interval, $t_{settle}$ is the ingestion settling window (end_offset beyond one bucket), and $m$ is a safety margin, typically a fraction of $t_{sched}$ . Any lag below $L_{alert}$ is indistinguishable from a job that simply hasn’t run since the last bucket closed; any lag above it means a run was skipped, stalled, or failing. Because every term is a fixed property of the aggregate, the threshold is deterministic — you never tune it by watching a graph.

Measuring Lag From the Watermark

TimescaleDB exposes the watermark as an internal integer through _timescaledb_internal.cagg_watermark(mat_hypertable_id), keyed by the materialization hypertable’s id. _timescaledb_internal.to_timestamp converts that integer to a timestamptz, and joining timescaledb_information.continuous_aggregates to the catalog gives you the id per view. Wrap it in a monitoring view so every collector and verification query reads lag the same way:

sql

-- Per-aggregate refresh lag. On TimescaleDB 2.12+ the helper functions live in
-- the _timescaledb_functions schema; swap the schema prefix if the calls error.
CREATE OR REPLACE VIEW cagg_refresh_lag AS
SELECT
    ca.view_schema,
    ca.view_name,
    _timescaledb_internal.to_timestamp(
        _timescaledb_internal.cagg_watermark(cagg.mat_hypertable_id)
    )                                                     AS watermark,
    now() - _timescaledb_internal.to_timestamp(
        _timescaledb_internal.cagg_watermark(cagg.mat_hypertable_id)
    )                                                     AS refresh_lag
FROM timescaledb_information.continuous_aggregates ca
JOIN _timescaledb_catalog.continuous_agg cagg
  ON  cagg.user_view_schema = ca.view_schema
  AND cagg.user_view_name   = ca.view_name;

cagg_watermark returns the raw internal storage value of the aggregate’s time dimension. For the timestamptz buckets used by IoT rollups, to_timestamp decodes it correctly; for aggregates bucketed on an integer time column it returns that integer verbatim, so keep the view scoped to time-based aggregates. Reading the view is now a single select:

sql

SELECT view_name, watermark, refresh_lag
FROM cagg_refresh_lag
ORDER BY refresh_lag DESC;

Collecting lag per view in Python

A collector reads the view, compares each aggregate against its deterministic threshold, and emits one sample per view — ready to push to a time-series backend or scrape endpoint. Thresholds come from the formula above, held per view so a daily rollup and an hourly rollup are judged on their own cadence. The snippet uses psycopg v3:

python

import psycopg
from psycopg.rows import dict_row

# Deterministic thresholds in seconds, computed from L_alert per aggregate.
LAG_THRESHOLDS_S: dict[str, int] = {
    "device_metrics_hourly": 3 * 3600,   # 1h buckets, hourly schedule
    "device_metrics_daily": 30 * 3600,   # 1d buckets, 6h schedule
}
DEFAULT_THRESHOLD_S = 3 * 3600  # fallback for un-profiled aggregates


def collect_refresh_lag(conn_str: str) -> list[dict]:
    """Return one lag sample per continuous aggregate, with a firing flag."""
    with psycopg.connect(conn_str, row_factory=dict_row) as conn:
        rows = conn.execute(
            """
            SELECT view_name,
                   EXTRACT(EPOCH FROM refresh_lag)::float AS lag_seconds
            FROM cagg_refresh_lag;
            """
        ).fetchall()

    samples: list[dict] = []
    for row in rows:
        threshold = LAG_THRESHOLDS_S.get(row["view_name"], DEFAULT_THRESHOLD_S)
        samples.append(
            {
                "view": row["view_name"],
                "lag_seconds": row["lag_seconds"],
                "threshold_seconds": threshold,
                "firing": row["lag_seconds"] > threshold,
            }
        )
    return samples

Each sample carries the raw lag, its threshold, and the boolean decision, so an exporter can publish both the gauge and the alert state. Feeding lag_seconds into a Prometheus gauge is the natural next step — see exporting TimescaleDB job metrics to Prometheus for the registry and endpoint wiring.

Worked Example

Take an hourly rollup over device telemetry: 1-hour buckets, an hourly schedule (schedule_interval => INTERVAL '1 hour'), and a 15-minute settling window to absorb late gateway flushes (end_offset => INTERVAL '1 hour 15 minutes', i.e. one closed bucket plus 15 minutes). Choose a margin of 30 minutes — half the schedule interval — for run jitter:

L_{alert} = 60 + 60 + 15 + 30 = 165 \text{ min} \approx 2.75 \text{ h}

Round to a clean 3 hours. Sanity-check the band: right after a successful refresh the watermark sits about 60 + 15 = 75 minutes behind now(); just before the next hourly run it has drifted another 60 minutes, to about 135 minutes. Healthy lag therefore tops out near 135 minutes, comfortably under the 165-minute boundary, so a job merely between runs never fires. Only a skipped or stalled run pushes lag past 3 hours. That 3-hour threshold becomes 3 * 3600 in the collector’s LAG_THRESHOLDS_S map, and the alert reads directly off the watermark:

sql

SELECT view_name, refresh_lag,
       refresh_lag > INTERVAL '3 hours' AS alert_firing
FROM cagg_refresh_lag
WHERE view_name = 'device_metrics_hourly';

Edge Cases & When to Deviate

The threshold assumes a forward-advancing watermark and a steady schedule. Three situations break that assumption:

Late-arriving data. Rows written with timestamps behind the watermark trigger invalidation and get reprocessed on the next refresh, but they never move the now()-relative watermark backward — so lag alerting will not catch data you missed because your settling window was too short. That is a completeness problem, tracked through the invalidation log, not through lag. If you widen end_offset to absorb more late data, raise $t_{settle}$ in the formula so the healthy baseline still sits below the threshold.
Real-time aggregates masking lag. When an aggregate is defined with materialized_only = false, queries union the materialized rows with a live sub-query over raw data past the watermark. Dashboards keep returning current numbers even after materialization has completely stalled, so any freshness check that inspects query output stays green while the watermark is hours stale. This is precisely why you monitor the watermark directly rather than the query result — the two diverge exactly when it matters. The trade-off is covered in real-time aggregates vs manual refresh.
Paused or failing jobs. A refresh policy with scheduled => false never advances the watermark, so lag climbs linearly and unbounded — the cleanest possible alert signal. A job that errors on every run behaves the same way, since a failed refresh leaves the watermark where it was. When lag fires, correlate it with job history, and start from troubleshooting stale continuous aggregates in production to find the root cause.

Verification

To prove the alert fires and clears, drive the watermark manually. First confirm the baseline is clear, then pause the refresh job and let time pass so the watermark falls behind:

sql

-- 1. Baseline: alert_firing should be false on a healthy aggregate.
SELECT view_name, refresh_lag,
       refresh_lag > INTERVAL '3 hours' AS alert_firing
FROM cagg_refresh_lag
WHERE view_name = 'device_metrics_hourly';

-- 2. Pause the refresh job so the watermark stops advancing.
SELECT alter_job(job_id, scheduled => false)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_refresh_continuous_aggregate'
  AND hypertable_name = (
    SELECT materialization_hypertable_name
    FROM timescaledb_information.continuous_aggregates
    WHERE view_name = 'device_metrics_hourly'
  );

Once wall-clock time has advanced past the 3-hour boundary with the job paused, re-running the baseline query returns alert_firing = true. To clear it, materialize the gap and resume the schedule — the watermark jumps forward to now() − end_offset and lag collapses back inside the healthy band:

sql

-- 3. Materialize the outstanding range, then resume the policy.
CALL refresh_continuous_aggregate(
    'device_metrics_hourly', now() - INTERVAL '1 day', now()
);

SELECT alter_job(job_id, scheduled => true)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_refresh_continuous_aggregate'
  AND hypertable_name = (
    SELECT materialization_hypertable_name
    FROM timescaledb_information.continuous_aggregates
    WHERE view_name = 'device_metrics_hourly'
  );

-- 4. Re-run the baseline: alert_firing is false again.
SELECT view_name, refresh_lag,
       refresh_lag > INTERVAL '3 hours' AS alert_firing
FROM cagg_refresh_lag
WHERE view_name = 'device_metrics_hourly';

The transition from false to true to false — driven entirely by the watermark and never by query output — confirms the alert responds to real materialization state. Wire step 4’s boolean into your pager, keep the collector scraping cagg_refresh_lag, and a silently stalled aggregate becomes a page instead of a support ticket.

Refresh Policy Design & Scheduling — where bucket width, schedule interval, and settling window are set
Troubleshooting Stale Continuous Aggregates in Production — root-causing a job that has fallen behind
Real-Time Aggregates vs Manual Refresh Tradeoffs — why live-unioned views hide a stale watermark
Exporting TimescaleDB Job Metrics to Prometheus — publishing the lag gauge to your metrics backend
Up to Monitoring, Observability & Alerting for TimescaleDB Automation

← Back to Job Stats & Scheduler Diagnostics

Alerting on Continuous Aggregate Refresh Lag

# Input Profiling: Metrics to Gather First

# Deriving a Deterministic Alert Threshold

# Measuring Lag From the Watermark

# Collecting lag per view in Python

# Worked Example

# Edge Cases & When to Deviate

# Verification

# Related