Skip to content
HeliosDB HeliosDB Lite

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

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

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.

Refresh Strategies

Manual Refresh

REFRESH MATERIALIZED VIEW monthly_sales;

Concurrent Refresh (Non-blocking)

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Incremental Refresh

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.

OptionTypeDescription
auto_refreshbooleanMark the view for automatic refresh (planned)
staleness_thresholdintegerSeconds before view is considered stale
max_cpu_percentintegerCPU limit for background refresh
priorityintegerRefresh priority (0=low, 3=critical)
refresh_strategystring’manual’, ‘auto’, ‘incremental’

Alter Options After Creation

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

Monitoring

Check Staleness

SELECT * FROM pg_mv_staleness();

REPL Commands

Terminal window
\dmv              # List materialized views
\dmv view_name    # Describe specific view

Dropping Views

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