What is a Materialized View?

A Materialized View (MV) is a database object that stores the result of a query physically on disk. Unlike a regular view, which is a virtual representation of a query, a materialized view improves query performance by precomputing and storing complex query results.

---

Advantages of Materialized View

1. Improved Query Performance: Since data is precomputed, queries run significantly faster.

2. Reduced System Load: Lessens the burden on source tables, making it ideal for read-heavy applications.

3. Data Aggregation: Useful in Data Warehousing, where data is aggregated from multiple sources.

---

Disadvantages of Materialized View

1. Storage Consumption: Takes up additional disk space as data is stored physically.

2. Stale Data: Needs periodic refreshes to keep the data up-to-date.

3. Resource-Intensive Refreshing: Updating an MV can be resource-intensive if not managed properly. A full refresh recalculates the entire query result, which can involve scanning large tables and processing complex aggregations. This can consume significant CPU, memory, and I/O resources, especially if the underlying data set is huge

---

Materialized View in PostgreSQL

Creating a Materialized View

CREATE MATERIALIZED VIEW climate_data_mv AS
SELECT location, AVG(temperature) AS avg_temp
FROM climate_data
GROUP BY location;

Refreshing a Materialized View

REFRESH MATERIALIZED VIEW climate_data_mv;

Note:

• Use WITH CONCURRENTLY to avoid locking the view during refresh if there are ongoing queries:

REFRESH MATERIALIZED VIEW CONCURRENTLY climate_data_mv;

• CONCURRENTLY requires a unique index on the materialized view.

---

Changing data stored in Materialized View

• You cannot directly INSERT or UPDATE a Materialized View because it is not a standard table; it is a stored result of a query.

• To change data stored in Materialized View, you have to change data from Source table(s) then refresh Materialized View. The entire dataset may be recalculated if you perform a complete refresh. However, if your database supports partial (or fast) refresh—like Oracle does with Materialized View Logs—the refresh process will only update the portions of the view that have changed, rather than recalculating all of the data.