A TimescaleDB retention policy can delete history from a continuous aggregate without raising an error. When the retention job drops raw chunks that the refresh window still covers, the aggregate loses those buckets and both jobs report success.
In this guide, you'll learn how the drop and the refresh interact, how to set the retention horizon and the refresh window so they can never overlap, and how to find and repair aggregates that already have the gap.
What you will build
- A reproduction of the break on a test hypertable.
- An audit query that flags every continuous aggregate whose retention horizon sits inside its refresh window.
- A repair sequence for aggregates that have already lost buckets.
- A corrected policy pair with margin for a missed refresh, plus a retention policy on the aggregate.
Why it matters
Retention keeps a high-rate hypertable inside the storage limits of the Industrial Internet of Things (IIoT) PostgreSQL performance envelope, and a continuous aggregate keeps summaries after the raw rows are gone.
A dropped chunk is the only source the aggregate could compute its buckets from. Buckets the refresh hasn't built yet never arrive, and because TimescaleDB treats the deleted raw data as a change, the next refresh also deletes any buckets inside its window that it has already built. The jobs succeed, and the aggregate still returns rows, with low or empty totals for part of its history.
Take raw retention of 90 days and a refresh window that reaches back 120 days, for example. The retention job drops each day at 90 days old, which leaves that day 30 more days inside the window with no raw rows behind it, so the next refresh deletes it. The aggregate built for multi-year reporting loses each day as it ages past 90 days. You will only notice the gap when a year-over-year figure disagrees with an older export.
Prerequisites
- TimescaleDB 2.13 or later, self-hosted or on Tiger Cloud.
- A hypertable with a timestamp time column, a continuous aggregate, and a retention policy. The audit casts offsets to intervals, so integer time columns need a different comparison.
- A role that can read the
timescaledb_information
views and run alter_job()
and run_job()
on the policy jobs. - A scratch schema for the reproduction.
- A backup holding raw rows older than your retention horizon, for repairs.
Build a policy pair that keeps history
Step 1: Reproduce the break
The SQL statements below create a hypertable of ten energy meters that report once a minute for ten days in 6-hour chunks, plus an empty daily aggregate. export_daily
stands in for an older export, and readings_backup
stands in for a backup.
CREATE TABLE readings (
ts timestamptz NOT NULL,
meter_id int NOT NULL,
kwh double precision NOT NULL
);
SELECT create_hypertable('readings',
by_range('ts', INTERVAL '6 hours'));
INSERT INTO readings
SELECT ts, meter_id, 0.20 + random() * 0.10
FROM generate_series(
date_trunc('minute', now()) - INTERVAL '10 days',
date_trunc('minute', now()) - INTERVAL '1 minute',
INTERVAL '1 minute') AS ts,
generate_series(1, 10) AS meter_id;
CREATE TABLE readings_backup AS
SELECT * FROM readings;
CREATE TABLE export_daily AS
SELECT time_bucket('1 day', ts) AS day,
round(sum(kwh)::numeric, 1) AS kwh
FROM readings
GROUP BY 1;
CREATE MATERIALIZED VIEW energy_daily
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', ts) AS day,
count(*) AS readings,
sum(kwh) AS kwh
FROM readings
GROUP BY day
WITH NO DATA;
Next, the SQL below adds a 3-day retention policy and a refresh policy whose window reaches back 7 days, and pauses both in the same transaction so the scheduler can't run either one first.
BEGIN;
SELECT add_retention_policy('readings',
drop_after => INTERVAL '3 days');
SELECT add_continuous_aggregate_policy('energy_daily',
start_offset => INTERVAL '7 days',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
SELECT alter_job(job_id, scheduled => false)
FROM timescaledb_information.jobs
WHERE hypertable_schema = current_schema()
AND hypertable_name IN ('readings', 'energy_daily');
COMMIT;
When the refresh stalls, the drop runs first. The calls below use run_job()
to run them in that order. Replace 1038 and 1039 with the job identifiers (IDs) the add calls returned.
CALL run_job(1038);
CALL run_job(1039);
Compare each day in the aggregate with the export, in kWh, using the query that follows.
SELECT e.day::date AS day, e.kwh AS export_kwh,
coalesce(round(a.kwh::numeric, 1), 0) AS agg_kwh,
round(100 * coalesce(a.kwh, 0)::numeric / e.kwh)
AS pct
FROM export_daily e
LEFT JOIN energy_daily a USING (day)
ORDER BY e.day;
The output below comes from a synthetic dataset generated for this guide, run on Tiger Cloud with PostgreSQL 18.6 and TimescaleDB 2.29.2. Your dates and totals will differ, and every step applies unchanged. Rows cover the days around the drop.
day | export_kwh | agg_kwh | pct
------------+------------+---------+-----
2026-09-22 | 3607.4 | 0 | 0
2026-09-23 | 3597.9 | 0 | 0
2026-09-24 | 3598.9 | 0 | 0
2026-09-25 | 3596.1 | 0 | 0
2026-09-26 | 3600.7 | 3600.7 | 100
2026-09-27 | 3602.2 | 3602.2 | 100
2026-09-28 | 3604.2 | 3604.2 | 100
Every day older than the 3-day horizon reads zero, and both calls complete without an error.
Real-time aggregation doesn't bring those days back. This version leaves it off by default (materialized_only = true
), and the SQL below turns it on.
ALTER MATERIALIZED VIEW energy_daily
SET (timescaledb.materialized_only = false);
The comparison query now returns the output below, where only today's open bucket fills in from raw rows.
day | export_kwh | agg_kwh | pct
------------+------------+---------+-----
2026-09-24 | 3598.9 | 0 | 0
2026-09-25 | 3596.1 | 0 | 0
2026-09-26 | 3600.7 | 3600.7 | 100
2026-09-27 | 3602.2 | 3602.2 | 100
2026-09-28 | 3604.2 | 3604.2 | 100
2026-09-29 | 279.0 | 279.0 | 100
Once raw chunks are gone, the materialized buckets are the only record.
Step 2: Map both policies on one timeline
A bucket enters the refresh window once it closes and end_offset
has passed. Any refresh can recompute it, whenever its range changes, until it's older than start_offset
. The retention job drops a chunk once the chunk's whole time range is older than drop_after
. The query below reads those offsets and schedules from timescaledb_information.jobs
.
SELECT job_id, hypertable_name,
config->>'start_offset' AS start_offset,
config->>'drop_after' AS drop_after,
schedule_interval AS every
FROM timescaledb_information.jobs
WHERE proc_name IN ('policy_retention',
'policy_refresh_continuous_aggregate')
ORDER BY hypertable_name;
Trimmed to the Step 1 pair, the output below shows both windows.
job_id | hypertable_name | start_offset | drop_after | every
--------+-----------------+--------------+------------+----------
1039 | energy_daily | 7 days | | 01:00:00
1038 | readings | | 3 days | 1 day
Every day stays inside the refresh window for 4 days after the drop removes its raw chunks. The fix is one invariant. drop_after
must exceed start_offset
, with enough margin to notice and fix a failed refresh before the drop reaches its range. Size that margin to the time your team takes to see a failed job and respond, plus one refresh interval from the every
column.
Step 3: Watch an up-to-date aggregate lose days
An aggregate that keeps up with its refresh still loses data under the Step 1 pair. The SQL below drops the Step 1 objects. Rerun the first two blocks of Step 1 afterward.
DROP MATERIALIZED VIEW energy_daily;
DROP TABLE readings, readings_backup, export_daily;
With the tables rebuilt, the calls below refresh the whole aggregate so every bucket starts correctly, then run the drop and the refresh with your new job IDs.
CALL refresh_continuous_aggregate('energy_daily',
NULL, NULL);
CALL run_job(1040);
CALL run_job(1041);
After those calls, the comparison query returns the output below for the days around the horizon.
day | export_kwh | agg_kwh | pct
------------+------------+---------+-----
2026-09-22 | 3607.4 | 3607.4 | 100
2026-09-23 | 3597.9 | 0 | 0
2026-09-24 | 3598.9 | 0 | 0
2026-09-25 | 3596.1 | 0 | 0
2026-09-26 | 3600.7 | 3600.7 | 100
The drop logged each removed chunk's time range as changed, and the refresh recomputed the days between 3 and 7 days old from no rows. Older days survived only because they sat outside the window when the policy first ran. In steady operation the drop reaches every day inside the window, so the aggregate keeps no more history than the raw table.
Step 4: Plan for backfills
When a gateway flushes late readings for a day whose chunks are gone, TimescaleDB creates a new chunk holding only those rows. The SQL below writes an hour of late readings 5 days back and runs the refresh.
INSERT INTO readings
SELECT ts + INTERVAL '30 seconds', meter_id, 0.25
FROM generate_series(
time_bucket('1 day', now() - INTERVAL '5 days')
+ INTERVAL '6 hours',
time_bucket('1 day', now() - INTERVAL '5 days')
+ INTERVAL '6 hours 59 minutes',
INTERVAL '1 minute') AS ts,
generate_series(1, 10) AS meter_id;
CALL run_job(1041);
After the refresh, the output below shows that day's row.
day | export_kwh | agg_kwh | pct
------------+------------+---------+-----
2026-09-24 | 3598.9 | 150.0 | 4
The refresh replaced a full day with one hour of readings. In the run, the next drop and refresh deleted the day. Under the Step 7 pair, a backfill older than start_offset
sits outside the window, so refresh its range by hand before its chunk reaches drop_after
.
Step 5: Audit every aggregate
The query below joins each continuous aggregate to the retention policy on its hypertable and to its refresh policy, which the jobs view lists under the aggregate's view name.
WITH refresh AS (
SELECT hypertable_schema AS view_schema,
hypertable_name AS view_name,
(config->>'start_offset')::interval
AS start_offset
FROM timescaledb_information.jobs
WHERE proc_name =
'policy_refresh_continuous_aggregate'
), retention AS (
SELECT hypertable_schema, hypertable_name,
(config->>'drop_after')::interval AS drop_after
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
)
SELECT c.view_name, r.start_offset, d.drop_after,
d.drop_after - r.start_offset AS margin,
r.start_offset IS NULL
OR d.drop_after <= r.start_offset AS overlap
FROM timescaledb_information.continuous_aggregates c
JOIN retention d
USING (hypertable_schema, hypertable_name)
LEFT JOIN refresh r USING (view_schema, view_name)
ORDER BY overlap DESC, c.view_name;
Against the Step 3 aggregate, the audit returns the output below.
view_name | start_offset | drop_after | margin | overlap
--------------+--------------+------------+---------+---------
energy_daily | 7 days | 3 days | -4 days | t
A row with overlap
set to true needs Steps 6 and 7. The audit also flags an aggregate with no refresh policy, or one refreshing from the start of time.
Step 6: Repair what the drop already removed
Pause the drop before anything else, then find where raw coverage begins in timescaledb_information.chunks
. The SQL below does both.
SELECT alter_job(1040, scheduled => false);
SELECT min(range_start) AS raw_from
FROM timescaledb_information.chunks
WHERE hypertable_schema = current_schema()
AND hypertable_name = 'readings';
Raw coverage starts at the boundary in the output below.
raw_from
------------------------
2026-09-26 00:00:00+00
The SQL below refreshes everything from that boundary forward, loads the rows older than raw_from
, and refreshes that range. readings_backup
stands in for the table you restore from backup. If raw_from
falls inside a bucket, use the next bucket boundary in both refresh calls.
CALL refresh_continuous_aggregate('energy_daily',
'2026-09-26', now() - INTERVAL '1 hour');
INSERT INTO readings
SELECT * FROM readings_backup
WHERE ts < '2026-09-26';
CALL refresh_continuous_aggregate('energy_daily',
NULL, '2026-09-26');
Every closed day should now read 100 in the comparison query. Leave the drop paused until Step 7 is in place.
Step 7: Replace the policy pair
The SQL below replaces the pair with a 3-day refresh window and a 7-day retention horizon, leaving 4 days to catch a failed refresh. It also gives the aggregate its own 2-year retention policy.
SELECT remove_continuous_aggregate_policy('energy_daily');
SELECT remove_retention_policy('readings');
SELECT add_continuous_aggregate_policy('energy_daily',
start_offset => INTERVAL '3 days',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
SELECT add_retention_policy('readings',
drop_after => INTERVAL '7 days');
SELECT add_retention_policy('energy_daily',
drop_after => INTERVAL '2 years');
Schedule the refresh ahead of the drop. Policy jobs start with fixed_schedule set to false, so their run times drift. The calls below use alter_job() to fix the refresh on the hour and the daily drop 30 minutes past. Replace 1042 and 1043 with the new job IDs.
SELECT alter_job(1042, fixed_schedule => true,
initial_start => date_trunc('hour', now())
+ INTERVAL '1 hour');
SELECT alter_job(1043, fixed_schedule => true,
initial_start => date_trunc('hour', now())
+ INTERVAL '90 minutes');
Validate the policy pair
The Step 5 audit should now show overlap as false on every row, as in the output below.
view_name | start_offset | drop_after | margin | overlap
--------------+--------------+------------+--------+---------
energy_daily | 3 days | 7 days | 4 days | f
Run the drop and then the refresh, the same order as Step 1, and compare each bucket with the raw rows behind it, using the SQL below.
CALL run_job(1043);
CALL run_job(1042);
SELECT a.day::date AS day, a.readings AS agg_readings,
count(r.ts) AS raw_readings
FROM energy_daily a
LEFT JOIN readings r
ON r.ts >= a.day AND r.ts < a.day + INTERVAL '1 day'
GROUP BY a.day, a.readings
ORDER BY a.day;
Trimmed to the oldest days, the output below shows three buckets that survived the loss of their raw chunks. The rest match the raw rows.
day | agg_readings | raw_readings
------------+--------------+--------------
2026-09-19 | 13280 | 0
2026-09-20 | 14400 | 0
2026-09-21 | 14400 | 0
2026-09-22 | 14400 | 14400
2026-09-23 | 14400 | 14400
The SQL below backfills one hour into the oldest bucket the refresh window covers and runs the refresh.
INSERT INTO readings
SELECT ts + INTERVAL '30 seconds', meter_id, 0.25
FROM generate_series(
time_bucket('1 day', now() - INTERVAL '2 days')
+ INTERVAL '6 hours',
time_bucket('1 day', now() - INTERVAL '2 days')
+ INTERVAL '6 hours 59 minutes',
INTERVAL '1 minute') AS ts,
generate_series(1, 10) AS meter_id;
CALL run_job(1042);
Rerun the bucket comparison, and the backfilled day in the output below counts the late rows in both columns.
day | agg_readings | raw_readings
------------+--------------+--------------
2026-09-27 | 15000 | 15000
After a few days of scheduled runs, confirm the run order in job_stats
. The query below reads both jobs.
SELECT job_id, total_runs AS runs,
total_failures AS failures,
last_run_status AS status,
last_run_started_at::time(0) AS last_start,
next_start::time(0) AS next_start
FROM timescaledb_information.job_stats
WHERE job_id IN (1042, 1043)
ORDER BY last_run_started_at;
The output below comes from a run with both jobs on 1-minute schedules. The refresh starts ahead of the drop, and neither job has failed.
job_id | runs | failures | status | last_start | next_start
--------+------+----------+---------+------------+------------
1042 | 3 | 0 | Success | 01:55:00 | 01:56:00
1043 | 3 | 0 | Success | 01:55:30 | 01:56:30
Conclusion
A continuous aggregate keeps its history only when the retention horizon sits beyond the refresh window.
Run the Step 5 audit query against your own database this week and list every aggregate where overlap is true. For each one, pause the retention job before its next scheduled drop, then follow Steps 6 and 7 using the data retention with continuous aggregates guide.