--- title: "How Retention Policies Silently Break Continuous Aggregates" published: 2026-10-23T08:28:41.000-04:00 updated: 2026-10-23T08:28:41.000-04:00 excerpt: "Retention at 3 days with a refresh window reaching back 7 means the aggregate keeps no more history than the raw table. Here’s the invariant that fixes it." tags: TimescaleDB, Continuous Aggregates authors: John Shi --- > **TimescaleDB is now Tiger Data.** 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](https://www.tigerdata.com/blog/the-iiot-postgresql-performance-envelope), and a [continuous aggregate](https://www.tigerdata.com/docs/learn/continuous-aggregates) 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](https://www.tigerdata.com/docs/learn/data-lifecycle/data-retention/data-retention-with-continuous-aggregates), 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. ```SQL 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](https://www.tigerdata.com/docs/reference/timescaledb/data-retention/add_retention_policy) and a [refresh policy](https://www.tigerdata.com/docs/reference/timescaledb/continuous-aggregates/add_continuous_aggregate_policy) whose window reaches back 7 days, and pauses both in the same transaction so the scheduler can't run either one first. ```SQL 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()`](https://www.tigerdata.com/docs/reference/timescaledb/jobs-automation/run_job) to run them in that order. Replace 1038 and 1039 with the job identifiers (IDs) the add calls returned. ```SQL CALL run_job(1038); CALL run_job(1039); ``` Compare each day in the aggregate with the export, in kWh, using the query that follows. ```SQL 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. ```markdown 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](https://www.tigerdata.com/docs/learn/continuous-aggregates/real-time-aggregates) doesn't bring those days back. This version leaves it off by default (`materialized_only = true`), and the SQL below turns it on. ```SQL 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. ```markdown 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`](https://www.tigerdata.com/docs/reference/timescaledb/informational-views/jobs). ```SQL 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. ```SQL DROP MATERIALIZED VIEW energy_daily; DROP TABLE readings, readings_backup, export_daily; ``` With the tables rebuilt, the calls below [refresh the whole aggregate](https://www.tigerdata.com/docs/reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate) so every bucket starts correctly, then run the drop and the refresh with your new job IDs. ```SQL 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. ```markdown 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. ```SQL 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. ```markdown 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. ```SQL 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. ```markdown 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`](https://www.tigerdata.com/docs/reference/timescaledb/informational-views/chunks). The SQL below does both. ```SQL 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. ```markdown 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. ```SQL 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. ```SQL 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()](https://www.tigerdata.com/docs/reference/timescaledb/jobs-automation/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. ```SQL 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. ```markdown 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. ```SQL 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. ```SQL 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. ```markdown day | agg_readings | raw_readings ------------+--------------+-------------- 2026-09-27 | 15000 | 15000 ``` After a few days of scheduled runs, confirm the run order in [`job_stats`](https://www.tigerdata.com/docs/reference/timescaledb/informational-views/job_stats). The query below reads both jobs. ```SQL 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](https://www.tigerdata.com/docs/learn/data-lifecycle/data-retention/data-retention-with-continuous-aggregates).