Redshift - Managing updates to Materialized Views without using CASCADE

Question

We are currently creating materialized views using a procedure that drops the existing view if it exists and then recreates it. However, we see an issue with a materialized view that is depended on by other views and functions. We need to update this materialized view without using CASCADE, as we want to avoid dropping the dependent objects.
Could you advise on how to alter or update the materialized view while preserving its dependencies? What are the best practices for managing this in Redshift?

MilindOke · Answer

At this time there is no way to circumvent the dependency and you will need to drop-create. However, you can minimize impact of these drop-creates to your users by controlling when you deploy newer version of your application. It is expected that system rollouts are communicated to users and they are not accessing the system during these times. And they should anticipate disruptions if they are still going to access the systems during releases, in fact they can cause disruptions to the release also! If your systems are expected to be 24x7 then this needs to be factored in when architecting it.

secondabhi_aws · Answer

Hi,

Thank you for posting your question here.

Based on my experience, some of the recommendations would be as below:

When managing materialized views in Redshift, it's important to consider the dependencies between the materialized view and other objects in your database. Dropping a materialized view that is depended upon by other views or functions can lead to issues, as you've experienced.

Here are some best practices for updating materialized views in Redshift while preserving dependencies:

1. **Refreshing the Materialized View**: Instead of dropping and recreating the materialized view, you can use the `REFRESH MATERIALIZED VIEW` command to update the contents of the materialized view. This will update the materialized view without affecting the dependent objects.

```sql
REFRESH MATERIALIZED VIEW my_materialized_view;
```

2. **Incremental Refreshes**: If your materialized view is being updated frequently, consider using incremental refreshes. This involves updating the materialized view with only the changes since the last refresh, rather than a full refresh. This can be more efficient and minimize the impact on dependent objects.

```sql
CREATE MATERIALIZED VIEW my_materialized_view
REFRESH INCREMENTAL;
```

3. **Scheduling Refreshes**: Automate the refresh process by scheduling regular updates to the materialized view. This can be done using a scheduler tool or by creating a scheduled query in Redshift.

```sql
CREATE EVENT SCHEDULE my_schedule
    WHEN SCHEDULE EVERY 1 HOUR;

REFRESH MATERIALIZED VIEW my_materialized_view
    ON SCHEDULE my_schedule;
```

4. **Monitoring Dependencies**: Keep track of the objects that depend on your materialized views. You can use the `PG_DEPEND` system catalog view to identify these dependencies.

```sql
SELECT
    dependent.relname AS dependent_object,
    dependent.relkind AS dependent_type,
    referenced.relname AS referenced_object,
    referenced.relkind AS referenced_type
FROM
    pg_depend
    JOIN pg_class AS dependent ON pg_depend.dependentid = dependent.oid
    JOIN pg_class AS referenced ON pg_depend.referencedid = referenced.oid
WHERE
    referenced.relname = 'my_materialized_view'
    AND dependent.relkind IN ('v', 'f');
```

5. **Gradual Rollouts**: If you need to make significant changes to a materialized view, consider a gradual rollout approach. This involves creating a new materialized view with the desired changes, and then gradually migrating dependent objects to use the new view, rather than dropping the old one.

By following these best practices, you can effectively manage and update materialized views in Redshift while preserving the dependencies on those views.

Comment here if you have additional questions, happy to help.

Abhishek

Redshift - Managing updates to Materialized Views without using CASCADE

Relevant content