A materialized view is a query result stored as a physical table. Unlike a regular view (which re-executes its SQL every time you select from it), a materialized view holds the answer on disk. Reads become a simple table scan or index lookup instead of a 12-way join over a billion rows.

You reach for this pattern when:

• A query is read-heavy and computation-heavy (dashboards, leaderboards, reports).
• The underlying data changes far less often than it's queried.
• Users tolerate some staleness — seconds, minutes, or even hours.

Concrete example. An e-commerce site shows "Top 100 products this week" on its homepage. The honest query joins orders, order_items, and products, filters by date, groups, sums, and sorts — maybe 800ms on a warm cache. The homepage is hit 50,000 times an hour. That's 40 minutes of database CPU every hour just for one widget. Instead, define a materialized view top_products_weekly refreshed every 5 minutes. Each refresh runs the expensive query once; every page load reads 100 rows from an indexed table in under 2ms.

The refresh question is the whole game. Three common strategies:

• Scheduled refresh — cron-style, every N minutes. Simple, predictable load. Staleness bounded by interval.
• On-demand refresh — triggered by a write or a queue event. Fresher, but couples writers to view cost.
• Incremental refresh — only recompute rows affected by recent changes. Best performance, hardest to get right. Postgres supports this poorly out of the box; tools like pg_ivm or stream processors (Materialize, RisingWave) handle it.

Rule of thumb for sizing the refresh interval: set it to roughly 1/10th of the acceptable staleness window. If product owners say "an hour-old leaderboard is fine," refresh every 6 minutes. This gives buffer for failed refreshes and avoids the trap of "refresh exactly at the staleness boundary" — one missed run and you're serving stale data.

Pitfalls to watch:

• Refresh storms. A 30-second refresh that now takes 45 seconds will overlap itself. Always guard with a lock or use Postgres's REFRESH MATERIALIZED VIEW CONCURRENTLY.
• Index drift. The view needs its own indexes — they don't inherit from base tables.
• Hidden coupling. Downstream consumers start assuming the view exists. Treat its schema like a public API.
• Cost of consistency. If users compare the dashboard number to a "live" number elsewhere and they don't match, you'll get bug reports. Show "as of HH:MM" timestamps.

Materialized views are caching, but with the database doing the work — which means transactional guarantees on the refresh and no separate cache layer to invalidate.