Materialized View Refresh Policies

In this lesson, you will learn how to navigate the classic trade-off between lightning-fast query performance and the "freshness" of your analytical data.

The Performance vs. Freshness Trade-off

When you build complex analytical views—such as those aggregating millions of rows for year-over-year growth or top-N rankings—running the query on-demand every time a user clicks "refresh" can bring your database to its knees. A Materialized View solves this by physically storing the result of your query on disk. However, this creates a dilemma: the stored data is a "snapshot" that inevitably becomes stale as new records enter your operational tables.

Choosing the Right Refresh Policy

Your refresh policy dictates when and how that stored snapshot gets updated. You typically choose between two extremes based on your business requirements:

1. Immediate Refresh (Synchronous): The materialized view is updated the moment the underlying source data changes.
   • Pros: Data is always 100% accurate.
   • Cons: It imposes a massive performance penalty on INSERT, UPDATE, and DELETE operations because the database must recalculate the view for every single transaction.
2. Deferred/Scheduled Refresh (Asynchronous): You tell the database to update the view only at specific intervals (e.g., every hour or once a night).
   • Pros: Your operational tables remain high-speed, and complex analytics run against a stable, pre-computed dataset.
   • Cons: Your dashboards or reports will show "stale" data until the next scheduled update.

Implementation Illustration

If you are calculating heavy metrics like Percentile-Based Thresholds or Moving Averages, you might define a refresh policy like this:

-- Refreshing the materialized view at a scheduled interval
CREATE MATERIALIZED VIEW monthly_sales_summary
REFRESH NEXT CURRENT_TIMESTAMP + INTERVAL '1 hour'
AS 
SELECT category, AVG(sales) as avg_sales
FROM transactions
GROUP BY category;

If your business needs "live" data, you cannot use a materialized view. But if your analytics are for daily executive reporting, the "stale" data from an hourly or daily refresh policy is usually a small price to pay for the massive gain in query speed.

Key Takeaway: Materialized view refresh policies require you to weigh the business need for real-time accuracy against the system’s need to maintain high performance during data ingestion.