How to Refresh Materialized View in PostgreSQL Quickly

In PostgreSQL, you refresh a materialized view using the REFRESH MATERIALIZED VIEW command. This updates the data stored in the materialized view to match the current state of the underlying tables.

Syntax

The basic syntax to refresh a materialized view is:

• REFRESH MATERIALIZED VIEW [CONCURRENTLY] view_name;

REFRESH MATERIALIZED VIEW tells PostgreSQL to update the stored data.

CONCURRENTLY allows the view to be refreshed without locking out reads, but requires a unique index on the materialized view.

view_name is the name of the materialized view you want to refresh.

sql

REFRESH MATERIALIZED VIEW [CONCURRENTLY] view_name;

Example

This example shows how to create a materialized view and refresh it to update its data.

sql

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

-- Refresh the materialized view to update data
REFRESH MATERIALIZED VIEW sales_summary;

-- Refresh concurrently (if unique index exists)
-- REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Common Pitfalls

Common mistakes when refreshing materialized views include:

• Trying to use CONCURRENTLY without a unique index on the materialized view, which causes an error.
• Not refreshing the materialized view after changes in the underlying tables, leading to stale data.
• Expecting REFRESH MATERIALIZED VIEW to be automatic; it must be run manually or scheduled.

sql

/* Wrong: Refresh concurrently without unique index */
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

/* Right: Create unique index first */
CREATE UNIQUE INDEX sales_summary_product_id_idx ON sales_summary(product_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Quick Reference

Summary tips for refreshing materialized views:

Command	Description
REFRESH MATERIALIZED VIEW view_name;	Refreshes the materialized view, locking reads during refresh.
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;	Refreshes without locking reads; requires unique index.
CREATE UNIQUE INDEX idx_name ON view_name(column);	Needed for concurrent refresh to work.

Key Takeaways

Use REFRESH MATERIALIZED VIEW to update stored data in a materialized view.

Add CONCURRENTLY to refresh without blocking reads, but only if a unique index exists.

Always refresh materialized views after underlying data changes to avoid stale results.

Create a unique index on the materialized view before using CONCURRENTLY.

Refreshing materialized views is a manual or scheduled operation; it does not happen automatically.