What is the difference between a materialized view and a regular View?

A materialized view is a database object that stores the result of a query physically, meaning the data is saved on disk and updated periodically or on demand. A regular view is a virtual table that does not store data but executes the query anew each time it is accessed.

Main differences:

• Performance: A materialized view speeds up read operations because the data is precomputed, but it requires resources for updating.
• Data freshness: A regular view always reflects current data, while a materialized view may be outdated until it is refreshed.

Example in PostgreSQL:

-- Creating a materialized view
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

-- Refreshing the materialized view
REFRESH MATERIALIZED VIEW sales_summary;