---
title: "Materialized Views"
---

# Materialized Views

Pre-compute and store query results for faster access, refreshed in full, concurrently or incrementally with `REFRESH MATERIALIZED VIEW`. Automatic background refresh is planned.

## Creating Materialized Views

```sql
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as order_count,
    SUM(total) as revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at);
```

### With Options

```sql
CREATE MATERIALIZED VIEW active_users AS
SELECT id, name, last_login FROM users WHERE active = true
WITH (
    auto_refresh = true,
    max_cpu_percent = 25
);
```

The options are stored with the view. Automatic background refresh is planned for the
Lite server, so `auto_refresh` does not refresh the view on its own yet; see
[Auto-Refresh](/docs/lite/features/materialized_views/auto_refresh_integration/).

## Refresh Strategies

### Manual Refresh

```sql
REFRESH MATERIALIZED VIEW monthly_sales;
```

### Concurrent Refresh (Non-blocking)

```sql
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
```

### Incremental Refresh

```sql
REFRESH MATERIALIZED VIEW monthly_sales INCREMENTALLY;
```

## Configuration Options

These options are accepted and stored today; they control the planned background refresh.
`auto_refresh` and `max_cpu_percent` can be set at creation, the others with
`ALTER MATERIALIZED VIEW ... SET`.

| Option | Type | Description |
|--------|------|-------------|
| `auto_refresh` | boolean | Mark the view for automatic refresh (planned) |
| `staleness_threshold` | integer | Seconds before view is considered stale |
| `max_cpu_percent` | integer | CPU limit for background refresh |
| `priority` | integer | Refresh priority (0=low, 3=critical) |
| `refresh_strategy` | string | 'manual', 'auto', 'incremental' |

### Alter Options After Creation

```sql
ALTER MATERIALIZED VIEW monthly_sales SET (
    staleness_threshold = 1800,
    max_cpu_percent = 15,
    priority = 2
);
```

## Monitoring

### Check Staleness

```sql
SELECT * FROM pg_mv_staleness();
```

### REPL Commands

```bash
\dmv              # List materialized views
\dmv view_name    # Describe specific view
```

## Dropping Views

```sql
DROP MATERIALIZED VIEW IF EXISTS monthly_sales;
```

## Best Practices

1. Use incremental refresh for large views with small changes
2. Schedule refreshes (for example with cron) to match how fresh each view must be
3. Schedule heavy refreshes during low-traffic periods
4. Use `pg_mv_staleness()` to refresh only the views with pending changes

## Related

- [SQL Reference](https://www.heliosdb.com/docs/lite/user-guide/sql-reference/) - Complete MV syntax