Case q07 · howto

How do we replace fast-refresh materialized views when moving to Azure Database for PostgreSQL?

Expected: PostgreSQL has no incremental (fast) refresh; options: REFRESH MATERIALIZED VIEW (full, optionally CONCURRENTLY), trigger-maintained summary tables, or pg_cron scheduling; effort and caveats cited.

No tools

The model alone, answering from memory.

larger model · 1 turn · 22 s · 16.5k in / 2.2k out

✓ verdict ✗ grounded ✓ cited

Grader: Complete options; unsourced claims, contradicts KB on pg_ivm, no effort

Answer

Short answer: PostgreSQL has no equivalent of Oracle's REFRESH FAST (incremental refresh from MV logs). On Azure Database for PostgreSQL Flexible Server, choose a replacement for each MV based on how fresh its data needs to be:

Default: full refresh on a schedule. Use REFRESH MATERIALIZED VIEW CONCURRENTLY and schedule it with pg_cron.
Near-real-time: trigger-maintained summary tables. Use these in place of ON COMMIT or fast-refresh MVs.
Large or high-change data: your own change log plus a merge. Build it yourself, or push the aggregation outside the database.

Key facts

No native incremental refresh. Community PostgreSQL only supports a full re-run of the MV query. CONCURRENTLY keeps the MV readable during the refresh, but it needs a unique index on the MV. It is still a full recompute, plus a diff, so it is often slower than a plain refresh. It costs I/O and WAL, and you can't use it for the MV's first population. https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html

Scheduling. pg_cron is supported on Flexible Server. Add it to shared_preload_libraries and azure.extensions, then restart. It replaces START WITH … NEXT and DBMS_JOB/DBMS_SCHEDULER refresh groups. Wrap related refreshes in one procedure so they run in the right order. https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-extensions

pg_ivm (incremental view maintenance). This third-party extension is the closest thing to fast refresh. As far as I know it is not on Azure's list of allowed extensions, and you can't install unsupported extensions on a managed service. Check the current list for your PG version before you plan around it. If you must have it, you'd have to run PostgreSQL on an IaaS VM. https://github.com/sraoss/pg_ivm

Trigger-maintained summary tables (replaces ON COMMIT or fast refresh):

Replace the MV with a regular table.
Put AFTER INSERT/UPDATE/DELETE triggers on the base tables. Statement-level triggers with transition tables (REFERENCING NEW TABLE) work best.
The triggers apply changes to the summary with INSERT … ON CONFLICT DO UPDATE.
Downsides: extra write overhead and lock contention on hot aggregate rows. This works well for SUM and COUNT, but MIN and MAX need care on deletes.

Rebuilding Oracle's MV logs yourself. Triggers write changed keys into a delta table. A pg_cron job then recomputes only the affected groups and MERGEs them into the summary table (MERGE needs PG 15+). This is the closest behavior to fast refresh and scales better than triggers that update the summary on every change.

Partition-scoped refresh. For time-series aggregates, keep the summary in a partitioned table and rebuild only the current or recent partitions. This replaces Oracle's partition change tracking (PCT) refresh.

Offload the aggregation. For heavy reporting MVs, you can move the work elsewhere instead:

Logical replication or CDC (Debezium, or Azure Data Factory / Fabric mirroring) feeding an analytics store.
Azure Cosmos DB for PostgreSQL (Citus) for distributed rollups.

No query rewrite. PostgreSQL doesn't redirect queries to MVs automatically (Oracle's ENABLE QUERY REWRITE). Any SQL that relied on that has to be changed to query the MV or summary table explicitly. People often miss this during migration.

Tooling. Ora2Pg converts MVs to native PG MVs but drops the REFRESH FAST/ON COMMIT settings. Its report flags them, so use that list to decide which option each MV gets.

Practical triage

Refresh interval of an hour or more, and the full query runs in minutes: pg_cron + CONCURRENTLY.
ON COMMIT or needs to be current within seconds: trigger-maintained table.
Big base tables with fast refresh every few minutes: delta table + scheduled MERGE, or partition-scoped rebuild.

Benchmark refresh times on production-sized data before you commit to an option for each MV. Fast refresh often hid how expensive the full query really is.

Keyword search

BM25 over the same 446 documents: top 8 passages pasted in, one pass, references not followed.

larger model · 1 turn · 10 s · 18.4k in / 0.9k out

✓ verdict ✓ grounded ✗ cited

Grader: pg_cron, CONCURRENTLY, summary tables, caveats; effort not stated

8 keyword-search passages

feature_materialized-views-fast-refresh score 24.761
mapping_materialized-views-fast-refresh--azure-pg-flex score 24.725
mapping_materialized-views-fast-refresh--azure-sql-db score 20.395
caveat_pg-no-fast-refresh-mv score 19.443
source_postgres-docs-matviews score 19.107
source_oracle-cncpt-19-schema-objects score 15.964
source_postgres-docs-refresh-matview score 14.358
caveat_ssma-mview-indexed-view-restrictions score 13.901

Answer

Verdict: There is no direct equivalent to fast refresh. PostgreSQL materialized views on Azure Database for PostgreSQL only support full recompute. You have three options: move to scheduled full refreshes, use pg_ivm where the query fits, or rebuild the view as your own summary table.

What you lose [2][4][7]

There are no materialized view logs, no fast (incremental) refresh and no refresh on commit.
REFRESH MATERIALIZED VIEW always re-runs the whole query. Adding CONCURRENTLY stops it from blocking readers, but it still recomputes everything. It also needs a unique index on plain columns.
There is no query rewrite. PostgreSQL never swaps a materialized view in for base-table queries automatically [2].

Replacement approach [2]

Export: Use Ora2Pg with TYPE=MVIEW to convert the view definitions.
Default pattern: Add a unique index to each view, then schedule REFRESH MATERIALIZED VIEW CONCURRENTLY with pg_cron.
Near-real-time needs: Evaluate the pg_ivm extension. It's available on Flexible Server for PostgreSQL 13 and later, and it maintains views incrementally, but only for some query shapes.
Query-rewrite code: Change the reporting SQL so it queries the materialized view directly.

Harder cases [1] Some views won't fit the patterns above. Examples are fast-refresh views with joins and aggregates, views that refresh on commit, and reports that depend on query rewrite. These usually need re-engineering into scheduled loads or change-driven summary tables.

Sources

[5] PostgreSQL docs, CREATE MATERIALIZED VIEW: https://www.postgresql.org/docs/current/sql-creatematerializedview.html
[7] PostgreSQL docs, REFRESH MATERIALIZED VIEW: https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
[6] Oracle 19c Concepts (the Oracle refresh methods and query rewrite): https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/partitions-views-and-other-schema-objects.html
[2] Oracle → Azure PG Flexible Server mapping note. No URL was given for it.

The passages don't list which query shapes pg_ivm supports. Check its documentation before committing to it.

Sanity Context · GROQ

The agent with the GROQ endpoint: schema, joins, Portable Text.

smaller model · 6 turns · 27 s · 46.0k in / 2.1k out

✓ verdict ✓ grounded ✓ cited

Grader: Full refresh, triggers, pg_ivm, effort M; pg_cron implied only

5 tool calls

initial_context on groq · 4,126 chars back

groq_query on groq · 757 chars back

*[_type=="oracleFeature" && (name match "*aterialized*" || slug.current match "*materialized*")]{_id, name, slug, category, introducedIn, deprecatedIn, desupportedIn, oracleReplacement}

groq_query on groq · 6,161 chars back

*[_type=="mapping" && oracleFeature._ref=="feature_materialized-views-fast-refresh"]{
  _id,
  fidelity,
  effort,
  rationale,
  appliesToOracleVersions,
  "azureTarget": azureTarget->{name, service, tierNotes},
  "sources": sources[]->{title, url, publisher},
  "caveats": *[_type=="caveat" && references(^._id)]{
    severity, statement, appliesTo, firsthand,
    "evidence": evidence->{title, url, publisher}
  }
}

groq_query on groq · 1,857 chars back

*[_type=="oracleFeature" && slug.current=="materialized-views-fast-refresh"][0]{summary}

groq_query on groq · 411 chars back

*[_type=="pattern" && references("mapping_materialized-views-fast-refresh--azure-pg-flex")]{_id, title, problem, whenNotTo, "sources": sources[]->{title,url}}

Answer

No pattern doc references this mapping. Here's what the dataset supports.

Verdict: Partial fidelity, no direct replacement — Azure Database for PostgreSQL Flexible Server has no fast-refresh/incremental materialized views; you must re-engineer to full refresh (optionally via pg_ivm) or scheduled/change-driven summary tables.

Scoped facts (Oracle 11.2–26ai, all editions per appliesToOracleVersions):

Oracle's fast-refresh materialized views use materialized view logs (or partition change tracking) to apply only changed rows, can refresh on commit or on demand, and support query rewrite so the optimizer transparently redirects base-table queries to the MV. [Fast-refresh materialized views feature summary](internal — no external url in this entry)
Azure Database for PostgreSQL Flexible Server (mapping fidelity: partial, effort: M): PostgreSQL materialized views only support REFRESH MATERIALIZED VIEW, which recomputes the entire result set. CONCURRENTLY avoids blocking readers but still recomputes everything — there is no materialized view log, no incremental/fast refresh, and no refresh-on-commit. PostgreSQL documentation: REFRESH MATERIALIZED VIEW, PostgreSQL documentation: CREATE MATERIALIZED VIEW
- The pg_ivm extension, available on Flexible Server for PostgreSQL 13+, adds incrementally maintained views for a subset of query shapes — this is the closest workaround to fast refresh. Extensions and modules by name in Azure Database for PostgreSQL flexible server
- Caveat (severity: major): confirms no incremental/fast refresh path exists natively in PostgreSQL. PostgreSQL documentation: REFRESH MATERIALIZED VIEW

Practical re-engineering options recorded in the rationale (not a separate pattern document — none in the dataset references this mapping):

Use pg_ivm where the query shape is supported (subset of joins/aggregates).
Otherwise, re-engineer to scheduled full-refresh jobs (REFRESH MATERIALIZED VIEW CONCURRENTLY) or change-driven summary tables maintained by triggers/application logic.

Side note — if you're also considering Azure SQL Database instead: the dataset maps the same Oracle feature there too (fidelity: partial, effort: L) via indexed views, but that path is unrelated to PostgreSQL and comes with its own SSMA conversion restrictions (outer joins, DISTINCT, MIN/MAX, UNION, HAVING, window functions, nondeterministic expressions all fail conversion). SSMA for Oracle: Converting Oracle Schemas, Create indexed views (SQL Server, Azure SQL) — only relevant if you're comparing targets, not for a PostgreSQL Flexible Server migration.

Gaps: No dispute document touches this feature. No pattern document is linked to the PostgreSQL mapping, so there's no dataset-recorded step-by-step migration playbook beyond the rationale above — the dataset has no evidence on a detailed runbook.

GROQ + Knowledge Base

The agent with both endpoints.

smaller model · 7 turns · 28 s · 66.3k in / 2.3k out

✓ verdict ✓ grounded ✓ cited

Grader: CONCURRENTLY+pg_cron, pg_ivm, effort M; omits trigger tables

6 tool calls

initial_context on groq · 4,126 chars back
initial_context on kb · 7,679 chars back

groq_query on groq · 808 chars back

*[_type=="oracleFeature" && (name match "*aterialized*" || slug.current match "*aterialized*")]{_id, name, slug, category, introducedIn, deprecatedIn, desupportedIn, oracleReplacement, reviewStatus}

groq_query on groq · 7,453 chars back

*[_type=="mapping" && oracleFeature->slug.current=="materialized-views-fast-refresh"]{
  _id, fidelity, effort, rationale, appliesToOracleVersions, steps,
  "azureTarget": azureTarget->{name, slug, service},
  "sources": sources[]->{title, url},
  "caveats": *[_type=="caveat" && references(^._id)]{
    _id, severity, statement, appliesTo, firsthand,
    "evidence": evidence->{title, url, publisher}
  }
}

knowledge_base_read on kb · 4,882 chars back

{
 "knowledgeBase": "kb6MdvV3ZA3Q",
 "paths": [
  "target_platforms"
 ]
}

groq_query on groq · 687 chars back

*[_type=="dispute" && (claimA->mapping->_id == "mapping_materialized-views-fast-refresh--azure-pg-flex" || claimB->mapping->_id == "mapping_materialized-views-fast-refresh--azure-pg-flex" || claimA._ref match "*mview*" || claimB._ref match "*mview*")]{title, whatDisagrees, resolution, "claimA": claimA->{statement}, "claimB": claimB->{statement}, "resolvedBy": resolvedBy->{title,url}}

Answer

No dispute recorded on this topic. I have what I need to answer.

Verdict

Fast-refresh materialized views have no direct equivalent on Azure Database for PostgreSQL Flexible Server — fidelity is rated partial, effort M, for all Oracle versions 11.2 through 26ai. PostgreSQL documentation: CREATE MATERIALIZED VIEW

Why

PostgreSQL materialized views only support full recomputation via REFRESH MATERIALIZED VIEW — there is no materialized view log, no fast/incremental refresh, and no refresh-on-commit. REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking readers but still recomputes the entire result. PostgreSQL documentation: REFRESH MATERIALIZED VIEW

Caveat (major): "PostgreSQL REFRESH MATERIALIZED VIEW completely replaces the view's contents by re-running its query; the CONCURRENTLY option (which requires a unique index on plain columns) avoids blocking readers but still recomputes everything, and there is no incremental or fast refresh, no materialized view log and no refresh on commit." PostgreSQL documentation: REFRESH MATERIALIZED VIEW

The pg_ivm extension — available on Flexible Server for PostgreSQL 13+ — adds incrementally maintained views, but only for a subset of query shapes. Extensions and modules by name in Azure Database for PostgreSQL flexible server

Recommended approach (from the mapping's steps)

Export with Ora2Pg TYPE=MVIEW and schedule REFRESH MATERIALIZED VIEW CONCURRENTLY via pg_cron.
Add a unique index to each view so CONCURRENTLY is permitted.
Evaluate pg_ivm for views needing near-real-time freshness, if the query shape is supported.
Rewrite any code that depends on Oracle's automatic query rewrite to materialized views — PostgreSQL never substitutes a materialized view automatically.

Sources for the mapping: PostgreSQL documentation: CREATE MATERIALIZED VIEW, PostgreSQL documentation: REFRESH MATERIALIZED VIEW, Extensions and modules by name in Azure Database for PostgreSQL flexible server

Gaps

No dispute entry exists in the dataset touching this mapping.
The dataset has no evidence on Cosmos DB or other non-relational Azure targets for this feature — only Azure Database for PostgreSQL Flexible Server and Azure SQL Database mappings exist. (If you're also considering Azure SQL Database, note: its closest equivalent is the indexed view, which is stronger than fast refresh — synchronous, no staleness — but far more restrictive and SSMA conversion fails on many common constructs; happy to detail that path if relevant to your target.)

← q06 All cases q08 →