Postgres materialized views (MVs) cache the result of expensive queries on disk. Query the MV instead of re-running the heavy SQL. The trade-off: data freshness. Choosing a refresh strategy is the whole game.
Basic syntax
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day,
SUM(amount) AS total,
COUNT(*) AS n_orders
FROM orders
GROUP BY 1;
CREATE UNIQUE INDEX ON daily_sales (day);
-- Refresh (blocking — readers blocked during refresh):
REFRESH MATERIALIZED VIEW daily_sales;
-- Refresh (concurrent — readers see old data until done):
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;CONCURRENTLY requires unique index
REFRESH MATERIALIZED VIEW CONCURRENTLY doesn't lock readers — they continue using the old data. But it needs a UNIQUE INDEX on the MV to compute the diff. Without one, you must use the blocking refresh.
Refresh scheduling
Cron + psql call is the simplest pattern. For continuous freshness: triggers on source tables enqueue a refresh, a worker debounces and refreshes every N seconds. Tools: pg_cron extension for in-DB scheduling, no external system needed.
When NOT to use MVs
Source table changes faster than refresh cadence → MV always stale → users complain. Required real-time data → use a regular indexed view or compute on read. Tiny query that's fast already → MV is unnecessary overhead.
Alternatives at higher scale
For massive aggregations: TimescaleDB continuous aggregates (incremental refresh — only recalc changed time buckets). For cross-DB: stream changes via CDC to a materialized cache (Materialize, RisingWave). MVs are right for medium scale; specialized tools beyond.