---
title: "Incremental Materialized View Refresh"
---

# 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:

```sql
-- 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:

```sql
-- 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](/docs/lite/features/materialized_views/auto_refresh_integration/).

## 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)

```sql
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:

```sql
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:

```sql
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.