Materialized Views

Storing the Result Instead of Re-Running It

A regular view (19.1–19.3) stores only a query definition and re-executes it on every access. A materialized view flips that trade-off: it computes the query once and physically stores the result, like a real table. Subsequent reads hit the stored data directly — fast, but potentially stale until an explicit refresh happens.

PostgreSQL / Oracle Syntax

sql
CREATE MATERIALIZED VIEW department_summary_mv AS
SELECT
    d.id AS department_id,
    d.name AS department_name,
    COUNT(e.id) AS headcount,
    AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON e.department = d.id
GROUP BY d.id, d.name;

Reading it is a plain, fast table scan:

sql
SELECT * FROM department_summary_mv WHERE avg_salary > 60000;

But it does not automatically pick up changes to employees/departments. You must explicitly refresh it:

sql
REFRESH MATERIALIZED VIEW department_summary_mv;                 -- PostgreSQL: blocks reads during refresh (by default)
REFRESH MATERIALIZED VIEW CONCURRENTLY department_summary_mv;    -- requires a unique index; allows concurrent reads

In Oracle, materialized views additionally support automatic refresh scheduling (REFRESH FAST ON COMMIT, REFRESH COMPLETE ON DEMAND, etc.) tied to the underlying tables' change logs.

The Trade-off

Materialized views make sense when: the underlying query is expensive (heavy joins/aggregation), it's queried far more often than the base data changes, and some staleness (seconds, minutes, or even a day) is acceptable for the use case — dashboards, reports, analytics rollups are classic examples.

MySQL Has No Native Materialized Views

Unlike PostgreSQL and Oracle, MySQL does not support `CREATE MATERIALIZED VIEW` at all. To get the same effect in MySQL, you simulate it manually:

sql
-- Simulated "materialized view" in MySQL:
CREATE TABLE department_summary_snapshot AS
SELECT d.id AS department_id, d.name, COUNT(e.id) AS headcount, AVG(e.salary) AS avg_salary
FROM departments d LEFT JOIN employees e ON e.department = d.id
GROUP BY d.id, d.name;

-- "Refresh" job (e.g., run by a scheduled EVENT or external cron):
TRUNCATE TABLE department_summary_snapshot;
INSERT INTO department_summary_snapshot
SELECT d.id, d.name, COUNT(e.id), AVG(e.salary)
FROM departments d LEFT JOIN employees e ON e.department = d.id
GROUP BY d.id, d.name;

MySQL can automate the refresh with a scheduled EVENT:

sql
CREATE EVENT refresh_department_summary
ON SCHEDULE EVERY 1 HOUR
DO
  CALL refresh_department_summary_proc();

This is functionally a hand-rolled materialized view — a real table plus a scheduled job — with none of the engine-level bookkeeping (like CONCURRENTLY refresh or dependency tracking) that PostgreSQL/Oracle provide natively.

Edge Cases

• Staleness risk: a report built on a materialized view refreshed nightly can show numbers that don't match a "live" query against the base tables — this must be communicated to users (e.g., "as of last refresh: ...").
• Refresh cost: a full REFRESH recomputes the entire result; for very large aggregations this can be as expensive as the original query, and may lock the materialized view against reads unless CONCURRENTLY (or an engine's incremental/fast-refresh mode) is used.
• `REFRESH ... CONCURRENTLY` requires a unique index on the materialized view in PostgreSQL — without one, only the blocking refresh is available.

Key Takeaways / Interview Q&A

Q: What is the core difference between a view and a materialized view? A: A view re-runs its query live on every access and stores no data; a materialized view computes and physically stores the result once, requiring an explicit REFRESH to stay current.

Q: Does MySQL support materialized views natively? A: No — MySQL has no CREATE MATERIALIZED VIEW; you simulate one with a real table populated by a scheduled refresh job (e.g., an EVENT or external cron).

Q: When would you choose a materialized view over a regular view? A: When the underlying query is expensive, read far more often than the data changes, and some staleness is acceptable in exchange for much faster reads.