Accelerating Queries with Materialized Views

1. The Challenge: Slow, Repetitive Queries

Imagine you are responsible for a dashboard that displays daily sales summaries. The underlying query joins a massive orders table with a multi-billion-row lineitem table and calculates aggregates. Every time a user loads or refreshes the dashboard, Redshift must perform this expensive join and aggregation from scratch. This is slow for the user and consumes significant cluster resources.

What if we could run this complex query just once, save the results, and then have all subsequent dashboard requests read from this pre-calculated summary? This is the core idea behind materialized views.

To build a solid intuition for this concept, let's watch a short, conceptual video. While it uses a different database for its examples, the principles it explains are universal.

2. Materialized Views vs. Standard Views in Redshift

As the video explained, a materialized view physically stores its result set, whereas a standard view is just a stored query that re-executes every time it's called. Let's formalize this distinction within the context of Amazon Redshift.

The following AWS Prescriptive Guidance document provides a clear comparison.

To summarize the key distinctions from that document:

3. Designing and Creating Materialized Views

Now, let's get practical. How do you create and use a materialized view in Redshift? The syntax is straightforward and builds on the standard CREATE VIEW statement.

The following AWS blog post provides a detailed, hands-on walkthrough.

The Creation Process

As you saw in the article, you create a materialized view by prefixing a standard SELECT query with CREATE MATERIALIZED VIEW view_name AS .... The SELECT query can contain complex joins, filters, and aggregations.

A powerful feature is that you can define physical properties for a materialized view, just like a regular table, to further optimize its performance. This includes setting a distribution style (DISTSTYLE) and sort keys (SORTKEY), which can be different from those of the underlying base tables.

Automatic Query Rewriting

The most powerful aspect of Redshift's implementation is automatic query rewriting. If you submit a query that can be answered using an existing, up-to-date materialized view, the query optimizer will automatically rewrite your query to use the MV. This is transparent to the user or BI tool, meaning you can gain massive performance benefits without changing any application code. As shown in the blog post, you can verify this by checking the EXPLAIN plan of your query.

CREATE MATERIALIZED VIEW daily_event_summary
BACKUP YES -- Optional but good practice for data durability
DISTSTYLE KEY(event_date) -- Good for joining on date
SORTKEY(event_date, event_type) -- Good for filtering by date and type
AS
SELECT
  CAST(event_time AS DATE) AS event_date,
  event_type,
  COUNT(*) AS event_count
FROM
  public.events
WHERE
  event_type IN ('login', 'purchase')
GROUP BY
  CAST(event_time AS DATE),
  event_type;

4. Keeping Data Fresh: The Refresh Process

A materialized view is a snapshot in time. If the data in the underlying base tables changes, the MV becomes stale and no longer reflects the true state of the data. To be useful, it must be updated. This process is called refreshing.

Redshift provides two ways to manage this:

1. Manual Refresh: You can explicitly update the view at any time using the REFRESH MATERIALIZED VIEW view_name; command. Redshift performs an incremental refresh if possible (only applying the changes), which is very fast. If not, it performs a full refresh (re-running the entire query). This is often scheduled as part of an ETL/ELT workflow after the base tables are loaded.

2. Automatic Refresh: You can configure the view to refresh itself automatically by adding the AUTO REFRESH YES clause to your CREATE MATERIALIZED VIEW statement. When this is enabled, Redshift monitors the base tables and refreshes the view as soon as possible after changes are committed. To avoid impacting your primary workloads, Redshift intelligently schedules these refreshes based on cluster load and resource availability.

You can monitor the state (e.g., is_stale) and refresh status of your materialized views by querying system tables like STV_MV_INFO and SVL_MV_REFRESH_STATUS.

5. Advanced Use Cases

Materialized views can be composed in powerful ways.

Nested Materialized Views
You can create a materialized view that is based on another materialized view. This is useful for building multi-stage aggregation pipelines where each layer is optimized. For example, a base MV could perform a large join, and a second, nested MV could perform aggregations on top of the first one.

Automated Materialized Views (AutoMVs)
Identifying the best materialized views to create can be complex. The Automated Materialized View (AutoMV) feature in Redshift removes this burden. When enabled (it is on by default), Redshift uses machine learning to monitor your query workload, identify candidate queries that would benefit from materialization, and automatically creates, maintains, and drops MVs in the background. This provides performance improvements with zero manual effort. You can read more about this in the "Automatically create materialized views" section of the blog post (LINK) we reviewed earlier.

Conclusion

You have now learned how to design and use one of the most effective tools for query acceleration in Amazon Redshift. By pre-computing the results of expensive operations, materialized views can reduce query times from minutes to milliseconds.

Key Takeaways:

• Materialized views store the pre-computed results of a query on disk, making them ideal for accelerating repetitive, complex queries found in BI dashboards and reports.
• Redshift's automatic query rewriting feature allows it to use MVs transparently, improving performance without application changes.
• MVs can become stale and must be updated via manual (REFRESH) or automatic (AUTO REFRESH YES) mechanisms.
• You can optimize MVs further with DISTSTYLE and SORTKEY, and even create nested MVs for multi-stage pipelines.
• Redshift's AutoMV feature can automate the entire process of creating and managing MVs.

Next Up:
We have now covered two crucial aspects of performance management: workload management (WLM) and query optimization with materialized views. The final piece of the puzzle for managing a production data warehouse is security. In our next lesson, we will cover how to secure a Redshift cluster using encryption, IAM roles, and network controls.