Best practice for working with materialized views in Postgres

Question

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?

Answer 1

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.

Answer 2

I use a number of scripts that work as installation packages, and I open and run from PgAdminIII. For example, one of them would be all my materialized views, with CREATE OR REPLACE, allocation of permissions, etc. Another would be a script for creating or altering tables:

CREATE TABLE IF NOT EXISTS mytable (myfield serial int);

DO $$BEGIN IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'mytable'::regclass AND attname = 'newfield') THEN
  ALTER TABLE mytable ADD newfield type; END IF;
END;$$;

DO $$BEGIN IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'mytable'::regclass AND attname = 'oldfield') THEN
  ALTER TABLE mytable DROP oldfield CASCADE;
END;$$;

So I'd open the altering tables script, add an alteration somewhere or at the end of the script, run it, and then if there's a cascade dropping or affecting other objects, open the script for those objects and re-run it. Note that running scripts in this manner on a 24/7 system can lead to a few seconds of downtime/failures, so it would be best preparing a separate script with all modifications to be run at once.