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
| Vista | Vista materializzata | |
|---|---|---|
| Memorizza righe | no | sì |
| Dati aggiornati | sempre | all'ultimo refresh |
| Velocità di lettura | come la query | come una tabella |
| Indici | no (usa gli indici delle tabelle) | sì |
| Scrivibile | le 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.