ALTER MATERIALIZED VIEW

Use ALTER MATERIALIZED VIEW to:

Rename a materialized view.
Change owner of a materialized view.
Change retain history configuration for the materialized view.

Replace a materialized view. (Public preview)

Syntax

Rename

To rename a materialized view:

ALTER MATERIALIZED VIEW <name> RENAME TO <new_name>;

Syntax element	Description
`<name>`	The current name of the materialized view you want to alter.
`<new_name>`	The new name of the materialized view.

Change owner

To change the owner of a materialized view:

ALTER MATERIALIZED VIEW <name> OWNER TO <new_owner_role>;

Syntax element	Description
`<name>`	The name of the materialized view you want to change ownership of.
`<new_owner_role>`	The new owner of the materialized view.

To change the owner of a materialized view, you must be the owner of the materialized view and have membership in the <new_owner_role>. See also Privileges.

(Re)Set retain history config

To set the retention history for a materialized view:

ALTER MATERIALIZED VIEW <name> SET (RETAIN HISTORY [=] FOR <retention_period>);

Syntax element	Description
`<name>`	The name of the materialized view you want to alter.
`<retention_period>`	Private preview. This option has known performance or stability issues and is under active development. Duration for which Materialize retains historical data, which is useful to implement durable subscriptions. Accepts positive interval values (e.g. `'1hr'`). Default: `1s`.

To reset the retention history to the default for a materialized view:

ALTER MATERIALIZED VIEW <name> RESET (RETAIN HISTORY);

Syntax element	Description
`<name>`	The name of the materialized view you want to alter.

Replace materialized view

PREVIEW This feature, available starting in v26.10, is only recommended for use in development environments. It is in public preview. It is under active development and may have stability or performance issues. It isn't subject to our backwards compatibility guarantees.

To replace an existing materialized view in-place with a replacement materialized view:

ALTER MATERIALIZED VIEW <name> APPLY REPLACEMENT <replacement_materialized_view>;

Syntax element	Description
`<name>`	The name of the materialized view to replace.
`<replacement_materialized_view>`	The name of a replacement materialized view specifically created for the target materialized view. See `CREATE REPLACEMENT MATERIALIZED VIEW <replacement_view>...FOR <name>...`.

Details

Replacing a materialized view

You can use CREATE REPLACEMENT MATERIALIZED VIEW with ALTER MATERIALIZED VIEW ... APPLY REPLACEMENT to replace materialized views in-place without recreating dependent objects or incurring downtime.

When replacing a materialized view, the operation:

Replaces the materialized view’s definition with that of the replacement view and drops the replacement view at the same time.
Emits a diff representing the changes between the old and new output.

Recommended checks before replacing a view

Before applying, verify that the replacement materialized view is hydrated to avoid downtime:

SELECT
   mv.name,
   h.hydrated
FROM mz_catalog.mz_materialized_views AS mv
JOIN mz_internal.mz_hydration_statuses AS h ON (mv.id = h.object_id)
WHERE mv.name = '<replacement_view>';

Considerations

When applying the replacement, dependent objects must process the diff emitted by the operation. Depending on the size of the changes, this may cause temporary CPU and memory spikes.

Troubleshooting

Issue: Command does not return.

Common cause: The original materialized view is lagging behind the replacement. If the original is lagging behind the replacement, the command waits for the original view to catch up.

Action: Cancel the command and check whether the original materialized view is lagging behind the replacement.

To check whether the original materialized view is lagging behind the replacement, run the following query to check their write frontiers, substituting the names of your original and replacement materialized views.

SELECT o.name, f.write_frontier
FROM mz_objects o, mz_cluster_replica_frontiers f
WHERE o.name in ('<view>', '<view_replacement>')
AND f.object_id = o.id;

If the original materialized view is behind, rerun the query to check the progress of the original materialized view. If the rate of advancement suggests that catch up will take an extended period of time, it is recommended to drop the replacement view.

Privileges

The privileges required to execute this statement are:

Ownership of the materialized view.
In addition, to change owners:
- Role membership in new_owner.
- CREATE privileges on the containing schema if the materialized view is namespaced by a schema.

In addition, to apply a replacement:
- Ownership of the replacement materialized view.

Examples

Replace a materialized view

A replacement materialized view can only be applied to the target materialized view specified in the FOR clause of the CREATE REPLACEMENT MATERIALIZED VIEW statement.

Example Prerequisite

The following example creates a replacement materialized view winning_bids_replacement for the winning_bids materialized view. The replacement view specifies a different filter mz_now() > a.end_time than the existing view mz_now() >= a.end_time.

CREATE REPLACEMENT MATERIALIZED VIEW winning_bids_replacement
FOR winning_bids AS
SELECT DISTINCT ON (a.id) b.*, a.item, a.seller
FROM auctions AS a
JOIN bids AS b
  ON a.id = b.auction_id
WHERE b.bid_time < a.end_time
  AND mz_now() > a.end_time
ORDER BY a.id,
  b.amount DESC,
  b.bid_time,
  b.buyer;

The replacement view hydrates in the background.

Apply the replacement

Assume that winning_bids_replacement is hydrated to avoid downtime (see Recommended checks before replacing a view for details).

The following example replaces the winning_bids materialized view with winning_bids_replacement:

ALTER MATERIALIZED VIEW winning_bids
APPLY REPLACEMENT winning_bids_replacement;

For a step-by-step tutorial on replacing a materialized view, see Replace materialized views guide.