0Pricing
SQL Academy · Lesson

Continuous Aggregates

Pre-aggregate time-series data with CONTINUOUS AGGREGATE materialised views and incremental refresh.

Continuous Aggregates is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Continuous Aggregate?

A pre-computed, incrementally-refreshed aggregate over a hypertable — like a materialised view that updates intelligently as new data arrives.

Creating One

Hourly device averages:

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT device_id,
       time_bucket('1 hour', ts) AS hour,
       AVG(value) AS avg_value,
       COUNT(*)   AS samples
FROM metrics
GROUP BY device_id, hour;

Refresh Policy

TimescaleDB auto-refreshes:

SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset => INTERVAL '1 day',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '15 minutes');
-- Every 15 minutes, refresh the window from 1 day ago to 1 hour ago.

Querying

Just SELECT — TimescaleDB merges materialised data with raw data for the most recent unmaterialised period:

SELECT * FROM metrics_hourly
WHERE device_id = 42
  AND hour >= NOW() - INTERVAL '7 days'
ORDER BY hour;

Real-Time Aggregation

By default, continuous aggregates show pre-computed data PLUS raw data for the unmaterialised tail. You always see the latest values, just at the cost of computing the tail on-the-fly.

Materialised-Only Mode

For fully-precomputed dashboards (no on-the-fly computation), turn off real-time aggregation:

ALTER MATERIALIZED VIEW metrics_hourly
  SET (timescaledb.materialized_only = true);

Multi-Tier Aggregation

Build hourly → daily → monthly:

-- daily aggregate from the hourly cagg
CREATE MATERIALIZED VIEW metrics_daily
WITH (timescaledb.continuous) AS
SELECT device_id, time_bucket('1 day', hour) AS day,
       AVG(avg_value) AS avg_value
FROM metrics_hourly
GROUP BY device_id, day;

Compressing the Hypertable Doesn't Block Aggregation

You can compress the underlying hypertable; continuous aggregates keep working.

When Continuous Aggregates Beat Materialized Views

  • Time-series data: built-in time semantics
  • Incremental refresh — never re-scan the entire history
  • Real-time tail integration
  • Multi-tier rollups

Compatibility

Continuous aggregates work on TimescaleDB hypertables. For non-TS Postgres, you have to roll your own materialised view + refresh logic.

Backfilling

Refresh historical data manually if you change the aggregate definition:

CALL refresh_continuous_aggregate('metrics_hourly',
  NULL, NOW());
-- Recompute from the beginning of time.

Recap

Continuous aggregates are time-series materialised views.

  • Defined with WITH (timescaledb.continuous)
  • Auto-refresh policy
  • Real-time + materialised blend
  • Stack daily/weekly on top of hourly

Quick Check

What makes a TimescaleDB continuous aggregate refresh efficient compared to REFRESH MATERIALIZED VIEW?

Frequently asked questions

Is the “Continuous Aggregates” lesson free?

Yes — the full text of “Continuous Aggregates” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Continuous Aggregates”?

Pre-aggregate time-series data with CONTINUOUS AGGREGATE materialised views and incremental refresh. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Continuous Aggregates” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. TimescaleDB Hypertables
  2. Continuous Aggregates
  3. Compression and Retention Policies
  4. Time-Series Index Choices
← Back to SQL Academy