Technical Analysis: Review Observations for Partial REFRESH MATERIALIZED VIEW Patch

Core Problem

Materialized views in PostgreSQL currently support only full refresh semantics — the entire materialized view is recomputed from scratch on each REFRESH MATERIALIZED VIEW invocation. For large materialized views where only a subset of rows have changed in the underlying base tables, this is extremely wasteful. The patch under discussion adds a WHERE clause to REFRESH MATERIALIZED VIEW, enabling partial (selective) refresh — only recomputing and replacing rows matching a given predicate.

This is architecturally significant because:

1. Performance: High-churn workloads where only a fraction of the materialized view is stale benefit enormously from avoiding full recomputation.
2. Availability: Shorter refresh windows mean less lock contention and less time holding exclusive locks on the materialized view.
3. Operational flexibility: Users can implement partition-like refresh strategies (e.g., refreshing only data for the current day/hour).

Concurrency Design Challenges

The review raises three critical concurrency concerns about the patch:

1. DELETE → INSERT Consistency Gap

The original implementation approach apparently used a two-step process:

• DELETE rows matching the WHERE predicate from the materialized view
• INSERT freshly computed rows from the base query (filtered by WHERE)

The problem: between DELETE and INSERT, there's a window where concurrent readers see the materialized view with missing rows. Worse, once rows are deleted, any tuple-level locks held on those rows disappear, creating a visibility anomaly.

The newer approach uses FOR UPDATE + single-CTE to atomically replace matching rows, which eliminates the intermediate state. This is reminiscent of how REFRESH MATERIALIZED VIEW CONCURRENTLY uses a diff-based approach internally, but applied to a subset.

2. Deadlock Risk with UPSERT Conflicts

Even with the single-CTE approach, if two overlapping partial refreshes target overlapping row sets, UPSERT (INSERT ... ON CONFLICT) operations could create deadlock scenarios. Two concurrent refreshes attempting to lock/update the same rows in different orders is a classic deadlock pattern. The patch likely needs either:

• Deterministic row ordering during the UPSERT phase
• Advisory locking on the predicate range
• Documentation that overlapping partial refreshes are not safe concurrently

3. Counterintuitive CONCURRENTLY + WHERE Semantics

Standard REFRESH MATERIALIZED VIEW CONCURRENTLY is more permissive for concurrent readers/writers — it allows continued access during refresh by building a new version and swapping. However, the reviewer notes that with a WHERE clause, the non-CONCURRENT path appears more permissive for writers than CONCURRENTLY WHERE.

This inversion likely arises because:

• Non-concurrent partial refresh only holds locks on the subset of rows being replaced
• CONCURRENTLY WHERE may still need to hold broader locks to maintain the diff-based consistency guarantees that CONCURRENTLY normally provides
• The unique index requirement for CONCURRENTLY adds additional locking overhead in the partial case

This is a significant UX/documentation concern — users' mental model of "CONCURRENTLY = less blocking" would be violated.

Architectural Implications

The partial refresh feature sits at the intersection of several PostgreSQL subsystems:

• Executor: The CTE-based approach means the refresh is compiled as a single query plan
• MVCC: Visibility guarantees during partial refresh must be clearly defined
• Lock Manager: The granularity of locking (row-level vs. relation-level) differs between approaches
• Catalog: The pg_matviews / pg_class metadata may need to track partial refresh state

Current State

This review thread is procedural in nature — the reviewer posted observations but did so in a new thread rather than replying to the original patch thread. The substantive technical discussion would be in the original thread (referenced via the corrected message-id link). The review itself is high-quality, identifying real concurrency hazards that would need resolution before commit.

Missing Information

The actual patch implementation details, performance benchmarks, and the original author's responses to these concurrency concerns are not visible in this thread fragment. The technical substance would be in the original thread referenced by the message-id link.