I'm currently using a standard view in my database, and I want to change it to a materialized view. I'm looking for a way to perform this kind of maintenance, not just for views but also for sources and sinks, without having to stop any dependent applications or reset the entire database. Is there a way to update materialized view definitions in place, similar to a 'create or replace' functionality, so that I don't have to drop the existing view?
Kai
Asked on Jan 14, 2024
Currently, the recommended approach for updating materialized views with minimal downtime is to create new materialized views with the desired changes and then use the rename command to replace the old ones. Here's an example process:
CREATE MATERIALIZED VIEW mv1_new AS ...DROP MATERIALIZED VIEW mv1;ALTER MATERIALIZED VIEW mv1_new RENAME TO mv1;This process reduces downtime but still requires applications to be resilient to the materialized view temporarily disappearing. There is consideration for adding an atomic 'rename and drop' command in the future to further minimize disruption. For now, if you have dependent materialized views, you'll need to recreate them as well and use the 'rename and drop' method for each dependent view.