Materialized Views
SQL · Extra Concepts
Theory
A regular view is just a saved query: every time you select from it, the database runs the underlying query again. A materialized view runs the query once and stores the result physically, like a table. Reading it is fast because the expensive joins and aggregations are already done, but the stored result is only as fresh as its last refresh.
In PostgreSQL you create one with CREATE MATERIALIZED VIEW and update it with REFRESH MATERIALIZED VIEW, which recomputes the whole result. Oracle supports scheduled and incremental refresh, SQL Server offers a similar idea called indexed views, and Snowflake maintains materialized views automatically in the background. MySQL and SQLite have no materialized views; the usual workaround is a summary table rebuilt by a scheduled job. Materialized views fit dashboards that read the same heavy aggregate many times a day while the source changes slowly.
Why we use it
Dashboards often run the same expensive aggregation over large tables again and again. A materialized view computes it once and serves the stored result, saving time and compute on every read.
When to use it
Use materialized views for heavy, frequently read summaries where slightly stale data is acceptable. Use a regular view when results must always be current or the query is cheap. Use a scheduled summary table in databases without them.
How it works
- CREATE MATERIALIZED VIEW runs the query once
- Result is stored on disk like a table
- Readers query the stored result quickly
- Source changed and freshness matters? Yes: REFRESH the view. No: Keep serving the stored result.
- Fast reads, freshness controlled by refresh
Syntax
CREATE MATERIALIZED VIEW view_name AS
SELECT ...;Example
CREATE MATERIALIZED VIEW dept_summary AS
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;Key points
- Trades freshness for speed — must be manually or automatically REFRESHed
- Supported in Postgres/Oracle; SQLite and MySQL only have regular (non-materialized) views
- PostgreSQL REFRESH ... CONCURRENTLY needs a unique index on the view
- SQL Server's similar feature is called an indexed view
- Without them, rebuild a summary table on a schedule