Incremental vs Full Refresh Strategies

Choosing how a continuous aggregate re-materializes its results is the single decision that most directly controls query latency, background worker load, and storage churn in a TimescaleDB analytics tier. An incremental refresh touches only the time ranges that changed since the last run; a full refresh recomputes an entire span from the raw hypertable. Pick the wrong one and you either serve stale dashboards to your IoT operators or you saturate the background worker pool recomputing hours of telemetry that never changed. This guide frames the mechanism, walks through implementing both strategies against a sensor pipeline, and gives you the system-view queries to prove which one is actually running. It sits inside the broader continuous aggregate refresh lifecycle, and assumes you already have a materialized view in place.

The mechanism that makes incremental refresh possible is the materialization watermark — an internal marker, stored per aggregate in the TimescaleDB catalog, that records the boundary between data already summarized and raw rows still awaiting aggregation. Every refresh either advances that watermark (incremental) or ignores it and rewrites a fixed range from scratch (full). The rest of this page is organized around that distinction: when the watermark is your friend, and when you must deliberately step around it.

# Prerequisites

Before either strategy will behave predictably, confirm the following against your target database. All examples target TimescaleDB 2.10+ on PostgreSQL 14+.

• The TimescaleDB extension is installed and background workers are enabled (timescaledb.max_background_workers ≥ 8 for a mixed refresh/compression/retention workload).
• The source table is a hypertable with a defined chunk_time_interval — see time-based chunk partitioning strategies if you have not sized chunks yet.
• The continuous aggregate already exists, created WITH (timescaledb.continuous); if not, start from materialized view architecture and syntax.
• The aggregation query uses time_bucket on the hypertable’s time column and contains no non-immutable expressions, window functions, or DISTINCT on non-grouped columns.
• Your connecting role owns the aggregate (or has MAINTAIN/ownership rights) — refresh_continuous_aggregate requires it.
• psycopg v3 is available in your automation environment if you plan to trigger refreshes from Python.

A minimal connection setup for the automation examples below:

import psycopg

CONN_STR = "postgresql://svc_refresh@db.internal:5432/telemetry"

# refresh_continuous_aggregate() issues an internal COMMIT, so it can never run
# inside an open transaction block. Autocommit is mandatory, not optional.
def connect() -> psycopg.Connection:
    return psycopg.connect(CONN_STR, autocommit=True)

# Step-by-step implementation

The numbered steps below map to the decision nodes in the flowchart above: steps 1–3 build and drive the incremental path (the inc branch), while steps 4–5 cover the full-refresh path (the full branch).

# Step 1 — Create the aggregate that the watermark will track

The watermark only exists once the view is materialized. Create it idempotently so the step is safe to re-run from a migration.

-- Hourly rollup of raw sensor telemetry. The watermark starts at the
-- minimum bucket the first refresh materializes.
CREATE MATERIALIZED VIEW IF NOT EXISTS sensor_hourly_stats
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', ts) AS bucket,
  sensor_id,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp,
  MIN(temperature) AS min_temp,
  COUNT(*)         AS reading_count
FROM sensor_readings
GROUP BY 1, 2
WITH NO DATA;

Creating WITH NO DATA keeps the DDL fast and leaves the first materialization to a controlled refresh, which matters when the source hypertable already holds months of history.

# Step 2 — Advance the watermark with a bounded incremental refresh

An incremental refresh is simply a refresh_continuous_aggregate call over a window that starts at or after the current watermark. TimescaleDB recomputes only the buckets in that window and moves the watermark to the window’s end.

-- Materialize the last two hours. The newest, still-filling bucket is left
-- out by ending the window one hour back so partial buckets don't churn.
CALL refresh_continuous_aggregate(
  'sensor_hourly_stats',
  now() - INTERVAL '2 hours',
  now() - INTERVAL '1 hour'
);

# Step 3 — Automate incremental refresh with a policy

In production you do not call the refresh by hand; you register a policy and let the scheduler advance the watermark on a cadence. Getting the offsets right is the core of refresh policy design and scheduling.

-- Every 15 minutes, refresh the window from 2 hours ago to 15 minutes ago.
-- start_offset must exceed your worst-case ingestion lag; end_offset leaves
-- the in-progress bucket to finalize before it is materialized.
SELECT add_continuous_aggregate_policy(
  'sensor_hourly_stats',
  start_offset      => INTERVAL '2 hours',
  end_offset        => INTERVAL '15 minutes',
  schedule_interval => INTERVAL '15 minutes',
  if_not_exists     => TRUE
);

The per-run cost of an incremental refresh is proportional to the number of buckets inside the window, not the size of the whole aggregate:

$$C_{\text{incremental}} \approx \left\lceil \frac{\text{start\_offset} - \text{end\_offset}}{\text{bucket\_width}} \right\rceil \times n_{\text{groups}}$$

Keeping start_offset − end_offset tight is what keeps steady-state refresh cheap. For deeper tuning of this window under heavy write rates, see incremental refresh performance tuning for large datasets.

# Step 4 — Run a full refresh for backfills and corrections

A full refresh ignores the watermark and recomputes an explicit range, or the entire aggregate when both bounds are NULL. Reserve it for the cases where incremental cannot guarantee correctness:

• Historical backfill — you loaded raw rows older than the current watermark; an incremental refresh would never revisit them.
• Definition or schema change — you altered a source column type or added a metric, so previously materialized buckets are wrong.
• Watermark or partial corruption — manual chunk manipulation, a bad restore, or replication lag left the materialized layer inconsistent.

-- Full recompute of a bounded historical range after a backfill load.
CALL refresh_continuous_aggregate(
  'sensor_hourly_stats',
  '2026-01-01 00:00:00+00',
  '2026-02-01 00:00:00+00'
);

-- Full recompute of the ENTIRE aggregate (both bounds NULL). Expensive:
-- scans every relevant chunk. Run only in a maintenance window.
CALL refresh_continuous_aggregate('sensor_hourly_stats', NULL, NULL);

Passing NULL, NULL forces a scan of every underlying chunk, so its cost scales with total retained data rather than the refresh window. On a fleet with months of high-frequency telemetry this can run for many minutes and compete with ingestion for I/O.

# Step 5 — Drive full refresh from idempotent Python automation

Backfills and schema-migration refreshes belong in CI/CD, not in a DBA’s terminal. This pattern issues the refresh with autocommit, retries transient failures with backoff, and is safe to re-run because refresh_continuous_aggregate is itself idempotent over a range.

import time
import psycopg

def full_refresh(view: str, start_ts: str | None, end_ts: str | None,
                 attempts: int = 3) -> None:
    """Idempotent bounded/full refresh with bounded exponential backoff.

    start_ts/end_ts as None, None recomputes the whole aggregate.
    """
    for attempt in range(1, attempts + 1):
        try:
            with psycopg.connect(CONN_STR, autocommit=True) as conn:
                conn.execute(
                    "CALL refresh_continuous_aggregate(%s, %s, %s)",
                    (view, start_ts, end_ts),
                )
            return
        except psycopg.OperationalError as exc:
            if attempt == attempts:
                raise
            backoff = 2 ** attempt
            print(f"[{view}] transient error: {exc}; retry in {backoff}s")
            time.sleep(backoff)

For structured retry state, dead-letter handling, and alerting around these jobs, layer this pattern under error handling and retry mechanisms rather than growing the loop above.

# Configuration parameters reference

# Integration with adjacent features

Refresh strategy never operates in isolation — it is bounded on one side by how data enters the aggregate and on the other by how raw data leaves the hypertable.

Refresh jobs share the background worker pool with compression and retention. If a chunk has already moved into columnar compression for high-frequency telemetry, a refresh that touches it must read compressed data, which is slower — so schedule heavy full refreshes before, not after, compression fires on the same range.

The interaction with retention is the more dangerous one. Retention policies from data retention and compression lifecycle automation drop old raw chunks; a full refresh over a range whose raw chunks have already been purged silently materializes zeros or gaps. Always confirm the raw data still exists for any range you plan to fully refresh, and keep TTL policy mapping windows wider than the deepest backfill you expect to run. When your aggregate must present sensible values across gaps in irregular telemetry, pair the refresh with gap-filling as covered in creating continuous aggregates with time_bucket_gapfill. When many aggregates need refreshing on overlapping schedules, coordinate them through asynchronous execution and queue management so a full refresh does not starve the incremental jobs behind it.

# Performance validation

Do not assume a refresh ran the way you intended — verify it against the system views.

Inspect the current watermark position for an aggregate. The value is stored as an internal integer time; convert it with the hypertable’s time type:

SELECT
  ca.user_view_name,
  _timescaledb_internal.to_timestamp(
    _timescaledb_internal.cagg_watermark(ca.mat_hypertable_id)
  ) AS watermark
FROM _timescaledb_catalog.continuous_agg ca
WHERE ca.user_view_name = 'sensor_hourly_stats';

Check whether the refresh policy is keeping up, and how long each run takes, via job_stats:

SELECT
  j.hypertable_name,
  js.last_run_started_at,
  js.last_successful_finish,
  js.last_run_duration,
  js.total_runs,
  js.total_failures
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats js USING (job_id)
WHERE j.proc_name = 'policy_refresh_continuous_aggregate';

A last_run_duration that climbs run over run signals the refresh window is outgrowing the schedule interval — the watermark is falling behind ingestion. Confirm no refresh is currently blocked on a lock:

SELECT pid, state, wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
WHERE query ILIKE '%refresh_continuous_aggregate%'
  AND state <> 'idle';

# Troubleshooting

ERROR: refresh_continuous_aggregate cannot run inside a transaction block The call issues an internal commit and must run in autocommit mode. In psql run it as a top-level CALL; in psycopg v3 set autocommit=True on the connection (as in the examples above). This is the most common failure when wiring refreshes into an ORM or a transaction-wrapped migration.

ERROR: invalid refresh window / refresh window too small The window must span at least one full bucket. A start–end narrower than the aggregate’s time_bucket width — or an end_offset larger than start_offset in a policy — produces an empty or invalid window. Widen start_offset or shrink end_offset so the window covers whole buckets.

Dashboards show stale numbers despite a running policy The watermark is lagging. Usual causes: start_offset too small to catch late-arriving rows, schedule_interval longer than the window fills, or worker starvation. Diagnose watermark drift and lagging windows step by step in troubleshooting stale continuous aggregates in production.

Backfill loaded but the aggregate never reflects it Rows inserted with timestamps older than the watermark are behind the incremental frontier and will not be picked up by policy runs. Issue an explicit bounded full refresh (Step 4) over the backfilled range.

Full refresh over old data returns zeros or gaps The raw chunks for that range were already dropped by a retention policy. Restore the raw data or accept that the aggregate cannot be recomputed there; then widen retention so future backfills stay recomputable.

Frequently Asked Questions

# Related

• Materialized View Architecture & Syntax — how the materialized layer and watermark are structured.
• Refresh Policy Design & Scheduling — choosing offsets and cadence for automated refreshes.
• Asynchronous Execution & Queue Management — coordinating many refresh jobs without starvation.
• Error Handling & Retry Mechanisms — resilient automation around refresh failures.
• Troubleshooting Stale Continuous Aggregates in Production — diagnosing watermark drift and lagging windows.

← Back to Continuous Aggregate Creation & Refresh Management