Materialized Views
A materialized view stores the result of a query on disk, like a precomputed cache. Queries against it are instant (no aggregation needed), but the data may be stale until refreshed. REFRESH MATERIALIZED VIEW updates it.
A materialized view is a printed report filed in a drawer — instant to read (no computation), but potentially outdated. REFRESH is printing a new report. CONCURRENTLY is printing it in the back room and swapping it in the drawer without disturbing people reading the current one.
Regular views are just named queries — no stored data, no performance benefit (the planner inlines them). Materialized views physically store the result. REFRESH MATERIALIZED VIEW CONCURRENTLY updates data without locking out readers (requires a unique index on the view). Use cases: expensive aggregations over large tables (monthly sales totals), pre-joining tables for reporting, computing OLAP metrics from OLTP data. Freshness tradeoff: refresh more frequently for more current data, less frequently for less compute overhead.
REFRESH MATERIALIZED VIEW (without CONCURRENTLY) acquires an ExclusiveLock — blocking all reads during refresh. CONCURRENTLY computes the new result separately, then diffs and applies changes, releasing the old version only when the new one is ready. No blocking reads, but twice the compute and time. Automating refreshes: pg_cron or external schedulers (cron, Airflow) trigger REFRESH on a schedule. Incremental materialized views (full incremental maintenance) aren't natively supported in PostgreSQL — the entire view is recomputed on each refresh. Solutions: dbt (models as materialized views with incremental refresh logic), Materialize (streaming SQL, continuously updated), or application-level incremental aggregation tables.
Materialized views are my go-to for expensive reports that run frequently. I create a unique index on the view and use REFRESH CONCURRENTLY to avoid blocking reads. I schedule refreshes via pg_cron — every hour for dashboards, nightly for heavy aggregations. For real-time freshness needs, I layer an application-level incremental table updated by triggers or CDC rather than refreshing the entire materialized view.
Refreshing a large materialized view without CONCURRENTLY in a production system. It acquires an ExclusiveLock, blocking all reads for the duration — potentially minutes for large views.