PostgreSQL Materialized Views

A regular view stores a query. Each time you read the view, PostgreSQL runs the query again. A materialized view stores the query and its result. Reading it feels like opening a saved photograph instead of walking outside to look at the scenery again.

  Regular view:       SELECT --> runs query every time --> fresh data
  Materialized view:  SELECT --> reads saved result    --> fast, maybe old

The saved result sits on disk like a real table. Speed improves a lot when the original query is slow.

When to Use One

• Dashboards that summarize millions of rows.
• Monthly reports where fresh-to-the-second data is unnecessary.
• Complex queries with many joins and calculations.

Think of a shop owner who counts the day's sales every evening and writes the total on a board. Customers read the board instead of recounting the till each time.

Creating and Refreshing

A materialized view needs one command to build and one command to bring up to date.

Creating a Materialized View

CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY order_date;

PostgreSQL runs the query once and stores the rows. Read the data like any table.

SELECT * FROM daily_sales
WHERE order_date >= '2026-01-01';

Data Becomes Old

New orders arrive after the view is built. The materialized view does not notice them. Its data stays frozen at the moment of creation until you refresh it.

  Time 9:00  View built      -> 100 orders saved
  Time 12:00 50 new orders   -> view still shows 100
  Time 12:05 REFRESH         -> view shows 150

Refreshing the Data

REFRESH MATERIALIZED VIEW daily_sales;

This command rebuilds the stored result. It locks the view, so readers wait until the refresh ends. A short refresh causes little trouble. A long refresh can block users.

Refreshing Without Blocking Readers

The CONCURRENTLY option lets users keep reading the old data while PostgreSQL builds the new data in the background.

CREATE UNIQUE INDEX idx_daily_sales_date
ON daily_sales (order_date);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

This option demands a unique index on the view. Create the index first. Concurrent refresh takes longer than a normal refresh, but nobody waits.

Managing a Materialized View

Indexes, empty builds, and changes keep a materialized view useful over time.

Adding Indexes

A materialized view holds real stored rows, so indexes work on it. Add an index on columns that your queries filter or sort by.

CREATE INDEX idx_daily_sales_revenue
ON daily_sales (revenue);

Regular views cannot hold indexes. This ability makes materialized views a strong tool for speed.

Creating Without Data

Build the structure now and fill it later with WITH NO DATA.

CREATE MATERIALIZED VIEW monthly_summary AS
SELECT date_trunc('month', order_date) AS month,
       SUM(amount) AS total
FROM orders
GROUP BY 1
WITH NO DATA;

Reading the view before the first refresh produces an error. Run REFRESH once to fill it.

Changing and Removing

ALTER MATERIALIZED VIEW daily_sales RENAME TO sales_by_day;
DROP MATERIALIZED VIEW sales_by_day;

PostgreSQL cannot edit the query of an existing materialized view. Drop it and create it again with the new query.

Comparison

Feature	View	Materialized View
Stores data	No	Yes
Always current	Yes	No, needs refresh
Supports indexes	No	Yes
Read speed on heavy queries	Slow	Fast
Disk space	None	Uses space

Scheduling Refreshes

PostgreSQL does not refresh a materialized view on its own. Use an outside scheduler such as cron on Linux, or the pg_cron extension, to run the REFRESH command every night or every hour.

Practice Exercise

Create a table of sales with a product and an amount. Insert ten rows. Build a materialized view that totals sales per product. Insert two more rows and read the view. Notice the totals stay the same. Refresh the view and read it again.