Engineering Atlas › System Design Fundamentals

Precomputing results with materialized views

11 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

View versus materialized view

A regular view is a stored query; every read re-executes it. A materialized view runs the query once and persists the rows. You can index it, and reads cost the same as reading any table. The database (or your pipeline) owns the copy, which is the difference from hand-rolled denormalization.

Full refresh

The simplest strategy reruns the defining query and swaps in the result. In PostgreSQL, REFRESH MATERIALIZED VIEW takes a lock that blocks readers during the rebuild; REFRESH ... CONCURRENTLY builds a new copy, diffs it against the old one, and applies changes, so readers continue but the refresh takes longer. It needs a unique index on plain columns with no WHERE clause, works only on a view that is already populated, and still allows just one refresh at a time per view (Postgres docs). Cost is proportional to the base data, not to what changed. A 30-minute refresh of a 500 GB fact table every hour burns half your I/O to update maybe 1% of rows.

Incremental maintenance

Incremental view maintenance (IVM) treats each insert, update, or delete on a base table as a delta and computes the effect on the view. For SUM and COUNT grouped by a key, this is easy: an inserted row adds to its group, a deleted row subtracts, and a group disappears when its count reaches zero. AVG is maintained as sum and count. MIN and MAX are harder: deleting the current minimum forces a rescan of that group. Joins require looking up matching rows on the other side for each delta. Postgres itself has no incremental mode, only full refresh; the pg_ivm extension adds one that updates the view in AFTER triggers inside the writing transaction, supports count, sum, avg, min and max, and charges every base-table write for the trigger (pg_ivm). The general theory, which handles joins, recursion and nested aggregates, is an active research area (Budiu et al., DBSP).

Systems that do this natively include Oracle (fast refresh with materialized view logs, either on demand or at every commit) (Oracle docs), ClickHouse (insert-triggered views that transform each block as it lands), and streaming databases such as Materialize and RisingWave, which maintain views continuously from change streams (Materialize docs).

ClickHouse's insert trigger has a sharp edge: the view sees only newly inserted blocks. Rows in the right-hand table of a join do not trigger anything when they change (ClickHouse docs), and an update or delete on the source is not an insert, so the view never hears about it. For queries that cannot be maintained that way, ClickHouse added refreshable materialized views, which rerun the whole query on a schedule (REFRESH EVERY 1 MINUTE) and were declared production-ready in release 24.10 in late 2024 (ClickHouse 24.10).

Staleness and correctness

With scheduled refresh, staleness equals the refresh interval plus refresh duration. With IVM fed by change data capture, staleness is the replication lag of the change stream, usually sub-second. Deltas must be applied exactly once and in order; replaying a delta double-counts. Track the last applied log position (LSN or offset) per view and skip anything at or below it.

Down the stack

Layer 1The contract

A PostgreSQL materialized view promises that a reader sees the complete result of the defining query as of the last successful refresh, never a half-refreshed state, because the refresh runs in one transaction. It promises nothing about freshness: if nobody refreshes, it stays stale forever. With CONCURRENTLY, readers are not blocked, but the view must have a unique index. An incremental view built on a change stream promises something different: the view equals the query result as of a specific log position, and lag is the distance between that position and the source's current one.

Layer 2The mechanism

A plain REFRESH MATERIALIZED VIEW runs the query into a brand-new heap file and swaps it in under an ACCESS EXCLUSIVE lock, which blocks even SELECT. REFRESH ... CONCURRENTLY instead builds the new result in a temporary table, diffs it against the current contents with a full outer join, and applies only the changed rows as deletes and inserts, holding a weaker lock that still allows reads. ClickHouse materialized views are insert triggers: each INSERT block into the source table is run through the view's SELECT, and the output is written to a target table, often a SummingMergeTree or AggregatingMergeTree that stores partial aggregates and merges them in the background. Oracle keeps a materialized view log per base table, recording changed rows so a fast refresh can apply only those.

Layer 3The machine

A full refresh over a 500 GB fact table reads every page: at 1 GB/s of sequential throughput that is over 8 minutes of pure I/O, before hashing for GROUP BY (which spills to temporary files if it outgrows work_mem) and before writing the new heap. The whole new contents also go into the WAL, so replicas replay the full refresh too. The concurrent variant does more work: the diff join plus row-by-row writes, each leaving a dead tuple for vacuum. In ClickHouse every insert creates a new on-disk part with one compressed file per column, so thousands of tiny inserts per second create thousands of parts. The default thresholds are parts_to_delay_insert = 1,000, after which inserts are slowed, and parts_to_throw_insert = 3,000, after which they fail with a "too many parts" error (ClickHouse docs); batching inserts to tens of thousands of rows avoids that. Every materialized view on a table adds its own insert into its target table, so the part count multiplies with the number of views. An incremental upsert per delta costs one index lookup and one WAL record, a few microseconds of CPU, with the fsync amortized across a batch.

Why it's built this way

  1. The same expensive aggregation is requested many times, so storing its result is cheaper than recomputing it.
  2. A stored result goes stale, so it needs a refresh mechanism.
  3. Full recomputation costs grow with total data, not with what changed, so large views move to incremental deltas.
  4. Deltas only work for aggregates that can be undone, like sums and counts, so MIN, MAX and DISTINCT need extra state or rescans.
  5. Delta consumers crash and replay, so the applied log position is stored atomically with the view rows.

Diagram

flowchart LR
    W[Writes] --> B["Base tables"]
    B --> L["Change log (WAL / CDC)"]
    L --> D["Delta processor"]
    D -->|"apply +/- per group"| MV["Materialized view"]
    B -.->|"scheduled full refresh"| MV
    R[Readers] --> MV
    D --> P["Last applied LSN"]

Worked example

An analytics product shows revenue per merchant per day. The payments table has 2 billion rows and grows by 400 per second. The dashboard query (SUM(amount), COUNT(*) GROUP BY merchant_id, day) takes 90 seconds, and the dashboard serves 300 requests per second. Running it live is impossible.

A materialized view keyed on (merchant_id, day) has about 50 million rows. Reads become indexed point lookups under 5 ms.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Regular viewAlways fresh, no storageFull cost on every readQuery is already cheap
Full refreshSimple, always correct after refreshCost tracks data size, stale between runsSmall data or loose freshness
Concurrent refreshReaders not blockedSlower, needs a unique keyReaders cannot tolerate a lock
Incremental (IVM)Fresh, cost tracks change rateLimited query shapes, exactly-once neededLarge base, small changes, sums and counts
Streaming databaseContinuous, complex joinsNew system to operateMany live views over event streams

Pitfalls in production

Leadership lens

Check yourself

  1. Why is SUM easy to maintain incrementally but MAX is not?

    Show answer

    A sum can subtract a deleted row's value, while deleting the current maximum gives no way to know the next largest value without rescanning the group.

  2. What determines staleness for a CDC-fed incremental view?

    Show answer

    The lag between a commit on the base table and the delta being applied, which is mostly change-stream lag.

  3. Why should the applied offset be stored in the same transaction as the view update?

    Show answer

    Otherwise a crash between the two either loses or replays deltas, and replayed deltas double-count.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →