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

# Materialized Views

Pre-compute and cache query results for faster access with automatic or incremental refresh.

## 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,
    staleness_threshold = 3600
);
```

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

| Option | Type | Description |
|--------|------|-------------|
| `auto_refresh` | boolean | Enable automatic refresh |
| `staleness_threshold` | integer | Seconds before view is considered stale |
| `max_cpu_percent` | integer | CPU limit for refresh operations |
| `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. Set appropriate staleness thresholds for your use case
3. Schedule heavy refreshes during low-traffic periods
4. Monitor CPU usage with `max_cpu_percent`

## Related

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