REFRESH MATERIALIZED VIEW in PostgreSQL

Introduction

A materialized view stores the result of a query physically. REFRESH MATERIALIZED VIEW updates this stored data to match the current data in the original tables.

When you want to update a summary table that speeds up reports.

After data changes in base tables and you want the materialized view to show fresh data.

To improve performance by avoiding running complex queries repeatedly.

When you have a snapshot of data that needs periodic refreshing.

Before running reports that rely on the latest data in the materialized view.

Syntax

PostgreSQL

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] view_name;

The CONCURRENTLY option allows the view to be refreshed without locking it for selects, but requires a unique index.

Without CONCURRENTLY, the view is locked during refresh, blocking reads.

Examples

Refreshes the materialized view named sales_summary and locks it during the refresh.

PostgreSQL

REFRESH MATERIALIZED VIEW sales_summary;

Refreshes sales_summary without locking it for selects, allowing users to query it during refresh.

PostgreSQL

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Sample Program

This example creates a materialized view that counts products by category, refreshes it to store current data, then selects all rows ordered by category.

PostgreSQL

CREATE MATERIALIZED VIEW product_counts AS
SELECT category, COUNT(*) AS count
FROM products
GROUP BY category;

REFRESH MATERIALIZED VIEW product_counts;

SELECT * FROM product_counts ORDER BY category;

Important Notes

Refreshing a materialized view can take time depending on the data size.

Use CONCURRENTLY to avoid blocking reads, but ensure a unique index exists on the materialized view.

Materialized views do not update automatically; you must refresh them manually or via a schedule.

Summary

REFRESH MATERIALIZED VIEW updates stored query results to reflect current data.

Use CONCURRENTLY to refresh without blocking reads if supported.

Refreshing is needed after changes in underlying tables to keep data accurate.