Creating Continuous Aggregates with time_bucket_gapfill

To serve gap-free rollups from sparse IoT telemetry you must split the work in two: materialize deterministic buckets inside the continuous aggregate with time_bucket(), then apply time_bucket_gapfill() at query time — because gap-filling generates synthetic rows the incremental refresh engine cannot track. This recipe shows time-series data engineers, IoT platform developers, and Python automation builders exactly where each function belongs, how to size the gap-filled output, and how to verify the result. It builds directly on the materialized view architecture and syntax that defines the WITH (timescaledb.continuous) contract underneath.

Network partitions, aggressive device sleep cycles, and batched uplinks mean a fleet rarely reports on a perfectly uniform cadence. Those silent intervals become missing buckets that break dashboards, moving averages, and alerting thresholds. The instinct is to embed time_bucket_gapfill() in the aggregate definition so gaps never appear — but that fights the incremental materialization engine, and TimescaleDB rejects or mis-materializes it. The reliable approach keeps storage deterministic and defers interpolation to the read path.

# Profiling your telemetry before you gap-fill

Gap-filling is only correct if you understand the shape of the data feeding it. Before writing a line of DDL, gather these inputs:

• Reporting cadence per device — the nominal interval between samples (e.g. one reading per 60 s). This sets the smallest time_bucket() width that carries signal; bucketing finer than the cadence manufactures gaps you then have to fill back in.
• Gap distribution — how long devices typically go silent (a 30 s radio retry versus an 8 h overnight sleep). Short gaps suit locf(); long gaps often should stay NULL so a dashboard shows “no data” rather than a stale flat line.
• Fleet cardinality — the number of distinct device_id values in a query window. Gap-fill emits one row per bucket per group, so cardinality multiplies the output row count and the payload a dashboard must render.
• Typical query window — the dashboard’s default range (last 24 h, last 7 d). Combined with the bucket width this determines how many synthetic rows a single query produces.
• Aggregate retention horizon — how far back the materialized rollup is kept, since a gap-fill request that reaches past it correctly returns NULL. This ties into the data retention and lifecycle automation policies governing the aggregate.
• Missing-value semantics — a per-metric decision: should an absent bucket read as NULL, the last known value (locf()), or a straight-line interpolate() between neighbours? Temperature interpolates sensibly; a monotonic counter does not.

# Where each function belongs — the two-stage pattern

Continuous aggregates are optimized for incremental refresh: the engine tracks an invalidation watermark and only re-processes buckets touched by new or late-arriving rows. time_bucket_gapfill() is a query-time function that fabricates rows for intervals that do not exist in the hypertable. Because those synthetic rows were never stored, the refresh mechanism cannot track, invalidate, or update them — so gap-fill has no valid place inside the materialized definition.

The fill strategy is a second, per-metric decision applied only on the query side:

The three strategies produce visibly different traces across the same silent interval. Below, one device’s hourly temperature buckets 3 and 4 hold no stored rows; each fill function draws that gap differently:

The DDL below materializes hourly rollups deterministically; every statement is idempotent and safe to re-run in CI/CD.

-- Enable TimescaleDB (idempotent)
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 1. Base hypertable for raw device telemetry
CREATE TABLE IF NOT EXISTS iot_telemetry (
    time TIMESTAMPTZ NOT NULL,
    device_id UUID NOT NULL,
    temperature NUMERIC,
    humidity NUMERIC,
    battery_pct NUMERIC
);
SELECT create_hypertable('iot_telemetry', 'time', if_not_exists => TRUE);

-- 2. Continuous aggregate using deterministic time_bucket (NO gapfill here)
CREATE MATERIALIZED VIEW IF NOT EXISTS iot_telemetry_1h
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    device_id,
    avg(temperature) AS avg_temp,
    avg(humidity)    AS avg_humidity,
    avg(battery_pct) AS avg_battery,
    count(*)         AS sample_count
FROM iot_telemetry
GROUP BY bucket, device_id;

-- 3. Automated refresh policy (runs hourly, refreshes the last 24h)
SELECT add_continuous_aggregate_policy('iot_telemetry_1h',
    start_offset      => INTERVAL '24 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour',
    if_not_exists     => TRUE);

Gap-fill is then applied at read time. time_bucket_gapfill() derives its range from the bounded WHERE clause on the bucket column, and locf() or interpolate() fills the empty buckets. Choosing and staggering the schedule is covered in refresh policy design and scheduling.

-- Query-time gap-filling. The gapfill range comes from the bounded WHERE clause.
SELECT
    time_bucket_gapfill('1 hour', bucket) AS gf_bucket,
    device_id,
    locf(avg(avg_temp))       AS temp_locf,        -- carry last known
    interpolate(avg(avg_temp)) AS temp_interpolated -- linear estimate
FROM iot_telemetry_1h
WHERE bucket >= '2026-07-01T00:00:00Z'
  AND bucket <  '2026-07-02T00:00:00Z'
GROUP BY gf_bucket, device_id
ORDER BY gf_bucket;

# Sizing the gap-filled output

Because gap-fill emits one row per bucket per group regardless of whether data existed, the output size is deterministic. For a query window of duration $W$, a bucket width $b$, and $D$ distinct devices in that window:

$$N_{\text{rows}} = \left\lceil \frac{W}{b} \right\rceil \times D$$

This is the row count the dashboard must transport and render — it does not depend on how sparse the underlying telemetry is, which is exactly why gap-fill makes dashboards predictable.

# Worked example: a 5,000-device sensor fleet

Consider an industrial fleet of $D = 5{,}000$ devices, each meant to report once per minute, feeding the iot_telemetry_1h aggregate above. A control-room dashboard renders the trailing 7 days at 1-hour buckets:

• $W = 7 \text{ days} = 168 \text{ h}$, bucket width $b = 1 \text{ h}$, so $\lceil W/b \rceil = 168$ buckets.
• Gap-filled rows: $168 \times 5{,}000 = 840{,}000$ rows returned — regardless of how many devices went dark overnight.

Without gap-fill, a device that slept from 22:00–06:00 would simply be absent for those 8 buckets, and a per-device sparkline would draw a misleading straight line across the gap or collapse the axis. With locf(), those 8 buckets carry the last battery and setpoint reading (correct for slow-moving state); with NULL left in place, the temperature trace shows an honest break. If the aggregate only retains 14 days but a user requests 30, the earliest 16 days of buckets return NULL — the intended behavior, not an error. Note the materialization stays tiny: only non-empty buckets are stored, so an overnight-silent fleet costs nothing extra on disk.

# Edge cases & when to deviate

• Unbounded query — time_bucket_gapfill() requires a bounded range. Omit either side of the WHERE and you get ERROR: invalid time_bucket_gapfill argument: ts_end; always constrain both bucket >= and bucket <.
• Gapfill inside the aggregate — placing time_bucket_gapfill() in the CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) body fails validation or mis-materializes. Keep it strictly on the read path.
• locf() at the window’s leading edge — if the first buckets in the range have no prior observation to carry, locf yields NULL until the first real value. Widen the lower bound or use locf(..., treat_null_as_missing => true) deliberately.
• interpolate() on counters — linear interpolation between two monotonically increasing counter reads invents values that never occurred. Restrict interpolate() to smooth physical signals and leave counters as NULL or locf.
• Bucket finer than cadence — bucketing at 10 s when devices report every 60 s manufactures empty buckets you then gap-fill, inflating both storage churn and output rows. Match the bucket to the cadence; for hierarchical rollups, aggregate the coarse view from the fine one.
• Reaching past retention — a window extending beyond the aggregate’s retention horizon returns NULL for dropped buckets. This overlaps with diagnosing gaps that look like staleness — see troubleshooting stale continuous aggregates in production.

# Verification

First confirm the aggregate is a genuine continuous aggregate and that its refresh job is healthy, so any gaps you see are real absences rather than un-materialized buckets:

-- Confirm the view is a continuous aggregate and inspect its refresh job status.
SELECT ca.view_name,
       ca.materialization_hypertable_name,
       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
WHERE ca.view_name = 'iot_telemetry_1h';

Then confirm gap-fill actually produced the expected bucket count for a known window — it should equal $\lceil W/b \rceil$ per device even where telemetry was sparse:

-- Expect 24 rows per device for a 24h window at 1h buckets, gaps included.
SELECT device_id, count(*) AS filled_buckets
FROM (
    SELECT time_bucket_gapfill('1 hour', bucket) AS gf_bucket,
           device_id,
           locf(avg(avg_temp)) AS t
    FROM iot_telemetry_1h
    WHERE bucket >= '2026-07-01T00:00:00Z'
      AND bucket <  '2026-07-02T00:00:00Z'
    GROUP BY gf_bucket, device_id
) g
GROUP BY device_id
ORDER BY filled_buckets;

Automation can wrap the manual refresh-and-verify step so a backfill or ad-hoc correction confirms the job’s last run. The snippet below uses psycopg v3 with autocommit, since refresh_continuous_aggregate() cannot run inside a transaction block:

import psycopg

def refresh_and_verify(dsn: str, view_name: str) -> None:
    """Trigger a full manual refresh, then read back the job's last run."""
    with psycopg.connect(dsn, autocommit=True) as conn, conn.cursor() as cur:
        # Manual refresh over the full range (name is bound, not interpolated).
        cur.execute("CALL refresh_continuous_aggregate(%s, NULL, NULL)", (view_name,))
        cur.execute(
            """
            SELECT 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
            WHERE ca.view_name = %s
            """,
            (view_name,),
        )
        print("last run:", cur.fetchone())

if __name__ == "__main__":
    refresh_and_verify("postgresql://user:pass@ts-host:5432/iot_db", "iot_telemetry_1h")

# Related & Navigation

← Back to Materialized View Architecture & Syntax · Continuous Aggregate Creation & Refresh Management

• Refresh Policy Design & Scheduling — set the start_offset, end_offset, and cadence that keep the buckets you gap-fill current.
• Incremental vs Full Refresh Strategies — decide how much of the aggregate each run re-materializes.
• Troubleshooting Stale Continuous Aggregates in Production — tell a real gap from an un-refreshed bucket.
• Setting up Automatic Refresh Policies for 5-Minute Intervals — a finer-grained bucket cadence worked end to end.