Matview Dependencies and Schema Changes #

A migration to widen orders.status from varchar(20) to text fails with an error naming a materialized view nobody remembered. Dropping and recreating that matview then breaks two others that select from it, and after the dust settles the dashboard shows yesterday’s numbers because the rebuilt views were never refreshed in the right order.

Materialized views are useful precisely because they are stored, as covered in materialized view strategy. Being stored objects with recorded dependencies also makes them part of every schema change.

The Condition #

A materialized view records a dependency on every object its defining query references: tables, columns, functions, types, operators and other views. PostgreSQL uses those dependencies to protect the definition:

Matviews can also depend on each other. A layered design — raw events to daily summary to monthly summary — creates a chain that determines both the rebuild order and the refresh order. Refreshing the monthly view before the daily one produces stale results with no error.

Unlike regular views, a matview also holds data, so recreating one is not free: it must be refreshed, which can take as long as the original build.

A dependency chain in three layers A stack from bottom to top. The orders table is the base. The daily revenue materialized view depends on it. The monthly revenue materialized view depends on the daily one. A dashboard view depends on the monthly one. Schema changes propagate upward, and refreshes must run bottom-up. orders (table) base data mv_revenue_daily depends on orders mv_revenue_monthly depends on mv_revenue_daily v_dashboard (view) depends on the monthly matview ALTER blocks from the bottom; refreshes must run bottom-up

Annotated Evidence #

The failing migration:

ALTER TABLE orders ALTER COLUMN status TYPE text;
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  materialized view mv_revenue_daily depends on column "status"

Find the full dependency tree before touching anything:

WITH RECURSIVE deps AS (
  SELECT DISTINCT d.refobjid AS base, c.oid AS dependent, c.relname, c.relkind, 1 AS depth
  FROM pg_depend d
  JOIN pg_rewrite r ON r.oid = d.objid
  JOIN pg_class c ON c.oid = r.ev_class
  WHERE d.refobjid = 'orders'::regclass AND c.relkind IN ('m', 'v')
  UNION ALL
  SELECT deps.dependent, c2.oid, c2.relname, c2.relkind, deps.depth + 1
  FROM deps
  JOIN pg_depend d2 ON d2.refobjid = deps.dependent
  JOIN pg_rewrite r2 ON r2.oid = d2.objid
  JOIN pg_class c2 ON c2.oid = r2.ev_class AND c2.oid <> deps.dependent
  WHERE c2.relkind IN ('m', 'v')
)
SELECT DISTINCT depth, relkind, relname FROM deps ORDER BY depth, relname;
 depth | relkind |       relname
-------+---------+----------------------
     1 | m       | mv_revenue_daily
     2 | m       | mv_revenue_monthly
     3 | v       | v_dashboard
-- three objects must be dropped and recreated in reverse, then refreshed in order

Check freshness after a rebuild:

SELECT c.relname, s.last_refresh
FROM pg_class c
LEFT JOIN (SELECT relid, greatest(last_vacuum, last_analyze) AS last_refresh FROM pg_stat_all_tables) s
  ON s.relid = c.oid
WHERE c.relkind = 'm';
Three signals to act on Three items are annotated. The ALTER TABLE error naming a dependent materialized view. A recursive dependency query returning several layers with depths. A monthly matview refreshed before the daily one, producing stale numbers with no error. ERROR: cannot alter type … depends on column a matview blocks the migration depth 1..3 in the dependency query three objects to rebuild in order monthly refreshed before daily stale data, no error raised refresh order is never enforced; only you can get it right

Step-by-Step Resolution #

  1. Map the dependencies before the migration with the recursive query above, or \d+ orders in psql, which lists dependent views.

  2. Keep every matview definition in version control, so a DROP … CASCADE is recoverable and reviewable.

  3. Sequence the change: drop dependents top-down, alter the base table, recreate dependents bottom-up, then refresh bottom-up:

    BEGIN;
DROP VIEW v_dashboard;
DROP MATERIALIZED VIEW mv_revenue_monthly;
DROP MATERIALIZED VIEW mv_revenue_daily;
ALTER TABLE orders ALTER COLUMN status TYPE text;
CREATE MATERIALIZED VIEW mv_revenue_daily AS …;      -- WITH NO DATA for speed
CREATE MATERIALIZED VIEW mv_revenue_monthly AS …;
CREATE VIEW v_dashboard AS …;
COMMIT;

    CREATE MATERIALIZED VIEW … WITH NO DATA defers the expensive part; the view is unusable until refreshed, which keeps the transaction short.

  4. Recreate indexes on each matview as part of the same migration — they are dropped with the view.

  5. Refresh in dependency order, lowest layer first, and analyze each one afterwards.

  6. Consider avoiding the chain. Building the monthly summary from the base table instead of the daily matview removes a dependency and a refresh-ordering constraint, at the cost of reading more rows.

The safe order of operations Five steps in order. Map dependencies. Drop dependents from the top down. Alter the base table. Recreate the views from the bottom up with no data. Refresh and analyze from the bottom up so each layer sees fresh input. map dependencies catalog query drop top-down view, monthly, daily alter base table the actual change recreate bottom-up WITH NO DATA refresh bottom-up then ANALYZE refreshing top-down silently produces stale results

Before and After #

-- BEFORE: migration fails, dependencies unknown
ALTER TABLE orders ALTER COLUMN status TYPE text;
ERROR: cannot alter type of a column used by a view or rule

-- AFTER: dependency-ordered migration
DROP … → ALTER TABLE → CREATE … WITH NO DATA → REFRESH daily → REFRESH monthly → ANALYZE both

Reducing dependency pain by design #

Two habits keep matview dependencies manageable. First, select explicit columns rather than SELECT * in the defining query: a matview built on * depends on every column of the base table, so any column type change anywhere blocks the migration. Second, keep chains shallow. Each additional layer multiplies the rebuild work and adds an ordering constraint to every refresh cycle; two layers is usually the practical limit before a scheduling mistake becomes likely.

Where a chain is genuinely useful — the monthly summary really is much cheaper to build from the daily one — encode the order in one job rather than in separate schedules, so the dependency cannot be violated by timing. The same job should analyze each layer after refreshing it, as described in indexing materialized views for dashboards, because a layer built from stale statistics can be planned badly even when its inputs are fresh.

A last consideration is timing. Because the whole sequence runs as one migration, it holds locks on the base table for its duration, and a REFRESH of a large matview inside that transaction would extend the lock for minutes. Creating the views WITH NO DATA inside the transaction and refreshing them afterwards, outside it, keeps the locked window to the length of the ALTER TABLE itself — usually milliseconds for a type change that does not rewrite the table, and a full rewrite otherwise.

Common Pitfalls #

Discovering dependencies during a migration. The error arrives at the worst moment. Diagnostic signal: failed ALTER TABLE in a deploy. Fix: map dependencies in advance and include the rebuild in the migration.

DROP … CASCADE without saved definitions. The matview is gone with its definition. Diagnostic signal: missing objects after a migration. Fix: definitions in version control.

Refreshing in the wrong order. Upper layers show stale data with no error. Diagnostic signal: monthly totals not matching daily sums. Fix: one job, bottom-up order.

Forgetting indexes after a rebuild. They are dropped with the view. Diagnostic signal: dashboards slow after a migration. Fix: recreate indexes in the same migration.

Frequently Asked Questions #

Why can’t I alter a column that a materialized view uses? #

The materialized view records a dependency on that column, and changing its type would invalidate the stored definition and data. PostgreSQL blocks the change; you must drop the dependent objects, alter the column, and recreate them.

How do I find everything that depends on a table? #

Query pg_depend joined with pg_rewrite and pg_class recursively, or use psql’s \d+ on the table, which lists dependent views and materialized views.

In what order should layered materialized views be refreshed? #

Bottom-up: refresh the views closest to the base tables first, then the ones built on them. PostgreSQL does not enforce or check this, so refreshing out of order silently produces stale results.

Up: Materialized View Strategy