Materialised views: saving a query's answer instead of recomputing it
A regular view re-runs its query every time you read it. A materialised view runs it once and remembers the answer, until you tell it to refresh.
A regular VIEW is just a saved query. Read from it, and the database re-runs the whole thing every
time. A materialised view is different: it runs the query once and stores the actual result, like
a real table, so reading it afterward is instant.
🎯 Explain Like I'm Hired A regular view is a saved question: ask it again and the database redoes all the work. A materialised view is a saved answer: it computes once, and reading it afterward is instant, but that answer slowly goes stale until you explicitly ask for a refresh. Example: a "revenue by day" dashboard read by hundreds of people an hour doesn't need to re-add-up millions of rows on every single page load. Refresh the materialised view once an hour (or nightly) and let everyone read the cached result instantly.
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT DATE_TRUNC('day', placed_at) AS day, SUM(amount) AS revenue
FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW daily_revenue; -- must be run explicitly to update it
Sign up to keep reading
Sign up free to unlock the worked examples, edge cases, and interview traps below.