Continuous Aggregates vs Materialized Views

Scope: one decision, how roll-ups stay current

This article is for developers and data engineers who already keep raw sensor readings in SQL and now need fast trend charts, energy reports or KPI tiles. The decision it covers is narrow: once you have decided to pre-aggregate, which mechanism keeps the roll-up correct as new and late readings arrive, and what each one costs to run. It does not re-explain hypertables or retention policies; for those see TimescaleDB explained and retention and downsampling design. For the wider PostgreSQL versus TimescaleDB choice, see TimescaleDB vs PostgreSQL.

The running example is a central plant: chiller power (kW) for several chillers, read every minute by the BMS gateway, published over MQTT and written in batches to a chiller_kw table. Operators want hourly averages and peaks per chiller; facilities managers want daily totals; maintenance planners in the CMMS want peak-demand hours next to the chiller's open work orders.

Three refresh models

Full recompute (PostgreSQL materialized view). The view stores the output of its query. REFRESH MATERIALIZED VIEW re-runs the whole query and replaces the contents. Without CONCURRENTLY, readers are blocked while it runs; with it, readers continue, but the option requires at least one unique index on plain columns. Either way, cost grows with the size of the source table, not with the size of the change.

Invalidation and refresh window (TimescaleDB continuous aggregate). The aggregate is a materialization hypertable plus an invalidation engine. Inserts with recent timestamps, beyond the invalidation threshold, need no extra bookkeeping; when older data is changed, each transaction logs the minimum and maximum timestamps it touched, and the next refresh recomputes only the complete buckets affected. A policy decides when to refresh and which window (start_offset to end_offset) to look at. Since TimescaleDB 2.13 real-time aggregation, which adds not-yet-materialized raw rows at query time, is off by default.

Insert-driven (QuestDB, ClickHouse). QuestDB materialized views are defined with SAMPLE BY over a base table and process only new rows as they arrive. A ClickHouse materialized view is described in its documentation as a trigger that runs a query on blocks of data as they are inserted, writing the result to a target table. In both, only the base table (in ClickHouse, the left-most table in the query) triggers updates.

Design decisions before you choose

• Bucket width and alignment. Pick the widths users read (15 minutes, 1 hour, 1 day) and one origin for all of them, for example date_bin(interval '1 hour', ts, '2000-01-01') in PostgreSQL or time_bucket in TimescaleDB. Mixing time zones between tiers is a common source of daily totals that do not add up.
• What to store. Store sum, count, min and max; derive the average as sum / count. An average of averages is wrong when buckets hold different numbers of samples, which the Tiger Data hierarchical aggregates page also warns about; the downsampling article shows the difference on real output.
• How late is late. BMS gateways that buffer through a network outage can deliver readings hours later. The refresh window (or the recompute window of your own job) must reach back at least that far, or late readings reach the roll-up only through a later reconciliation, if at all. See late-arriving and out-of-order events.
• Freshness target. A TimescaleDB policy that runs hourly and excludes the latest hour can take up to two hours to reflect a new reading, according to the refresh policy documentation. If a control-room tile must be current to the minute, either enable real-time aggregation or read the latest raw values directly.
• Interaction with retention. When raw data is dropped inside a refresh window, the next refresh can remove the aggregated rows too. Decide raw retention and refresh window together.

Recommended in plain PostgreSQL: recompute a recent window on every run

Without TimescaleDB, the simplest roll-up whose correctness rests only on documented PostgreSQL behaviour is a summary table that each scheduled run recomputes for a bounded window of recent buckets, from the raw rows committed at that moment, with an idempotent upsert. Nothing records which buckets changed, so there is no marker for a concurrent writer to lose. Two documented behaviours carry the argument. Under the default READ COMMITTED isolation, a query sees only data committed before it began, so a reading committed after one run started is seen by every later run. And INSERT ... ON CONFLICT DO UPDATE guarantees an atomic insert-or-update outcome even under concurrency. A late reading is therefore included by the first run that starts after it commits, provided its bucket is still inside that run's window. Buckets older than the window are repaired by a periodic reconciliation over a longer, closed range.

The script below was run in a single session on PostgreSQL 18.6 (PGlite). Concurrent sessions cannot be run here, so the timeline is simulated: run 1 at 09:05, late readings committed after it, run 2 at 09:35. The 3-hour window and the run times are illustrative assumptions.

SET TIME ZONE 'UTC';
CREATE TABLE chiller_kw (
    point_id  integer          NOT NULL,
    ts        timestamptz      NOT NULL,
    kw        double precision NOT NULL,
    PRIMARY KEY (point_id, ts)
);
INSERT INTO chiller_kw
SELECT p, t, 100 * p + extract(minute FROM t)
FROM generate_series(1, 2) AS p,
     generate_series(timestamptz '2026-10-27 06:00+00',
                     timestamptz '2026-10-27 08:59+00', interval '1 minute') AS t;
CREATE MATERIALIZED VIEW chiller_kw_hourly AS          -- used only for the cross-check
SELECT point_id, date_bin(interval '1 hour', ts, timestamptz '2000-01-01') AS bucket,
       sum(kw) AS sum_kw, count(*) AS samples, min(kw) AS min_kw, max(kw) AS max_kw
FROM chiller_kw GROUP BY 1, 2;
CREATE UNIQUE INDEX ON chiller_kw_hourly (point_id, bucket);

CREATE TABLE chiller_kw_1h (
    point_id integer NOT NULL, bucket timestamptz NOT NULL,
    sum_kw double precision NOT NULL, samples integer NOT NULL,
    min_kw double precision NOT NULL, max_kw double precision NOT NULL,
    PRIMARY KEY (point_id, bucket)
);

-- Recompute every bucket from p_from (rounded down to the hour) to p_to from committed raw rows
CREATE PROCEDURE recompute_chiller_kw_1h(p_from timestamptz, p_to timestamptz DEFAULT 'infinity')
LANGUAGE sql AS $$
  INSERT INTO chiller_kw_1h (point_id, bucket, sum_kw, samples, min_kw, max_kw)
  SELECT point_id, date_bin(interval '1 hour', ts, timestamptz '2000-01-01'),
         sum(kw), count(*), min(kw), max(kw)
  FROM chiller_kw
  WHERE ts >= date_bin(interval '1 hour', p_from, timestamptz '2000-01-01') AND ts < p_to
  GROUP BY 1, 2
  ON CONFLICT (point_id, bucket) DO UPDATE
  SET sum_kw = EXCLUDED.sum_kw, samples = EXCLUDED.samples,
      min_kw = EXCLUDED.min_kw, max_kw = EXCLUDED.max_kw;
  -- buckets in the range whose raw rows have all been deleted
  DELETE FROM chiller_kw_1h r
  WHERE r.bucket >= date_bin(interval '1 hour', p_from, timestamptz '2000-01-01')
    AND r.bucket < p_to
    AND NOT EXISTS (SELECT 1 FROM chiller_kw c
                    WHERE c.point_id = r.point_id
                      AND c.ts >= r.bucket AND c.ts < r.bucket + interval '1 hour');
$$;

-- Run 1 at 09:05 with an illustrative 3-hour window (in a real job: now() - interval '3 hours')
CALL recompute_chiller_kw_1h(timestamptz '2026-10-27 09:05+00' - interval '3 hours');

-- After run 1, a gateway's late readings commit: two for 06:xx and one at 09:00
INSERT INTO chiller_kw VALUES (1, '2026-10-27 06:30:30+00', 500),
                              (1, '2026-10-27 06:31:30+00', 500),
                              (2, '2026-10-27 09:00+00', 200);
SELECT point_id, bucket, samples FROM chiller_kw_1h WHERE point_id = 1 AND bucket = '2026-10-27 06:00+00';

-- Run 2 at 09:35: 06:00 is still inside the window, so the late rows are included
CALL recompute_chiller_kw_1h(timestamptz '2026-10-27 09:35+00' - interval '3 hours');
SELECT point_id, bucket, samples, round((sum_kw / samples)::numeric, 2) AS avg_kw, max_kw
FROM chiller_kw_1h ORDER BY point_id, bucket;

-- A reading for 06:45 commits after the 06:00 bucket has left the window
INSERT INTO chiller_kw VALUES (2, '2026-10-27 06:45:30+00', 900);
CALL recompute_chiller_kw_1h(timestamptz '2026-10-27 10:05+00' - interval '3 hours');
SELECT point_id, bucket, samples FROM chiller_kw_1h WHERE point_id = 2 AND bucket = '2026-10-27 06:00+00';

-- Periodic reconciliation over a longer closed range (illustrative: the whole example day)
CALL recompute_chiller_kw_1h(timestamptz '2026-10-27 00:00+00', timestamptz '2026-10-28 00:00+00');
SELECT point_id, bucket, samples FROM chiller_kw_1h WHERE point_id = 2 AND bucket = '2026-10-27 06:00+00';

-- Cross-check against a full recompute
REFRESH MATERIALIZED VIEW CONCURRENTLY chiller_kw_hourly;
SELECT count(*) AS mismatches
FROM chiller_kw_hourly m FULL JOIN chiller_kw_1h r USING (point_id, bucket)
WHERE m.samples IS DISTINCT FROM r.samples OR m.sum_kw IS DISTINCT FROM r.sum_kw;

-- chiller 1 at 06:00 after run 1: the late rows committed after it
 point_id |         bucket         | samples
----------+------------------------+---------
        1 | 2026-10-27 06:00:00+00 |      60

-- chiller_kw_1h after run 2
 point_id |         bucket         | samples | avg_kw | max_kw
----------+------------------------+---------+--------+--------
        1 | 2026-10-27 06:00:00+00 |      62 | 141.45 |    500
        1 | 2026-10-27 07:00:00+00 |      60 | 129.50 |    159
        1 | 2026-10-27 08:00:00+00 |      60 | 129.50 |    159
        2 | 2026-10-27 06:00:00+00 |      60 | 229.50 |    259
        2 | 2026-10-27 07:00:00+00 |      60 | 229.50 |    259
        2 | 2026-10-27 08:00:00+00 |      60 | 229.50 |    259
        2 | 2026-10-27 09:00:00+00 |       1 | 200.00 |    200

-- chiller 2 at 06:00 after the 10:05 run (bucket outside the window), then after reconciliation
 samples = 60   (the 06:45:30 reading is not yet included)
 samples = 61   (after the reconciliation run)

 mismatches
------------
          0

What the run shows, and the limits that remain

The two late readings for chiller 1 committed after run 1, so run 1 could not include them; run 2 recomputed 06:00 because it was still inside the window and reached 62 samples, the same figures a full refresh of the materialized view produces. The reading for 06:45 committed when 06:00 had already left the 3-hour window, so the next run did not touch it; only the reconciliation over the whole day picked it up. That is the trade the pattern makes: anything later than the window waits for the reconciliation.

• Window length. A reading is caught by the regular runs only if its bucket is still inside the window when the first run after its commit starts. So the window must be at least the longest expected gap between a reading's event time and its commit (gateway buffering plus the longest write transaction, since a row becomes visible only at commit) plus the interval between runs. The 3 hours here is illustrative; derive yours from measured lateness (see late-arriving and out-of-order events) and enforce an upper bound on writer transactions if you rely on one.
• Reconciliation. Run it over closed periods (for example the previous day or week) on a slower schedule, and keep its range clear of raw data removed by retention: the DELETE step removes roll-up rows whose raw rows are gone, which is right for corrections but would erase history over dropped partitions. The DELETE step had nothing to remove in this run.
• Cost. Every run re-reads the whole window, so cost grows with window length times data rate, not with the number of late rows. Keep the window as short as your lateness allows and make sure the raw table's key or index serves the time range.
• Overlapping runs. Correctness does not depend on locks, but two overlapping runs waste work, and the one that commits last writes its own snapshot's figures, which the next run corrects while the bucket is still inside the window. Let the scheduler run one at a time.
• Freshness. Figures are as of the last run; a dashboard that needs the current minute should read recent raw rows.

Other routes that rely only on documented behaviour: REFRESH MATERIALIZED VIEW CONCURRENTLY, which recomputes the whole view from committed data and keeps readers unblocked (practical while the full query stays cheap enough), or TimescaleDB continuous aggregates, whose invalidation and refresh are part of the product and are described from its documentation below.

Educational example: a trigger-maintained incremental roll-up

Educational example: shows the idea in a single session; not safe for production as shown, because of the race described below. Use the window recompute above for production work. The script below builds both options on the chiller data: a materialized view refreshed in full, and an incremental roll-up table maintained from an invalidation table. A statement-level trigger records which (chiller, hour) buckets each insert touched; a refresh procedure claims those buckets with DELETE ... RETURNING and recomputes only them with an upsert, in one statement. The script was run in a single session only; how the pattern behaves with concurrent writers is analysed, not demonstrated, in the section after the output.

SET TIME ZONE 'UTC';
CREATE TABLE chiller_kw (
    point_id  integer          NOT NULL,
    ts        timestamptz      NOT NULL,
    kw        double precision NOT NULL,
    PRIMARY KEY (point_id, ts)
);
-- Two chillers, one reading a minute, 06:00 to 08:59
INSERT INTO chiller_kw
SELECT p, t, 100 * p + extract(minute FROM t)
FROM generate_series(1, 2) AS p,
     generate_series(timestamptz '2026-10-27 06:00+00',
                     timestamptz '2026-10-27 08:59+00', interval '1 minute') AS t;

-- Option A: an ordinary materialized view
CREATE MATERIALIZED VIEW chiller_kw_hourly AS
SELECT point_id, date_bin(interval '1 hour', ts, timestamptz '2000-01-01') AS bucket,
       sum(kw) AS sum_kw, count(*) AS samples, min(kw) AS min_kw, max(kw) AS max_kw
FROM chiller_kw GROUP BY 1, 2;
REFRESH MATERIALIZED VIEW CONCURRENTLY chiller_kw_hourly;   -- fails: no unique index yet
CREATE UNIQUE INDEX ON chiller_kw_hourly (point_id, bucket);
REFRESH MATERIALIZED VIEW CONCURRENTLY chiller_kw_hourly;   -- now allowed

-- Option B: a roll-up table maintained incrementally
CREATE TABLE chiller_kw_1h (
    point_id integer NOT NULL, bucket timestamptz NOT NULL,
    sum_kw double precision NOT NULL, samples integer NOT NULL,
    min_kw double precision NOT NULL, max_kw double precision NOT NULL,
    PRIMARY KEY (point_id, bucket)
);
CREATE TABLE rollup_invalidation (
    point_id integer NOT NULL, bucket timestamptz NOT NULL,
    PRIMARY KEY (point_id, bucket)
);
CREATE FUNCTION log_invalidation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO rollup_invalidation
  SELECT DISTINCT point_id, date_bin(interval '1 hour', ts, timestamptz '2000-01-01') FROM changed
  ON CONFLICT DO NOTHING;
  RETURN NULL;
END $$;
CREATE TRIGGER chiller_kw_ins AFTER INSERT ON chiller_kw
REFERENCING NEW TABLE AS changed FOR EACH STATEMENT EXECUTE FUNCTION log_invalidation();

CREATE PROCEDURE refresh_chiller_kw_1h() LANGUAGE sql AS $$
  WITH claimed AS (
      DELETE FROM rollup_invalidation RETURNING point_id, bucket
  )
  INSERT INTO chiller_kw_1h (point_id, bucket, sum_kw, samples, min_kw, max_kw)
  SELECT c.point_id, cl.bucket, sum(c.kw), count(*), min(c.kw), max(c.kw)
  FROM claimed cl
  JOIN chiller_kw c ON c.point_id = cl.point_id
                   AND c.ts >= cl.bucket AND c.ts < cl.bucket + interval '1 hour'
  GROUP BY c.point_id, cl.bucket
  ON CONFLICT (point_id, bucket) DO UPDATE
  SET sum_kw = EXCLUDED.sum_kw, samples = EXCLUDED.samples,
      min_kw = EXCLUDED.min_kw, max_kw = EXCLUDED.max_kw;
$$;

-- Initial load: mark every existing bucket once, then refresh
INSERT INTO rollup_invalidation
SELECT DISTINCT point_id, date_bin(interval '1 hour', ts, timestamptz '2000-01-01') FROM chiller_kw;
CALL refresh_chiller_kw_1h();
SELECT count(*) AS buckets FROM chiller_kw_1h;

-- A gateway reconnects: two late readings for 06:xx, one new reading at 09:00
INSERT INTO chiller_kw VALUES (1, '2026-10-27 06:30:30+00', 500),
                              (1, '2026-10-27 06:31:30+00', 500),
                              (2, '2026-10-27 09:00+00', 200);
SELECT point_id, bucket FROM rollup_invalidation ORDER BY 1, 2;
CALL refresh_chiller_kw_1h();
SELECT point_id, bucket, samples, round((sum_kw / samples)::numeric, 2) AS avg_kw, max_kw
FROM chiller_kw_1h ORDER BY point_id, bucket;

-- The materialized view is stale until the next full refresh
SELECT point_id, bucket, samples FROM chiller_kw_hourly
WHERE point_id = 1 AND bucket = '2026-10-27 06:00+00';
REFRESH MATERIALIZED VIEW CONCURRENTLY chiller_kw_hourly;
SELECT point_id, bucket, samples FROM chiller_kw_hourly
WHERE point_id = 1 AND bucket = '2026-10-27 06:00+00';

-- Cross-check: both roll-ups agree
SELECT count(*) AS mismatches
FROM chiller_kw_hourly m FULL JOIN chiller_kw_1h r USING (point_id, bucket)
WHERE m.samples IS DISTINCT FROM r.samples OR m.sum_kw IS DISTINCT FROM r.sum_kw;

REFRESH MATERIALIZED VIEW CONCURRENTLY chiller_kw_hourly;   -- first attempt
ERROR:  cannot refresh materialized view "public.chiller_kw_hourly" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
SQLSTATE 55000

 buckets
---------
       6

-- rollup_invalidation after the late insert
 point_id |         bucket
----------+------------------------
        1 | 2026-10-27 06:00:00+00
        2 | 2026-10-27 09:00:00+00

-- chiller_kw_1h after the incremental refresh
 point_id |         bucket         | samples | avg_kw | max_kw
----------+------------------------+---------+--------+--------
        1 | 2026-10-27 06:00:00+00 |      62 | 141.45 |    500
        1 | 2026-10-27 07:00:00+00 |      60 | 129.50 |    159
        1 | 2026-10-27 08:00:00+00 |      60 | 129.50 |    159
        2 | 2026-10-27 06:00:00+00 |      60 | 229.50 |    259
        2 | 2026-10-27 07:00:00+00 |      60 | 229.50 |    259
        2 | 2026-10-27 08:00:00+00 |      60 | 229.50 |    259
        2 | 2026-10-27 09:00:00+00 |       1 | 200.00 |    200

-- chiller_kw_hourly, chiller 1 at 06:00: before and after the full refresh
 samples = 60   (stale)
 samples = 62   (after REFRESH ... CONCURRENTLY)

 mismatches
------------
          0

What the run shows, and what this pattern does not cover

The late insert invalidated exactly two buckets, and the refresh recomputed those two while the other five rows were left alone. The materialized view reached the same numbers only after recomputing all seven buckets. On three hours of data the difference is irrelevant; on two years of one-minute readings it is the difference between a refresh that touches a few thousand rows and one that scans the whole table.

The pattern is deliberately small. It handles inserts only: corrections by UPDATE or DELETE need equivalent triggers that log both old and new buckets. Every insert statement writes to the invalidation table, which TimescaleDB avoids for recent data with its invalidation threshold. If the raw table is partitioned, create the trigger on the partitioned parent; in a separate run on PostgreSQL 18.6, the same statement-level trigger with a transition table, created on a range-partitioned parent, logged the buckets of rows routed to its partition. As shown, the design also has the race described next, which is why it stays an educational example on this page.

Concurrent writers: known races (not verified here)

The run above used one session, so it says nothing about concurrent writers, and the probes used for this site cannot open a second PostgreSQL session. The following is an analysis of the design under PostgreSQL's default READ COMMITTED isolation, where each statement sees a snapshot of the data committed before it began. Treat it as a review checklist, not a verified result.

• A marker that already exists. Writer W inserts readings for chiller 1 at 06:xx while a committed marker for (1, 06:00) is already in rollup_invalidation. Its trigger's ON CONFLICT DO NOTHING finds that committed row and inserts nothing. If the refresh statement starts before W commits, it deletes the marker and recomputes 06:00 from a snapshot that does not contain W's rows. When W commits, no marker is left, so the 06:00 bucket stays stale until another insert touches it. This is the important race: a "claim with DELETE ... RETURNING" alone does not prevent it.
• A new marker. If W's marker did not exist before, it is an uncommitted row that the refresh cannot see or delete, so it survives for the next refresh; this case is safe by the same snapshot rule.
• Two refreshes at once. The documented READ COMMITTED behaviour is that a second DELETE waits for the first and then skips rows the first deleted, so each marker should be recomputed once; both refreshes still upsert into chiller_kw_1h and can wait on each other. Simpler to make sure only one runs.

Documented options, not verified here. These are listed for readers who want to study the design further; the recommended path remains the window recompute above, whose correctness does not depend on locking that could not be exercised here. Take a table lock on the raw table in the refresh transaction before claiming markers. SHARE ROW EXCLUSIVE conflicts with the ROW EXCLUSIVE lock that every INSERT takes, so the refresh waits until in-flight writer transactions have committed (their markers and readings are then visible to the next statement) and holds new inserts back until it commits; the mode is also self-exclusive, so two refreshes cannot overlap. The cost is that ingestion pauses for the length of each refresh, so keep refreshes short. If you only need to stop refreshes overlapping, a transaction-level advisory lock (pg_advisory_xact_lock) is enough, but it does not fix the first race. As a backstop, a periodic recompute of recently closed periods (for example the previous day) repairs any bucket that was missed; how far back to go is a judgement for your data.

BEGIN;
LOCK TABLE chiller_kw IN SHARE ROW EXCLUSIVE MODE;  -- waits for writers in flight, blocks new ones
CALL refresh_chiller_kw_1h();
COMMIT;

The same roll-up as a continuous aggregate

In TimescaleDB the invalidation log, the refresh procedure and the scheduler are built in. The aggregate must group by a time_bucket on the hypertable's time column, and functions in SELECT, GROUP BY and HAVING must be immutable. Creating it WITH NO DATA avoids materializing all history inside the CREATE; you then backfill with a manual refresh over a bounded range. The documentation advises not refreshing the latest incomplete bucket, which is what end_offset achieves in a policy. The definition of the hourly view itself is on the TimescaleDB page; the statements below show the operational side.

-- chiller_kw is a hypertable; the view was created WITH NO DATA
CALL refresh_continuous_aggregate('chiller_kw_hourly', '2026-01-01', '2026-10-27');

-- Illustrative values: refresh the last 3 days every 30 minutes, leaving the current hour alone.
-- Choose start_offset from your worst expected data delay and schedule_interval from the freshness you need.
SELECT add_continuous_aggregate_policy('chiller_kw_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

-- Include not-yet-materialized raw rows at query time (off by default since 2.13)
ALTER MATERIALIZED VIEW chiller_kw_hourly SET (timescaledb.materialized_only = false);

-- Daily tier on top of the hourly one: the bucket must be a multiple of 1 hour
CREATE MATERIALIZED VIEW chiller_kw_daily
WITH (timescaledb.continuous) AS
SELECT point_id,
       time_bucket(INTERVAL '1 day', bucket) AS day,
       sum(sum_kw) / sum(samples) AS avg_kw,   -- from sum and count, not avg(avg)
       max(max_kw) AS max_kw
FROM chiller_kw_hourly
GROUP BY point_id, day
WITH NO DATA;

Trade-offs and limitations

Licence note. Tiger Data's editions page says TimescaleDB Community Edition is "available under the terms of the Tiger Data License (TSL)", and its feature table lists continuous aggregates and their refresh policies for Community Edition only, not for the Apache 2 Edition. Tiger Data's licenses page names it the Timescale License and links the licence text. What the licence permits in your situation is not something this page decides: this is not legal advice; read the licence and take advice for your situation.

Two limitations catch people out. First, a continuous aggregate tracks changes to the hypertable only: if it joins to a standard PostgreSQL table such as a point register, edits to that table are not tracked and old buckets keep the old values. Second, insert-driven views in QuestDB and ClickHouse have the same property for joined tables, and ClickHouse targets that use merge-based engines may hold several rows per key until a background merge, so queries should aggregate again rather than assume one row per bucket.

When to use which, and when to use neither

• Materialized view: small or slow-changing sources (a few hundred meters at 15-minute intervals), reports refreshed nightly, or complex SQL that the other mechanisms do not allow. Add a unique index so you can refresh CONCURRENTLY.
• Continuous aggregate: you already run TimescaleDB Community Edition (check its licence terms for your use), sources are large, late data is bounded, and you want the refresh and scheduling handled for you.
• Insert-driven views: the data already lives in QuestDB or ClickHouse and corrections are rare or written as new rows.
• Roll-up table with a window recompute: plain PostgreSQL, the source has outgrown full refreshes, lateness is bounded, and you can own and monitor the job. Treat the trigger-based design on this page as a learning example, not a production pattern.
• Neither: the query is already fast on raw data with the right index (see what drives query speed), the roll-up is needed once (run it as a report), or the numbers must be exact to the latest second, in which case query the raw table or enable real-time aggregation.

Operations and security

• Privileges. In PostgreSQL 18, refreshing a materialized view needs the MAINTAIN privilege on it; give that to the job role, not to dashboard users.
• Monitoring. For continuous aggregates, watch job failures and how far the materialized data lags behind the raw data. For materialized views, record refresh duration; a steadily growing duration is the signal to move to an incremental design.
• Backfills. After loading historical data, refresh the affected range explicitly; with TimescaleDB, the documentation warns that refreshing very large ranges can affect performance and interact with retention.
• Testing changes. Before replacing a full-refresh view with an incremental one, run both side by side and compare them, as the cross-check query above does.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Materialized Views
• PostgreSQL 18 documentation: REFRESH MATERIALIZED VIEW
• PostgreSQL 18 documentation: CREATE MATERIALIZED VIEW
• Tiger Data docs: Understand continuous aggregates
• Tiger Data docs: Create a continuous aggregate
• Tiger Data docs: Refresh continuous aggregates
• Tiger Data docs: Real-time aggregates
• Tiger Data docs: Hierarchical continuous aggregates
• QuestDB docs: Materialized views
• ClickHouse docs: Incremental materialized view
• ClickHouse docs: CREATE VIEW
• QuestDB docs: REFRESH MATERIALIZED VIEW
• QuestDB docs: Out-of-order data
• PostgreSQL 18 documentation: Transaction Isolation
• PostgreSQL 18 documentation: Explicit Locking
• PostgreSQL 18 documentation: System Administration Functions (advisory locks)
• PostgreSQL 18 documentation: INSERT (ON CONFLICT)
• Tiger Data docs: Compare TimescaleDB editions
• Tiger Data: Licenses

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.