Incremental Aggregation in Postgres: When Materialized Views Are Not Enough

The dashboard was fine until the customer imported historical events. A materialized view that refreshed in 20 seconds started taking nine minutes. The product team thought the view was "incremental" because the dashboard only changed by a few thousand rows. PostgreSQL did not share that assumption.

A materialized view stores query results. Refreshing it reruns the query. REFRESH MATERIALIZED VIEW CONCURRENTLY can avoid blocking readers if the view has the required unique index, but it is still not the same thing as "only process the new rows."

When the pain is delta processing, build a delta pipeline. That usually means a raw event table, a summary table, a watermark, and a clear policy for late data.

The framework: separate freshness from correctness

I start incremental aggregation design with two questions:

• How fresh does the dashboard need to be?
• How late can source data arrive and still change the answer?

If the answer is "fresh within five minutes, late data up to three days," that is the design. Not the database object. Not the job schedule. The data contract.

The summary table is the product surface

A typical hourly summary table should be explicit about the dimensions users filter on.

CREATE TABLE account_usage_hourly (
  bucket timestamptz NOT NULL,
  account_id bigint NOT NULL,
  event_type text NOT NULL,
  event_count bigint NOT NULL DEFAULT 0,
  total_bytes bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (bucket, account_id, event_type)
);

This table is not a cache in the hand-wavy sense. It is the serving model for the dashboard. If the dashboard filters by account and event type, those keys belong in the summary table.

Process deltas with an id watermark

The simplest reliable version processes events in id order and remembers how far it got.

CREATE TABLE aggregation_watermarks (
  name text PRIMARY KEY,
  last_event_id bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now()
);

WITH watermark AS (
  SELECT last_event_id
  FROM aggregation_watermarks
  WHERE name = 'account_usage_hourly'
  FOR UPDATE
),
batch AS (
  SELECT *
  FROM usage_events
  WHERE id > (SELECT last_event_id FROM watermark)
  ORDER BY id
  LIMIT 50000
),
rolled AS (
  SELECT
    date_trunc('hour', occurred_at) AS bucket,
    account_id,
    event_type,
    count(*) AS event_count,
    sum(bytes) AS total_bytes,
    max(id) AS max_event_id
  FROM batch
  GROUP BY 1, 2, 3
)
INSERT INTO account_usage_hourly AS dst
  (bucket, account_id, event_type, event_count, total_bytes)
SELECT bucket, account_id, event_type, event_count, total_bytes
FROM rolled
ON CONFLICT (bucket, account_id, event_type)
DO UPDATE SET
  event_count = dst.event_count + EXCLUDED.event_count,
  total_bytes = dst.total_bytes + EXCLUDED.total_bytes,
  updated_at = now();

The watermark update should happen in the same transaction after the batch succeeds. If the job fails, the next run reprocesses the same batch rather than losing it.

Make the worker singleton

Aggregation workers are easy to accidentally run twice: deploy race, cron overlap, manual retry. Use an advisory lock or a job table to make the worker singleton.

BEGIN;

SELECT pg_try_advisory_xact_lock(8100, 1) AS got_lock;

-- If got_lock is false, another aggregation worker is already running.

COMMIT;

This is one of the good uses for transaction-scoped advisory locks. The protected section is short and the invariant is logical: one updater for one aggregate.

Late data changes the design

An id watermark works only if event ids and event meaning arrive in a useful order. In real systems, old events arrive late: mobile clients reconnect, imports backfill history, queues retry, and partners send yesterday's file today.

For late data, I use a repair window:

• Run fast incremental updates for new ids.
• Recompute recent buckets for the accepted late-arrival window.
• Close older windows and require explicit backfill jobs to change them.

DELETE FROM account_usage_hourly
WHERE bucket >= date_trunc('hour', now() - interval '3 days');

INSERT INTO account_usage_hourly
SELECT
  date_trunc('hour', occurred_at),
  account_id,
  event_type,
  count(*),
  sum(bytes),
  now()
FROM usage_events
WHERE occurred_at >= date_trunc('hour', now() - interval '3 days')
GROUP BY 1, 2, 3;

This hybrid approach is often simpler and safer than trying to make every late correction perfectly incremental.

Triggers are not the default answer

Trigger-maintained aggregates look attractive because they are always fresh. They also put aggregation cost on the write path. That can be fine for low write rates and small summaries. It is usually a bad surprise for high-ingest event tables.

If the dashboard can be a minute stale, a batch worker keeps write latency predictable. If the product needs read-your-write aggregate behavior, then triggers may be worth the cost.

What I monitor

• Watermark lag: source rows waiting to be aggregated.
• Job duration: whether a run finishes before the next run starts.
• Rows per batch: sudden spikes usually mean backfill or queue recovery.
• Late-arrival rate: rows landing outside the normal window.
• Correction count: how often repair windows change visible numbers.

The pragmatic default

Use materialized views when full refresh is cheap and freshness requirements are simple. Use incremental aggregation when full refresh time grows with history but the dashboard changes mostly by deltas.

The mistake is treating "stored query results" as "incrementally maintained results." PostgreSQL gives you the building blocks. The product semantics are yours to design.