How do materialized views work in PostgreSQL?

Question

Accepted Answer

A **materialized view** stores the *results* of a query physically (precomputed), unlike a regular view (which re-runs its query each time). This makes reading fast for expensive queries, at the cost of the data being only as fresh as the last **refresh** — a caching mechanism within the database.

## Regular view vs materialized view

```sql
-- regular VIEW — runs the query EVERY time (always fresh, but recomputed)
CREATE VIEW sales_summary AS SELECT region, SUM(amount) FROM orders GROUP BY region;

-- MATERIALIZED VIEW — stores the RESULTS physically (fast reads, but can be stale)
CREATE MATERIALIZED VIEW sales_summary_mat AS
SELECT region, SUM(amount) AS total FROM orders GROUP BY region;

SELECT * FROM sales_summary_mat;     -- reads STORED results — fast (no recompute)
```

A materialized view precomputes and stores the results, so querying it is fast (like reading a table) — ideal for expensive aggregations queried often.

## Refreshing (updating the stored data)

```sql
-- the data is stale until you refresh it
REFRESH MATERIALIZED VIEW sales_summary_mat;          -- recompute (locks reads during refresh)

-- CONCURRENTLY — refresh WITHOUT blocking reads (requires a unique index)
CREATE UNIQUE INDEX idx_sales_region ON sales_summary_mat(region);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary_mat;   -- reads continue during refresh
```

You must **refresh** to update the data. `REFRESH ... CONCURRENTLY` (needing a unique index) updates without blocking concurrent reads — important for production.

## When to use materialized views

```text
✓ EXPENSIVE queries (complex aggregations, joins) queried FREQUENTLY
✓ Data that doesn't need real-time freshness (dashboards, reports, analytics)
✓ Reducing load from repeatedly running the same heavy query
→ Refresh on a schedule (cron / pg_cron) or after data changes.

✗ Data needing real-time accuracy → use a regular view or query directly
```

## The trade-off

```text
Fast reads (precomputed) ⟷ Stale data (until refreshed) + storage cost.
You trade freshness and storage for read speed — a caching decision.
You can also index a materialized view (it's a physical table of results).
```

## Why it matters

Materialized views are a valuable PostgreSQL feature for **performance optimization** — they provide an in-database caching mechanism for expensive queries, so understanding them is useful for optimizing applications with costly, frequently-run queries.

The core value is precomputing and storing the results of expensive queries (complex aggregations, heavy joins) so that reading them is fast (like querying a table) rather than re-running the costly query each time — which is a significant optimization for dashboards, reports, and analytics where the same heavy query runs repeatedly.

Understanding the key **trade-off** is important: materialized views give fast reads but the data is **stale** until refreshed (and they cost storage), so they're a caching decision suited to data that doesn't need real-time freshness.

Knowing how to **refresh** them — especially `REFRESH ... CONCURRENTLY` (which updates without blocking reads, important for production availability, requiring a unique index) — and refresh strategies (on a schedule via cron/pg_cron, or after data changes) is practical knowledge for using them effectively.

Understanding **when to use** materialized views (expensive, frequently-run queries tolerant of slight staleness — dashboards, analytics) versus when not to (data needing real-time accuracy) reflects sound judgment.

Since expensive repeated queries are a real performance concern, and since materialized views provide an effective in-database caching solution (precomputed, indexable results) with a clear freshness/speed trade-off, understanding them — how they work, refreshing (especially concurrently), the trade-off, and appropriate use — is valuable, practically-relevant PostgreSQL knowledge for optimizing performance of analytical and reporting queries, a common need where materialized views offer a clean, built-in solution.