Last modified: May 11, 2025

This article is written in: 🇺🇸

Materialized Views

Materialized views are a database feature that allows you to store the result of a query physically on disk, much like a regular table. Unlike standard views, which are virtual and execute the underlying query each time they are accessed, materialized views cache the query result and can be refreshed periodically. This approach significantly improves performance for complex queries, especially when dealing with large datasets or computationally intensive operations.

After reading the material, you should be able to answer the following questions:

1. What are materialized views, and how do they differ from standard (virtual) views in a database system?
2. What are the primary benefits of using materialized views, and in what scenarios are they most advantageous?
3. How do different refresh methods (complete refresh vs. incremental refresh) impact the performance and accuracy of materialized views?
4. What are the key considerations and best practices when implementing materialized views, such as choosing the right refresh strategy and indexing the materialized view?
5. How do materialized views enhance performance in data warehousing and reporting environments, and what trade-offs must be managed?
6. Can you explain how materialized views are created and maintained in PostgreSQL, Oracle Database, and Microsoft SQL Server, highlighting any system-specific features or commands?

Overview

Imagine you have a complex query that aggregates sales data across multiple regions and products. Running this query every time can be time-consuming and resource-intensive. A materialized view lets you store the result of this query, so subsequent accesses are faster because the database doesn't have to re-execute the computation each time.

How it works?

Here's a simple representation of how materialized views fit into a database system:

────────────────────────────────────────────────────────────────
 Phase 1: Materialized-View Refresh (periodic or on-demand)
────────────────────────────────────────────────────────────────

    +-----------------------+       [1] Complex aggregation
    |     Base Tables       |  ────────────────────────────►
    +-----------------------+                                 
                    |                                       
                    |                                       
                    ▼                                       
    +-----------------------+                                
    |   Materialized View   |                                
    +-----------------------+                                
          (persisted snapshot of Base Tables)                

────────────────────────────────────────────────────────────────
 Phase 2: Query Phase (fast reads against the pre-computed view)
────────────────────────────────────────────────────────────────

    +-----------------------+       [2] Simple lookup/join
    |   Materialized View   |  ────────────────────────────►
    +-----------------------+                                
                    |                                       
                    |                                       
                    ▼                                       
    +-----------------------+                                
    |      Query Result     |                                
    +-----------------------+

• The base tables contain the raw data.
• A complex query is executed on these tables, and the result is stored in the materialized view.
• When you query the materialized view, you retrieve data directly from it without re-executing the complex query.

Benefits

Materialized views offer several advantages:

• They speed up query execution by providing quick access to precomputed results.
• Frequent querying of large tables can be resource-intensive; materialized views alleviate this by offloading queries.
• They can simplify complex data structures, making it easier for applications and users to retrieve data.

Refreshing Materialized Views

Since data in the underlying tables can change, materialized views can become outdated. Refreshing a materialized view updates it with the latest data.

Refresh Methods

• Complete Refresh: Recomputes the entire materialized view from scratch.
• Incremental (Fast) Refresh: Only applies changes since the last refresh, which can be more efficient.

Refresh Strategies

• On-Demand Refresh: Manually refresh the view whenever needed.
• Scheduled Refresh: Set up automatic refreshes at specific intervals (e.g., daily, hourly).

Creating a Materialized View in PostgreSQL

Let's walk through an example of creating and using a materialized view in PostgreSQL.

Scenario

Suppose you have a table called sales with millions of records, and you often run a query to get total sales per region.

Creating the Materialized View

CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

This command creates a materialized view named sales_summary that stores the total sales per region.

Querying the Materialized View

SELECT * FROM sales_summary;

When you run this query, PostgreSQL retrieves data directly from the sales_summary materialized view, which is faster than executing the aggregation on the entire sales table.

Refreshing the Materialized View

After new sales data is inserted into the sales table, you can refresh the materialized view to include the latest data:

REFRESH MATERIALIZED VIEW sales_summary;

Automating the Refresh

You can automate the refresh process using a scheduled task or a cron job. For example, to refresh the materialized view every night at midnight, you might set up a cron job with the following command:

0 0 * * * psql -U username -d database_name -c "REFRESH MATERIALIZED VIEW sales_summary;"

Indexing Materialized Views

Just like regular tables, you can create indexes on materialized views to further enhance query performance.

CREATE INDEX idx_sales_summary_region ON sales_summary(region);

This index speeds up queries that filter or join on the region column.

Use Cases for Materialized Views

Materialized views are especially useful in scenarios where complex queries are frequently executed, and real-time data is not a strict requirement.

Data Warehousing

In data warehouses, where analytical queries on large datasets are common, materialized views can precompute and store aggregated data, making reports and dashboards load faster.

Reporting and Analytics

For applications that generate regular reports, materialized views can store pre-aggregated data, reducing the time it takes to produce reports.

Performance Optimization

Applications experiencing performance bottlenecks due to heavy read operations on complex queries can use materialized views to alleviate the load on the database.

Considerations and Best Practices

While materialized views offer performance benefits, there are factors to consider:

• They consume additional disk space since they store data physically.
• Regularly refreshing materialized views is necessary to keep data up-to-date.
• There is a trade-off between performance and how current the data is; real-time data might require more frequent refreshes.

Choosing the Right Refresh Strategy

Your choice depends on how often the underlying data changes and how fresh you need the data in the materialized view to be.

• Fast-Changing Data: Consider more frequent or even real-time refreshes.
• Slow-Changing Data: Scheduled refreshes at longer intervals might suffice.

Materialized Views in Different Database Systems

Different database platforms have their own implementations and capabilities—ranging from simple indexed views to full-fledged materialized views with incremental refresh and query rewrite features. Below we explore how Oracle Database and Microsoft SQL Server approach materialized views.

Oracle Database

Oracle Database offers robust support for materialized views, including features such as query rewrite, fast (incremental) refresh, and integration with advanced replication and data warehousing scenarios. These views can automatically refresh on demand or according to a schedule, and can even incorporate only the changes made since the last refresh for optimal performance.

Creating a Materialized View with Fast Refresh

To create a materialized view that can be refreshed incrementally (also known as a fast refresh), you define it with BUILD IMMEDIATE to populate it immediately, and REFRESH FAST ON DEMAND so it can be refreshed on command, only applying changes since the last refresh:

CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Setting Up a Materialized View Log

Oracle requires a materialized view log on the base table to track changes (inserts, updates, deletes) so that a fast refresh can apply only the incremental differences. The log records rowids and specified columns, capturing new values when data changes:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (region, amount)
INCLUDING NEW VALUES;

Microsoft SQL Server

In Microsoft SQL Server, the equivalent of a materialized view is an indexed view. An indexed view physically materializes the results of a query by creating a unique clustered index on the view definition. Unlike Oracle, SQL Server does not natively support incremental refresh on demand—data changes to the underlying tables automatically propagate through the index during DML operations.

Creating an Indexed View

To define an indexed view in SQL Server, you must use WITH SCHEMABINDING so the view is tied to the schema of the underlying tables, then create a unique clustered index on the view. This forces SQL Server to maintain the view’s data as the base tables change:

CREATE VIEW dbo.sales_summary
WITH SCHEMABINDING AS
SELECT region, SUM(amount) AS total_sales, COUNT_BIG(*) AS count
FROM dbo.sales
GROUP BY region;
GO

CREATE UNIQUE CLUSTERED INDEX idx_sales_summary ON dbo.sales_summary(region);

Limitations

• They might not reflect the most recent data until refreshed.
• Requires additional management for refreshing and maintaining.
• Refreshing can be resource-intensive, especially for complete refreshes.