Materialized views and refreshing

Storing the answer

A view reruns its query every time. When the query is expensive and the answer changes slowly, like a monthly revenue report, that is wasted work. A materialized view runs the query once and stores the result:

CREATE MATERIALIZED VIEW monthly_revenue_mv AS
SELECT date_trunc('month', o.ordered_at) AS month, p.category,
       sum(i.quantity * i.unit_price) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
GROUP BY 1, 2;

To get new data into it you ask explicitly:

REFRESH MATERIALIZED VIEW monthly_revenue_mv;

A refresh throws the old contents away and runs the whole query again.

What it buys and what it costs

We built a bigger version of the shop: one million orders with 2.5 million order lines (196 MB), and the report above, which has 72 rows (12 months, 6 categories). Medians of five runs, PostgreSQL 18.3 on a laptop:

Reading became essentially free. But every refresh costs as much as running the report once, and that grows with the tables, not with how much changed.

Stale between refreshes

      source       |   revenue
-------------------+--------------
 view              | 847157123.16
 materialized view | 847107123.16

Refreshing without locking readers out

A plain refresh takes the strongest table lock there is, ACCESS EXCLUSIVE, which blocks even SELECT. We started a refresh and, 0.3 seconds later, a simple query on the materialized view:

CONCURRENTLY refreshes without locking readers out: it holds a weaker lock that still allows SELECT. It needs two things:

1. A unique index on the materialized view, on plain columns, covering every row: here CREATE UNIQUE INDEX ON monthly_revenue_mv (month, category);.
2. The view must already be populated: the very first fill cannot be concurrent.

It is not free either: it still runs the full query. The documentation says a plain refresh tends to use fewer resources when many rows change, and a concurrent one may be faster when few do; in our test the two took the same time.

Reproduce the measurements

The data generator we used, on a throwaway database:

SELECT setseed(0.42);
CREATE TABLE products (id int PRIMARY KEY, category text NOT NULL);
INSERT INTO products
SELECT g, (ARRAY['Accessories','Audio','Displays','Furniture','Storage','Video'])[1 + g % 6]
FROM generate_series(1, 600) g;

CREATE TABLE orders (id int PRIMARY KEY, ordered_at timestamptz NOT NULL);
INSERT INTO orders
SELECT g, timestamptz '2025-01-01' + random() * interval '365 days'
FROM generate_series(1, 1000000) g;

CREATE TABLE order_items (
  order_id int NOT NULL REFERENCES orders, product_id int NOT NULL REFERENCES products,
  quantity int NOT NULL, unit_price numeric(10,2) NOT NULL);
INSERT INTO order_items
SELECT o, 1 + (random() * 599)::int, 1 + (random() * 3)::int, (100 + random() * 20000)::numeric(10,2)
FROM generate_series(1, 1000000) o, LATERAL generate_series(1, 1 + (random() * 3)::int + 0 * o) n;
VACUUM ANALYZE;

The + 0 * o matters: it makes the number of lines random per order. Without it, PostgreSQL evaluates the inner random() once and every order gets the same number of lines, which is what happened on our first try.

Check yourself

1. What is the difference between a view and a materialized view in what they store and when they do the work?

   Show answer

   A view stores only its query and runs it on every use. A materialized view stores the query’s result and runs the query only when refreshed.

   I could explain this without looking

2. Why did readers wait 1.4 seconds during a plain refresh but not during a concurrent one?

   Show answer

   A plain refresh takes an ACCESS EXCLUSIVE lock, which conflicts with every other lock, including the one a SELECT needs. CONCURRENTLY takes an EXCLUSIVE lock, which still allows reads.

   I could explain this without looking

3. What must exist before REFRESH MATERIALIZED VIEW CONCURRENTLY will run?

   Show answer

   A unique index on the materialized view that uses only column names and covers all rows, and the view must already have been populated once.

   I could explain this without looking