49 Materialized Views

A materialized view is a view whose result is stored on disk. Unlike a regular view, it does not re-run the query on every read — it returns the saved rows. The trade-off is staleness: until you REFRESH it, you see the result as of the last refresh. Materialized views are the standard way to cache expensive aggregations, search indexes, and pre-computed reports inside PostgreSQL.

What you’ll learn

• Creating and refreshing a materialized view
• REFRESH MATERIALIZED VIEW vs REFRESH MATERIALIZED VIEW CONCURRENTLY
• Indexing a materialized view
• Detecting how stale a matview is
• When to use a matview vs. a regular view vs. a cache table

Example

-- A materialized view: aggregate per customer
DROP MATERIALIZED VIEW IF EXISTS customer_spend_mv;
CREATE MATERIALIZED VIEW customer_spend_mv AS
SELECT c.id              AS customer_id,
       c.name            AS customer_name,
       count(o.id)       AS order_count,
       COALESCE(sum(o.total), 0) AS total_spent,
       max(o.id)         AS last_order_id
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Query it like a table
SELECT * FROM customer_spend_mv ORDER BY total_spent DESC;

-- Index it (a matview is a real table, so it supports indexes)
CREATE UNIQUE INDEX customer_spend_mv_pk ON customer_spend_mv (customer_id);
CREATE INDEX customer_spend_mv_total_idx ON customer_spend_mv (total_spent);

-- Add another order, then refresh
INSERT INTO orders_example VALUES (200, 1, 25.00);

-- Plain REFRESH locks the matview against reads for the duration
REFRESH MATERIALIZED VIEW customer_spend_mv;

-- CONCURRENTLY lets readers continue. Requires a UNIQUE index on the matview.
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_spend_mv;

SELECT * FROM customer_spend_mv ORDER BY total_spent DESC;

-- Find matviews and when they were last refreshed (PostgreSQL keeps the relation modification time)
SELECT schemaname, matviewname, hasindexes, ispopulated
FROM pg_matviews
WHERE schemaname = 'public';

DROP MATERIALIZED VIEW IF EXISTS customer_spend_mv;

A materialized view is a table that PostgreSQL keeps in sync with a query — but only when you ask. REFRESH MATERIALIZED VIEW name re-runs the underlying SELECT and replaces the stored rows. By default, the refresh takes an ACCESS EXCLUSIVE lock — readers cannot query during the refresh.

REFRESH MATERIALIZED VIEW CONCURRENTLY updates the matview without blocking readers. The price is that it requires an already populated matview and a UNIQUE index covering all rows, using column names rather than expressions. It reruns the full query and reconciles the results; it is not incremental view maintenance. For matviews that need to stay queryable, this is the right choice.

A matview is a real table on disk — you can create indexes on it, ANALYZE it, even VACUUM it. That makes it ideal for caching expensive query results that many other queries then read.

Run the example

To run (requires customers_example and orders_example):

$ psql -f source/materialized-views.sql postgres
DROP MATERIALIZED VIEW
SELECT 3
 customer_id | customer_name | order_count | total_spent | last_order_id
-------------+---------------+-------------+-------------+---------------
           1 | Alice         |           3 |      179.00 |            10
           2 | Bob           |           0 |           0 |
           3 | Carol         |           0 |           0 |
(3 rows)

CREATE INDEX
CREATE INDEX
INSERT 0 1
REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
 customer_id | customer_name | order_count | total_spent | last_order_id
-------------+---------------+-------------+-------------+---------------
           1 | Alice         |           4 |      204.00 |           200
           2 | Bob           |           0 |           0 |
           3 | Carol         |           0 |           0 |
(3 rows)

 ...

Common pitfalls

• A matview is stale until refreshed. If you forget to refresh, reports lag silently. Set up a cron or scheduler.
• REFRESH ... CONCURRENTLY requires at least one UNIQUE index. Without one, PostgreSQL errors. Add a unique index on whatever you treat as the matview’s “primary key” (often a column from the source table’s PK).
• Foreign keys cannot reference a matview, and triggers do not fire on refresh — these are real differences from ordinary tables.
• REFRESH runs the original SELECT from scratch. If that query is slow, the refresh is slow. For incremental refreshes, you currently need to roll your own or use the pg_ivm extension.

Downloads

SQL file: materialized-views.sql · All lesson files · Setup guide