Postgres by Example: 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

-- 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 a UNIQUE index on the matview (so PostgreSQL can compute the diff incrementally), and it is slower than a plain refresh. 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.

To run (requires customers_example and orders_example):

$ psql -f source/materialized-views.sql postgres
DROP MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
 customer_id | customer_name | order_count | total_spent | last_order_id
-------------+---------------+-------------+-------------+---------------
           1 | Alice         |           2 |       80.00 |             2
           2 | Bob           |           0 |        0.00 |
           3 | Carol         |           0 |        0.00 |
(3 rows)
CREATE INDEX
CREATE INDEX
INSERT 0 1
REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
 ...

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.

Tip: Use matviews for dashboards: pre-aggregate "yesterday's metrics" once per night, then dashboards read instantly. For sub-minute freshness, a real-time write-path update or a regular view is usually a better fit.

Try it: Add several rows to orders_example. Query the matview — it still shows the old totals. Run REFRESH MATERIALIZED VIEW customer_spend_mv; and re-query. Then add more rows and use the CONCURRENTLY form while another session is reading.

Source: materialized-views.sql

Next: Functions and PL/pgSQL

Home: Postgres by Example