Incremental Summary Tables Instead of Full Refresh #

A daily revenue matview aggregates three years of orders. It refreshes every ten minutes, each refresh reads 400 million rows and takes six minutes, and 99.9% of the result — every day except today — is identical to the previous run. The dashboard it serves needs the current day to be fresh within minutes.

Materialized views have no built-in incremental refresh, as explained in materialized view strategy. When most of the result never changes, a plain table maintained incrementally is both cheaper and fresher.

The Condition #

REFRESH MATERIALIZED VIEW re-executes the defining query in full. There is no mechanism to recompute only the rows affected by recent changes; even CONCURRENTLY computes the whole new result and then diffs it, as analysed in REFRESH CONCURRENTLY cost analysis.

An incremental summary table replaces that with an ordinary table and a job that updates only what changed. Two shapes cover most cases:

The window recompute is the safer default: it is idempotent, easy to backfill, and its correctness does not depend on triggers firing for every path (including COPY, bulk loads and manual fixes).

Both need the base table to record enough to identify what changed — a timestamp, an ingestion id, or a change log. Late-arriving data determines how wide the recompute window must be.

Recompute everything, or only what changed Left, the materialized view refresh reads all 400 million order rows every ten minutes and rewrites 1,095 daily rows. Right, the summary table job reads only the 900 thousand rows of today and yesterday and upserts two rows, so its cost stays constant as history grows. REFRESH MATERIALIZED VIEW reads 400,000,000 rows rewrites every output row 6 minutes per refresh cost grows with history incremental summary table reads ~900,000 recent rows upserts 2 daily rows 1.4 seconds per run cost constant as history grows same numbers in the dashboard, different amount of work

Annotated EXPLAIN Evidence #

The matview refresh’s underlying query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', placed_at) AS day, region, sum(total) AS revenue, count(*) AS orders
FROM orders GROUP BY 1, 2;
Finalize GroupAggregate  (actual time=341204.6..362104.2 rows=8760 loops=1)
  ->  Gather Merge  (actual rows=43800 loops=1)
        ->  Partial GroupAggregate  (actual rows=8760 loops=5)
              ->  Sort  (actual rows=80004022 loops=5)
                    Sort Method: external merge  Disk: 4102208kB
                    ->  Parallel Seq Scan on orders  (actual rows=80004022 loops=5)
Execution Time: 362810.4 ms
-- 400M rows read and sorted to produce 8,760 rows, every ten minutes

The incremental job:

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO revenue_daily (day, region, revenue, orders)
SELECT date_trunc('day', placed_at), region, sum(total), count(*)
FROM orders
WHERE placed_at >= date_trunc('day', now()) - interval '1 day'
GROUP BY 1, 2
ON CONFLICT (day, region) DO UPDATE
   SET revenue = EXCLUDED.revenue, orders = EXCLUDED.orders;
Insert on revenue_daily  (actual time=1402.6..1402.6 rows=0 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: revenue_daily_pkey
  Tuples Inserted: 2
  Conflicting Tuples: 22
  ->  HashAggregate  (actual time=1398.2..1399.1 rows=24 loops=1)
        Group Key: date_trunc('day', placed_at), region
        ->  Index Scan using orders_placed_at_idx on orders  (actual rows=904220 loops=1)
              Index Cond: (placed_at >= …)
Execution Time: 1404.9 ms
-- 904k rows read; 24 summary rows upserted
What the incremental job avoids Three items are annotated. The full refresh sorts 400 million rows externally using four gigabytes of disk. The incremental job reads 904 thousand rows through an index range. The upsert writes 24 rows, of which 22 already existed and were updated. Sort … external merge Disk: 4102208kB full refresh sorts everything Index Cond: (placed_at >= …) rows=904220 only the recompute window Tuples Inserted: 2 Conflicting Tuples: 22 24 summary rows touched 260× less work for the same dashboard numbers

Step-by-Step Resolution #

  1. Check that the aggregate is decomposable by a key you can bound. Sums, counts, minimums, maximums and averages stored as sum and count all are. Percentiles and distinct counts are not, without approximation.

  2. Create the summary table with a primary key on the grouping columns, which also serves as the upsert arbiter:

    CREATE TABLE revenue_daily (
  day date NOT NULL,
  region text NOT NULL,
  revenue numeric NOT NULL,
  orders bigint NOT NULL,
  PRIMARY KEY (day, region)
);

  3. Backfill history once from the same query without the time filter.

  4. Schedule the window recompute every few minutes with a window wide enough to cover late-arriving data. Measure lateness from the data:

    SELECT max(now() - placed_at) FILTER (WHERE created_at > now() - interval '1 day')
FROM orders;

  5. Make the job idempotent and restartable. ON CONFLICT DO UPDATE with a full recompute of each touched bucket means re-running is always safe.

  6. Index the summary for the dashboard’s access pattern, as in indexing materialized views for dashboards.

  7. Monitor freshness by storing the recompute watermark alongside the data and alerting when it falls behind.

Rows read per refresh cycle The full materialized view refresh reads 400 million rows. A two-day window recompute reads 904 thousand. A trigger-based delta approach reads none at refresh time but adds work to each of the 900 thousand daily writes. full REFRESH 400M rows 2-day window recompute 904k rows trigger deltas per-write only the trigger design moves cost from the job to every insert

Before and After #

-- BEFORE: REFRESH MATERIALIZED VIEW revenue_daily every 10 minutes
Finalize GroupAggregate → Sort (4 GB on disk) → Parallel Seq Scan (400M rows)   362.8 s per refresh

-- AFTER: window recompute upsert every 2 minutes
Insert … ON CONFLICT → HashAggregate → Index Scan (904k rows)                    1.4 s per run

Choosing between window recompute and triggers #

Triggers give the freshest numbers and spread the cost across writes, but they carry real risks. Every write path must go through them, including bulk loads; concurrent updates to the same summary row serialise on that row, which can become a contention point on a busy counter; and a bug in the delta logic silently accumulates drift that nobody notices until someone recomputes from scratch. If you use them, add a periodic full recompute as a reconciliation step and compare results.

The window recompute has neither problem: it is a query, it reads the base data, and its results replace whatever was there. Its cost is that numbers are only as fresh as the last run, and that very wide lateness windows erode the savings. When data can arrive days late, a hybrid works well: recompute today and yesterday every few minutes, and recompute the trailing month once a night.

Both approaches keep the base table’s indexes relevant. The recompute window is a range scan on a timestamp, so the index that serves it is the one most tables already have; without it, the incremental job degenerates into a sequential scan and the saving disappears.

Common Pitfalls #

Non-decomposable aggregates. Distinct counts and percentiles cannot be summed across buckets. Diagnostic signal: dashboard numbers that differ from a direct query. Fix: store the raw sets, use approximation, or recompute those metrics separately.

Recompute window too narrow. Late data never lands in the summary. Diagnostic signal: historical totals drifting from the base table. Fix: widen the window and add a nightly reconciliation.

Missing index for the window filter. The job scans the whole table. Diagnostic signal: incremental job as slow as the refresh. Fix: index the timestamp used for the window.

Reading the summary while it updates. Long upserts can block dashboard reads on the touched rows. Diagnostic signal: dashboard latency spikes aligned with the job. Fix: small batches per bucket and short transactions.

Frequently Asked Questions #

Can PostgreSQL refresh a materialized view incrementally? #

Not natively. REFRESH MATERIALIZED VIEW always recomputes the full result, and CONCURRENTLY still computes the whole new result before diffing it. Incremental behaviour requires maintaining a table yourself.

Is a summary table better than a materialized view? #

When the aggregate is keyed by a bucket you can recompute selectively — typically a time window — a summary table updated incrementally does far less work and can be fresher. A materialized view is simpler when the full recompute is cheap enough.

How wide should the recompute window be? #

Wide enough to cover how late data can arrive. Measure the observed lateness in the base table, add margin, and back it up with a periodic full recompute for correctness.

Up: Materialized View Strategy