Notesstream🌱 seedling
Materialized View
Also: Materialised View · Incremental View Maintenance · IVM · Precomputed View
The stored result of a query over a source, kept up to date as the source changes, so reads become lookups. The design question is how it's kept current: full recomputation or incremental maintenance from deltas.
Why it exists
Expensive queries (joins, aggregates) run on every read are slow and load the source. Precomputing moves the cost to write time, or to background time.
How it works
- Full refresh: re-run the query and replace the contents. Simple, but the cost scales with the source, not with the change.
- Incremental view maintenance (IVM): turn each source change into a delta (+1 shipped, −1 pending) and compute only the delta of the result. The cost scales with the change.
- Counts and sums are easy to maintain. Joins, distinct counts and MIN or MAX need indexed state (deleting the max means finding the next one), which is where IVM engines spend their memory.
- Fed by a Change Data Capture stream or an event log, a view stays seconds behind its source.
Trade-offs
| Approach | Gains | Costs |
|---|---|---|
| full refresh | any query, trivially correct | stale between refreshes; cost of the whole query |
| incremental | fresh, cheap per change | state for joins and aggregates; restricted or complex engines |
In practice
- PostgreSQL: REFRESH MATERIALIZED VIEW completely replaces the contents. CONCURRENTLY avoids blocking readers but needs a unique index, and it still recomputes everything.
- Materialize (Differential Dataflow), RisingWave and Feldera (DBSP) maintain SQL views incrementally from streams. A stateful Flink or Kafka Streams job is a hand-written materialized view.
Pitfalls
- Assuming a database "materialized view" is incremental. Many, like Postgres, aren't.
- Reading two views as one snapshot: each may be at a different point in the source's history.
Connections
- Part of
- Derived Data
- Builds on this
- CQRS
- Related
- CachingEvent SourcingFan-Out on WriteLog CompactionStateful Stream ProcessingNormalizationStream JoinUnbundling the Database
- In real systems
- PostgreSQL
- Mentioned
- Change Data Capture
Recall
These cards are also in your review deck, scheduled with spaced repetition.