Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- 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.

SQL A view vs a stored copy ยท database: shop

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:

SQL Refreshing the emulated materialized view ยท database: shop

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

SQL
-- 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
-- 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 SCHEMABINDING blocks the change).