---
title: "Materialized View Auto-Refresh Integration Guide"
description: "Automatic materialized view refresh in HeliosDB Lite is planned. The refresh options it will use, refreshing with SQL today, and checking staleness."
---

# Materialized View Auto-Refresh Integration Guide

:::note[Planned]
Automatic background refresh of materialized views is planned for the HeliosDB Lite
server. Today the server accepts the refresh options shown below and stores them with
the view, but it does not run a refresh worker: views are refreshed with
`REFRESH MATERIALIZED VIEW`, and `pg_mv_staleness()` shows how stale each view is.
:::

## What Is Available Today

| Capability | Status |
|------------|--------|
| `REFRESH MATERIALIZED VIEW` (full) | Available |
| `REFRESH MATERIALIZED VIEW CONCURRENTLY` (readers are not blocked) | Available |
| `REFRESH MATERIALIZED VIEW ... INCREMENTALLY` (applies only the changes) | Available |
| `pg_mv_staleness()` | Available |
| `WITH (auto_refresh = true, ...)` on `CREATE MATERIALIZED VIEW` | Accepted and stored; no automatic refresh yet |
| `ALTER MATERIALIZED VIEW ... SET (...)` refresh options | Accepted and stored; no automatic refresh yet |
| Background refresh worker with staleness threshold, CPU limit and priority | Planned |
| `pg_mv_auto_refresh_status()`, `pg_mv_refresh_history()`, `pg_mv_cpu_usage()`, `pg_mv_scheduler_stats()` | Planned with the refresh worker |

## Refreshing Views Today

```sql
-- Full refresh
REFRESH MATERIALIZED VIEW sales_summary;

-- Full refresh without blocking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

-- Apply only the changes since the last refresh
REFRESH MATERIALIZED VIEW sales_summary INCREMENTALLY;
```

### Checking Staleness

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

| Column | Description |
|--------|-------------|
| `view_name` | Materialized view name |
| `base_tables` | Tables the view reads |
| `last_update` | Time of the last refresh |
| `pending_changes` | Changes in the base tables since the last refresh |
| `staleness_sec` | Seconds since the last refresh |
| `status` | Refresh status of the view |

### Scheduling Refreshes

Until the refresh worker is available, schedule refreshes outside the database, for
example with cron and `psql`:

```bash
# Every 15 minutes: incremental refresh of sales_summary
*/15 * * * * psql -h 127.0.0.1 -p 5432 -U postgres -c "REFRESH MATERIALIZED VIEW sales_summary INCREMENTALLY"
```

To refresh only views that are behind, query `pg_mv_staleness()` first and refresh the
views whose `staleness_sec` or `pending_changes` exceed your limits.

## Refresh Options

These options are accepted today and stored with the view. They take effect once the
refresh worker is available. At creation the `WITH` clause accepts `auto_refresh` and
`max_cpu_percent`; set the other options with `ALTER MATERIALIZED VIEW ... SET`.

### At Creation

```sql
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    product_id,
    DATE_TRUNC('day', sale_date) AS sale_day,
    SUM(amount) AS total_sales,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY product_id, sale_day
WITH (
    auto_refresh = true
);
```

### After Creation

```sql
-- Staleness threshold in seconds
ALTER MATERIALIZED VIEW sales_summary SET (staleness_threshold = 1800);

-- Refresh strategy: 'manual', 'auto' or 'incremental'
ALTER MATERIALIZED VIEW sales_summary SET (refresh_strategy = 'incremental');

-- Several options at once
ALTER MATERIALIZED VIEW sales_summary SET (
    staleness_threshold = 3600,
    max_cpu_percent = 25,
    incremental_enabled = true,
    priority = 2
);
```

| Option | Type | Meaning once the worker is available |
|--------|------|--------------------------------------|
| `staleness_threshold` | numeric | Seconds before the view counts as stale |
| `max_cpu_percent` | numeric | CPU limit for background refreshes |
| `priority` | numeric | 0 = low, 1 = medium, 2 = high, 3 = critical |
| `refresh_strategy` | string | `manual`, `auto` or `incremental` |
| `incremental_enabled` | boolean | Allow incremental refresh |

## Planned Design

The planned refresh worker watches the staleness of views created with
`auto_refresh = true`, queues the stale ones by priority, refreshes them incrementally
when only a few rows changed, and pauses while CPU use is above `max_cpu_percent`. The
monitoring functions listed above will report its status, history and CPU use.

## See Also

- [Materialized Views](/docs/lite/features/materialized-views/)
- [Incremental Materialized Views](/docs/lite/features/incremental_materialized_views/)
- [Materialized Views Tutorial](/docs/lite/guides/tutorials/materialized_views_tutorial/)