Postgresql – Best practice for working with materialized views in Postgres

postgresql

As background, I work with many materialized views which are based on PostGIS spatial queries, some of which take days to refresh (these views are infrequently refreshed whenever the underlying spatial data (ie road networks) are updated). I then have many other views which are dependent on these materialized views.

While using materialized views in general works well, it becomes a nightmare when I need to alter the definition of one of the views (for instance, changing a join condition or adding new columns). Since there's no equivalent of CREATE OR REPLACE ... for materialized views, I end up having to delete and then recreate all the dependent objects.

Recently I've started "wrapping" all my materialized views in standard views as a way around this. So basically every materialized view has a corresponding standard view which selects all columns direct from the materialized view. Then no dependents directly reference the materialized view, they only ever reference the wrapper view. This allows me to temporarily replace the definition of the wrapper to point at a different data source (not the materialized view), so I can then alter the definition of the materialized view and then lastly re-direct the wrapper back to the materialized view. Phew! It works, and avoids the need for the cascading drop/recreate object process, but still seems very clunky.

Is there a best practice process for working with materialized views to overcome these limitations? What's the usual approach to take here to avoid the cascading delete/recreate steps?

Best Answer

While using materialized views in general works well, it becomes a nightmare when I need to alter the definition of one of the views (for instance, changing a join condition or adding new columns).

One such idea is to put them all in the same schema, for instance foo_matviews or foo_cache, and then to create a script that drops the schema entirely (with CASCADE), and then reinitializes the schema and all the internal mat-views back.

To be honest, it's always worked so well I've been kind of confused at why this is an issue for anyone. I always have a few SQL scripts in my code repo that do something similar to this. I've never even needed anything more complex like schema migration tools like flyway.

That all said, after you have them all in your schema you can create an extension if you wish.

Related Question