Materialized Views

Materialized Views

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

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

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

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

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

\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. 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 - Complete MV syntax