Menu

PostgreSQL Materialized Views and Views: Create and Refresh

A view is a saved query that runs every time you read it. A materialized view stores the query's result and changes only when you run REFRESH MATERIALIZED VIEW.

This page includes runnable editors. Edit, run, and see output instantly.

A view is a saved query you read like a table: CREATE VIEW name AS SELECT .... It stores no data, so every read runs the query again and always sees current rows. A materialized view, CREATE MATERIALIZED VIEW name AS SELECT ..., stores the result on disk and changes only when you run REFRESH MATERIALIZED VIEW name.

The order inserted after the view was created is in the result (ben with 90), because the view's query ran at the moment of the SELECT. Views name a join or filter you repeat, hide columns from users who should not see them, and keep old queries working when tables change.

Change or remove a view

CREATE OR REPLACE VIEW swaps the query in place. The new query must return the same columns, with the same names and types, in the same order; it may only add new columns at the end. For any other change, drop the view and create it again.

CREATE OR REPLACE VIEW paid_totals AS
  SELECT customer, sum(amount) AS total, count(*) AS orders
  FROM orders WHERE paid GROUP BY customer;

DROP VIEW paid_totals;
DROP VIEW IF EXISTS paid_totals CASCADE;  -- also drops views built on it

Insert and update through a view

A view over one table is automatically updatable if it has no GROUP BY, DISTINCT, aggregates, window functions, LIMIT or set operations. INSERT, UPDATE and DELETE on the view then change the table. WITH CHECK OPTION rejects rows the view could not show afterward:

Both changes reached products. Inserting (4, 'shelf', false) through the view fails with new row violates check option for view "active_products", because the view would not show it. For a view that is not updatable, an INSTEAD OF trigger can define what a write does.

Create and refresh a materialized view

A materialized view runs its query once and stores the rows. Reads are as fast as reading a table, but new data does not appear until you refresh:

Before the refresh, ana still shows 120; after it, 180. REFRESH reruns the whole query and replaces the stored rows. Use a materialized view for results that are expensive to compute and may be minutes or hours old: dashboards, reports, search summaries.

CREATE MATERIALIZED VIEW ... WITH NO DATA creates it empty, and reading it fails until the first refresh.

Refresh without blocking reads

A plain REFRESH locks the materialized view, so queries that read it wait until the refresh ends. REFRESH MATERIALIZED VIEW CONCURRENTLY builds the new result separately and applies only the differences, so reads continue. It needs a unique index on the materialized view, on plain column names and with no WHERE:

Without the unique index, the refresh fails with a hint to create one. CONCURRENTLY may be faster than a plain refresh when few rows change, and it cannot be used on a materialized view that was never populated. Either way, only one refresh of a materialized view runs at a time. Other indexes work as on a table, so index the columns your queries filter on.

Refresh on a schedule

PostgreSQL does not refresh materialized views by itself. Run the refresh from a scheduler, such as cron calling psql, or the pg_cron extension where it is installed:

# crontab: every 15 minutes
*/15 * * * * psql "$DATABASE_URL" -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals'
SELECT cron.schedule('refresh-totals', '*/15 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals');

View vs materialized view

ViewMaterialized view
Stores rowsnoyes
Data is currentalwaysas of the last refresh
Read speedsame as the querysame as a table
Indexesno (uses the tables' indexes)yes
Writablesimple views, yesno

The CREATE MATERIALIZED VIEW reference lists the remaining options, such as storage parameters and tablespaces.

Frequently Asked Questions

Does a PostgreSQL materialized view update automatically?

No. It keeps the result from its last REFRESH MATERIALIZED VIEW (or its creation) until you refresh it again. Schedule the refresh with cron, the pg_cron extension, or your application.

Is a view faster than running the query?

No. A plain view stores no data; PostgreSQL expands it into the query that reads it, so it runs at the same speed as writing the query out. Only a materialized view saves the work, at the cost of stale data.

Can I add an index to a materialized view?

Yes. A materialized view is stored like a table, so CREATE INDEX works on it. A unique index is also what REFRESH MATERIALIZED VIEW CONCURRENTLY requires.

How do I list views and materialized views?

In psql, \dv lists views and \dm lists materialized views. In SQL, query pg_views and pg_matviews.

Coddy programming languages illustration

Learn to code with Coddy

Get started