Come funzionano le materialized view in PostgreSQL?

Question

Accepted Answer

Una **materialized view** memorizza i *risultati* di una query fisicamente (precomputati), a differenza di una vista regolare (che riesegue la sua query ogni volta). Questo rende la lettura veloce per query costose, al prezzo che i dati sono freschi solo quanto l'ultimo **refresh** — un meccanismo di caching all'interno del database.

## Vista regolare vs materialized view

```sql
-- regular VIEW — runs the query EVERY time (always fresh, but recomputed)
CREATE VIEW sales_summary AS SELECT region, SUM(amount) FROM orders GROUP BY region;

-- MATERIALIZED VIEW — stores the RESULTS physically (fast reads, but can be stale)
CREATE MATERIALIZED VIEW sales_summary_mat AS
SELECT region, SUM(amount) AS total FROM orders GROUP BY region;

SELECT * FROM sales_summary_mat;     -- reads STORED results — fast (no recompute)
```

Una materialized view precomputa e memorizza i risultati, quindi interrogarla è veloce (come leggere una tabella) — ideale per aggregazioni costose interrogate frequentemente.

## Refreshing (aggiornamento dei dati memorizzati)

```sql
-- the data is stale until you refresh it
REFRESH MATERIALIZED VIEW sales_summary_mat;          -- recompute (locks reads during refresh)

-- CONCURRENTLY — refresh WITHOUT blocking reads (requires a unique index)
CREATE UNIQUE INDEX idx_sales_region ON sales_summary_mat(region);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary_mat;   -- reads continue during refresh
```

Devi **refreshare** per aggiornare i dati. `REFRESH ... CONCURRENTLY` (richiedendo un indice univoco) aggiorna senza bloccare le letture concorrenti — importante per la produzione.

## Quando usare le materialized view

```text
✓ EXPENSIVE queries (complex aggregations, joins) queried FREQUENTLY
✓ Data that doesn't need real-time freshness (dashboards, reports, analytics)
✓ Reducing load from repeatedly running the same heavy query
→ Refresh on a schedule (cron / pg_cron) or after data changes.

✗ Data needing real-time accuracy → use a regular view or query directly
```

## Il trade-off

```text
Fast reads (precomputed) ⟷ Stale data (until refreshed) + storage cost.
You trade freshness and storage for read speed — a caching decision.
You can also index a materialized view (it's a physical table of results).
```

## Perché è importante

Le materialized view sono una feature preziosa di PostgreSQL per l'**ottimizzazione delle prestazioni** — forniscono un meccanismo di caching all'interno del database per query costose, quindi comprenderle è utile per ottimizzare applicazioni con query onerose eseguite frequentemente.

Il valore principale è precomputare e memorizzare i risultati di query costose (aggregazioni complesse, join pesanti) in modo che leggerli sia veloce (come interrogare una tabella) piuttosto che rieseguire la query costosa ogni volta — il che è un'ottimizzazione significativa per dashboard, report e analytics dove la stessa query pesante viene eseguita ripetutamente.

Comprendere il **trade-off** principale è importante: le materialized view forniscono letture veloci ma i dati sono **stali** fino al refresh (e consumano storage), quindi sono una decisione di caching adatta ai dati che non necessitano di freschezza in tempo reale.

Sapere come **refresharle** — specialmente `REFRESH ... CONCURRENTLY` (che aggiorna senza bloccare le letture, importante per la disponibilità in produzione, richiedendo un indice univoco) — e le strategie di refresh (su un programma tramite cron/pg_cron, oppure dopo le modifiche ai dati) è conoscenza pratica per usarle efficacemente.

Comprendere **quando usare** le materialized view (query costose eseguite frequentemente tolleranti di una leggera staleness — dashboard, analytics) rispetto a quando non usarle (dati che necessitano di accuratezza in tempo reale) riflette un giudizio solido.

Poiché le query costose ripetute sono una vera preoccupazione di prestazioni, e poiché le materialized view forniscono una soluzione di caching efficace all'interno del database (risultati precomputati, indicizzabili) con un chiaro trade-off freschezza/velocità, comprenderle — come funzionano, il refreshing (specialmente concorrente), il trade-off, e l'uso appropriato — è conoscenza PostgreSQL preziosa e praticamente rilevante per ottimizzare le prestazioni di query analitiche e di reporting, un'esigenza comune dove le materialized view offrono una soluzione pulita e built-in.