Questa è la terza ed ultima parte dell’articolo dedicato a pg_rewind. Negli ultimi due abbiamo visto come pg_rewind può essere utile per correggere uno "split-brain" causato erroneamente durante la procedura di scambio dei ruoli tra un master ed uno standby, evitando di dover risincronizzare i nodi tramite un nuovo base backup. Abbiamo anche visto che questo è possibile per cluster di replica semplici che non coinvolgono più di due standby. In questo caso solo due nodi possono essere allineati dopo lo switchover, mentre gli altri necessitano di essere risincronizzati con un base backup. Dalla versione 9.6 di PostgreSQL, adesso è possibile utilizzare pg_rewind su cluster di replica più complessi.

A partire da PostgreSQL 9.6, pg_rewind ha una nuova funzionalità che gli permette di estendere l’orizzonte di visibilità della timeline di un intero cluster di alta disponibilità (HA), come quello di esempio nel mio precedente articolo. Adesso è infatti in grado di individuare il punto più recente nella timeline condiviso tra due o più nodi e risincronizzarli (e non più solo dall’ultimo checkpoint eseguito sul master prima della promozione dello standby, come nella versione 9.5).

Consideriamo quindi lo stesso esempio, ma adesso basato su PostgreSQL 9.6:

~$ # Set PATH variable
~$ export PATH=/usr/pgsql-9.6/bin:${PATH}
~$ 
~$ # This is the directory where we will be working on
~$ # Feel free to change it and the rest of the script
~$ # will adapt itself
~$ WORKDIR=/var/lib/pgsql/9.6
~$ 
~$ # Environment variables for PGDATA and archive directories
~$ MASTER_PGDATA=${WORKDIR}/master
~$ STANDBY1_PGDATA=${WORKDIR}/standby1
~$ STANDBY2_PGDATA=${WORKDIR}/standby2
~$ ARCHIVE_DIR=${WORKDIR}/archive
~$ 
~$ # Create the archive directory
~$ mkdir -p ${ARCHIVE_DIR}
~$ 
~$ # Create the HA cluster
~$ initdb --data-checksums -D ${WORKDIR}/master
~$ cat >> ${MASTER_PGDATA}/postgresql.conf <<EOF
~$ archive_command = 'cp %p ${ARCHIVE_DIR}/%f'
~$ archive_mode = on
~$ wal_level = hot_standby
~$ max_wal_senders = 10
~$ min_wal_size = '32MB'
~$ max_wal_size = '32MB'
~$ hot_standby = on
~$ wal_log_hints = on
~$ EOF
~$ cat >> ${MASTER_PGDATA}/pg_hba.conf <<EOF
~$ # Trust local access for replication
~$ # BE CAREFUL WHEN DOING THIS IN PRODUCTION
~$ local replication replication trust
~$ EOF
~$ pg_ctl -D /var/lib/pgsql/9.6/master -l ${WORKDIR}/master.log start
~$ psql -c "CREATE USER replication WITH replication"
~$ pg_basebackup -D ${STANDBY1_PGDATA} -R -c fast -U replication -x
~$ echo "port = 5433" >> ${STANDBY1_PGDATA}/postgresql.conf
~$ pg_ctl -D ${STANDBY1_PGDATA} -l ${WORKDIR}/standby.log start
~$ pg_basebackup -D ${STANDBY2_PGDATA} -R -c fast -U replication -x
~$ echo "port = 5434" >> ${STANDBY2_PGDATA}/postgresql.conf
~$ pg_ctl -D ${STANDBY2_PGDATA} -l ${WORKDIR}/standby2.log start

Simuliamo una promozione non voluta di uno dei due standby come nuovo master, lasciando gli altri nodi a formare un cluster HA indipendente:

~$ pg_ctl -D ${STANDBY1_PGDATA} promote

Adesso lo standby promosso procede nella timeline 2, mentre gli altri continuano sulla 1.

Completiamo lo "split-brain" creando una nuova tabella sul master che ha ancora in replica il secondo standby, e che non sarà visibile sul nodo appena promosso.

L’obiettivo adesso è quello di ricreare il cluster HA originale, con un master allineato alla situazione precedente lo "split-brain" (ovviamente senza la nuova tabella), che replichi due standby.

Dal momento che pg_rewind, con PostgreSQL 9.6, permette di rendere ogni nodo un master nel cluster HA, l’idea è di:

1. Fermare gli standby (incluso quello promosso)
2. Fermare il vecchio master e risincronizzarlo con lo standby promosso tramite pg_rewind
3. Modificare i parametri port e primary_conninfo nella configurazione (del vecchio master), in modo da seguire lo standby promosso
4. Risincronizzare l’altro standby con quello promosso usando pg_rewind
5. Modificarne poi i parametri port e primary_conninfo nella configurazione, in modo da seguire lo standby promosso a nuovo master del cluster HA

Vediamo come:

~$ pg_ctl -D ${STANDBY2_PGDATA} stop
waiting for server to shut down.... done
server stopped
~$ pg_ctl -D ${MASTER_PGDATA} stop
waiting for server to shut down.... done
server stopped
~$ pg_rewind --target-pgdata=${MASTER_PGDATA} --source-server="port=5433 user=postgres dbname=postgres"
servers diverged at WAL position 0/A0002C8 on timeline 1
rewinding from last common checkpoint at 0/A000220 on timeline 1
Done!
~$ pg_rewind --target-pgdata=${STANDBY2_PGDATA} --source-server="port=5433 user=postgres dbname=postgres"
servers diverged at WAL position 0/A0002C8 on timeline 1
rewinding from last common checkpoint at 0/A000220 on timeline 1
Done!

Una volta cambiate le configurazioni del vecchio master e dell’altro standby non promosso, riavviarli:

~$ pg_ctl -D ${MASTER_PGDATA} start
~$ pg_ctl -D ${STANDBY2_PGDATA} start

Adesso tutti e tre i nodi sono attivi e:

• Esiste un solo master in replica due standby, come all’inizio
• La tabella creata non è visibile, quindi i dati sono consistenti con la situazione iniziale

Ottimo!! Pensate se avessimo dovuto risincronizzare due nodi utilizzando un backup completo… :S

Conclusioni

pg_rewind è uno degli strumenti più utili in ambito HA: permette di evitare la risincronizzazione tramite l’esecuzione di nuovi base backup, in caso di "split-brain" accidentali. PostgreSQL 9.6 aggiunge nuove e potenti funzionalità a pg_rewind, e permette nel caso di cluster HA complessi di ricreare lo stato originale a seguito di split-brain accidentali, partendo da qualsiasi nodo nel cluster per la resincronizzazione.

Come raccomandazione finale: prendete cura dell’archiviazione dei WAL! Generalmente, con la replica fisica, gli amministratori di database basano la loro architettura in contesto alta disponibilità solo sulla connessione streaming. Per poter usare pg_rewind è necessario aver configurata l’archiviazione dei WAL, in modo da poterli prelevare in caso non siano più presenti sul master. Vi consiglio di considerare l’utilizzo di Barman, e la sua funzionalità get-wal, per facilitare la gestione degli archivi e l’eventuale recupero dei WAl necessari.

Nell’articolo precedente abbiamo visto come funziona pg_rewind per riallineare le timeline di un cluster di replica semplice composto da un master che replica su di un singolo standby. In un tale contesto, in un eventuale switchover, solo due nodi sono chiamati in causa. Ma cosa succede quando iniziano ad esserci diversi nodi di standby, anche in cascata?

Consideriamo adesso un cluster di replica un po’ più complesso, ma sempre basato su PostgreSQL 9.5, nel quale ci sono due standby non in cascata; in modo simile a quanto già fatto nel mio primo articolo dedicato a pg_rewind, creiamo questo cluster. Iniziamo col master:

# Set PATH variable
export PATH=/usr/pgsql-9.5/bin:${PATH}

# This is the directory where we will be working on
# Feel free to change it and the rest of the script
# will adapt itself
WORKDIR=/var/lib/pgsql/9.5

# Environment variables for PGDATA and archive directories
MASTER_PGDATA=${WORKDIR}/master
STANDBY1_PGDATA=${WORKDIR}/standby1
STANDBY2_PGDATA=${WORKDIR}/standby2
ARCHIVE_DIR=${WORKDIR}/archive

# Initialise the cluster
initdb --data-checksums -D ${MASTER_PGDATA}

# Basic configuration of PostgreSQL
cat >> ${MASTER_PGDATA}/postgresql.conf <<EOF
archive_command = 'cp %p ${ARCHIVE_DIR}/%f'
archive_mode = on
wal_level = hot_standby
max_wal_senders = 10
min_wal_size = '32MB'
max_wal_size = '32MB'
hot_standby = on
wal_log_hints = on
EOF

cat >> ${MASTER_PGDATA}/pg_hba.conf <<EOF
# Trust local access for replication
# BE CAREFUL WHEN DOING THIS IN PRODUCTION
local replication replication trust
EOF

# Create the archive directory
mkdir -p ${ARCHIVE_DIR}

# Start the master
pg_ctl -D ${MASTER_PGDATA} -l ${WORKDIR}/master.log start

# Create the replication user
psql -c "CREATE USER replication WITH replication"

E procediamo poi con il primo standby:

# Create the first standby
pg_basebackup -D ${STANDBY1_PGDATA} -R -c fast -U replication -x

cat >> ${STANDBY1_PGDATA}/postgresql.conf <<EOF
port = 5433
EOF

# Start the first standby
pg_ctl -D ${STANDBY1_PGDATA} -l ${WORKDIR}/standby1.log start

In modo identico, creiamo il secondo standby:

# Create the second standby
pg_basebackup -D ${STANDBY2_PGDATA} -R -c fast -U replication -x

cat >> ${STANDBY2_PGDATA}/postgresql.conf <<EOF
port = 5434
EOF

# Start the second standby
pg_ctl -D ${STANDBY2_PGDATA} -l ${WORKDIR}/standby2.log start

Consideriamo anche in questo caso che siano mantenuti pochi WAL sul master (notare come è stato valorizzato il parametro max_wal_size) che vengono opportunamente archiviati.

Se inseriamo un po’ di dati sul master, li vedremo visibili anche su entrambi gli (hot) standby.

Promuoviamo adesso uno dei due standby a nuovo master (ad esempio, quello basato su ${STANDBY1_PGDATA}), lasciando gli altri nodi inalterati:

pg_ctl -D ${STANDBY1_PGDATA} promote

Le modifiche eventualmente apportate al precedente master non saranno visibili sullo standby promosso, mentra saranno visibili sull’altro; nella directory archive/ è possibile trovare il file 00000002.history, che mostra un cambio nella timeline avvenuto durante la promozione, come visto anche nel precedente caso.

Tentiamo adesso di correggere l’errore, ricreando il cluster di replica come in origine, con lo standby promosso come nuovo master e gli altri due nodi come rispettivi standby: la procedurà che cercherò di seguire sarà

1. spengere il vecchio master e risincronizzarlo a partire dallo standby promosso usando pg_rewind
2. spengere il secondo standby e risincronizzarlo a partire dallo standby promosso, come fatto nel punto precedente

Iniziamo col primo punto:

~$ pg_ctl -D ${MASTER_PGDATA} stop
waiting for server to shut down.... done
server stopped
~$ pg_rewind --target-pgdata=${MASTER_PGDATA} \
    --source-server="port=5433 user=postgres dbname=postgres"
servers diverged at WAL position 0/501E680 on timeline 1
could not open file "/var/lib/pgsql/9.5/master/pg_xlog/000000010000000000000005": No such file or directory

could not find previous WAL record at 0/501E680
Failure, exiting

Come ci aspettavamo, mancano i WAL! Sì, so di essere ripetitivo, ma come già detto è sempre buona norma archiviare i WAL! Infatti, adesso è possibile recuperare i WAL mancanti: qui la procedurà sarà quella di copiarli manualmente per poi inserirli all’interno della pg_xlog/ del master, per poi rilanciare nuovamente pg_rewind (ma considerate anche l’uso di Barman per questo):

~$ cp ${ARCHIVE_DIR}/00000001000000000000000[56] ${MASTER_PGDATA}/pg_xlog/
~$ pg_rewind --target-pgdata=${MASTER_PGDATA} \
    --source-server="port=5433 user=postgres dbname=postgres"
servers diverged at WAL position 0/501E680 on timeline 1
rewinding from last common checkpoint at 0/501E5D8 on timeline 1
Done!

Ricordiamoci di cambiare opportunamente il parametro primary_conninfo all’interno del file recovery.conf ed il parametro port nel postgresql.conf, ed il vecchio master è ora pronto per seguire lo standby promosso. Facciamo adesso lo stesso anche col secondo standby:

~$ pg_ctl -D ${STANDBY2_PGDATA} stop
waiting for server to shut down.... done
server stopped
~$ pg_rewind --target-pgdata=${STANDBY2_PGDATA} \
    --source-server="port=5433 user=postgres dbname=postgres"
could not find common ancestor of the source and target cluster's timelines
Failure, exiting

Dunque, in questo caso non funziona: il secondo standby deve essere comunque risincronizzato dallo standby promosso tramite un nuovo base backup…

Conclusioni

pg_rewind è molto utile per risincronizzare i nodi tra di loro in un cluster di replica. Tuttavia, per infrastrutture che prevedono più standby non è possibile risincronizzare ogni nodo con solo questo tool.

Nonostante questo l’eventuale downtime degli standby è ridotto: un nodo può essere comunque velocemente riallineato, nell’attesa che gli altri vengano poi man mano aggiunti con nuovi base backup.

PostgreSQL 9.6 introduce una nuova, interessante funzionalità per pg_rewind: il suo orizzonte di visibilità può essere esteso e sarà sempre possibile trovare un punto comune nella timeline di un cluster di replica… non perdere la terza e ultima parte, dedicata alle novità di pg_rewind presenti in PostgreSQL 9.6!

PostgreSQL 9.5 introduce pg_rewind, un tool per risincronizzare un vecchio master con uno standby promosso che funziona anche se nel frattempo, non volutamente, quest’ultimo ha proseguito nella sua timeline. Questo è il caso, ad esempio, di uno switchover non eseguito con successo.

Avete mai concluso uno switchover con uno “split brain”? Questa situazione succede quando invece che aver invertito i ruoli tra master e standby, ottenete due master ciascuno con la sua propria timeline. È in situazioni come queste che pg_rewind viene in aiuto dei DBA PostgreSQL che si devono confrontare con i problemi di alta disponibilità (HA).

Fino alla versione 9.5 di PostgreSQL c’era una sola soluzione possibile: risincronizzare la PGDATA del vecchio master con un nuovo base backup a partire dallo standby promosso e poi aggiungerlo come nuovo standby al cluster di replica. Questo diventa un problema quando le dimensioni del database sono notevoli: nel caso di diverse centinaia di GB non è semplice effettuare queste operazioni mantenendo allo stesso tempo downtime ridotti.

Riportare un database allo stato in cui si trovava in un determinato momento del passato può essere complicato, ma nonostante questo ci sono varie strategie. Suggerisco a chi è interessato di dare un’occhiata agli articoli di Gulcin che affrontano il tema in PostgreSQL e che menziona anche l’uso di pg_rewind.

Come funziona pg_rewind

pg_rewind è in grado di leggere tutti i file contenuti nella PGDATA del vecchio master, identificare i blocchi modificati durante un eventuale cambio di timeline e quindi copiare solo questi dallo standby promosso, in modo da riallinearsi. Come “effetto collaterale”, anche i file di configurazione vengono copiati e sovrascritti, quindi è compito del DBA quello di riadattarli eventualmente al nodo in esame. Ad ogni modo, questo evita di dover risincronizzare totalmente la PGDATA.

Per fare questo, è necessario avere tutti i WAL prodotti negli ultimi istanti di vita del vecchio master precedenti lo switchover. Le modifiche sono individuate dal confronto tra i blocchi di dati presenti nella PGDATA con le modifiche inserite nei WAL. Una volta identificati i blocchi modificati, vengono sostituiti con quelli presenti nello standby promosso, mimando una sorta di “rewind” della timeline.

Inoltre:

• le istanze debbono essere state inizializzate con l’opzione “-k” (o --data-checksums)
• il parametro wal_log_hints deve essere abilitato

Fino a PostgreSQL 9.5, i WAL necessari sono quelli a partire dall’ultimo checkpoint, dato che pg_rewind non è in grado di andare indietro nella timeline ulteriormente.

Per capire meglio come funziona, consideriamo questo semplice esempio:

# Set PATH variable
export PATH=/usr/pgsql-9.5/bin:${PATH}

# This is the directory where we will be working on
# Feel free to change it and the rest of the script
# will adapt itself
WORKDIR=/var/lib/pgsql/9.5

# Environment variables for PGDATA and archive directories
MASTER_PGDATA=${WORKDIR}/master
STANDBY1_PGDATA=${WORKDIR}/standby1
ARCHIVE_DIR=${WORKDIR}/archive

# Initialise the cluster
initdb --data-checksums -D ${MASTER_PGDATA}

# Basic configuration of PostgreSQL
cat >> ${MASTER_PGDATA}/postgresql.conf <<EOF
archive_command = 'cp %p ${ARCHIVE_DIR}/%f'
archive_mode = on
wal_level = hot_standby
max_wal_senders = 10
min_wal_size = '32MB'
max_wal_size = '32MB'
hot_standby = on
wal_log_hints = on
EOF

cat >> ${MASTER_PGDATA}/pg_hba.conf <<EOF
# Trust local access for replication
# BE CAREFUL WHEN DOING THIS IN PRODUCTION
local replication replication trust
EOF

# Create the archive directory
mkdir -p ${ARCHIVE_DIR}

# Start the master
pg_ctl -D ${MASTER_PGDATA} -l ${WORKDIR}/master.log start

# Create the replication user
psql -c "CREATE USER replication WITH replication"

(notare il basso numero di WAL mantenuti volutamente nel master), ed uno standby:

# Create the first standby
pg_basebackup -D ${STANDBY1_PGDATA} -R -c fast -U replication -x

cat >> ${STANDBY1_PGDATA}/postgresql.conf <<EOF
port = 5433
EOF

# Start the first standby
pg_ctl -D ${STANDBY1_PGDATA} -l ${WORKDIR}/standby1.log start

Inseriamo alcuni dati sul master: questi saranno visibili anche dallo (hot) standby.

Promuoviamo adesso lo standby, lasciando inalterato il master:

pg_ctl -D ${STANDBY1_PGDATA} promote

Se adesso aggiorniamo il master i cambiamenti non saranno più visibili dallo standby. Inoltre, nella directory archive/ è presente il file 00000002.history e questo mostra che c’è stato un cambio di timeline durante la promozione.

Proviamo adesso ad effettuare il “rewind” del master, e ad aggiungerlo in replica allo standby promosso:

~$ pg_ctl -D ${MASTER_PGDATA} stop
waiting for server to shut down.... done
server stopped
~$ pg_rewind --target-pgdata=${MASTER_PGDATA} \
    --source-server="port=5433 user=postgres dbname=postgres"

Va notato che per la connessione al server sorgente – lo standby promosso – è stato usato l’utente postgres perché pg_rewind necessita di una connessione tramite superuser per ispezionare i blocchi di dati.

Se il parametro max_wal_size non è sufficientemente alto per mantenere nella pg_xlog/ del (vecchio) master i WAL necessari, come volutamente configurato nel nostro esempio, viene emesso un errore come il seguente:

The servers diverged at WAL position 0/3015938 on timeline 1.
could not open file "/var/lib/pgsql/9.5/master/pg_xlog/000000010000000000000002": No such file or directory

could not find previous WAL record at 0/3015938
Failure, exiting

Ci sono due possibili soluzioni a questo:

• copiare manualmente i WAL mancanti dall’archivio alla directory pg_xlog/ del master, a partire da quello riportato nel messaggio di errore
• configurare opportunamente il parametro restore_command all’interno del file recovery.conf da includere nella PGDATA del (vecchio) master, così che pg_rewind troverà automaticamente i WAL mancanti.

La seconda è probabilmente la più adatta. Pensiamo, ad esempio, al caso in cui l’archivio di WAL è gestito tramite Barman: il restore_command potrebbe essere basato sulla funzionalità get-wal di Barman, come chiaramente spiegato in questo interessante articolo di Gabriele. Così facendo, Barman può essere usato come una possibile sorgente da cui prelevare i WAL necessari a pg_rewind.

Una volta che tutti i WAL necessari sono disponibili, pg_rewind può essere nuovamente eseguito, questa volta correttamente, ottenendo il seguente messaggio:

~$ pg_rewind --target-pgdata=${MASTER_PGDATA} \
    --source-server="port=5433 user=postgres dbname=postgres"
servers diverged at WAL position 0/3015938 on timeline 1
rewinding from last common checkpoint at 0/3000140 on timeline 1
Done!

Va ribadito che adesso verranno copiati solo pochi blocchi di dati della PGDATA, quelli modificati durante lo split-brain, anche se il database occupasse centinaia di GB! Ricordiamoci poi che anche le configurazioni sono state copiate e sovrascritte, incluso un eventuale recovery.conf già presente nella PGDATA del vecchio master che deve essere convertito in un nuovo standby. Quindi, **bisogna ricordarsi di*:

• modificare la porta utilizzata dall’istanza (5432 nel nostro caso) nel postgresql.conf;
• modificare la primary_conninfo nel recovery.conf in modo da assicurarsi che il vecchio master sia in grado di poter effettuare la connessione streaming verso lo standby promosso a nuovo master.

Una volta che questo è stato fatto, basta far partire nuovamente il vecchio master e questo sarà in grado di seguire in replica quello nuovo.

Avete cluster di replica più complessi di questo basato su due soli nodi? Non preoccupatevi! La seconda parte entrerà ancor più nel dettaglio nel funzionamento di pg_rewind in PostgreSQL 9.5!

Già nel mio precedente articolo sui BRIN ho evidenziato le novità introdotte soprattutto in ambito geospaziale con la 9.5. Oggi mostrerò una funzionalità della nuova versione di PostgreSQL che amplia ancora di più gli strumenti messi a disposizione in ambito GIS: la distanza punto-poligono, definita matematicamente come la minima delle distanze tra il punto di interesse e ciascun punto del poligono. Sebbene PostgreSQL abbia già nativamente introdotto alcune geometrie bidimensionali (point, circle, polygon, …), per poter calcolare la distanza di un punto da un poligono in 2D, fino ad oggi, era necessaria l’installazione di PostGIS (per esempio per trovare i punti di interesse più vicini ad un dato perimetro, o viceversa). Con PostgreSQL 9.5 non sarà più strettamente necessaria l’installazione di PostGIS (per quanto utilissima in ambito GIS) per questo tipo di operazioni.

La patch per il calcolo delle distanze tra point e polygon è stata introdotta da Alexander Korotkov, nome già noto nel mondo PostgreSQL soprattutto per i suoi lavori sugli indici GiST. Alexander ha implementato un algoritmo che iterativamente calcola la distanza del punto dai singoli segmenti, per poi prenderne la minima; tecnicamente, ha esteso l’overloading dell’operatore <-> in modo che non fosse limitato al calcolo della distanza fra coppie di geometrie dello stesso tipo point o polygon.

Cerchiamo di capire con un piccolo esempio come funziona, utilizzando uno script che permette di creare 10 milioni di quadrati con lato di lunghezza unitaria uniformemente distribuiti sul piano. La creazione della tabella sul mio desktop è stata completata in circa 13 minuti. Cerchiamo adesso quali sono i 10 quadrati più vicini all’origine (ovvero il punto di coordinate x=0, y=0), ossia facciamo una ricerca di tipo “the k nearest neighbours” (kNN):

SELECT point(0., 0.) <-> polygons AS distance
FROM polygons
ORDER BY distance DESC
LIMIT 10;

L’esecuzione della query ha richiesto, sul mio desktop, circa 17 secondi. Il piano di esecuzione prevede ovviamente un sequential scan di tutta la tabella per la ricerca:

QUERY PLAN    --------------------------------------------------------------------------------------------------------------------------------------
    -
     Limit  (cost=505032.60..505032.62 rows=10 width=101) (actual time=16505.291..16505.976 rows=10 loops=1)
       ->  Sort  (cost=505032.60..530032.69 rows=10000035 width=101) (actual time=16504.416..16504.420 rows=10 loops=1)
             Sort Key: (('(0,0)'::point <-> polygons)) DESC
             Sort Method: top-N heapsort  Memory: 26kB
             ->  Seq Scan on polygons  (cost=0.00..288935.44 rows=10000035 width=101) (actual time=0.533..13198.879 rows=10000000 loops=1)

Uso degli indici

La patch di Alexander introdotta in PostgreSQL non prevede il supporto per gli indici GiST per l’operatore <-> in ricerche kNN tra point e polygon: proviamo ad esempio a costruire l’indice sui polygon (sul mio desktop l’indicizzazione ha richiesto circa 30 minuti):

CREATE INDEX gist_index
ON polygons
USING gist(polygons);

Rilanciamo la stessa ricerca “nearest neighbours” di prima:

SELECT point(0., 0.) <-> polygons AS distance
FROM polygons
ORDER BY distance DESC
LIMIT 10;

Il tempo di esecuzione è stato nuovamente di 17 secondi, gli stessi del caso in assenza di indici. Infatti se andiamo a vedere quale è stato il piano di esecuzione troveremo che nuovamente è stata eseguita una sequential scan:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
    -
     Limit  (cost=505031.61..505031.63 rows=10 width=101) (actual time=15284.445..15285.214 rows=10 loops=1)
       ->  Sort  (cost=505031.61..530031.62 rows=10000006 width=101) (actual time=15283.761..15283.765 rows=10 loops=1)
             Sort Key: (('(0,0)'::point <-> polygons)) DESC
             Sort Method: top-N heapsort  Memory: 25kB
             ->  Seq Scan on polygons  (cost=0.00..288935.08 rows=10000006 width=101) (actual time=0.236..12805.262 rows=10000000 loops=1)

Conclusioni

PostgreSQL 9.5 ha introdotto la possibilità di effettuare ricerche kNN anche tra point e polygon sul piano, senza necessariamente installare PostGIS. Sebbene le ricerche kNN tra point o circle supportano l’indice GiST, ad oggi ciò non è possibile per quelle miste, tra point e polygon, in quanto richiedono una ricerca basata sul sequential scan completo dei dati.

Questa situazione sta per essere risolta. Alexander ha già presentato una patch in cui vuole introdurre una modifica degli indici GiST per supportare ricerche del tipo kNN-GiST with recheck [¹] tra geometrie miste. Questo dovrebbe, tra le altre cose, rendere l’indice GiST utilizzabile anche per ricerche kNN tra point e polygon.

Gli indici BRIN (Block Range INdex) rappresentano una delle maggiori novità presenti in PostgreSQL 9.5. Si tratta di un nuovo tipo di indice che arricchisce la collezione già presente, aggiungendosi a quelli “ad albero” (btree, GiST/SP-GiST e GIN) ed Hash. Si tratta comunque di un indice molto differente dagli altri: non è basato sui singoli valori che devono essere indicizzati, ma sulle pagine da 8kB di PostgreSQL.

L’algoritmo alla base di questa indicizzazione unisce le caratteristiche della scansione sequenziale dei record di una tabella (SeqScan) con quella basata su un indice ad albero (IndexScan): durante la costruzione dei BRIN, le pagine di PostgreSQL vengono scansionate in blocchi, sequenzialmente, e per ogni blocco vengono mappati gli estremi dei valori contenuti che devono essere indicizzati. In un secondo tempo poi, il planner di PostgreSQL saprà quali sono i blocchi di pagine PostgreSQL che devono essere presi in considerazione durante l’esecuzione delle query.

Esistono due tipi di supporto per questo tipo di indice:

• minmax, che si occupa di mappare i valori minimi e massimi di un attributo indicizzato;
• inclusion, in cui vengono mappati gli estremi dell’intervallo in cui i valori dell’attributo indicizzato possono variare.

La differenza tra quest’ultimo tipo di supporto ed il precedente sta nel fatto che esso permette anche l’indicizzazione di tipi di dato cosiddetti “non-ordinabili”, ovvero che non presentano cardinalità come ad esempio per i numeri o le stringhe.

Per come sono definiti, gli indici BRIN presentano due grandi vantaggi:

• occupano uno spazio su disco notevolmente minore degli altri indici (che hanno invece una dimensione paragonabile a quella dell’intera tabella indicizzata), e dunque sono particolarmente utili nel caso di tabelle molto grandi;
• richiedono poca manutenzione rispetto agli altri indici.

Vari esempi sono stati mostrati sull’uso dei BRIN[¹][²][³], in particolare per il tipo di supporto minmax. Vorrei invece parlare adesso del supporto di tipo inclusion, e di quanto sia utile se si ha a che fare con dati geospaziali.

Un esempio di uso per i punti

I punti, come ogni altra entità geospaziale, soffrono del fatto che non contemplano criteri di ordinamento (non è possibile definire “un punto maggiore di un altro”), per cui non possono essere indicizzabili con le metodologie standard usate ad esempio per gli indici btree.

Esistono tuttavia in PostgreSQL l’indice GiST, che si basa su algoritmi “di ordinamento” quali R-tree ed k-NN, e quello SP-GiST, basato invece su Quad-tree e kd-tree, che sono in grado di indicizzare dati geospaziali. La differenza tra i due indici è data dal fatto che, mentre il primo utilizza una struttura ad albero bilanciato, il secondo ne usa uno non bilanciato.

Le strutture non bilanciate non sono generalmente molto utili quando si ha a che fare con numeri e stringhe; viceversa, tendono ad essere usati in ambito geospaziale, soprattutto per ricerce del tipo “inclusione all’interno di bounding box”.

Il mio intento adesso è di presentare un semplice esempio in cui confrontare le prestazioni ottenibili effettuando questo tipo di ricerche basandosi sugli indici attualmente presenti (GiST, SP-GiST) e sui futuri BRIN sfruttando il supporto di tipo inclusion.

Innanzitutto è necessario installare la versione beta (per il momento in cui è stato scritto questo articolo) di PostgreSQL 9.5: ad esempio, io ho eseguito i miei test su una macchina CentOS 6.5, ed ho dunque installato il repository yum di PGDG per poter accedere ai pacchetti di PostgreSQL 9.5beta[⁴].

Consideriamo poi, ad esempio, di costruire una tabella contenente 10000000 di punti casualmente distribuiti sul piano all’interno di un’area 100unità X 100unità (sfruttiamo qui a titolo di esempio il tipo di dato point presente nel core del database PostgreSQL, tralasciando le geometrie fornite dall’estensione PostGIS):

CREATE TABLE points AS (
SELECT id,
point(100.0 * random(), 100.0 * random()) AS point
FROM generate_series(1,10000000) AS id);

Costruiamo poi sulla colonna point un primo indice di tipo GiST:

CREATE INDEX gist_index ON points USING gist(box(point));

ed un secondo indice di tipo SP-GiST:

CREATE INDEX spgist_index ON points USING spgist(box(point));

Osserviamo dimensioni e tempi di esecuzione nella costruzione dei due indici:

L’indice SP-GiST, essendo non bilanciato, presenta una struttura meno complessa che si traduce in minor spazio occupato e minori tempi di esecuzione per la sua costruzione.

Costruiamo adesso l’indice BRIN sullo stesso campo della tabella:

CREATE INDEX brin_index ON points
USING brin(box(point) box_inclusion_ops);

Da notare la specifica dell’operator class box_inclusion_ops, che detta all’indice quali siano gli operatori con supporto inclusion che dovranno utilizzarlo. È bene precisare che gli indici BRIN vengono costruiti considerando di default blocchi da 128 pagine da 8kB di PostgreSQL: questo significa che potrà restituire un numero di blocchi (fino a 128) che potrebbero anche non contenere i dati richiesti sulla base della ricerca e che quindi, come anticipato sopra, dovranno essere scartati in un secondo tempo dal planner PostgreSQL.

È possibile comunque aumentare la “risoluzione” dell’informazione immagazzinata dall’indice BRIN diminuendo la dimensione del blocco di pagine PostgreSQL usato durante la sua costruzione, in modo da aumentarne l’efficienza di utilizzo: questo a scapito ovviamente della dimensione dell’indice che occuperà più spazio. Proviamo a confrontare come cambia un indice BRIN richiedendo che la dimensione del blocco di pagine PostgreSQL considerate sia pari a 64 (la metà del default) configurando il parametro pages_per_range:

CREATE INDEX brin64_index ON points
USING brin(box(point) box_inclusion_ops)
WITH (pages_per_range = 64);

Il risultato ci sorprende: l’indice BRIN occupa davvero uno spazio molto ridotto, con tempi di esecuzione considerabili “istantanei” rispetto a quelli degli altri indici.

Conclusioni

Proviamo adesso a lanciare la query di ricerca dei punti inclusi all’interno di un quadrato 50×50, “immerso” tra i punti della tabella:

SELECT * FROM points
WHERE box(point) <@ box(point(20, 20), point(70, 70));

Effettivamente notiamo, soffermandoci agli indici finora presenti in PostgreSQL, come la struttura non bilanciata sia più efficiente in questo tipo di ricerche (~50% in meno di tempo necessario):

Confrontiamo i tempi di esecuzione della query sfruttando gli indici BRIN:

Anche qui rimaniamo piacevolmente sorpresi: gli indici BRIN assicurano tempi di esecuzione paragonabili a quelli dell’indice non bilanciato SP-GiST nel caso di ricerche del tipo “inclusione all’interno di bounding box”, ma potendo vantare dimensioni e tempi di costruzione dell’indice stesso praticamente irrisori rispetto all’SP-GiST.

Concludendo, dagli altri blog[⁵][⁶] abbiamo imparato che gli indici BRIN, se usati per ricerche che si basano sul supporto minmax ad esempio su numeri o stringhe, hanno prestazioni generalmente inferiori agli altri indici (esempio il btree) che aumentano man mano che la “risoluzione” del BRIN viene espansa tramite il parametro pages_per_range.

In questo articolo abbiamo visto come gli indici BRIN usati per ricerche che si basano sul supporto inclusion hanno prestazioni del tutto simili a quelle degli altri indici, occupando uno spazio su disco molto inferiore.

In ogni caso, con PostgreSQL 9.5 i BRIN possono vantare miglioramenti in termini di manutenibilità, dimensione e tempi di creazione rispetto agli indici finora presenti in PostgreSQL.

Fix per corruzione dati

Per gli utenti delle versioni 9.3 e 9.4 di PostgreSQL, l’aggiornamento corregge i problemi derivanti dal “multixact wraparound”, che comporta la possibile corruzione dei dati o la loro perdita. I database con una frequenza di transazioni molto alta (1 milione/ora) contenenti molte chiavi esterne sono particolarmente vulnerabili. Sollecitiamo vivamente tutti coloro che fanno uso delle versioni 9.4 e 9.3 ad aggiornare le loro installazioni nei prossimi giorni.

Le versioni 9.2 o precedenti non sono affette da questo problema.

Aggiornamenti di sicurezza

Questo aggiornamento risolve due falle di sicurezza riscontrate in PostgreSQL nei mesi passati. Nessuna di queste è particolarmente urgente. Ad ogni modo, gli utenti dovrebbero prenderle in considerazione nel caso le loro installazioni sono articolarmente vulnerabili:

• CVE-2015-3165 Double “free” after authentication timeout.
• CVE-2015-3166 Unanticipated errors from the standard library.
• CVE-2015-3167 pgcrypto has multiple error messages for decryption with an incorrect key.

In aggiunta, raccomandiamo a tutti gli utenti che fanno uso delle autenticazioni Kerberos, GSSAPI, o SSPI di impostare il parametro include_realm ad 1 nel pg_hba.conf, che sarà il valore di default a partire dalle versioni future. Maggiori informazioni sui problemi finora riscontrati sono disponibili nella PostgreSQL Security Page.

Altre correzioni e miglioramenti

Una nuova versione dell’estensione citext corregge un problema dato dalle precedenti (e non documentate) funzioni regexp_matches() con cui era implementata. Adesso viene ritornato un tipo diverso di dato rispetto alla precedente versione, quindi gli utenti che fanno uso di CIText dovrebbero testare le loro applicazioni prima di aggiornare l’estensione tramite il comando “ALTER EXTENSION citext UPDATE”.

Inoltre, più di 50 problematiche segnalate sono state corrette in aggiunta agli altri aggiornamenti rilasciati. Molte di queste incidono su tutte le versioni attualmente supportate. Le correzioni includono:

• Restituzione di date e timestamp infiniti come infinito nella conversione in json
• Correzione delle funzioni populate_record() e to_record() dei dati json/jsonb
• Correzione dei check finali degli exclusion constraint posticipati
• Migliore strategia del planner con query di tipo star-schema
• Correzione tre problemi emersi con le join
• Garanzia di corretto funzionamento dei lock con le security barrier delle viste
• Correzione dei problemi di deadlock in fase di startup quando max_prepared_transactions è troppo piccolo
• Esecuzione ricorsiva di fsync() della data directory dopo un crash
• Correzione dei possibili fallimenti del launcher degli autovacuum
• Migliore gestione di segnali non aspettati all’interno della funzione LockBufferForCleanup()
• Correzione di crash causati dall’esecuzione di COPY IN su tabelle aventi check constraint
• Rimozione di attese nella replica sincrona durante transazioni read-only
• Correzione di due problemi con indici hash
• Prevenzione di memory leak durante l’esecuzione del vacuum di indici GIN
• Correzione di due problemi relativi ai backgroud worker
• Correzioni alla replica basata su decodifica logica
• Correzione di alcuni problemi minori di pg_dump e pg_upgrade

Questo rilascio include un aggiornamento a tzdata versione 2015d, con aggiornamenti per Egitto, Mongolia e Palestina, più modifiche storiche per Canada e Cile.

Uscita dal supporto comunitario per la versione 9.0

La versione 9.0 uscirà dal supporto comunitario a settembre 2015. Questo significa che questi aggiornamenti saranno probabilmente gli ultimi per questa versione. Gli utenti di PostgreSQL 9.0 dovrebbero iniziare a pianificare un aggiornamento a una versione più recente prima di tale data. Si veda il link http://www.postgresql.org/support/versioning/ per maggiori informazioni sulle date di fine supporto comunitario per le varie versioni di PostgreSQL.

Istruzioni di aggiornamento

Come ogni aggiornamento di minor release, non è necessario eseguire alcun dump e restore dei database e neppure utilizzare pg_upgrade per effettuare questi ultimi aggiornamenti; è sufficiente spegnere il servizio PostgreSQL ed aggiornare i binari. Gli utenti dell’estensione CIText necessitano soltanto di eseguire un comando. Gli utenti che hanno saltato aggiornamenti precedenti, potrebbero necessitare di ulteriori operazioni da eseguire dopo l’aggiornamento; si vedano le varie note di rilascio per maggiori dettagli.

Link

• http://www.postgresql.org/download
• http://www.postgresql.org/docs/current/static/release.html
• http://www.postgresql.org/support/security/

Last updated 2015-05-22 14:28:50 CEST

La clausola WITHIN GROUP

La clausola WITHIN GROUP è particolarmente utile nei casi in cui si vogliano effettuare aggregazioni su subset ordinati di dati.
PostgreSQL ha introdotto, fin dalla versione 9.0, le window function per poter lavorare su subset di dati correlabili a ciascun record corrente delle tabelle, definendo una sorta di “finestre di aggregazione” centrate su ogni specifico record man mano che la query viene eseguita tramite la clausola SQL OVER (PARTITION BY/ORDER BY) e sfruttando tali funzioni che possono essere eseguite su tali aggregazioni.

Con la versione 9.4 di PostgreSQL è stata introdotta la clausola SQL WITHIN GROUP che permette di semplificare molte operazioni finora possibili solo con l’uso delle window function, definendo aggregazioni di subset ordinati di dati.
Sono state introdotte, inoltre, nuove funzioni che possono essere applicate su tali subset e che ampliano la collezione delle window function presenti:

• percentile_cont(), percentile_disc() per il calcolo di percentili;
• mode() funzione statistica che calcola la moda su subset ordinati;
• rank(), dense_rank(), percent_rank(), cume_dist(): window function già presenti in PostgreSQL per essere eseguite sui subset ottenuti tramite la clausola OVER (PARTITION BY/ORDER BY ) e da adesso in grado di prendere come parametro subset ordinati prodotti con la clausola WITHIN GROUP.

Per capire meglio, supponiamo ad esempio di voler calcolare il 25°, il 50°, il 75° ed il 100° percentile dei primi 20 numeri interi. Finora era possibile solo partizionando i numeri in 4 set tramite la clausola OVER (PARTITION BY/ORDER BY) per poi ordinarli internamente in 4 subset ordinati di cui poi prendiamo il massimo valore, ad esempio sfruttando una CTE:

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ WITH subset AS (
    SELECT val,
       ntile(4) OVER (ORDER BY val) AS tile
    FROM t
  )
  SELECT max(val)
  FROM subset GROUP BY tile ORDER BY tile;

   max
  ------
   5
  10
  15
  20
 (4 rows)

Con PostgreSQL 9.4 tutto si riduce ad un solo comando SQL, comportando notevoli vantaggi in termini di leggibilità degli script e di esecuzione dei comandi:

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ SELECT unnest(percentile_disc(array[0.25,0.5,0.75,1])
    WITHIN GROUP (ORDER BY val))
  FROM t;

   max
  ------
   5
  10
  15
  20
 (4 rows)

Clausola FILTER di SQL

Questa seconda clausola dei comandi SQL è utile nei casi in cui si vogliano applicare dei filtri su subset di dati senza necessariamente eseguire aggregazioni.
Ad esempio, è ora possibile effettuare un count totale dei record di una tabella ed anche parziale di un suo subset che soddisfi una certa condizione (espressa mediante la clausola WHERE) all’interno di una unica query, senza doverne usare ulteriori da eseguire sulle aggregazioni:

$ SELECT count(*) count_all,
         count(*) FILTER(WHERE bid=1) count_1,
         count(*) FILTER(WHERE bid=2) count_2
  FROM pgbench_history;

 count_all | count_1 | count_2
 ----------+---------+---------­­­­­­­­­
      7914 |     758 |     784
 (1 row)

Semplificando, anche in questo caso, la leggibilità degli script e migliorando le performance in esecuzione.

Conclusioni

L’estensione dello standard SQL tramite l’introduzione di queste nuove clausole facilita ulteriormente il compito degli sviluppatori, che si trovano a poter delegare sempre più al database la manipolazione e l’aggregazione di subset di dati.
Con la clausola WITHIN GROUP diventa più semplice la gestione di subset di dati ordinabili, introducendo nuove window function. La clausola FILTER facilita la gestione di subset di dati che soddisfano certe condizioni, evitando le aggregazioni.

Con l’introduzione del tipo di dato JSONB in PostgreSQL emerge definitivamente il lato “NoSQL” di questo DBMS relazionale, andando incontro a tutti coloro che prediligono una struttura dei dati in forma “chiave-valore” stile dizionario, molto usata in ambito sviluppo, garantendo allo stesso tempo tutti i vantaggi di un database relazionale.

Già PostgreSQL 9.2 prevedeva l’uso del tipo JSON, permettendo direttamente la persistenza su database di un dato JSON. Tuttavia, si trattava di fatto di un dato di tipo testo, con in più la capacità di validare la sintassi JSON. Col nuovo tipo di dato JSONB le informazioni sono memorizzate in un formato binario dedicato, potendo così beneficiare di algoritmi specifici che ne migliorano le prestazioni di accesso e ottimizzano la memorizzazione su disco:

• operatori avanzati di accesso e confronto: grazie alla sua struttura specializzata JSONB ha permesso l’implementazione di nuovi operatori, che, oltre a dare una maggiore flessibilità all’utente, permettono di usare tutta la potenza di indici hash, btree, GIST e GIN;
• dimensioni su disco ridotte: lo spazio di memorizzazione richiesto per memorizzare documenti con una struttura complessa con il dato JSONB è inferiore rispetto a quanto richiesto per il formato JSON;
• organizzazione interna come un dizionario con chiave univoca: questo significa che l’accesso è molto veloce, ma l’ordine di inserimento delle chiavi nella struttura JSONB non viene preservato. Inoltre, in presenza di chiavi duplicate, viene mantenuto solo l’ultimo valore inserito, a differenza di quanto accadeva nel dato JSON:

$ SELECT '{"a":1, "b":2}'::JSONB = '{"b":2, "a":1}'::JSONB
 ?column?
 --------
  t
 (1 row)

$ SELECT '{"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"}'::JSON
              JSON
  ----------------------------------------------
  {"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"}
  (1 row)

$ SELECT '{"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"}'::JSONB
              JSON
  ----------------------------------------------
  {"a":"abc", "d":"overwritten","z":[1,2,3]}
  (1 row)

È bene comunque precisare che il dato JSONB è compatibile con tutte le funzioni introdotte per il dato JSON.

L’effetto della possibilità di indicizzare il tipo JSONB si traduce in una migliore disponibilità dei dati in lettura, permettendo di accedere in modo efficiente all’intero contenuto di un campo JSONB.

Questo rende possibile usare efficientemente PostgreSQL per analizzare dati privi di uno schema predefinito, avvicinandolo ulteriormente al mondo “NoSQL”. A tale proposito Thom Brown ha condotto alcuni test mostrando come si rilevi un aumento di prestazioni in lettura (ed un più ridotto spazio occupato dagli indici) rispetto a un campo JSON, arrivando a prestazioni in lettura superiori anche a DBMS tipicamente NoSQL quali MongoDB.

Conclusioni

Sicuramente l’introduzione del tipo JSONB avvicina PostgreSQL a quegli sviluppatori che abitualmente usano dati in formato JSON. Primi fra tutti, gli sviluppatori web che fanno ampio uso di JavaScript e che magari hanno già iniziato a lavorare con PostgreSQL usando il tipo JSON per memorizzare i dati. Passando a JSONB avranno la possibilità di usare tutta la potenza del motore di PostgreSQL per elaborare quei dati con facilità ed efficienza.

Dalla versione 9.3 di PostgreSQL è possibile aggiornare e inserire nuovi dati direttamente su viste. Le operazioni vengono in modo trasparente e automatico indirizzate sulla tabella sottostante (è infatti necessario che la struttura delle viste sia semplice – ad esempio, che ci sia un solo elemento FROM).
Con PostgreSQL 9.4 è possibile aggiungere l’opzione CHECK per l’INSERT sulla vista.

Consideriamo, ad esempio, una tabella contenente un solo campo di numeri interi e due viste, una relativa ai numeri della tabella divisibili per 2 e l’altra relativa ai numeri divisibili per 3.

Proviamo ad inserire il numero 123 sulla prima vista, chiamata first:

$ CREATE TABLE some_data(id int4 PRIMARY KEY);
CREATE TABLE
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id%2;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM some_data WHERE 0 = id%3;
CREATE VIEW
$ INSERT INTO first(id) VALUES (123);

Questo verrà inserito nella tabella sottostante (some_data), nonostante la vista sia dedicata solo ai numeri divisibili per 2 (ma non sarà visibile in essa).
L’opzione CHECK in PostgreSQL 9.4 serve proprio a gestire i casi di inserimento sulle viste eseguendo preventivamente un controllo dei valori che si intende inserire, compatibilmente con la definizione della vista.

Sono previste due possibili opzioni:

• CASCADED CHECK (default)  in cui i check vengono applicati in cascata anche sulle altre viste eventualmente presenti sulla stessa tabella;
• LOCAL CHECK in cui i check vengono applicati sulla singola vista in cui viene effettuato la INSERT.

Riprendendo l’esempio già descritto, proviamo a utilizzare l’opzione CHECK:

$ CREATE TABLE some_data (id int4 PRIMARY KEY);
CREATE TABLE
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2 WITH CHECK OPTION;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH CHECK OPTION;
CREATE VIEW
$ INSERT INTO first(id) VALUES (14);
INSERT 0 1
$ INSERT INTO first(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"
$ INSERT INTO second(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"

Il valore 14 viene correttamente inserito nella prima vista, mentre il valore 15 no – come è lecito attendersi.
Meno chiaro è l’errore sull’inserimento di 15 sulla seconda vista: non viene inserito (seppure divisibile per 3) in quanto l’opzione CHECK deve essere definita in una sola delle due viste, e di tipo LOCAL CHECK.

Non è sufficente definire su entrambe le viste l’opzione LOCAL CHECK per aggirare il problema:

$ DROP VIEW first;
DROP VIEW
$ DROP VIEW second;
DROP VIEW
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ INSERT INTO second(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"

Di seguito l’esempio funzionante:

$ DROP VIEW first;
DROP VIEW
$ DROP VIEW second;
DROP VIEW
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ INSERT INTO second(id) VALUES (15);
INSERT 0 1

La clausola CHECK sulle viste aggiornabili rappresenta un nuovo meccanismo di controllo da inserire nel database, direttamente sull’inserimento di nuovi dati utilizzando viste. Un ulteriore passo che rafforza sempre di più il ruolo del database in materia di applicazione di integrità dei dati.

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

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