WHERE support for REFRESH MATERIALIZED VIEW
Partial refresh restricted by a predicate, including concurrency semantics.
The session explored adding a WHERE clause to REFRESH MATERIALIZED VIEW so selected rows can be refreshed without rebuilding the whole result. Reading the concurrent-refresh implementation revealed that Postgres creates a temporary difference table, then applies ordinary SQL deletes and inserts through SPI. After experimentally bypassing the normal DML guard, updates to a materialized view and its indexes worked. The group concluded that partial refresh looked technically approachable, while delete semantics, grammar, safety, and consistency still needed design work. Viewer Adam Brusselback explicitly credited this session as the inspiration for the patch he later submitted to pgsql-hackers and CommitFest.
[timecodes · 7]
- The case for partial materialized-view refresh
- Building a concurrent-refresh test case
- Tracing REFRESH through parser and executor code
- How concurrent refresh computes and applies a diff
- Designing a filtered merge without global deletes
- Testing direct DML on a materialized view
- Conclusions and remaining partial-refresh semantics
direct mapping