Materialized View

Une Materialized View est une view dont le résultat de la query underlying est précomputé et stocké physiquement sur disque (vs regular view recalculée à chaque accès). Sacrifices freshness (data peut être stale) et storage pour gagner massive read performance — idéal pour reporting, dashboards, analytics aggregations recalculées rarement.

Support SGBD :
- **PostgreSQL** : `CREATE MATERIALIZED VIEW name AS SELECT ...`. Refresh manuel `REFRESH MATERIALIZED VIEW name` (locks table) ou `REFRESH MATERIALIZED VIEW CONCURRENTLY name` (no lock, requires unique index).
- **Oracle** : matured feature avec auto-refresh policies (ON COMMIT, ON DEMAND, scheduled), query rewrite (optimizer transparently uses MV when query equivalent).
- **MySQL** : pas natif — emulate avec table + triggers ou ETL refresh.
- **SQL Server** : Indexed Views (similar concept) avec auto-maintained.
- **BigQuery** : Materialized Views avec auto-incremental refresh.
- **Snowflake** : Materialized Views avec auto-maintenance.
- **Redshift** : Materialized Views avec REFRESH manuel ou auto.

Use cases idéaux :
(1) **Reporting dashboards** — pre-aggregate daily/hourly metrics (revenue, signups, etc.) ; queries dashboard < 1s vs computing on raw events massive.
(2) **Analytics dimensions** — joins complex dimension+fact tables précomputed.
(3) **Complex calculations** — recursive CTEs, window functions, regex — cache result.
(4) **Search indexes** — denormalize for fast lookups.
(5) **API caching layer** — query-equivalent caching at DB level.

Refresh strategies :
(1) **Full refresh** — recompute entire view, simple but slow on large data.
(2) **Incremental refresh** — only update affected rows (Oracle, BigQuery, Snowflake supportent ; Postgres pas natif).
(3) **Scheduled** (cron job, pg_cron) — hourly, daily.
(4) **Event-triggered** (Postgres trigger, Debezium CDC).
(5) **On-demand** when query needs fresh data.

Trade-offs :
(1) **Stale data** — view data lag vs source ; acceptable for reporting, not for transactional.
(2) **Storage cost** — duplicate data.
(3) **Refresh overhead** — heavy queries periodically.
(4) **Maintenance complexity** — what if source schema change ?

Alternative moderne : Stream processing (Materialize, RisingWave, Flink SQL) — "streaming materialized views" with sub-second freshness via incremental computation. Compétences DP-300, DEA-C01.