A view is a name for a query; a materialized view is that query’s result written down as data. You trade freshness for speed, and the trade only works if you decide how stale the readers can tolerate before you build it.

That decision is what makes the mechanism interesting: the refresh is not maintenance you can forget, and the wrong way to run it takes a lock that stops everyone reading the very data you precomputed for them.

A saved answer, not a live one

A plain view is a stored query, recomputed on every read. A materialized view persists the result as a table you can index and query directly, so the join and the big range scan happen at refresh time instead of per request. On sqlite 3.53 with 200,000 sales rows across 200 regions and 400 days, the region summary as a view costs 15.286 ms per read; the same answer precomputed costs 0.039 ms. That is 390x, so the saved answer pays for itself after about 390 reads — under ten seconds at fifty reads a second.

The cost does not disappear, it moves. The precomputed table is correct only as of its last refresh, and Postgres gives you no incremental maintenance by default: REFRESH MATERIALIZED VIEW completely replaces the contents and discards the old ones, rebuilding the whole result no matter how little changed.

You choose when to refresh, and how

A full refresh is blunt, so materialize at a grain you can repair cheaply. Rebuilding the whole region summary took 16.41 ms and rewrote all 200 rows to reflect two changed source rows. Materialized at day grain instead, an incremental refresh deletes and recomputes only the affected day: 2.08 ms and 4 rows. Same answer, work proportional to the change rather than to the table.

CONCURRENTLY exists because of the lock. A plain refresh takes ACCESS EXCLUSIVE, which conflicts with every other lock mode including a bare SELECT. REFRESH MATERIALIZED VIEW CONCURRENTLY takes EXCLUSIVE, which allows concurrent reads and blocks only writes. The price is real: it requires at least one unique index on the view that uses only column names and includes all rows — not an expression index, not one with a WHERE clause — the view must already be populated, WITH NO DATA cannot be combined with it, and only one refresh at a time may run against a given view.

Staleness is a correctness decision

Adding two rows to the source made 2 of 200 regions report a different revenue: region 3 shows 1,550,199 live against 550,200 materialized. Every read still succeeded and still looked authoritative. That is the shape of the problem — the wrongness is silent, and its size is bounded only by the refresh interval multiplied by the write rate.

So name the freshness budget first and pick the cadence from it: a weekly rollup and a near-real-time dashboard need intervals an order of magnitude apart, and the second probably should not be a materialized view at all. When every read must reflect the latest committed write, a stored answer is a bug with a delay on it.

When it breaks

The first failure is the refresh that blocks its own readers. Running the rebuild inside one transaction, 4 of 5 reads failed while the lock was held for 1,530 ms — the view was unavailable for exactly as long as the rebuild took. In WAL mode that same 1,530 ms refresh blocked nothing at all (0 of 308,923 reads failed), and building into a staging table and swapping it in behind a short transaction kept the exclusive window to 2 ms with 0 of 194,123 reads failing. The blast radius is every request that touches the view, for the whole rebuild, and it grows with the size of the answer rather than the size of the change.

The second failure is the view nobody refreshes. Triple the source and an un-refreshed summary still reports 111,923,298 instead of 335,769,894 — under-reporting by 67%, with no error raised anywhere. The numbers stay plausible, which is worse than a crash: dashboards, alerts, and reports keep working, and every decision made from them uses last week’s data.

Check it quickly

Confirm the view can be refreshed concurrently, then confirm it actually was:

psql -c "\d+ mv_region_summary"
psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_region_summary;"
psql -c "SELECT pid, wait_event_type, left(query,60) FROM pg_stat_activity WHERE state <> 'idle';"
psql -c "SELECT count(*), max(refreshed_at) FROM mv_refresh_log;"

\d+ must show a unique index that uses plain columns and has no WHERE clause; without it the CONCURRENTLY refresh fails immediately rather than silently degrading. While it runs, pg_stat_activity should show readers proceeding — a session parked in Lock: relation means something is holding a conflicting lock. The last query is the one that catches the silent failure: if max(refreshed_at) is older than the freshness budget you declared, you are serving stale numbers no matter how healthy they look.

Sources