Materialized Views in PostgreSQL

A regular view re-runs its query every single time — great for correctness, but potentially slow for expensive aggregations run repeatedly. A materialized view instead physically stores the query's result, like a snapshot, that you refresh on demand.

Creating a Materialized View

CREATE MATERIALIZED VIEW department_summary AS
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

The heavy GROUP BY/AVG calculation runs once, right now — not every time someone queries it.

Querying It

SELECT * FROM department_summary;
-- instant — reads the stored snapshot, no recalculation

Refreshing the Data

Unlike a regular view, a materialized view doesn't automatically stay in sync with the underlying tables — you must refresh it explicitly.

REFRESH MATERIALIZED VIEW department_summary;

By default this locks the view against reads while refreshing. To allow queries to keep reading the old data while a refresh runs:

REFRESH MATERIALIZED VIEW CONCURRENTLY department_summary;
-- requires a UNIQUE index on the materialized view first

Regular View vs Materialized View

	View	Materialized View
Stores data	No — re-runs the query	Yes — stores a snapshot
Always up-to-date	Yes	Only after a REFRESH
Query speed	Same as underlying query	Fast — reads pre-computed data
Good for	Simplifying/restricting access to live data	Expensive aggregations queried often, where slightly stale data is acceptable

When to Use One

Reach for a materialized view when a query is expensive (heavy joins/aggregations) and gets run often, but the data doesn't need to be second-by-second fresh — a dashboard refreshed every 15 minutes is a classic example.

Common Errors & Troubleshooting

• Data looking stale after the underlying table changed — this is expected; a materialized view only updates when you explicitly run REFRESH MATERIALIZED VIEW — it does not auto-sync.
• ERROR: cannot refresh materialized view "department_summary" concurrently — REFRESH ... CONCURRENTLY requires at least one UNIQUE index on the materialized view first; create one, or drop CONCURRENTLY if a brief lock during refresh is acceptable.
• Refresh taking a long time and blocking reads — a plain REFRESH MATERIALIZED VIEW locks the view for the duration; use CONCURRENTLY (once you've added the required unique index) if reads need to keep working during the refresh.

Key Takeaways

• A materialized view physically stores its result; a regular view recalculates every time.
• Data can go stale — you must explicitly REFRESH it, there's no automatic sync.
• REFRESH ... CONCURRENTLY avoids blocking reads during the refresh, but needs a unique index on the view first.