Incremental View Maintenance (IVM) for PostgreSQL — Deep Technical Analysis

The Core Problem

Materialized views in PostgreSQL are static snapshots: they must be fully recomputed via REFRESH MATERIALIZED VIEW whenever their underlying base tables change. For large, complex views over frequently-updated tables, this full recomputation is prohibitively expensive. Incremental View Maintenance (IVM) solves this by propagating only the delta (the changes) from base table modifications to the materialized view, updating it incrementally rather than recomputing from scratch.

This is one of the most architecturally significant features that could be added to PostgreSQL's materialized view infrastructure. It touches the executor, trigger system, catalog design, MVCC/snapshot management, WAL, and concurrency control.

Historical Context

This thread is "take 2" of an ongoing effort tracked at commitfest patch #4337. The feature has been developed externally as the pg_ivm extension (https://github.com/sraoss/pg_ivm) by Yugo Nagata at SRA OSS, and the goal is to integrate it into core PostgreSQL. The patch set has gone through multiple rebases as the PostgreSQL trunk evolves.

Architectural Design and Key Technical Decisions

1. Refresh Timing: Immediate vs. Deferred

The current implementation uses an immediate approach: the materialized view is updated synchronously within the same transaction that modifies the base table, via AFTER triggers with transition tables.

Trade-offs:

• Immediate: No need for change-tracking infrastructure; view is always consistent with base tables at transaction commit. However, it imposes write amplification on every DML statement and introduces concurrency complexities.
• Deferred: Could update asynchronously or on-demand (e.g., at transaction commit, or lazily on next read). Requires change-tracking infrastructure (logical decoding, change tables, or similar), but decouples write performance from view complexity.

The immediate approach was chosen as the simpler starting point requiring less new infrastructure.

2. Maintenance Placement: Trigger-Based vs. Executor-Integrated

The current design is trigger-based, modeled after how RI (referential integrity) triggers work for foreign keys:

• Transition tables (OLD TABLE, NEW TABLE) capture row-level changes
• An AFTER trigger fires and executes the delta computation/application to the IMMV
• The relationship between views and base tables is represented indirectly through pg_trigger entries

Alternative: Direct executor integration with a dedicated catalog (e.g., pg_matview_tables), similar to declarative partitioning's pg_inherits. This could simplify interactions with nested triggers and user-defined triggers but requires deeper executor changes.

The trigger approach reuses existing infrastructure but introduces complexity when nested queries are executed from other triggers (user-defined triggers, RI triggers firing in the same statement).

3. Capturing Pre-Update State

When multiple base tables are modified in a single query, the maintenance logic needs the pre-modification state of tables to compute correct deltas.

Current approach: A special function ivm_visible_in_prestate() in the WHERE clause that checks row visibility using a snapshot captured in a BEFORE trigger. This is described as "awkward."

Proposed improvement: Reconstruct pre-state using transition tables:

SELECT * FROM tbl
  UNION ALL SELECT * FROM old_table
  EXCEPT ALL SELECT * FROM new_table;

The ALL variants are critical to preserve tuple multiplicity (duplicate counts).

Rejected alternative: Introducing AS-OF snapshot infrastructure (like Oracle's flashback query) — deemed over-engineered for this use case.

4. Concurrency Control

A critical bug was identified and fixed: view inconsistency when a concurrent transaction modifies a base table while creating or refreshing an IMMV. The fix introduces a shared hash table storing, for each IMMV, the transaction ID of the last transaction that updated the view. This allows detection of stale states and proper serialization.

The hash table is currently fixed-size but could be made configurable via GUC or converted to a dynamically-managed shared hash table (similar to ApplyLauncher{Set,Get}WorkerStartTime()).

5. Syntax and Catalog Representation

Current: CREATE MATERIALIZED VIEW ... INCREMENTAL with a new keyword/field in pg_class.

Proposed simplification: Use a reloption instead:

CREATE MATERIALIZED VIEW ... WITH (incremental)

This avoids new syntax keywords and new pg_class fields, using existing reloption infrastructure.

6. Feature Scope Considerations

The full patch supports DISTINCT, aggregates, and duplicate tuple handling (via hidden columns for counting). Nagata proposes narrowing the initial scope to SPJ (Select-Project-Join) views only, with a key simplification:

If each base table has a primary key (or replica identity other than FULL) and all such columns appear in the target list, the resulting view cannot contain duplicate tuples. This eliminates the need for:

• Hidden count columns for duplicate tracking
• Complex SPI-based aggregate maintenance logic
• DISTINCT handling

This is a sound engineering decision — validate the core delta-propagation mechanism first, then layer complexity.

Identified Bugs and Fixes

1. Dropped columns on base tables: Base tables with previously-dropped columns caused incorrect column mapping during delta application.
2. Same table modified multiple times by triggers: The maintenance logic didn't correctly handle cascading trigger scenarios where the same base table gets modified more than once.
3. Concurrent modification during CREATE/REFRESH: Race condition where another transaction modifies a base table between the IMMV creation/refresh and the trigger installation.

Error Handling Concern

Alexandre Felipe identified a potential resource leak in IVM_immediate_maintenance() (line 1688 of src/backend/commands/matview.c): when apply_delta fails and PG_RE_THROW is called, cleanup code (from line 1699 onwards) is skipped. Nagata suggests this may be handled by AtAbort_IVM() in the abort path, but acknowledged needing to verify this more carefully.

Future Directions Mentioned

• Materialized view status catalog/statistics view: Track last refresh time, staleness metrics — useful for planners and users
• Query rewrite / automatic view substitution: Planner optimizations that could leverage IVM-maintained views
• Configurable shared hash table size: Via GUC for the concurrency control mechanism

Assessment of Patch Maturity

This is still in the design-exploration phase. The May 2026 message from Nagata effectively resets expectations: rather than pushing the full patch forward, he's advocating for a simplified, reviewable core that the community can evaluate architecturally. The PGConf.dev 2026 poster presentation was explicitly about gathering feedback on open questions rather than proposing a final design.

The key open questions requiring community consensus:

1. Trigger-based vs. executor-integrated maintenance
2. Immediate vs. deferred (or both as options)
3. Catalog design for view-table relationships
4. Scope of initial commit (SPJ-only vs. full aggregate support)