Materialized Views (Overview)

Objectives

By the end of this lesson, you should be able to:

• Create a materialized view with CREATE MATERIALIZED VIEW
• Explain why a materialized view can go stale
• Refresh a materialized view with REFRESH MATERIALIZED VIEW

💡 Why this matters: A view (previous lesson) recalculates its query every single time it’s used, fine for something cheap, expensive for a heavy report run constantly. A materialized view stores the actual results instead, trading always-fresh data for speed.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from the previous lesson.

Creating a Materialized View

CREATE MATERIALIZED VIEW dept_summary AS
SELECT department, COUNT(*) AS headcount, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE department IS NOT NULL
GROUP BY department;

SELECT * FROM dept_summary ORDER BY department;

 department  | headcount | avg_salary
--------------+------------+------------
 Engineering |         4 |   85000.00
 Marketing   |         2 |   69000.00
 Sales       |         2 |   62000.00

CREATE MATERIALIZED VIEW looks almost identical to CREATE VIEW, but the query runs once, immediately, and its actual result rows are stored, like a snapshot. Querying dept_summary afterward reads that stored snapshot directly, no aggregation work happens again, this can be significantly faster for an expensive query, at the cost of the data no longer being guaranteed current.

A Materialized View Goes Stale

INSERT INTO employees (first_name, last_name, department, salary, hire_date, email)
VALUES ('Casey', 'Fontaine', 'Engineering', 90000, '2024-02-01', 'casey.fontaine@example.com');

SELECT * FROM dept_summary ORDER BY department;

 department  | headcount | avg_salary
--------------+------------+------------
 Engineering |         4 |   85000.00
 Marketing   |         2 |   69000.00
 Sales       |         2 |   62000.00

Even after inserting Casey Fontaine into employees, dept_summary still shows Engineering’s old headcount of 4, not 5. This is the defining tradeoff: unlike a plain view, a materialized view does not automatically reflect new data, it shows exactly what existed at the moment it was created (or last refreshed).

Refreshing

REFRESH MATERIALIZED VIEW dept_summary;

SELECT * FROM dept_summary ORDER BY department;

 department  | headcount | avg_salary
--------------+------------+------------
 Engineering |         5 |   86000.00
 Marketing   |         2 |   69000.00
 Sales       |         2 |   62000.00

REFRESH MATERIALIZED VIEW re-runs the underlying query and replaces the stored snapshot, now dept_summary correctly shows 5 Engineering employees. In a real system, a refresh typically runs on a schedule (nightly, hourly), balancing staleness against the cost of recalculating.

Choosing Between a View and a Materialized View

A plain view fits when the underlying query is cheap, or absolute freshness matters more than speed. A materialized view fits when the query is expensive (heavy aggregation across a large table, for instance) and slightly-stale data, refreshed periodically, is an acceptable tradeoff for much faster reads.

Try It

1. Create a materialized view summarizing total salary per department.
2. Insert a new employee, query the materialized view, and confirm it doesn’t yet reflect the new row.
3. Refresh the materialized view and confirm it now reflects the new employee.
4. Explain, in your own words, a real reporting scenario where a materialized view’s staleness would be an acceptable tradeoff, and one where it wouldn’t be.

Recap

• CREATE MATERIALIZED VIEW name AS query runs the query once and stores its actual results, unlike a plain view.
• A materialized view goes stale as the underlying data changes, it doesn’t update automatically.
• REFRESH MATERIALIZED VIEW name re-runs the query and replaces the stored snapshot with current data.

Next lesson: indexes, speeding up lookups on specific columns.