How to Alter Materialized Views in PostgreSQL

Materialized views in PostgreSQL are an efficient way to store query results physically, which can drastically improve performance for complex queries or large datasets. You refresh the data when it suits you and avoid the cost of recomputing the query every time. This guide covers how to create, refresh, and modify them.

What are materialized views in PostgreSQL?

In PostgreSQL, a materialized view acts like a regular view but stores its data physically. It holds a snapshot of a query’s results that stays unchanged until you update the view. Materialized views are most useful for data that doesn’t change often and for resource-intensive queries.

Creating a materialized view

To create a materialized view, use the CREATE MATERIALIZED VIEW syntax:

CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

This creates a new materialized view in your database that holds the query results for fast access.

Refreshing a materialized view

As the underlying data changes, you’ll need to refresh the materialized view to keep it current. Run the REFRESH MATERIALIZED VIEW command to update it with fresh data:

REFRESH MATERIALIZED VIEW view_name;

If you need to read from the view while it refreshes, add the CONCURRENTLY keyword:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

This requires the materialized view to have a unique index.

Modifying a materialized view

PostgreSQL doesn’t support changing a materialized view’s structure or query directly, so to alter one you might have to drop it and recreate it. You can change properties such as the owner with commands like:

ALTER MATERIALIZED VIEW view_name OWNER TO new_owner;

For larger changes, you’ll need to recreate the view:

DROP MATERIALIZED VIEW IF EXISTS view_name;
CREATE MATERIALIZED VIEW view_name AS
SELECT new_columns
FROM new_source
WHERE new_condition;

Be careful when dropping a materialized view, because this erases its stored data.

Materialized views can significantly speed up data retrieval in PostgreSQL, especially for applications that rely on heavy data processing.