Materialized Views

A regular view stores no data of its own — it re-runs its query every time it is accessed. A materialized view flips that trade-off: it physically stores the result set of its query, just like a real table, and only updates that stored data when explicitly told to. This makes reading from a materialized view very fast, at the cost of the data potentially being out of date until the next refresh.

Creating and refreshing a materialized view

In PostgreSQL, a materialized view is created with CREATE MATERIALIZED VIEW and updated on demand with REFRESH MATERIALIZED VIEW.

Creating a materialized view for an expensive report

SQL

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
  order_date,
  region,
  COUNT(*)         AS order_count,
  SUM(amount)      AS total_revenue,
  AVG(amount)      AS avg_order_value
FROM orders
GROUP BY order_date, region;

Querying it is instant, because the aggregation has already been computed and stored — the database is just reading rows, not recalculating SUM and AVG across the whole orders table.

Reading from the materialized view

SQL

SELECT * FROM daily_sales_summary
WHERE order_date = CURRENT_DATE - 1;

To bring the stored data back in sync with the underlying tables, the materialized view must be refreshed explicitly:

Refreshing the stored result set

SQL

REFRESH MATERIALIZED VIEW daily_sales_summary;

REFRESH MATERIALIZED VIEW

Data can become stale between refreshes

A materialized view does not automatically track changes to its underlying tables. If new orders are inserted after the last REFRESH, daily_sales_summary will not reflect them until it is refreshed again. This is a deliberate design trade-off, not a bug — it is exactly what makes materialized views fast to read. Any system relying on one needs a clear strategy (a scheduled job, a cron task, or a trigger-based approach) for how often it gets refreshed, and that staleness window needs to be acceptable for the use case.

When to reach for a materialized view

Materialized views shine for expensive, repeatedly-run aggregate queries where perfectly real-time data is not required. A classic example is a daily sales dashboard: recomputing revenue-by-region-by-day across millions of order rows on every page load would be slow and wasteful, but refreshing that summary once, on a nightly schedule, and letting the dashboard read the pre-computed result is fast and perfectly acceptable, since the dashboard is not expected to show data from the last few minutes anyway.

PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY

A plain REFRESH locks the materialized view against reads while it rebuilds. PostgreSQL also offers REFRESH MATERIALIZED VIEW CONCURRENTLY, which rebuilds the data in the background and swaps it in without blocking concurrent SELECT queries — at the cost of requiring a unique index on the materialized view first.

A non-blocking refresh

SQL

CREATE UNIQUE INDEX ON daily_sales_summary (order_date, region);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

MySQL has no native materialized views

MySQL does not provide a CREATE MATERIALIZED VIEW statement at all. The common workaround is to simulate one manually: create a regular table with the same shape as the desired result, populate it with an INSERT ... SELECT, and set up a scheduled event (MySQL's EVENT scheduler) or an application-level job to periodically re-run that INSERT and replace the table's contents. It accomplishes the same goal, but it is entirely hand-rolled rather than a built-in database feature — a real dialect gotcha to be aware of when porting a design from PostgreSQL to MySQL.

A materialized view physically stores its query result, unlike a regular view which stores nothing.
It must be explicitly refreshed (REFRESH MATERIALIZED VIEW in PostgreSQL) to pick up changes in the underlying tables.
The data can be stale between refreshes — this is an intentional trade-off for fast reads.
Best suited for expensive aggregate reports/dashboards that do not need real-time freshness.
MySQL has no native materialized views and typically simulates them with a real table plus a scheduled refresh job.