PostgresqlHow-ToBeginner · 3 min read

How to Refresh Materialized View Concurrently in PostgreSQL

Use the REFRESH MATERIALIZED VIEW CONCURRENTLY view_name; command in PostgreSQL to update a materialized view without locking it for selects. This requires the materialized view to have a unique index.

📐

Syntax

The syntax to refresh a materialized view concurrently in PostgreSQL is:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

Here, view_name is the name of your materialized view.

The CONCURRENTLY keyword allows the view to be refreshed without locking out selects, so queries can still read from it during the refresh.

Note: The materialized view must have a unique index for concurrent refresh to work.

sql

REFRESH MATERIALIZED VIEW CONCURRENTLY your_materialized_view;

💻

Example

This example shows how to create a materialized view with a unique index and refresh it concurrently.

sql

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

CREATE UNIQUE INDEX sales_summary_product_id_idx ON sales_summary(product_id);

-- Refresh the materialized view concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Output

CREATE MATERIALIZED VIEW CREATE UNIQUE INDEX REFRESH MATERIALIZED VIEW

⚠️

Common Pitfalls

Common mistakes when refreshing materialized views concurrently include:

Not having a unique index on the materialized view, which causes an error.
Trying to use CONCURRENTLY on a materialized view without the unique index.
Expecting the refresh to be instantaneous; it still takes time depending on data size.

Example of wrong usage:

sql

-- This will fail if no unique index exists
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

-- Correct approach: create a unique index first
CREATE UNIQUE INDEX sales_summary_product_id_idx ON sales_summary(product_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Output

ERROR: cannot refresh materialized view concurrently without a unique index CREATE UNIQUE INDEX REFRESH MATERIALIZED VIEW

📊

Quick Reference

Command	Description
REFRESH MATERIALIZED VIEW view_name;	Refreshes the materialized view with exclusive lock.
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;	Refreshes without locking selects; requires unique index.
CREATE UNIQUE INDEX index_name ON view_name(column);	Creates unique index needed for concurrent refresh.

✅

Key Takeaways

Use REFRESH MATERIALIZED VIEW CONCURRENTLY to update without blocking reads.

Ensure your materialized view has a unique index before refreshing concurrently.

Without a unique index, concurrent refresh will fail with an error.

Concurrent refresh allows your application to keep using the view during update.

Regular refresh keeps materialized views up to date with underlying data.