Menu

Viste e viste materializzate PostgreSQL: crea e aggiorna

Una vista è una query salvata che viene eseguita ogni volta che la leggi. Una vista materializzata memorizza il risultato della query e cambia solo quando esegui REFRESH MATERIALIZED VIEW.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

Una vista è una query salvata che leggi come una tabella: CREATE VIEW name AS SELECT .... Non memorizza dati, quindi ogni lettura esegue di nuovo la query e vede sempre le righe attuali. Una vista materializzata, CREATE MATERIALIZED VIEW name AS SELECT ..., memorizza il risultato su disco e cambia solo quando esegui REFRESH MATERIALIZED VIEW name.

L'ordine inserito dopo la creazione della vista compare nel risultato (ben con 90), perché la query della vista è stata eseguita nel momento della SELECT. Le viste danno un nome a una join o a un filtro che ripeti, nascondono colonne agli utenti che non devono vederle e fanno funzionare le vecchie query anche quando le tabelle cambiano.

Modificare o rimuovere una vista

CREATE OR REPLACE VIEW sostituisce la query sul posto. La nuova query deve restituire le stesse colonne, con gli stessi nomi e tipi, nello stesso ordine; può solo aggiungere nuove colonne in fondo. Per qualsiasi altra modifica, elimina la vista e creala di nuovo.

CREATE OR REPLACE VIEW paid_totals AS
  SELECT customer, sum(amount) AS total, count(*) AS orders
  FROM orders WHERE paid GROUP BY customer;

DROP VIEW paid_totals;
DROP VIEW IF EXISTS paid_totals CASCADE;  -- also drops views built on it

Inserire e aggiornare attraverso una vista

Una vista su una sola tabella è aggiornabile in automatico se non ha GROUP BY, DISTINCT, funzioni di aggregazione, funzioni finestra, LIMIT od operazioni di insieme. INSERT, UPDATE e DELETE sulla vista modificano allora la tabella. WITH CHECK OPTION rifiuta le righe che la vista non potrebbe mostrare dopo la modifica:

Entrambe le modifiche sono arrivate a products. Inserire (4, 'shelf', false) attraverso la vista fallisce con new row violates check option for view "active_products", perché la vista non la mostrerebbe. Per una vista non aggiornabile, un trigger INSTEAD OF può definire cosa fa una scrittura.

Creare e aggiornare una vista materializzata

Una vista materializzata esegue la sua query una volta e memorizza le righe. Le letture sono veloci come leggere una tabella, ma i dati nuovi non compaiono finché non fai il refresh:

Prima del refresh, ana mostra ancora 120; dopo, 180. REFRESH riesegue l'intera query e sostituisce le righe memorizzate. Usa una vista materializzata per risultati costosi da calcolare che possono avere minuti od ore di ritardo: dashboard, report, riepiloghi di ricerca.

CREATE MATERIALIZED VIEW ... WITH NO DATA la crea vuota, e leggerla fallisce fino al primo refresh.

Fare il refresh senza bloccare le letture

Un REFRESH semplice blocca la vista materializzata, quindi le query che la leggono aspettano finché il refresh non finisce. REFRESH MATERIALIZED VIEW CONCURRENTLY costruisce il nuovo risultato a parte e applica solo le differenze, così le letture continuano. Richiede un indice univoco sulla vista materializzata, su semplici nomi di colonna e senza WHERE:

Senza l'indice univoco, il refresh fallisce con un suggerimento a crearne uno. CONCURRENTLY può essere più veloce di un refresh semplice quando cambiano poche righe, e non si può usare su una vista materializzata che non è mai stata popolata. In ogni caso, su una vista materializzata gira un solo refresh alla volta. Gli altri indici funzionano come su una tabella, quindi indicizza le colonne su cui filtrano le tue query.

Refresh programmato

PostgreSQL non aggiorna da solo le viste materializzate. Esegui il refresh da uno scheduler, come cron che chiama psql, o con l'estensione pg_cron dove è installata:

# crontab: every 15 minutes
*/15 * * * * psql "$DATABASE_URL" -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals'
SELECT cron.schedule('refresh-totals', '*/15 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals');

Vista vs vista materializzata

VistaVista materializzata
Memorizza righenosì
Dati aggiornatisempreall'ultimo refresh
Velocità di letturacome la querycome una tabella
Indicino (usa gli indici delle tabelle)sì
Scrivibilele viste semplici sìno

Il riferimento di CREATE MATERIALIZED VIEW elenca le altre opzioni, come i parametri di storage e i tablespace.

Domande frequenti

Una vista materializzata PostgreSQL si aggiorna da sola?

No. Mantiene il risultato del suo ultimo REFRESH MATERIALIZED VIEW (o della sua creazione) finché non la aggiorni di nuovo. Programma il refresh con cron, con l'estensione pg_cron o dalla tua applicazione.

Una vista è più veloce che eseguire la query?

No. Una vista semplice non memorizza dati; PostgreSQL la espande dentro la query che la legge, quindi gira alla stessa velocità della query scritta per intero. Solo una vista materializzata risparmia il lavoro, al prezzo di dati non aggiornati.

Posso aggiungere un indice a una vista materializzata?

Sì. Una vista materializzata è memorizzata come una tabella, quindi CREATE INDEX funziona anche su di lei. Un indice univoco è proprio ciò che richiede REFRESH MATERIALIZED VIEW CONCURRENTLY.

Come elenco le viste e le viste materializzate?

In psql, \dv elenca le viste e \dm le viste materializzate. In SQL, interroga pg_views e pg_matviews.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia