Views & Materialized Views

Saved queries that act like tables, and stored query results for expensive reports.

A view gives a query a name: applications select from it like a table, and it always reflects the current data. A materialized view stores the query's result, trading freshness for speed.

A live view and a stored one

project_staffing recomputes its join and aggregation every time it is queried.

dept_payroll stores the result when it is created (SELECT 4: four rows stored).

After Rohan's raise the materialized view still shows the old payroll, 315000.00.

REFRESH MATERIALIZED VIEW recomputes it: 415000.00.

Uses

Expecting a materialized view to show today's data automatically

Schedule REFRESH MATERIALIZED VIEW (e.g. with cron or pg_cron)

PostgreSQL never refreshes materialized views by itself.

What is the main difference between a view and a materialized view?

A view stores only the query and runs it on every use (always current). A materialized view stores the result and must be refreshed (fast reads, possibly stale).