Incremental Materialized View Refresh

Overview

Incremental computation for materialized views enables efficient delta-based updates instead of full recomputation. This feature dramatically improves refresh performance by processing only the changes (deltas) to base tables rather than rescanning the entire dataset.

SQL Syntax

REFRESH MATERIALIZED VIEW INCREMENTALLY

Explicit SQL syntax to request incremental refresh:

-- Request incremental refresh explicitly
REFRESH MATERIALIZED VIEW sales_summary INCREMENTALLY;

-- Falls back to full refresh if incremental not possible

ALTER MATERIALIZED VIEW SET

Configure MV options after creation:

-- Enable incremental refresh strategy
ALTER MATERIALIZED VIEW sales_summary SET (refresh_strategy = 'incremental');

-- Configure staleness threshold (seconds) and CPU limits
ALTER MATERIALIZED VIEW sales_summary SET (
    staleness_threshold = 1800,
    max_cpu_percent = 25,
    incremental_enabled = true
);

-- Set refresh priority (0=low, 1=medium, 2=high, 3=critical)
ALTER MATERIALIZED VIEW sales_summary SET (priority = 2);

These options are stored with the view. Automatic background refresh, which will use them to schedule refreshes by staleness, CPU limit and priority, is planned; run REFRESH MATERIALIZED VIEW ... INCREMENTALLY from a scheduler today. See Auto-Refresh.

Key Features

1. Delta Tracking

The system tracks all changes (inserts, updates, deletes) to base tables.

2. Incremental Refresh Strategies

Three refresh strategies are supported:

• Full: Complete recomputation from base tables
• Incremental: Delta-based updates only
• Hybrid: Automatic selection based on cost estimation

3. Cost-Based Optimization

The system automatically estimates costs and recommends the optimal strategy.

4. Supported Operations

Aggregate Views

Efficiently update aggregates without full recomputation:

• COUNT: Increment/decrement counters
• SUM: Add/subtract values
• AVG: Update running averages
• MIN/MAX: Track extrema (with validation)

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id,
       COUNT(*) as order_count,
       SUM(amount) as total_sales,
       AVG(amount) as avg_sale
FROM orders
GROUP BY product_id;

-- Incremental refresh applies deltas efficiently
REFRESH MATERIALIZED VIEW sales_summary;

Filter Views

Apply filter predicates to deltas:

CREATE MATERIALIZED VIEW active_users AS
SELECT * FROM users
WHERE status = 'active' AND last_login > NOW() - INTERVAL '30 days';

-- Only processes changed rows
REFRESH MATERIALIZED VIEW active_users;

Join Views

Match deltas against join partners:

CREATE MATERIALIZED VIEW user_orders AS
SELECT u.name, o.order_id, o.amount
FROM users u
JOIN orders o ON u.user_id = o.user_id;

-- Processes deltas from both sides efficiently
REFRESH MATERIALIZED VIEW user_orders;

Architecture

Delta Tracking System

┌──────────────┐
│ Base Table   │
│  (users)     │
└──────┬───────┘
       │ Changes (Insert/Update/Delete)
       ▼
┌──────────────────────┐
│   Delta Tracker      │
│                      │
│  ┌────────────────┐  │
│  │ Delta Queue    │  │
│  │ - Insert ops   │  │
│  │ - Update ops   │  │
│  │ - Delete ops   │  │
│  └────────────────┘  │
└──────┬───────────────┘
       │ Get deltas since last refresh
       ▼
┌──────────────────────┐
│ Incremental          │
│ Refresher            │
│                      │
│  ┌────────────────┐  │
│  │ Apply Deltas   │  │
│  │ - Aggregates   │  │
│  │ - Filters      │  │
│  │ - Joins        │  │
│  └────────────────┘  │
└──────┬───────────────┘
       │ Updated results
       ▼
┌──────────────────────┐
│ Materialized View    │
│   (refreshed)        │
└──────────────────────┘

Performance Characteristics

Incremental vs Full Refresh

Figures below are indicative, measured on representative fixtures; reproduce on your own hardware.

Dataset Size	Delta Ratio	Incremental Time	Full Refresh Time	Speedup
10K rows	1%	10ms	100ms	10x
100K rows	1%	100ms	1,000ms	10x
1M rows	1%	1s	10s	10x
10K rows	50%	500ms	100ms	0.2x

Key Insights:

• Incremental refresh excels when delta ratio is low (<10%)
• Full refresh is better when most of the dataset changes
• Cost estimation automatically selects the optimal strategy

Memory Usage

• Delta Tracking: O(deltas) - Only stores changes since last refresh
• Incremental Refresh: O(deltas + groups) - Proportional to changes
• Full Refresh: O(base_table_size) - Must process entire dataset

Limitations

Current Limitations

1. MIN/MAX Aggregates: Require validation when deleted value is extremum
2. Complex Joins: Multi-way joins not fully optimized
3. Subqueries: Not yet supported for incremental refresh
4. Window Functions: Not supported incrementally

Workarounds

For unsupported operations, the system automatically falls back to full refresh.