Materialized Views Runbook

HeliosDB Materialized Views Runbook

Use this runbook when creating, refreshing, validating, or recovering materialized views in production.

Pre-Change Checks

Before creating or changing a materialized view, confirm:

• The source query is stable and has a clear owner.
• Source tables have appropriate indexes for the refresh path.
• The expected refresh frequency and staleness tolerance are documented.
• Storage growth has been estimated.
• Monitoring covers refresh duration, refresh failures, source-table lag, and query latency.
• A rollback path exists for application queries that may use the view.

Create or Replace a View

Start with an explicit view definition and avoid broad source queries that include unused columns.

CREATE MATERIALIZED VIEW revenue_by_day AS
SELECT
  date_trunc('day', created_at) AS day,
  sum(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1;

Create indexes that match the expected read path:

CREATE INDEX idx_revenue_by_day_day
  ON revenue_by_day (day);

Refresh Strategy

Choose the lowest-impact refresh strategy that satisfies the workload:

• Manual refresh for maintenance windows or operator-controlled updates.
• Scheduled refresh for dashboards and reporting workloads.
• Incremental refresh when the view supports delta processing and source change volume is high.
• On-commit refresh only when low staleness is required and write overhead is acceptable.

For manual refresh:

REFRESH MATERIALIZED VIEW revenue_by_day;

Use concurrent refresh when readers must remain online and the view supports it:

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_day;

Initial Refresh and Backfill

For large views:

1. Create the view during a planned maintenance window or in a low-traffic period.
2. Run the initial refresh with query and storage monitoring enabled.
3. Confirm source-table reads do not starve serving workloads.
4. Validate row counts and aggregate checks against source tables.
5. Enable the application query path only after validation passes.

Example validation:

SELECT count(*) FROM revenue_by_day;

SELECT date_trunc('day', created_at) AS day, sum(total_amount)
FROM orders
WHERE status = 'completed'
GROUP BY 1
ORDER BY 1 DESC
LIMIT 7;

Troubleshooting

Refresh Fails

1. Capture the error message and failed statement.
2. Check source-table schema changes.
3. Check permissions on source tables and target view.
4. Inspect lock waits and long-running transactions.
5. Retry only after the underlying cause is corrected.

View Is Stale

1. Confirm the scheduled refresh job is enabled.
2. Check the last successful refresh timestamp.
3. Compare source-table update volume with refresh capacity.
4. Run a manual refresh if the workload can tolerate the refresh cost.
5. Increase cadence or use incremental refresh if staleness is recurring.

Query Rewrite Does Not Use the View

1. Confirm the query is semantically equivalent to the view definition.
2. Refresh statistics on source tables and the materialized view.
3. Check that predicates and grouping expressions match the view shape.
4. Use explain output to inspect the selected plan.

ANALYZE revenue_by_day;
EXPLAIN SELECT * FROM revenue_by_day WHERE day >= current_date - interval '7 days';

Rollback

Rollback options depend on the change:

• For a new view, remove query routing to the view and drop it after validation.
• For a refresh cadence change, restore the previous schedule.
• For a changed definition, keep the previous view available until application reads have moved safely.

DROP MATERIALIZED VIEW IF EXISTS revenue_by_day;