Materialized Views

A materialized view looks like a regular view — it's defined by a SELECT query — but unlike a regular view it physically stores the query result on disk at the moment it's created. Reading from it afterwards just reads the stored rows; it does not re-run the underlying query.

Creating a materialized view

Create and populate

SQL

CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
  date_trunc('month', order_date) AS month,
  region,
  SUM(total) AS revenue,
  COUNT(*) AS order_count
FROM orders
GROUP BY 1, 2;

The query runs once, immediately, and its result is written to disk as if it were a normal table. From then on, SELECT * FROM monthly_sales_summary is just a plain table scan — no joins, no aggregation, no recomputation.

Refreshing the data

Because the data is a snapshot, it does not automatically track changes to the underlying tables. New orders inserted after the materialized view was created simply won't appear until it is explicitly refreshed:

SQL

REFRESH MATERIALIZED VIEW monthly_sales_summary;

Warning

Between refreshes, a materialized view's data is stale by definition — this is a deliberate trade-off, not a bug. You are trading real-time accuracy for the speed of not recomputing an expensive query on every read. If your use case genuinely needs up-to-the-second data, a materialized view is the wrong tool; use a regular view (or the base tables) instead.

Refreshing without blocking readers

A plain REFRESH MATERIALIZED VIEW takes an exclusive lock on the view for the duration of the refresh — anyone trying to SELECT from it has to wait until the refresh finishes. For a large or frequently-queried materialized view, that pause can be disruptive. CONCURRENTLY avoids it:

SQL

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

Note

REFRESH ... CONCURRENTLY works by computing the new result set alongside the old one and diffing them, which requires PostgreSQL to identify individual rows uniquely. Because of that, it only works if the materialized view has at least one UNIQUE index on it:

SQL

CREATE UNIQUE INDEX monthly_sales_summary_month_region_idx
  ON monthly_sales_summary (month, region);

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

Views vs. materialized views

	View	Materialized View
Stores data?	No — re-runs the query each time	Yes — stores the result on disk
Freshness	Always current	Stale until refreshed
Read cost	Cost of the full underlying query	Cost of a plain table scan
Refresh needed?	No	Yes, via `REFRESH MATERIALIZED VIEW`

When to reach for one

Materialized views shine for expensive aggregate reports and dashboards where the underlying data doesn't need to be real-time — a daily sales summary, a monthly active users count, a precomputed leaderboard. Refresh it on a schedule (a cron job, a scheduled task runner, or a trigger-based mechanism) that matches how fresh the data actually needs to be, and let every read against it be fast.

CREATE MATERIALIZED VIEW ... AS SELECT ... computes and stores the result immediately.
REFRESH MATERIALIZED VIEW recomputes the stored result — data is stale until you run it.
REFRESH MATERIALIZED VIEW CONCURRENTLY avoids locking out readers, but requires a unique index on the view.
Best suited to expensive, infrequently-changing aggregates rather than real-time data.