A view is a saved query. It stores no data, and every time you select from it the query runs again. A materialized view stores the result rows, so reading it is fast, but the stored copy can be out of date until it is refreshed.
Side by side
View Materialized view saves: query text saves: query text + result rows reads: re-runs the query reads: stored result (fast) fresh: always fresh: as of last refresh cost: paid on each read cost: paid on refresh + storage
How refresh works
This is where engines differ.
- Postgres: refresh is manual,
REFRESH MATERIALIZED VIEW name. It recomputes everything unless you do it in a specific way. Someone has to schedule it. - BigQuery: materialized views refresh automatically in the background, and it can use only the changed data (incremental). You can tune how often. If the base table changed since the last refresh, BigQuery can combine stored results with new rows at query time so you still get fresh answers.
- Snowflake: a background service keeps the view up to date automatically, and you pay for that compute. Materialized views are an Enterprise Edition feature and can only select from a single table. For joins, Snowflake offers dynamic tables.
The trade-off to explain
You are paying to move work from read time to write time. That is worth it when many users run the same expensive aggregation (a daily revenue summary on a 5 TB fact table) and the base data changes slowly. It is a poor deal if the base table changes constantly (you keep paying for refresh) or if every query is different.
Restrictions are common: limited joins, no non-deterministic functions such as CURRENT_TIMESTAMP, and limited aggregate functions. If the query you want to speed up breaks those rules, build a summary table with your own scheduled job.