PostgreSQL 9.4: REFRESH CONCURRENTLY di viste materializzate

Le viste materializzate sono state introdotte in PostgreSQL 9.3 e risultano essere particolarmente utili per query di lunga durata da ripetersi più volte. PostgreSQL 9.4 aggiunge la possibilità di eseguire il REFRESH completo di una vista materializzata in modalità concorrente.

Una vista materializzata è una vista particolare che permette di rendere persistente, memorizzandolo su disco come una qualsiasi tabella, il risultato della propria esecuzione, creando una vera istantanea della situazione in quel momento all’interno del database.

Per questo motivo, agendo su una copia statica dei dati, i tempi di esecuzione di query su viste materializzate sono notevolmente inferiori rispetto a quelli su viste classiche. Inoltre, è possibile anche creare indici ad-hoc sulle viste materializzate, che risultano pertanto molto adatte in contesti di business intelligence e di data warehousing.

In PostgreSQL 9.3, l’unico sistema per aggiornare una vista materializzata è tramite il comando REFRESH, i cui tempi di esecuzione sono paragonabili a quelli di creazione della vista stessa (essendo l’aggiornamento completo e, non ancora, incrementale come in altri DBMS commerciali).
Ben più grave, però, è il fatto che, durante l’esecuzione del REFRESH, Postgres acquisisce sulla vista un AccessExclusiveLock, andando di fatto a bloccare tutti gli accessi concorrenti, anche di lettura.

Con la versione 9.4 di PostgreSQL è possibile lanciare il REFRESH di una vista materializzata in modo concorrente.

Supponiamo di avere una tabella t nel database mydb definita in questo modo:

CREATE TABLE t(
        i serial PRIMARY KEY,
        t timestamp with time zone DEFAULT clock_timestamp()
);

col campo i chiave primaria. Inseriamo poi 50 milioni di record nella tabella t:

mydb=# \timing
Timing is on.
mydb=# INSERT INTO t SELECT generate_series(1, 50000000) AS i;
INSERT 0 50000000
Time: 236580.268 ms

Creiamo adesso una vista materializzata (t_v) sulla query che effettua una SELECT dei record di t generati nei primi 5 secondi di ogni minuto:

mydb=# SELECT * FROM t WHERE extract(second FROM t) BETWEEN 0 AND 5;
[...]
Time: 135119.698 ms
mydb=# CREATE MATERIALIZED VIEW t_v AS SELECT * FROM t WHERE extract(second FROM t) BETWEEN 0 AND 5;
SELECT 3433227
Time: 29146.775 ms
mydb=# SELECT * FROM t_v;
[...]
Time: 4576.630 ms

È possibile vedere come i tempi di creazione della vista (che contiene 3433227 record estratti da t) siano paragonabili a quelli di esecuzione della query, mentre un comando SELECT lanciato sulla vista risulta essere circa 7-8 volte più veloce.

Sebbene l’operazione non abbia molto senso sul piano pratico, aggiorniamo adesso la tabella t, assegnando ai timestamp dei record generati nei primi 5 secondi di ogni minuto il valore now() (al momento in cui è stato lanciato questo comando di esempio il timestampo reso era pari a
2014-08-04 13:50:15.779483+00):

UPDATE t SET t=now() WHERE extract(second FROM t) BETWEEN 0 AND 5;

Adesso tabella e vista risulteranno disallineati (mentre la tabella t non prevede più record con timestamp compreso tra i primi 5 secondi di ogni minuto, la vista contiene ancora i vecchi dati non aggiornati):

mydb=# UPDATE t SET t=now() WHERE extract(second FROM t) BETWEEN 0 AND 5;
UPDATE 3433227
Time: 55795.959 ms
mydb=# SELECT * FROM t WHERE extract(second FROM t) BETWEEN 0 AND 5;
 i | t
---+---
 (0 rows)

Time: 28219.871 ms
mydb=# SELECT * FROM t_v ORDER BY i LIMIT 10;
    i    |               t
---------+-------------------------------
 6485918 | 2014-08-04 08:32:00.000002+00
 6485919 | 2014-08-04 08:32:00.000005+00
 6485920 | 2014-08-04 08:32:00.000007+00
 6485921 | 2014-08-04 08:32:00.00001+00
 6485922 | 2014-08-04 08:32:00.000012+00
 6485923 | 2014-08-04 08:32:00.000015+00
 6485924 | 2014-08-04 08:32:00.000018+00
 6485925 | 2014-08-04 08:32:00.00002+00
 6485926 | 2014-08-04 08:32:00.000023+00
 6485927 | 2014-08-04 08:32:00.000025+00
 (10 rows)

Time: 406.141 ms

Proviamo a lanciare, all’interno di una transazione (in modo da permetterci di effettuare ROLLBACK in un secondo tempo), il REFRESH della vista materializzata.

Apriamo quindi una seconda connessione sul database mydb, ed impostiamo il parametro statement_timeout a 10 secondi in modo che la connessione cada se l’esecuzione di una query supera in durata il timeout:

                 prima connessione

mydb=# BEGIN;
BEGIN
mydb=# REFRESH MATERIALIZED VIEW t_v;
REFRESH MATERIALIZED VIEW

                 seconda connessione

mydb=# set statement_timeout = 10000;
SET
mydb=# SELECT * FROM t_v;
ERROR:  canceling statement due to statement timeout

Tenuto conto che la SELECT sulla vista non supera il secondo di esecuzione, la perdita di connessione per timeout è sintomo del lock presente sulla vista stessa.

Eseguiamo ROLLBACK nella transazione aperta con il REFRESH, per lanciare un nuovo REFRESH, questa volta concorrente (sempre all’interno di una transazione in modo che resti “in sospeso” fino al successivo COMMIT):

mydb=# BEGIN;
BEGIN
mydb=# REFRESH MATERIALIZED VIEW CONCURRENTLY t_v;
ERROR:  cannot refresh materialized view "public.t_v" concurrently
HINT:  Create a UNIQUE index with no WHERE clause on one or more columns of the materialized view.
mydb=# ROLLBACK;
ROLLBACK

Important

In questo caso otteniamo errore perché, quando viene lanciato il comando REFRESH MATERIALIZED VIEW CONCURRENTLY, viene creata una tabella temporanea contenente i nuovi dati aggiornati della vista. La tabella temporanea viene messa in OUTER JOIN con i dati non aggiornati, ed è necessario che non esistano record duplicati. Ecco perché è richiesto che almeno un campo della vista sia indicizzato con vincolo di unicità (ad esempio, non basta l’indice sulla chiave primaria).

Dopo aver annullato la transazione precedente con ROLLBACK (comunque invalidata dall’errore), ne
rilanciamo una nuova creando opportunamente un indice con vincolo di unicità:

mydb=# \timing
Timing is on.
mydb=# CREATE UNIQUE INDEX idx_i on t_v (i);
CREATE INDEX
Time: 3466.765 ms
mydb=# BEGIN;
BEGIN
Time: 0.118 ms
mydb=# REFRESH MATERIALIZED VIEW CONCURRENTLY t_v;
REFRESH MATERIALIZED VIEW
Time: 50522.136 ms

mentre avviene il REFRESH concorrente della vista, proviamo ad interrogare la vista da una seconda
connessione, in cui abbiamo sempre attivato lo statement_timeout a 10s:

mydb=# SET statement_timeout = 10000;
SET
mydb=# SELECT * FROM t_v ORDER BY i LIMIT 10;
    i    |               t
---------+-------------------------------
 6485918 | 2014-08-04 08:32:00.000002+00
 6485919 | 2014-08-04 08:32:00.000005+00
 6485920 | 2014-08-04 08:32:00.000007+00
 6485921 | 2014-08-04 08:32:00.00001+00
 6485922 | 2014-08-04 08:32:00.000012+00
 6485923 | 2014-08-04 08:32:00.000015+00
 6485924 | 2014-08-04 08:32:00.000018+00
 6485925 | 2014-08-04 08:32:00.00002+00
 6485926 | 2014-08-04 08:32:00.000023+00
 6485927 | 2014-08-04 08:32:00.000025+00
 (10 rows)

Anche se il REFRESH non è terminato (impiega 50 secondi circa, comunque non termina finché non lanciamo il COMMIT della transazione) è possibile accedere alla vista materializzata, seppure mostri ancora i dati non aggiornati con il comando UPDATE. Proviamo quindi ad eseguire il COMMIT, ed a vedere cosa si osserva nella seconda connessione:

            prima connessione

mydb=# CREATE UNIQUE INDEX idx_i on t_v (i);
CREATE INDEX
Time: 3466.765 ms
mydb=# BEGIN;
BEGIN
Time: 0.118 ms
mydb=# REFRESH MATERIALIZED VIEW CONCURRENTLY t_v;
REFRESH MATERIALIZED VIEW
Time: 50522.136 ms
mydb=# COMMIT;
COMMIT
Time: 1.134 ms

            seconda connessione

mydb=# SELECT * FROM t_v ORDER BY i LIMIT 10;
 i | t
---+---
 (0 rows)

Time: 889.921 ms

Una volta quindi terminato il REFRESH della vista materializzata è possibile accederci mostrando
i dati aggiornati (adesso anche la vista non prevede più record con timestamp compreso tra i primi 5 secondi di ogni minuto).

In conclusione, con la versione 9.4 di PostgreSQL le viste materializzate non acquisiscono lock durante l’aggiornamento della vista stessa, permettendo l’accesso ai dati in modo concorrente (seppure i dati siano aggiornati all’ultima operazione di refresh).

Per maggiori informazioni:

• http://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html