Incremental Refresh Performance Tuning for Large Datasets

When a continuous aggregate covers billions of rows, the single question that decides whether it stays fresh is deterministic: does each incremental refresh finish before the next one is due? This guide gives time-series data engineers and IoT platform developers a formula that predicts refresh duration from ingestion rate and materialization throughput, then shows how to set schedule_interval, start_offset, and end_offset so the refresh queue never falls behind. It narrows the broader asynchronous execution and queue management topic down to one calculation: sizing a single aggregate’s refresh so its background worker keeps pace with the raw hypertable.

An incremental refresh advances a watermark; only data past it is recomputed each cycle.

An incremental refresh does not re-scan the whole aggregate. TimescaleDB tracks a watermark per continuous aggregate and an invalidation log that records which time ranges changed since the last run. Each cycle materializes only the invalidated region between start_offset and end_offset. Performance tuning is therefore the art of keeping that invalidated region small and the throughput that drains it high.

# Input Profiling — Numbers to Gather First

The formula below needs five measured inputs. Collect them from your running instance before changing any policy parameter; guessing produces either a starved queue or a refresh that scans far more than it needs to.

• Ingestion rate r — rows per second landing in the time span the aggregate covers. Derive it from SELECT count(*) over a recent one-minute window on the raw hypertable, not from the device count on paper.
• Materialization throughput m — rows per second the refresh job actually processes. Compute it from timescaledb_information.job_stats as rows touched divided by run duration (query in the Verification section).
• Late-arrival volume L — rows per cycle that land in already-materialized buckets, forcing re-computation. High on IoT fleets with gateway reconnects, clock skew, or store-and-forward buffers.
• Current schedule_interval — how often the policy fires, from timescaledb_information.jobs.
• Chunk alignment — the raw hypertable’s chunk_time_interval relative to the aggregate’s time_bucket width. Misalignment multiplies the chunks each refresh must lock. Size it with optimal chunk_interval for IoT sensor data.

# The Stability Formula

In steady state each refresh cycle scans the new data that arrived since the last run, plus the late rows that re-invalidated older buckets:

$$R_{scan} = r \cdot t_{sched} + L$$

Divide by measured throughput to get refresh duration, and require it to fit inside one scheduling interval so cycles do not overlap and queue up:

$$D = \frac{R_{scan}}{m} = \frac{r \cdot t_{sched} + L}{m} < t_{sched}$$

Solving for the interval yields the single most useful result for tuning:

$$t_{sched}^{\,min} = \frac{L}{m - r}, \qquad \text{stable only if } m > r$$

Two consequences fall out immediately. First, if materialization throughput m does not exceed ingestion rate r, no schedule_interval is stable — the aggregate falls permanently behind and you must raise throughput (more work_mem, better index coverage, fewer aggregated columns) or reduce r into the window (coarser time_bucket). Second, once m > r, choosing an interval comfortably above t_sched^min (aim for a duty cycle D / t_sched under 0.6) leaves headroom for ingestion spikes and shares the worker pool with compression and retention jobs.

The offsets shape L and protect the hot edge:

A compact planner that reads the live inputs and returns the minimum stable interval keeps this out of a spreadsheet:

import psycopg
from psycopg.rows import dict_row

def min_schedule_interval(dsn: str, view_name: str, late_rows_per_cycle: int) -> dict:
    """Return the minimum stable schedule_interval (seconds) for one aggregate."""
    q = """
        SELECT js.total_successes,
               EXTRACT(epoch FROM js.total_duration) AS dur_s
        FROM timescaledb_information.continuous_aggregates ca
        JOIN timescaledb_information.jobs j
          ON j.hypertable_name = ca.materialization_hypertable_name
         AND j.proc_name = 'policy_refresh_continuous_aggregate'
        JOIN timescaledb_information.job_stats js ON js.job_id = j.job_id
        WHERE ca.view_name = %s;
    """
    with psycopg.connect(dsn, row_factory=dict_row) as conn:
        s = conn.execute(q, (view_name,)).fetchone()
        ingest = conn.execute(
            "SELECT count(*)::float / 60 AS r FROM %s "
            "WHERE time > now() - interval '1 minute'" % view_name.replace("_rollup", "_raw")
        ).fetchone()["r"]

    # rows processed per successful run, divided by avg run duration -> throughput m
    avg_dur = (s["dur_s"] / s["total_successes"]) if s["total_successes"] else None
    m = (late_rows_per_cycle / avg_dur) if avg_dur else 0.0  # replace with rows/run metric
    if m <= ingest:
        return {"stable": False, "reason": "throughput m <= ingest r; raise work_mem or coarsen bucket"}
    t_min = late_rows_per_cycle / (m - ingest)
    return {"stable": True, "t_sched_min_s": round(t_min, 1), "ingest_r": ingest, "throughput_m": m}

Treat the returned t_sched_min_s as a floor and register the policy through a refresh policy design and scheduling definition well above it.

# Worked Example — a 50,000-Device Fleet

A telemetry platform ingests one reading every 10 seconds from 50,000 devices into a raw hypertable, feeding a one-minute rollup aggregate. The measured inputs:

• Ingestion rate: r = 50000 / 10 = 5,000 rows/s into the aggregate window.
• Measured throughput from job_stats: m = 40,000 rows/s.
• Late arrivals: a fleet of edge gateways reconnects on a rolling schedule and flushes buffered data landing in older one-minute buckets, L ≈ 1,000,000 rows per cycle.

The minimum stable interval:

$$t_{sched}^{\,min} = \frac{1{,}000{,}000}{40{,}000 - 5{,}000} = \frac{1{,}000{,}000}{35{,}000} \approx 28.6 \text{ s}$$

Set schedule_interval => '60 seconds' for margin. At 60 s the scan is R_scan = 5,000 · 60 + 1,000,000 = 1.30M rows, giving D = 1.30M / 40,000 ≈ 32.5 s — a duty cycle of 0.54, leaving the worker idle enough to absorb spikes and yield slots to other jobs. Set end_offset => '2 minutes' (twice the bucket width) so the newest, still-mutating buckets are not re-materialized every minute, and start_offset => '1 hour' to bound how far back the gateway flushes are honoured. The resulting policy:

SELECT add_continuous_aggregate_policy('sensor_1min_rollup',
    start_offset      => INTERVAL '1 hour',
    end_offset        => INTERVAL '2 minutes',
    schedule_interval => INTERVAL '60 seconds');

Had throughput measured m = 4,000 rows/s instead (below r), the formula returns no stable interval — the correct fix is not a larger interval but reducing rows in the window, for example by widening time_bucket to five minutes or moving cold raw chunks under columnar compression models before they enter the refresh path.

# Edge Cases & When to Deviate

• Late data beyond start_offset — arrivals older than the offset are silently dropped from the aggregate. If gateways can flush data hours late, widen start_offset and accept the larger L, or run a targeted CALL refresh_continuous_aggregate(agg, start, end) for the affected range instead of inflating every cycle.
• Backfills and bulk loads — a one-time historical load spikes L far above steady state. Do not size the policy for it; run an explicit windowed refresh once, then let the policy resume at the steady-state interval. See incremental vs full refresh strategies.
• Real-time aggregates — with materialized_only = false, every query unions the un-materialized tail at read time, so query latency, not refresh duration, becomes the constraint. Enforce true in production.
• Chunk misalignment — if the raw chunk_time_interval is far larger than the invalidated window, each refresh locks whole chunks regardless of R_scan, and the formula understates cost. Realign chunks before trusting t_sched^min.
• Shared worker pool — t_sched^min assumes a worker is free when the job fires. If compression and retention jobs saturate timescaledb.max_background_workers, refreshes wait in the queue and effective duty cycle rises; stagger policies with initial_start offsets.
• Hierarchical rollups — a daily aggregate built on the one-minute aggregate inherits its lag. Schedule the coarser tier to fire after the finer tier’s duty-cycle window, not concurrently.

# Verification

Confirm the applied policy behaves as calculated by comparing average measured refresh duration against the configured interval. A duty cycle approaching or exceeding 1.0 means cycles are overlapping and the queue is backing up:

SELECT ca.view_name,
       (j.config ->> 'schedule_interval')                        AS sched_interval,
       js.total_successes,
       (js.total_duration / NULLIF(js.total_successes, 0))       AS avg_refresh_duration,
       round(EXTRACT(epoch FROM js.total_duration / NULLIF(js.total_successes,0))
             / EXTRACT(epoch FROM (j.schedule_interval)), 2)     AS duty_cycle,
       js.last_run_status,
       js.last_successful_finish
FROM timescaledb_information.continuous_aggregates ca
JOIN timescaledb_information.jobs j
  ON j.hypertable_name = ca.materialization_hypertable_name
 AND j.proc_name = 'policy_refresh_continuous_aggregate'
JOIN timescaledb_information.job_stats js ON js.job_id = j.job_id
ORDER BY duty_cycle DESC NULLS LAST;

Then confirm the watermark is tracking the raw tail — a widening gap means throughput is losing to ingestion regardless of duty cycle, and diagnostics belong with troubleshooting stale continuous aggregates in production:

SELECT ca.view_name,
       now() - _timescaledb_internal.to_timestamp(
                 _timescaledb_internal.cagg_watermark(ca.mat_hypertable_id)) AS watermark_lag
FROM _timescaledb_catalog.continuous_agg ca_cat
JOIN timescaledb_information.continuous_aggregates ca
  ON ca.view_name = ca_cat.user_view_name;

If watermark_lag stays within one or two schedule_interval widths, the aggregate is keeping pace and the tuning holds. If it climbs monotonically, revisit m: raise work_mem for the aggregation sort, add partial indexes on the grouped dimensions, or coarsen the bucket to cut r.

# Related

• Asynchronous Execution & Queue Management — sizing the background-worker pool that runs these refreshes
• Refresh Policy Design & Scheduling — registering and staggering the policy this formula sizes
• Incremental vs Full Refresh Strategies — when a targeted full refresh beats the incremental path
• Setting Up Automatic Refresh Policies for 5-Minute Intervals — a concrete cadence walkthrough
• Troubleshooting Stale Continuous Aggregates in Production — diagnosing watermark lag when tuning is not enough

← Up to Asynchronous Execution & Queue Management · Continuous Aggregate Creation & Refresh Management