When should an expensive query become a materialized view, and how fresh must it stay?

Use a materialized view when the same expensive query is read often enough that precomputing it is cheaper than recomputing it, and when readers can tolerate defined staleness. In PostgreSQL, a materialized view stores query results and must be refreshed explicitly, so the real design question is not just performance; it is whether you can state a freshness contract such as “up to 15 minutes old” and operate it reliably with refresh cost, reader concurrency, and failure behavior in mind.

When is a materialized view the right tool?

PostgreSQL materialized views “persist the results in a table-like form,” and queries read directly from that stored data rather than rerunning the underlying statement each time, which is why they help with repeated expensive work (PostgreSQL materialized views documentation). That makes them a good fit for dashboard aggregates, remote-source caching, and summary tables that many readers reuse.

Do not materialize a query just because it is slow once. Materialize when three conditions hold together: the query is reused, the base data changes more often than readers need perfectly current answers, and a full refresh is operationally affordable. PostgreSQL refresh “completely replaces the contents of a materialized view,” so if recomputation is huge, frequent refreshes may simply move the pain into a background job (PostgreSQL REFRESH MATERIALIZED VIEW documentation).

How fresh must it stay?

Set freshness from product semantics, not database convenience. Ask: what decision breaks if the answer is 5 minutes old, 1 hour old, or yesterday old? PostgreSQL’s own example is historical sales summaries where users “may not care about the incomplete data for the current date,” and a nightly refresh is acceptable (PostgreSQL materialized views documentation).

A good contract is explicit and observable: “This account summary is refreshed every 15 minutes and may be up to 20 minutes old during failures.” Add a refreshed_at value to the result your application returns, or pair the materialized view with a metadata table updated by the same refresh job. Readers should know the age of the data without guessing.

Can readers continue during refresh?

Yes, if you design for it. PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY, which refreshes “without locking out concurrent selects,” but only if the view already has suitable data and at least one qualifying UNIQUE index covering all rows (PostgreSQL REFRESH MATERIALIZED VIEW documentation). Without CONCURRENTLY, refresh may complete faster and use fewer resources, but it can block readers.

There is also a hard boundary for operations: PostgreSQL allows only one refresh at a time per materialized view. So overlap prevention should be part of the contract. Do not rely on two schedulers politely checking whether a refresh is running; enforce a single refresh path in your job system, because the database itself serializes refreshes on the same view.

Worked example: hourly account summary?

Suppose account_events drives an expensive per-account rollup used by support and billing dashboards hundreds of times per hour. Create a materialized view for account_hourly_summary, add the required UNIQUE index for concurrent refresh, and publish this contract:

• Target freshness: 15 minutes
• Reader guarantee: data may be up to 20 minutes old
• Refresh mode: concurrent
• Failure behavior: keep serving last successful snapshot and show refreshed_at
• Escalation: if age exceeds 20 minutes, mark the widget stale or temporarily fall back to the base query for a narrow account lookup

That is better than an implicit promise of “real time” that your refresh process cannot actually keep.

What if refresh falls behind?

Widen the interval, shrink the view, or stop materializing. If the full refresh cost is so high that you miss your freshness window, the materialized view is not meeting its contract.

Q: Should every reporting query become a materialized view?
No. Reuse plus tolerated staleness is the threshold; one-off analysis usually does not justify refresh operations.

Q: Should I always use CONCURRENTLY?
No. Use it when readers must keep reading during refresh and you can satisfy the UNIQUE index requirement. Otherwise a plain refresh may be simpler.

Next step: write a one-sentence stale-result contract for your slow query, then test whether a full refresh can meet it before you commit to materializing.

Reviewed: 2026-09-05