SQL Reference ยท JSON and Special Features
SQL materialized views
Reference for materialized views: stored query results, REFRESH in PostgreSQL and Oracle, indexed views in SQL Server, and how to emulate them in SQLite and MySQL.
A normal view is a saved query: every time you select from it, the database runs the underlying query again. A materialized view stores the result of the query on disk, like a table. Reads are fast, because the work was done earlier, but the data can be out of date until you refresh it.
Materialized views are not part of the SQL standard, and support varies a lot. PostgreSQL and Oracle have them; SQL Server offers indexed views; MySQL and SQLite do not, so you emulate one with a summary table that you rebuild or update yourself.
-- PostgreSQL CREATE MATERIALIZED VIEW sales_by_category AS SELECT category_id, SUM(quantity * unit_price) AS revenue FROM order_items JOIN products ON products.id = order_items.product_id GROUP BY category_id; REFRESH MATERIALIZED VIEW sales_by_category;
Try it
SQLite has no CREATE MATERIALIZED VIEW, so the usual substitute is a table filled from the query. The ordinary view keeps up with new data; the stored copy does not, until you refresh it.
Press Run (or Ctrl + Enter) to see the result here.
The view shows 7528.05, which includes the new sale of 3,490.00; the stored copy still shows the old 4038.05. Refreshing means replacing the contents:
Press Run (or Ctrl + Enter) to see the result here.
Doing the delete and insert in one transaction means readers never see an empty table.
Syntax
-- PostgreSQL CREATE MATERIALIZED VIEW name [(columns)] AS query [WITH [NO] DATA]; REFRESH MATERIALIZED VIEW [CONCURRENTLY] name; DROP MATERIALIZED VIEW name; -- Oracle CREATE MATERIALIZED VIEW name REFRESH [FAST | COMPLETE | FORCE] [ON DEMAND | ON COMMIT] AS query;
| Part | Description |
|---|---|
WITH NO DATA |
Creates the view empty; it cannot be queried until the first refresh. |
CONCURRENTLY |
PostgreSQL: refresh without blocking readers. Needs a unique index on the view. |
COMPLETE / FAST |
Oracle: rebuild everything, or apply only changes recorded in a materialized view log. |
ON COMMIT |
Oracle: refresh automatically when the source transaction commits. |
Dialect differences
| Database | Support | How to refresh |
|---|---|---|
| PostgreSQL | MATERIALIZED VIEW |
Manual REFRESH, scheduled by you (cron, pg_cron). No incremental refresh in core. |
| Oracle | MATERIALIZED VIEW with fast refresh and query rewrite |
DBMS_MVIEW.REFRESH, on demand, on commit or on a schedule. |
| SQL Server | Indexed view (WITH SCHEMABINDING plus a unique clustered index) |
Maintained automatically on every write; strict rules on what the query may contain. |
| MySQL, MariaDB | None | Summary table refreshed by EVENTs or application jobs. |
| SQLite | None | Summary table refreshed by triggers or application code. |
-- SQL Server indexed view CREATE VIEW dbo.sales_by_category WITH SCHEMABINDING AS SELECT p.category_id, SUM(oi.quantity * oi.unit_price) AS revenue, COUNT_BIG(*) AS rows_counted FROM dbo.order_items AS oi JOIN dbo.products AS p ON p.id = oi.product_id GROUP BY p.category_id; CREATE UNIQUE CLUSTERED INDEX ix_sales_by_category ON dbo.sales_by_category (category_id); -- MySQL: rebuild a summary table (with a PRIMARY KEY on category_id) on a schedule CREATE EVENT refresh_mv_revenue ON SCHEDULE EVERY 1 HOUR DO REPLACE INTO mv_revenue SELECT * FROM v_revenue;
Usage notes
- Freshness is the trade-off. Decide how stale the data may be: seconds (use a trigger-maintained table), hours (a scheduled refresh), or "never stale" (use a normal view and an index).
- Refresh cost. A complete refresh recomputes the whole query. For huge sources, update only the changed groups (an upsert into the summary table).
- Index the stored result. A materialized view is a physical table, so it can have its own indexes.
- Small and unchanging queries do not need one: a good index on the base table is often enough.
- Atomic swaps. Rebuild into a new table and rename it, or refresh inside a transaction, so queries never see half-built data.
- Permissions and schema changes. Dropping a base column breaks the view definition (and in SQL Server
SCHEMABINDINGblocks the change).
Related
- CREATE VIEW, CREATE TABLE AS, CREATE TRIGGER, UPSERT, trigger patterns
- Lessons: Views, Triggers