What Does a Materialized View Buy You?

A materialized view stores the result of its query as actual data. It is refreshed explicitly or on a schedule, and reading from it is a table scan or an index lookup rather than a recomputation. That is the whole trade: you give up automatic freshness and gain speed.

It is worth it when the same expensive computation is read far more often than the underlying data changes. A reporting query that joins five tables and aggregates over fifty million rows, executed once a minute for a dashboard but recomputed on every page load, is the canonical case. The dashboard reads the materialised view in milliseconds; the underlying data is at most a minute old, which nobody can observe.

It is also worth it when the query is structurally awkward to keep fast. A query with a correlated subquery over a large table, or a window function over a partition with millions of rows, can be pre-computed once and then read cheaply.

It is not worth it when the data changes constantly and the report has to be current to the second. A materialised view on a table that is written a thousand times a minute has to be refreshed constantly, and at that point the refresh cost is the same as the query cost, only paid in the background. It is also not worth it when the query is already fast — the refresh job then adds operational overhead for no gain.

One caution about staleness. A materialised view is a snapshot, and if a downstream consumer does not know the snapshot time, it can compare a figure from the materialised view with one from a live table and get a mismatch that looks like a bug. Publishing the refresh timestamp alongside the data, and making sure consumers use it consistently, is what keeps that from happening.

SQLite has no materialised view statement, but a summary table populated by an INSERT ... SELECT is exactly the same idea, and it works in every engine.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

CREATE TABLE customer_revenue_summary (
  customer_id INTEGER PRIMARY KEY,
  order_count INTEGER NOT NULL,
  total_spend DECIMAL(12, 2) NOT NULL,
  refreshed_at TEXT NOT NULL
);

INSERT INTO orders (id, customer_id, amount, status) VALUES
  (1, 1, 120.00, 'shipped'),
  (2, 1, 340.50, 'pending'),
  (3, 2,  75.25, 'shipped');

-- Build the summary from the live data.
INSERT INTO customer_revenue_summary (customer_id, order_count, total_spend, refreshed_at)
SELECT customer_id, COUNT(*), ROUND(SUM(amount), 2), datetime('now')
FROM orders
GROUP BY customer_id;

SELECT * FROM customer_revenue_summary ORDER BY customer_id;

Two rows come back: customer 1 with 2 orders and 460.50, and customer 2 with 1 order and 75.25. Each row carries the timestamp of the refresh, so a consumer can tell how fresh the figures are.

The refresh pattern is the part worth getting right, and it is where most hand-rolled summaries go wrong. The naive version deletes everything and re-inserts, which leaves the summary table empty for the duration of the rebuild — and if the rebuild fails halfway, the table holds a partial summary that looks authoritative. The safe pattern builds the new contents in a separate table and swaps it in atomically.

On PostgreSQL and Oracle the built-in REFRESH MATERIALIZED VIEW does exactly that, and CONCURRENTLY lets you refresh without locking out readers — though it can only refresh a view that has a unique index. MySQL has no materialised views at all, which is why the summary table pattern is the standard approach there. SQLite has none either, so the pattern in the next answer is the portable one.

Build the new contents in a staging table, then swap it in inside a transaction. The reader sees either the old summary or the new one, never a partial rebuild.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

CREATE TABLE customer_revenue_summary (
  customer_id INTEGER PRIMARY KEY,
  order_count INTEGER NOT NULL,
  total_spend DECIMAL(12, 2) NOT NULL,
  refreshed_at TEXT NOT NULL
);

CREATE TABLE customer_revenue_summary_new (
  customer_id INTEGER PRIMARY KEY,
  order_count INTEGER NOT NULL,
  total_spend DECIMAL(12, 2) NOT NULL,
  refreshed_at TEXT NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

BEGIN;

-- Build the replacement out of sight.
DELETE FROM customer_revenue_summary_new;
INSERT INTO customer_revenue_summary_new (customer_id, order_count, total_spend, refreshed_at)
SELECT customer_id, COUNT(*), ROUND(SUM(amount), 2), datetime('now')
FROM orders
GROUP BY customer_id;

-- Swap it in atomically.
DELETE FROM customer_revenue_summary;
INSERT INTO customer_revenue_summary
SELECT * FROM customer_revenue_summary_new;

COMMIT;

SELECT * FROM customer_revenue_summary ORDER BY customer_id;

Two rows come back: customer 1 with 2 orders and 460.50, and customer 2 with 1 order and 75.25, both stamped with the refresh time.

The transaction is what makes the swap invisible. A reader that arrives before the commit sees the old summary; a reader that arrives after sees the new one. There is no moment at which the summary is empty or half-populated, because the DELETE and the INSERT are in the same transaction and neither is visible outside it until the commit.

That last point is exactly why the naive delete-then-insert is dangerous outside a transaction. If the process dies between the delete and the insert, the summary table is empty and the reports that read it silently return nothing — which is much worse than returning stale numbers, because it looks like a bug in the report rather than a failed job.

Two refinements for a larger system. Build the staging table once and reuse it rather than recreating it on every refresh, so the refresh does not fight with the table's own schema for locks. And if the refresh is expensive enough that you cannot rebuild the whole summary every time, refresh incrementally — apply only the rows whose source data changed since the last run, which means recording a watermark such as the maximum updated_at you have processed, and updating only the affected customer groups.