# Materialized Views

> Store query results in materialized views and refresh them while managing reader access.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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 {#example}

```sql {num="49.1" caption="Materialized Views"}
-- 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 {#run}

To run (requires `customers_example` and `orders_example`):

```text
$ 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 {#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] 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.

> [!NOTE] Try it
> Run this experiment before the script's final `DROP MATERIALIZED VIEW`, or recreate the view and its indexes first. 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.

## Downloads {#downloads}

SQL file: [materialized-views.sql](/examples/source/materialized-views.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)