Continuous Aggregates: Incremental Materialized Views for Time-Series Data

If you run aggregate queries over months of time-series data on standard PostgreSQL, you already know what happens when the dataset gets large. PostgreSQL’s native function, date_trunc, has limitations; for example, you can only aggregate in the units of "hour, day, week, or month". Even grouping in 15 minutes is quite poorly readable. A query that finishes in under a second at 10 million rows can take tens of seconds at 500 million rows. The query isn't wrong. It's doing exactly what PostgreSQL asks: scanning the entire table, recalculating every bucket, from the first timestamp to the last. A continuous aggregate on the same dataset can reduce that to single-digit seconds. A hierarchical continuous aggregate can drop it to sub-second response times. (Exact figures vary by schema, hardware, and query pattern.)

There's no native PostgreSQL mechanism to change this. REFRESH MATERIALIZED VIEW performs a full-table recompute every time it runs. A cron job calling it every hour doesn't make it faster; it just makes it run more often. This results in increased operational overhead: setting up the functionality, testing it, monitoring it, and maintaining it. For growing datasets, this doesn't scale.

Continuous aggregates are TimescaleDB's answer. Let’s explore them below. 

What are continuous aggregates?

A continuous aggregate is a TimescaleDB materialized view that refreshes incrementally over a hypertable. When new data arrives, only the affected time buckets are recalculated. Historical data that hasn't changed is never touched. The result is a pre-computed aggregate store that stays current without full-table rescans. For context on how this compares to standard views, see our PostgreSQL views guide.

Three properties define how they work:

1. Automatic background refresh via add_continuous_aggregate_policy(). No cron jobs, no manual REFRESH calls.

2. Real-time aggregation as an opt-in: pre-computed data combined with the most recent raw data at query time, so results stay current.

3. time_bucket() as the required grouping dimension. This is what makes continuous aggregates time-series-aware rather than a general-purpose incremental view. See the documentation for more details on time_bucket.

Continuous aggregates are a feature of TimescaleDB, the open-source PostgreSQL extension maintained by Tiger Data, and are available in both the self-hosted and managed versions. Tiger Cloud, Tiger Data's fully managed service, handles the underlying infrastructure automatically, so there's no server configuration, extension upgrades, or background worker management to worry about. 

Here's the minimal syntax to create one. This example buckets raw sensor readings into hourly averages, per device:

WITH NO DATA creates the view immediately without waiting for an initial backfill. The refresh policy picks it up from there: 

Once the policy is in place, TimescaleDB refreshes the aggregate automatically on the defined schedule — no manual REFRESH calls needed. 

For the full DDL reference, see the TimescaleDB continuous aggregates documentation.

Continuous aggregates vs. PostgreSQL materialized views

Developers who know PostgreSQL materialized views will immediately ask why a standard CREATE MATERIALIZED VIEW plus a cron job doesn't work. The answer is in how refresh works.

Historical data rarely changes. Only recent data does. A full-table refresh on a two-year sensor dataset to incorporate five minutes of new readings wastes significant compute. Continuous aggregates track a materialization watermark, a timestamp representing the upper bound of what has been pre-computed, and only recalculate the time buckets where the underlying data has changed.

One trade-off: continuous aggregates require TimescaleDB. They're not a vanilla PostgreSQL feature. If you're not using TimescaleDB, a scheduled REFRESH MATERIALIZED VIEW is still your best option. For a deeper look at how continuous aggregates compare to materialized views and when to use each approach, see our guide on the topic.

How continuous aggregates work

TimescaleDB tracks a materialization watermark per continuous aggregate: a timestamp representing the upper bound of what has been pre-computed. Data older than the watermark is served from the pre-computed store. Data newer than the watermark can be computed from the raw hypertable chunks at query time when real-time aggregation is enabled.

You configure when and how often the background job refreshes via add_continuous_aggregate_policy():

• start_offset: how far back in time to look for data to refresh

• end_offset: a buffer before the current time, to avoid refreshing chunks that may still be receiving writes

• schedule_interval: how frequently the background job runs

The policy runs automatically. You do not trigger it manually.

Real-time aggregation behavior is the most common point of confusion. Tiger Docs is the source of truth here:

• In TimescaleDB v2.13 and later, real-time aggregates are disabled by default. Queries return only pre-computed data up to the materialization watermark. Data newer than the watermark is not visible in the aggregate until the next refresh.

• To enable real-time aggregation, set materialized_only = false. With real-time on, queries combine pre-computed data for older time ranges with on-the-fly computation for data newer than the watermark. The result is always current.

• Use materialized_only = true (the v2.13+ default) when you need deterministic, time-bounded results. Use materialized_only = false when dashboard freshness matters more than strict consistency.

For the full implementation walkthrough, see create a continuous aggregate in the docs.

Downsampling time-series data with continuous aggregates

As data ages, per-second granularity becomes unnecessary and expensive to store. A monitoring system needs 1-second metrics for the last hour, 1-minute rollups for the last week, and hourly averages for the last year. Continuous aggregates handle this pattern natively.

Hierarchical continuous aggregates are the mechanism: a continuous aggregate built on top of another continuous aggregate rather than directly on the raw hypertable. A 15-minute CAGG feeds a 4-hour CAGG, which feeds a daily CAGG. Each tier refreshes from the tier below, not from the full raw dataset. The performance difference is real: queries that scan raw data at hundreds of millions of rows can go from tens of seconds on the raw table to single-digit seconds on a single-tier CAGG, and to sub-second on a hierarchical CAGG at the appropriate granularity. Exact numbers vary by schema and hardware.

Tiger Data has published examples showing continuous aggregate tables with columnstore compression achieving 80%+ storage reduction compared to the raw hypertable. Verify the specific figures for your schema in the docs.

Pair this with data retention policies and you get a full data lifecycle: keep raw high-frequency data for 30 days, hourly rollups for one year, daily aggregates indefinitely. For more on how retention policies fit this pattern, see our guide on data retention policies.

Hierarchical downsampling is built into TimescaleDB, not a separate feature or API. The Tiger Data docs on hierarchical continuous aggregates have worked examples.

When to use continuous aggregates

Continuous aggregates are a strong fit for these workloads:

Real-time monitoring and observability dashboards. Grafana panels and similar tools run aggregate queries over rolling time windows, often on refresh intervals measured in seconds. Pre-computing the aggregates keeps dashboards fast as datasets grow into the hundreds of millions of rows. This is one of the strongest arguments for using PostgreSQL as a real-time analytics database: the same database that ingests your data serves fast, pre-computed aggregate results without a separate OLAP layer. The FlightAware engineering team found that continuous aggregates dropped one of their query times from 6.4 seconds to 30 milliseconds.

Financial data: OHLC candlesticks from tick data. Generating open/high/low/close values per time interval is a canonical continuous aggregate use case. Continuous aggregates handle the aggregation continuously; queries read the pre-computed result.

IoT and sensor rollups. Per-device or per-site averages, min, and max at configurable intervals from high-frequency raw data. This covers industrial telemetry, energy monitoring, and logistics tracking.

Long-term trend analysis. Pre-computing daily or weekly aggregates over two or more years of data for historical analytics workloads. Without continuous aggregates, these queries scan the full raw table on every execution.

Dashboard query load reduction. If expensive GROUP BY queries are hitting your raw hypertable on every dashboard refresh, continuous aggregates move that work to a background job. Query the pre-computed result instead.

On the competitive landscape: continuous aggregates are a TimescaleDB-specific feature in the managed PostgreSQL ecosystem. InfluxDB 3.0 does not have native continuous aggregates. Earlier InfluxDB versions (1.x) had "Continuous Queries," which were deprecated in InfluxDB 2.x and are not present in InfluxDB 3.0. QuestDB does not support automatic incremental rollups or continuously-refreshed materialized views; it offers query-time downsampling via SAMPLE BY, but no built-in mechanism for pre-computing and auto-refreshing aggregate results. CrateDB has no equivalent feature.

For a broader comparison, see the best time-series databases compared.

Limitations to know

Window functions. Window functions inside a continuous aggregate view definition are experimental as of TimescaleDB v2.20.0 (disabled by default; enable with timescaledb.enable_cagg_window_functions). In earlier versions, window functions inside a CAGG definition are not supported. The workaround: define the continuous aggregate using standard aggregates, then apply window functions at query time against the CAGG result.

time_bucket() is required. The primary grouping dimension must be a time_bucket() call on the time column. You cannot use an arbitrary non-time GROUP BY as the primary bucket. Additional grouping dimensions (device_id, symbol, etc.) are supported as secondary dimensions alongside time_bucket().

Join support. As of TimescaleDB v2.10+, inner joins between a hypertable and a non-hypertable (a regular PostgreSQL table) are supported inside a continuous aggregate definition. As of v2.16+, left joins and lateral joins are also supported. Joins between two hypertables remain unsupported. If your continuous aggregate query needs to join two time-series tables, this is a current hard limit.

Hypertable requirement. The source table must be a TimescaleDB hypertable. Continuous aggregates cannot be created over standard PostgreSQL tables.

Most time-series workloads (monitoring, IoT, financial data) aren't affected by the window function or join limitations. For the authoritative, version-specific list, see the TimescaleDB continuous aggregates documentation.

For related context on PostgreSQL aggregation best practices and when standard aggregation is sufficient, that guide covers the decision points in more depth.

Getting started with continuous aggregates on Tiger Cloud

Tiger Cloud includes a built-in console with guided UX for creating and managing continuous aggregates - no SQL required to get started. Refresh policies, compression, and retention are all configurable from the UI, and the console provides built-in visualizations so you can inspect aggregate output directly.

For teams with existing observability stacks, Tiger Cloud ships with metrics exporters that let you pipe database and policy performance data into Prometheus, Grafana, or whatever tooling you already use.

To get hands-on, follow the create a continuous aggregate guide in the docs. To try Tiger Cloud, start a free trial. No credit card required.