A few dozen solar plants and wind farms push supervisory control and data acquisition (SCADA) readings into a TimescaleDB hypertable on Postgres. An hourly continuous aggregate serves the dashboards and the settlement exports, and the continuous aggregate refresh policy behind it was configured on the day the system went live. Nobody has touched it since. It has three numbers in it: start_offset, end_offset, and schedule_interval.
By the end of this guide, you'll be able to read those numbers off your own system, check them against the one rule the engine enforces, and pick the policy shape that fits each job your aggregates do. There are five shapes, each an answer to something a generation fleet actually does: a control room that needs the last hour, sites that flush days of buffered readings, rollups stacked on rollups, a retention policy dropping raw data, and a historian export that has to be loaded. The shapes combine, and most fleets run two or three at once. The guide assumes you know how refresh works and already have a continuous aggregate in production. The examples assume TimescaleDB 2.29.0 or later, and Shape 3 also uses the TimescaleDB Toolkit extension.
Everything here is do-it-yourself. If you'd rather tune against your own workload with someone who does this daily, that's what TigerData's support plans are for.
Read your three policy numbers
Together, the three settings define a schedule and a moving window. The policy wakes up every schedule_interval and refreshes the window that runs from now() - start_offset to now() - end_offset. start_offset sets how far back the policy looks for changed data, and end_offset keeps the window clear of the bucket that is still filling. The mechanism underneath (the invalidation log, the materialization watermark, and what happens when late-arriving data lands) belongs to Continuous Aggregate Refresh, Demystified.
SELECT ca.view_name,
j.config ->> 'start_offset' AS start_offset,
j.config ->> 'end_offset' AS end_offset,
j.schedule_interval
FROM timescaledb_information.jobs j
JOIN timescaledb_information.continuous_aggregates ca
ON ca.materialization_hypertable_schema = j.hypertable_schema
AND ca.materialization_hypertable_name = j.hypertable_name
WHERE j.proc_name = 'policy_refresh_continuous_aggregate'
ORDER BY ca.view_name;
A typical go-live policy on an hourly aggregate over site_readings looks like this:
Check every row the query returns against one rule: the window has to span at least two buckets, so start_offset must be at least end_offset plus two bucket widths, and end_offset must never be NULL. TimescaleDB enforces the first half, but not the second. Ask for a narrower window, and add_continuous_aggregate_policy refuses with policy refresh window too small, because both edges of the window snap to whole buckets and a job rarely fires exactly on a bucket boundary. A NULL end_offset is accepted, and the argument reference calls it possible but not recommended, so that half of the check is yours.
Past that floor, be generous withstart_offset. Refresh work tracks the buckets your writes invalidated, so in steady state a wide window is close to free: one late row dirties its own bucket, and the policy recomputes that bucket and nothing else. Width costs something in two cases. A retention drop inside the window is a change like any other, so the policy recomputes that range (Shape 4 deals with it), and the first run after you create or widen a policy materializes the whole window once.
The go-live policy is a sound default. Each shape below is what you reach for when one job the aggregate serves pulls against it.
Shape 1: Frequent refresh for recent data
The control room tracks generation against forecast, and when the grid operator issues a curtailment instruction, the effect has to show up in the hourly numbers right away. Real-time aggregation covers that: a query against generation_hourly combines the materialized buckets with the raw readings newer than them, so the latest hour is always current. It is off by default for continuous aggregates created on TimescaleDB 2.13 or later, so turn it on where you want it:
ALTER MATERIALIZED VIEW generation_hourly SET (timescaledb.materialized_only = false);
Real-time aggregation answers from raw data at query time; the policy decides how soon those rows become precomputed buckets. That still matters. Anything that reads materialized results only, like the settlement export, waits for the publication delay, roughly schedule_interval + end_offset, the figure Demystified recommends handing to downstream consumers. And every hour the policy hasn't materialized yet is aggregated again on each dashboard query. The go-live policy publishes a bucket about three hours after it closes.
A tight window on a frequent schedule brings that down. A continuous aggregate only takes a second policy when the windows don't overlap (Shape 2 covers that), so replace the go-live policy:
The 06:15 run publishes everything up to 05:00. The bucket that closed at 06:00 sits inside the end_offset until the 07:00 run picks it up, so the publication delay is now an hour, an hour and a quarter at most. Twelve hours of lookback still covers a site that drops off for a shift. A site that comes back after three days is beyond it, and that's Shape 2.
Shape 2: Daily refresh for late data
The usual reason generation data falls far behind its own timestamp is the site link. A remote met mast or a rural inverter cluster loses connectivity; the site controller or remote terminal unit (RTU) goes into store-and-forward, and on reconnect it flushes hours or days of readings in one burst, each carrying its original timestamp. Those rows land in buckets that were materialized days ago.
These rows matter as long as they can still change an invoice, so the lookback is set by your market's resettlement window. ERCOT issues its true-up statement 180 days after the operating day, and Great Britain's final reconciliation run comes at 14 months. Past that horizon a correction is an accounting matter, and the aggregate no longer needs to move. None of this needs to be fresh. It needs to be complete, and a sweep that size belongs off-peak, once a day:
-- Wide and daily. Its end_offset is Shape 1's start_offset.
SELECT add_continuous_aggregate_policy('generation_hourly',
start_offset => INTERVAL '90 days',
end_offset => INTERVAL '12 hours',
schedule_interval => INTERVAL '1 day');
The ninety days is a placeholder for the number your contracts already give you. On its own, this shape suits an aggregate that only feeds daily availability reports and the settlement export: a bucket appears within a day and a half of closing, and everything inside the resettlement window stays correct. If it replaces the go-live policy instead of joining Shape 1's, remove the old policy first, as in Shape 1.
Combining shapes 1 and 2
Shape 2's end_offset is Shape 1's start_offset on purpose. A refresh policy supports concurrent policies on one continuous aggregate as long as their windows do not overlap, and the documentation gives the pattern as one policy for recent data plus another for backfilled data in older chunks. Here the second policy runs on a schedule for a recurring late tail, the same mechanism applied to a different cause. The two windows meet at twelve hours and never cross.
One policy with a ninety-day start_offset would catch the same backlog. Splitting it in two buys each job its own schedule. The recent end refreshes every fifteen minutes, the deep sweep runs once a day, and when a site flushes five days of readings, that rewrite happens in the daily job instead of inside the one keeping the last hour fresh. In the diagram, the flushed readings carry timestamps beyond the tight band, and the next daily run picks them up.
Shape 3: One policy per level for hierarchical continuous aggregates
A generation fleet keeps four granularities. Raw telemetry arrives every one to sixty seconds. Above it sits the ten-minute statistical record. In wind, it comes from power performance testing and the contracts built on it: IEC 61400-12-1 builds the power curve from ten-minute averages, availability guarantees are counted in ten-minute periods, and turbine SCADA systems store each signal's ten-minute mean, minimum, maximum, and standard deviation as a matter of course. Solar sites vary in polling interval and report revenue metering on the system operator's settlement interval, so a solar stack often puts its base rollup somewhere other than ten minutes. Above that come hourly for dashboards and daily for availability reporting.
Hierarchical continuous aggregates build each level on the one below, and each level is itself a hypertable with its own columnstore and retention policies. The aggregate function is where a stack goes wrong. You cannot sum a mean, and an average of averages across buckets with unequal populations is arithmetically wrong. stats_agg and rollup from the Toolkit build a composable summary at the base and combine children into a parent correctly. Here the hourly aggregate is rebuilt on top of the ten-minute record:
CREATE MATERIALIZED VIEW generation_10min
WITH (timescaledb.continuous) AS
SELECT site_id,
time_bucket(INTERVAL '10 minutes', ts) AS bucket,
stats_agg(power_kw) AS power_stats,
sum(energy_kwh) AS energy_kwh
FROM site_readings
GROUP BY site_id, time_bucket(INTERVAL '10 minutes', ts)
WITH NO DATA;
CREATE MATERIALIZED VIEW generation_hourly
WITH (timescaledb.continuous) AS
SELECT site_id,
time_bucket(INTERVAL '1 hour', bucket) AS bucket,
rollup(power_stats) AS power_stats,
sum(energy_kwh) AS energy_kwh
FROM generation_10min
GROUP BY site_id, time_bucket(INTERVAL '1 hour', bucket)
WITH NO DATA;
CREATE MATERIALIZED VIEW generation_daily
WITH (timescaledb.continuous) AS
SELECT site_id,
time_bucket(INTERVAL '1 day', bucket) AS bucket,
rollup(power_stats) AS power_stats,
sum(energy_kwh) AS energy_kwh
FROM generation_hourly
GROUP BY site_id, time_bucket(INTERVAL '1 day', bucket)
WITH NO DATA;
Reads go through the accessors, so average(power_stats) and stddev(power_stats) are correct at every level. Energy stays a plain sum because energy_kwh here is energy per reading interval, which composes by addition; a cumulative meter register would need a different treatment.
Each level gets its own refresh policy, and the offsets step upward. Give every parent an end_offset that clears the publication delay of the level below it, so a parent only materializes buckets its child has finished:
Read bottom-up, each window ends behind the one below it: the ten-minute level publishes to twenty minutes ago, the hourly level to two hours ago, and the daily level to the last complete day. No parent ever materializes a bucket its child hasn't finished.
Shape 4: Keep the aggregate, drop the raw data
Contracts set how long renewable generation data has to exist, and the aggregates are what they need. Settlement and resettlement set one horizon, warranty availability disputes with the turbine or inverter manufacturer set a second (the claim is calculated from SCADA records), and power curve verification sets a third, often limited to a year or so after commissioning. Aggregated history is usually held for the contract term, and a power purchase agreement typically runs around 20 years. Nothing asks that of the raw stream: standard practice is to store the ten-minute averages, so how many months of raw telemetry you keep is your call. On Tiger Cloud you can tier it to low-cost object storage before dropping it; a policy window only reads tiered chunks when its include_tiered_data argument says so.
The refresh window and the retention interval are one decision with two knobs. The refresh policy documentation warns that when the window covers data the retention policy has removed, the next refresh of those buckets takes the data out of the aggregate too, and the drop-data guide notes that the aggregate can end up holding NULLs in its place. The control is a single inequality: keep the retention interval longer than the deepest start_offset on any aggregate built on the hypertable. With Shape 2 in place, that is ninety days:
The aggregate now keeps its history after the raw rows are gone. The deliberate opposite is an open-ended start_offset, which the docs present as a choice:
-- The aggregate follows removals from the raw hypertable.
SELECT add_continuous_aggregate_policy('generation_hourly',
start_offset => NULL,
end_offset => INTERVAL '2 hours',
schedule_interval => INTERVAL '1 hour');
What picks between them is whether the aggregate is a durable record or a derived view. Settlement exports, warranty evidence, and power curve submissions are records, so they take the bounded form. A curtailment analysis that should only ever show periods the raw data still covers is a view, so it takes the open-ended one. Paired this way, the economics work: twenty years of settlement-grade hourly and daily rollups on top of a few months of raw telemetry, on a hypertable small enough to stay fast.
Shape 5: Manual refresh for data older than any window
Bringing a site onto the platform means loading its history. Its live feed cut over in June, and the eighteen months before that sit in the plant historian (PI, FactoryTalk, or Ignition's built-in one), exported and loaded over a week with timestamps older than any policy window. No schedule will ever reach that range, so Demystified hands this case to a manual refresh_continuous_aggregate call. After an ordinary load, which records its invalidations as it goes, the plain form is enough:
Buckets that do not fit entirely inside the window are excluded, so bucket-align your bounds to keep the partial ones at each end. The window parameters have to match the type of the aggregate's time bucket expression, and force => true re-does buckets the engine already considers up to date.
Manual refresh runs incrementally in batches, forced refreshes included, each batch in its own transaction with locks released between them. The batch size defaults to ten buckets, buckets_per_batch => 0 gives a single atomic pass, and refresh_newest_first defaults to true. That suits an onboarding backfill, where the recent end is the part someone is waiting on.
The load itself has a setting worth knowing. Bulk-loading eighteen months of readings writes eighteen months of invalidation records, and timescaledb.skip_cagg_invalidation turns that tracking off for the transaction. Because nothing was recorded, an ordinary refresh would treat the loaded buckets as current, so the manual refresh for this backfill has to be a forced one:
BEGIN;
SET LOCAL timescaledb.skip_cagg_invalidation = ON;
COPY site_readings (site_id, ts, power_kw, energy_kwh)
FROM '/data/onboarding/site_47.csv' WITH (FORMAT csv, HEADER true);
COMMIT;
CALL refresh_continuous_aggregate('generation_10min',
TIMESTAMPTZ '2024-12-01 00:00:00+00',
TIMESTAMPTZ '2026-06-01 00:00:00+00',
force => true,
options => '{"buckets_per_batch": 5, "refresh_newest_first": true}'::jsonb);
On Tiger Cloud, where server-side files are unavailable, use psql's \copy inside the same transaction. The forced refresh rewrites the ten-minute level and logs the invalidations its parents need, so the hourly and daily levels follow with plain calls over the same range, in that order. Run all three, and the new site's history is in every rollup before its first settlement cycle.
Picking and combining your shapes
Your data's arrival behavior picks the shape, and what else runs against the hypertable modifies it.
Shape
When to use
How to use
1. Frequent, recent
Operators need closed buckets within the hour, and healthy sites deliver on time
Tight window (start_offset of hours, end_offset of one bucket) on a 15-minute schedule
2. Daily, long-term
Sites flush days of buffered readings that still fall inside a resettlement window
Wide window sized to the resettlement window, run once a day off-peak
3. Hierarchical
You stack ten-minute, hourly, and daily rollups
One policy per level with end_offset stepping upward, Toolkit stats_agg and rollup
4. Keep aggregates, drop raw
Raw telemetry is kept for months and rollups for the contract term
Retention interval longer than the deepest start_offset; open-ended start_offset only for a derived view
5. Manual
One-off history: onboarding a site, a corrected historian extract
skip_cagg_invalidation on the load, then a forced refresh_continuous_aggregate, level by level
The shapes combine, and a working fleet usually runs several. Shapes 1 and 2 share one aggregate as long as their windows meet without crossing. Shape 3 gives each level of a stack its own policy. Shape 4 puts a floor under how far Shape 2 can reach, and Shape 5 runs beside all of them whenever a site comes onboard. Manual refresh never substitutes for a policy, though: a recurring late tail belongs in Shape 2, not in a runbook.
After a change, run the jobs query from the first section again to confirm it took. With Shapes 1 and 2 in place, generation_hourly returns two rows whose offsets meet, and each row still passes the two-bucket rule. If the shape you need involves moving in years of history from another system, the migration team on Tiger Data's Enterprise plan can help plan it. Tuning the numbers inside the wrong shape will not get you to the right one.
Continuous Aggregate Refresh, Demystified: Invalidation, Lookback, and Late-Arriving Data
TimescaleDB continuous aggregates and late-arriving data: invalidation tracking, lookback windows, and the two offsets that prevent stale aggregate buckets.