Materialized view
A stored snapshot of a query result that reads quickly and gets refreshed when you can afford the recomputation.
See it
What it is
A materialized view stores the rows produced by a query instead of running that query on every read. You query the stored result like a table, then refresh it to pick up changes from the source tables. It trades freshness and refresh work for fast, predictable reads.
Reach for one when a dashboard or report repeatedly performs the same expensive joins and aggregates, and being a few minutes behind is acceptable. PostgreSQL can run REFRESH MATERIALIZED VIEW CONCURRENTLY so readers keep using the old result while the new one is built, provided the view has a suitable unique index.
Gotcha: the data is stale until a refresh finishes, and PostgreSQL normally recomputes the result rather than incrementally updating only changed rows. Refreshes can consume serious I/O and CPU, so schedule them around a stated freshness budget and monitor their duration instead of refreshing blindly.
Ask AI for it
Build this reporting result as a PostgreSQL materialized view with CREATE MATERIALIZED VIEW, explicit columns, and a unique index that allows REFRESH MATERIALIZED VIEW CONCURRENTLY. Schedule the refresh with pg_cron to meet a 15-minute freshness target, prevent overlapping refreshes with pg_try_advisory_lock, and record refresh start time, finish time, row count, and failure. Output the forward and rollback migrations, cron SQL, monitoring query, and a sample read.