Migrating Oracle Materialized Views to PostgreSQL

The DDL Converts. The Refresh Model Doesn't.

A materialized view looks like one of the easy migrations — both databases have CREATE MATERIALIZED VIEW, the defining query is mostly portable SQL, and the object moves across with little fuss. The trap isn't the view — it's how it stays current. Oracle has a rich refresh engine (FAST incremental refresh via MV logs, ON COMMIT, automatic query rewrite). PostgreSQL has exactly one refresh primitive: REFRESH MATERIALIZED VIEW, which rebuilds the whole thing. Everything in a successful migration of an Oracle MV comes down to replacing Oracle's refresh semantics with something PG can actually do.

The Definition Carries Over

The structure is the portable part. An Oracle MV and a PostgreSQL MV both store the result of a query on disk and are read like a table.

-- Oracle
CREATE MATERIALIZED VIEW sales_by_region
  BUILD IMMEDIATE
  REFRESH COMPLETE ON DEMAND
AS SELECT region, SUM(amount) AS total
   FROM   orders GROUP BY region;

-- PostgreSQL — WITH DATA = Oracle's BUILD IMMEDIATE
CREATE MATERIALIZED VIEW sales_by_region AS
  SELECT region, SUM(amount) AS total
  FROM   orders GROUP BY region
WITH DATA;

BUILD IMMEDIATE becomes WITH DATA; BUILD DEFERRED becomes WITH NO DATA (the MV exists but is unpopulated and unreadable until the first refresh). The SELECT itself travels through the same transpilation as any other query — NVL, DECODE, (+) joins and the rest all get rewritten.

Mapping the Refresh Modes

The Easy Case: COMPLETE Refresh on a Schedule

If the Oracle MV uses REFRESH COMPLETE ON DEMAND — refreshed by a job every night, say — the PostgreSQL translation is direct: a REFRESH MATERIALIZED VIEW on the same schedule, driven by pg_cron (or any external scheduler).

-- Nightly full refresh at 02:00, via pg_cron
SELECT cron.schedule('refresh-sales', '0 2 * * *',
  $$REFRESH MATERIALIZED VIEW sales_by_region$$);

REFRESH … CONCURRENTLY Needs a UNIQUE Index

A plain REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock — readers are blocked for the whole rebuild. REFRESH MATERIALIZED VIEW CONCURRENTLY keeps the MV readable during the refresh, but it has a hard prerequisite that Oracle never imposed: the MV must have at least one UNIQUE index. Miss it and the concurrent refresh fails outright.

-- Required before you can refresh CONCURRENTLY
CREATE UNIQUE INDEX sales_by_region_ux ON sales_by_region (region);

-- Now readers aren't blocked during the rebuild (but it's slower + needs more disk)
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_by_region;

REFRESH ON COMMIT Becomes a Trigger

Oracle's REFRESH ON COMMIT keeps the MV in lockstep with its base tables — every committed change re-derives it. PostgreSQL has no such mode, so the equivalent is an AFTER trigger on each base table that re-runs the refresh. It works, but the write cost is real: every INSERT / UPDATE / DELETE on the base table now pays for the MV rebuild. For anything but small, low-churn MVs, a scheduled refresh is usually the better trade.

CREATE OR REPLACE FUNCTION refresh_sales_by_region() RETURNS trigger AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY sales_by_region;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_refresh_sales
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT EXECUTE FUNCTION refresh_sales_by_region();

FAST Refresh Has No Native Equivalent

This is the real gap. Oracle's FAST refresh reads materialized view logs to apply only the changed rows — incremental maintenance. Core PostgreSQL has no incremental refresh at all; REFRESH MATERIALIZED VIEW always rebuilds the entire result. Three honest paths forward:

1. Scheduled full refresh — simplest and most common. If the MV can tolerate being minutes-stale, a periodic CONCURRENTLY refresh is the least moving parts. 2. Trigger-maintained summary table — for true real-time aggregates, replace the MV with a regular table that base-table triggers keep incrementally updated. More code, but genuinely incremental. 3. pg_ivm — an extension that adds Incremental View Maintenance to PostgreSQL. Promising, but it's an add-on with its own constraints — evaluate it against your query shapes before committing, and don't assume it covers every Oracle FAST MV.

The Silent One: Query Rewrite

Oracle can automatically rewrite a query against the base tables to read from a matching MV (ENABLE QUERY REWRITE), so applications get the MV's speed without naming it. PostgreSQL does nothing of the kind — a query only uses an MV if it references the MV by name. This one doesn't throw an error; it just quietly removes an optimization the Oracle workload silently depended on, and the symptom is a query that was instant on Oracle and slow on PostgreSQL. Any MV that relied on query rewrite needs the calling queries pointed at the MV explicitly.

How DBMigrateAIPro Handles It

The tool converts the CREATE MATERIALIZED VIEW and transpiles its defining query, then classifies the refresh mode rather than pretending it ported cleanly: COMPLETE ON DEMAND becomes a scheduled refresh, ON COMMIT becomes a base-table trigger, and FAST / query-rewrite MVs are flagged in the gap report with the concrete options above — because those are part of the honest 5% that needs a human decision, not a silent auto-conversion. It also reminds you to add the UNIQUE index that REFRESH … CONCURRENTLY requires.