Unlike standard views in PostgreSQL, which support CREATE OR REPLACE VIEW (with limitations on column layout), PostgreSQL does not offer a native CREATE OR REPLACE MATERIALIZED VIEW command. Furthermore, altering the defining query via ALTER MATERIALIZED VIEW is not supported.

Because PostgreSQL tracks dependencies between database objects using internal Object Identifiers (OIDs) rather than relation names, simple table-swapping techniques (like renaming relations in a transaction) will leave dependent views pointing to the old relation. This guide explores the best and safest strategies for updating a materialized view definition without breaking dependencies or incurring extended downtime.

The Core Problem: Dependency Tracking by OID

When a view or function references a materialized view, PostgreSQL registers a dependency in system catalogs like pg_depend. If you attempt to rename the old materialized view and introduce a new one with the original name:

BEGIN;
ALTER MATERIALIZED VIEW public.order_summary RENAME TO order_summary_old;
ALTER MATERIALIZED VIEW public.order_summary_new RENAME TO order_summary;
COMMIT;

Any dependent views (such as public.customer_report) will continue to point to order_summary_old because their underlying queries reference the original object's OID, not its text name.

Recommended Solution: Pre-Computation + Transactional Rebuild

The standard, robust method is to pre-compute your new data into a temporary materialized view to avoid blocking production queries, then perform a quick transactional replacement where dependent objects are dropped and recreated.

Step 1: Create the New Materialized View and Indexes

First, build the replacement materialized view outside a transaction so you do not hold locks while the heavy query runs:

-- Create with the new definition and populate data
CREATE MATERIALIZED VIEW public.order_summary_v2 AS
SELECT
    customer_id,
    count(*) AS order_count,
    sum(amount) AS total_amount
FROM orders
GROUP BY customer_id;

-- Build required indexes beforehand
CREATE UNIQUE INDEX ON public.order_summary_v2 (customer_id);

Step 2: Swap Objects Inside a Transaction

Next, use a fast transaction block to drop the old view (cascading to dependent objects) and recreate the view and dependencies. Because the data has already been populated, this transaction completes in milliseconds:

BEGIN;

-- 1. Drop the old materialized view and cascade to dependents
DROP MATERIALIZED VIEW public.order_summary CASCADE;

-- 2. Rename the new materialized view and its indexes to the target name
ALTER MATERIALIZED VIEW public.order_summary_v2 RENAME TO order_summary;

-- 3. Recreate the dependent views and permissions
CREATE VIEW public.customer_report AS
SELECT
    customer_id,
    order_count
FROM public.order_summary
WHERE order_count > 0;

-- 4. Re-apply any grants
GRANT SELECT ON public.order_summary TO app_user;
GRANT SELECT ON public.customer_report TO app_user;

COMMIT;

How to Automatically Discover Dependent Objects

If your database schema is large and you need to ensure no dependencies are missed before running DROP ... CASCADE, you can query PostgreSQL's system catalogs (pg_depend and pg_rewrite):

SELECT 
    dependent_ns.nspname   AS dependent_schema,
    dependent_view.relname AS dependent_view,
    pg_get_viewdef(dependent_view.oid) AS view_definition
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace as dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
WHERE pg_depend.refobjid = 'public.order_summary'::regclass
  AND dependent_view.relname != 'order_summary';

This query provides the exact schema, view name, and current definition string for every dependent view so you can generate your migration scripts safely.

Architectural Best Practice: The View Indirection Pattern

If your application frequently requires updating materialized view definitions, consider adding a thin abstraction layer using standard views:

  • Create the materialized view with a version suffix (e.g., order_summary_data_v1).
  • Create a standard view (e.g., order_summary) that simply runs SELECT * FROM order_summary_data_v1.
  • Point all downstream reports, functions, and views to the standard view order_summary.

When you need to update the definition:

  1. Create and populate order_summary_data_v2.
  2. Run CREATE OR REPLACE VIEW public.order_summary AS SELECT * FROM order_summary_data_v2; (as long as existing columns remain consistent).
  3. Safely drop order_summary_data_v1 when no longer needed.

Conclusion

Because PostgreSQL binds relation dependencies by OID, you cannot swap materialized views simply by renaming them. The most dependable and downtime-free approach is to prepare the new materialized view ahead of time and perform a rapid DROP ... CASCADE and object recreation within an explicit BEGIN ... COMMIT transaction block.