SQL Materialized Views: Caching Expensive Query Results

A materialized view is a query whose result is physically stored on disk as a table. You pay for the heavy SELECT once, at refresh time, and then read the precomputed answer in milliseconds.

Why you want one

A regular VIEW is just stored query text: every access re-runs the plan. If it aggregates millions of rows or joins half a table, your dashboard crawls. A materialized view computes the result ahead of time and keeps it around.

CREATE MATERIALIZED VIEW revenue_by_country AS
SELECT u.country,
       count(*)        AS orders_cnt,
       sum(o.amount)   AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country;

Now SELECT * FROM revenue_by_country reads precomputed rows without touching orders and users. The catch: the data is frozen at the moment of the last refresh.

Refreshing: REFRESH and CONCURRENTLY

To pull in fresh data, you recompute the view:

REFRESH MATERIALIZED VIEW revenue_by_country;

The problem: a plain REFRESH takes an ACCESS EXCLUSIVE lock, so readers block while it runs. On large datasets that is seconds of downtime. The fix is CONCURRENTLY: new data is built in the background and swapped in atomically without blocking SELECT.

-- CONCURRENTLY requires a UNIQUE index over the result rows
CREATE UNIQUE INDEX ON revenue_by_country (country);

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_country;

• Without a unique index, CONCURRENTLY errors out.
• CONCURRENTLY is slower (it diffs row by row) but never blocks reads.
• Drive the refresh on a schedule (cron, pg_cron) or as a post-ETL step.

Indexing the view

A materialized view is a table, so you can put ordinary indexes on it and speed up reads against it.

CREATE INDEX idx_rev_revenue ON revenue_by_country (revenue DESC);

SELECT country, revenue
FROM revenue_by_country
ORDER BY revenue DESC
LIMIT 10;

Indexes survive a REFRESH — Postgres rebuilds them for you. Just remember: the more indexes you add, the slower each refresh gets.

Staleness: the core trade-off

The price of speed is stale data. Between two REFRESH runs, the view shows an old picture.

Gotcha: a user places an order, but it does not appear in revenue_by_country until you call REFRESH. Never use a materialized view where you need transactional accuracy (account balances, stock levels).

Match the refresh cadence to the business: an analytics dashboard is fine refreshing hourly; near-real-time needs every couple of minutes.

View, MV, or summary table

Option	Data	Use when
VIEW	always fresh	light query, freshness required
MATERIALIZED VIEW	as of last refresh	heavy query, tolerates lag
summary table	you maintain it	you need incremental logic and UPSERT

A summary table is the manual equivalent: you populate it with INSERT ... SELECT and update it incrementally yourself.

INSERT INTO revenue_summary (country, revenue, day)
SELECT u.country, sum(o.amount), current_date
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= current_date
GROUP BY u.country
ON CONFLICT (country, day) DO UPDATE
SET revenue = EXCLUDED.revenue;

Engine differences:

• MySQL has no materialized views at all — you emulate them with summary tables plus triggers or the event scheduler.
• ClickHouse has a MATERIALIZED VIEW, but it is not a cache: it is an incremental trigger that updates aggregates on every insert into the source table.

The rule is simple: need freshness, use a VIEW; query is expensive and lag is acceptable, use a MATERIALIZED VIEW; need fine-grained incremental logic, roll your own summary table.