REFRESH MATERIALIZED VIEW takes an exclusive lock on the MV — reads fail while refresh runs. For large MVs, this lock duration is unacceptable. REFRESH CONCURRENTLY uses a unique index (required) and refreshes without blocking reads — it builds a new version of the MV, swaps when ready, and uses CREATE INDEX CONCURRENTLY semantics. The tradeoff: CONCURRENTLY takes slightly longer than a blocking refresh because it builds indexes one at a time and must ensure no duplicate keys. It also requires the MV to have a unique index.

The MV query definition determines its size. If you aggregate by day with no upper bound, 10 years of data makes the MV large and slow to refresh. Add a WHERE clause to limit the time window: WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'. Partition the MV the same way you partition source tables so old partitions can be detached and dropped. Alternatively, use a rolling window materialized view — a scheduled job drops old partitions and creates new ones daily.

Refresh on the primary triggers replication of the new data to replicas. A full refresh on a large MV can generate gigabytes of WAL that replicas must apply, causing lag. The fix: run REFRESH CONCURRENTLY on the replica instead of the primary if replicas serve read traffic. Alternatively, reduce refresh frequency — if the MV is refreshing every minute but the source data only changes hourly, you are paying replication cost for no benefit.

Materialized views let the database handle refresh logic — you define the query once, the database manages when and how to update results. Application-level denormalization requires your code to know about and maintain every denormalized copy. Choose materialized views when the database can manage the refresh efficiently (incremental, partition-aligned, not too frequent). Choose application denormalization when the denormalized data structure does not map cleanly to a SQL query — for example, precomputing a specific social graph adjacency list that requires graph traversal logic rather than a standard aggregation.

Use REFRESH MATERIALIZED VIEW (blocking) when: the MV is small enough that refresh completes in seconds, the MV is not queried during the refresh window (you can schedule downtime), or you do not have a unique index and cannot create one due to duplicate possibilities. Use REFRESH CONCURRENTLY when: the MV is large and refresh takes minutes, the MV must remain available for reads during refresh, or your SLA requires zero downtime MV refreshes. The key gotcha: CONCURRENTLY requires a unique index on at least one column. If your MV query cannot produce unique rows (no primary key), you cannot use CONCURRENTLY without adding a unique expression.

Two-hour refresh means the MV is stale for 2 hours after midnight, which is unacceptable for daily reports. Solutions: partition the source data so refresh only rebuilds yesterday's partition (incremental refresh); pre-create the MV as a partitioned table and load partitions incrementally instead of full refresh; or accept that the report reflects yesterday at most, not midnight. More fundamentally, if reports are always as-of-midnight, the MV should be partitioned by date and refreshed per partition — refresh only the partition that changed (yesterday's data) rather than rebuilding the entire view. If you cannot partition, consider whether incremental refresh (REFRESH FAST) is possible with materialized view logs rather than full rebuild.

A materialized view is defined by a query and stored as a table — it can be any arbitrary query result (joins, filters, aggregations). A materialized aggregate table is a specific type: precomputed aggregations like SUM, COUNT, AVG on fact tables. The distinction matters because materialized aggregate tables can often be refreshed incrementally (if the aggregation is additive), while materialized views over complex joins may require full refresh. In practice, "materialized view" is used generically for both, but the refresh strategy differs: aggregate views over fact tables can use incremental refresh with materialized view logs; complex join views typically need full refresh unless the underlying tables are partitioned aligned.

Key metrics: last_refresh time (staleness), refresh duration, and row count growth rate. Staleness matters because business decisions made on stale data can be wrong — if your MV is supposed to refresh hourly and it has been 4 hours, alert immediately. Refresh duration matters because if it suddenly doubles, something changed in the underlying data distribution or query plan. Row count growth rate matters because an unbounded MV query (no WHERE clause limiting scope) will grow indefinitely. Queries: SELECT matviewname, last_refresh FROM pg_matviews WHERE schemaname = 'public' for staleness. SELECT COUNT(*) FROM mv_name for row count. For alerts: staleness > threshold, refresh_duration > baseline × 2, row_count growing > 10% per day without expected business growth.

Use CREATE MATERIALIZED VIEW ... WITH NO DATA to create the empty MV immediately. Then populate it incrementally: backfill historical data in batches using INSERT statements filtered by date range or ID range. This allows the MV to exist and be queryable while historical data loads. Alternatively, populate with WITH DATA but accept that initial creation takes 30 minutes and the MV is empty until complete. For very large MVs, use partition-aligned loading: load one partition at a time, use REFRESH CONCURRENTLY if you have a unique index. If the query will always take a long time, schedule MV creation during maintenance windows and use NO DATA + background backfill for production deployments.

When the source fact table is partitioned and the MV aggregates at the partition level (e.g., daily aggregates), refresh can target only the changed partition rather than the entire table. PostgreSQL does not support partition-aware automatic refresh, so you must write a refresh script that identifies which partitions changed and runs REFRESH MATERIALIZED VIEW mv WHERE partition_key = '2025-05-14'. This dramatically reduces refresh time: instead of scanning 10 years of data for a 10-year MV, you scan only yesterday's partition. Oracle's partition tracking capabilities make this easier with materialized view logs and fast refresh.

PostgreSQL does not automatically update the MV schema when the underlying table changes. If you add a column to the source table, the MV does not automatically include it — you must DROP and RECREATE the MV. If you change a column type, the MV may error on refresh if the new type is incompatible with the MV's query. The MV does not automatically detect schema drift. The mitigation: treat MV definitions as versioned code, include them in migration scripts, and always test MV refresh after schema changes. Some teams use automated migration scripts that detect MVs depending on a table and include their recreation in the migration plan.

Use a regular view when: you need always-current data (materialized views are stale by definition), your query is not expensive enough to justify precomputation (sub-second response times are acceptable), or you query the view infrequently enough that the storage cost of materialization is not worth it. Use a regular view + index when: the underlying tables are small or the query is simple enough that the database handles it efficiently, or when query patterns are too diverse to precompute effectively. Materialized views trade freshness for speed; regular views give you freshness but no speedup. If your query is slow and data freshness matters less than performance, materialize. If data freshness matters more, index the underlying tables and use a regular view.

Per-tenant MVs or MV per tenant partition. If tenants are separated by tenant_id, create one MV per tenant and name it sales_by_month_tenant_123. Refresh each tenant's MV on their own schedule. Alternatively, use a single MV with tenant_id as a filter column, partitioning the MV data by tenant. Each tenant sees their slice. Refresh is still single-operation across all tenants, but you can add a WHERE tenant_id = ? clause to the refresh query if you need per-tenant refresh control. The complexity: if you have 1000 tenants, you have 1000 MV definitions to manage. Consider whether materialized views are the right tool for multi-tenant scenarios or whether application-level caching (Redis) with tenant-specific keys would be simpler.

Yes, you can create a materialized view on top of another materialized view in PostgreSQL. The dependent MV refreshes independently and does not automatically track the parent MV refresh. If MV A = SELECT * FROM table, and MV B = SELECT * FROM MV A, refreshing MV A does not refresh MV B — you must explicitly refresh MV B. This creates a dependency chain: refresh order matters. If MV B depends on MV A, always refresh MV A first. Some teams avoid nested MVs because the dependency chain becomes hard to manage. If you need layered precomputation (aggregating from raw fact to daily rollup to monthly summary), consider whether a single MV at the right aggregation level is simpler than nested MVs.

Materialized views are precomputed and stored on disk — the result is the MV itself, ready to query at any time. Query caching (Redis, Memcached) stores query results in memory with a TTL. Materialized views persist across restarts; query cache does not. Choose materialized views when: the same query runs frequently across many users, the underlying data changes infrequently, and you need consistent results without TTL expiration variation. Choose query caching when: results need to be served in sub-millisecond time, you are okay with stale data up to TTL, or the query is too dynamic to precompute effectively. Use both: materialized view as the source of truth for consistency, query cache in front for microsecond serving of already-computed results.

Zero-downtime MV maintenance requires REFRESH CONCURRENTLY with a unique index — the only way to refresh without blocking reads. Schedule refresh during low-traffic windows using pg_cron or external schedulers. For large MVs that take minutes to refresh, concurrent refresh keeps the old MV available throughout. If concurrent refresh is not viable (no unique index possible), use a blue-green approach: create a new MV with a different name, populate it in background, then atomically rename (swap) when ready. The swap is instant (DDL) but the population took time in background. Test the entire process in staging — MV refresh failures during maintenance windows can cascade into SLA breaches if not rehearsed.

When the source fact table is partitioned by date and the MV aggregates at the partition level, refresh can target only the changed partition rather than the entire table. PostgreSQL does not have automatic partition-aware refresh, so implement a refresh script that: identifies which partitions have changed since last refresh (using pg_partitions or partition metadata), runs REFRESH MATERIALIZED VIEW mv WHERE partition_key = '2025-05-14' for only those partitions. This reduces refresh time from scanning 10 years of data to scanning only yesterday's partition. For Oracle, materialized view logs track changed partitions automatically and fast refresh processes only affected partitions. In PostgreSQL, the application-level approach is required.

Oracle supports automatic refresh via refresh groups and schedules, on-commit triggers, query rewrite (optimizer auto-uses MVs), and fast refresh with materialized view logs. PostgreSQL has none of these — refresh is manual via REFRESH MATERIALIZED VIEW, there is no on-commit refresh, no query rewrite, and no built-in fast refresh. If you need automatic refresh in PostgreSQL, use pg_cron or external schedulers. If you need query rewrite, PostgreSQL does not support it and you must route queries to the MV explicitly in application code. Choose Oracle when operational simplicity and automatic refresh are priorities; choose PostgreSQL when you want simplicity and are willing to manage refresh manually.

Index the MV based on how it is queried, not how it was defined. If the MV sales_by_month(customer_id, month, total_sales) is always queried with WHERE month = '2025-01' and ORDER BY customer_id, create a composite index on (month, customer_id). The MV is a table — index it like any table based on access patterns. Common mistake: assuming the underlying query's indexes are enough. They are not because the MV is a separate table with its own data. For low-cardinality columns (status flags), bitmap indexes work well in Oracle but PostgreSQL only supports btree, hash, and gin. If your MV serves multiple query patterns, consider multiple indexes. For high-cardinality filtering, btree indexes are most effective.

MVs as a replication mechanism: create an MV on database B that selects from a foreign table (Postgres Foreign Data Wrapper) or materialized view on database A that is updated periodically. The pattern: production database has normalized tables; reporting database has MVs that pull from production via FDW or CDC (Change Data Capture). The MV serves the reporting workload without touching production. For multi-region replication, each region has local MVs that refresh from the primary. This is not real-time — there is lag between refreshes. For near-real-time replication, use logical replication (PostgreSQL's built-in) or Debezium + Kafka instead of MVs. MVs work well for hourly/daily batch synchronization to a reporting database.