Views and Materialized Views

Views Are Saved Queries

CREATE VIEW customer_spend AS
SELECT c.name, SUM(o.total) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

SELECT * FROM customer_spend WHERE total > 1000;

Views hide complexity, centralise logic and restrict what users can see.

Materialized Views Cache Results

CREATE MATERIALIZED VIEW mv_customer_spend AS
SELECT c.name, SUM(o.total) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

REFRESH MATERIALIZED VIEW mv_customer_spend;

A materialized view stores its result on disk. Queries against it are fast, but the data is only as fresh as the last REFRESH.

Updatable Views

Simple views over one table can be updated directly; complex joined views need INSTEAD OF triggers or rules.

Key Points

• Views are virtual; they run the query each time.
• Materialized views precompute and store results.
• Refresh materialized views manually or on a schedule.
• Views are a great tool for security and consistency.