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!

Annie Prévot, CIO del CNAF, la Cassa Nazionale per gli Assegni Familiari della Francia, afferma:

“Il CNAF fornisce servizi a 11 milioni di persone ed eroga 73 miliardi di Euro ogni anno, attraverso 26 tipi di prestazioni. Questo servizio, essenziale per la popolazione, si basa su un sistema informativo che deve essere efficiente e affidabile. Con soddisfazione, il sistema di CNAF si basa su PostgreSQL per la gestione dei dati.”

UPSERT

Da molti anni una delle funzionalità più richieste dagli sviluppatori di applicazioni, “UPSERT” è la forma breve di “INSERT, ON CONFLICT UPDATE” e permette di trattare in modo identico record nuovi e aggiornati. UPSERT semplifica lo sviluppo di applicazioni web e mobile incaricando il database di gestire conflitti fra modifiche concorrenti ai dati. Inoltre questa funzionalità abbatte l’ultima barriera significativa per la migrazione di applicazioni legacy MySQL verso PostgreSQL.

Sviluppata nel corso degli ultimi due anni da Peter Geoghegan di Heroku, l’implementazione di PostgreSQL di UPSERT è notevolmente più flessibile e potente di quelle offerte da altri database relazionali. La nuova clausola ON CONFLICT consente di ignorare nuovi dati, oppure di aggiornare diverse colonne o relazioni in modo da supportare complesse catene ETL (Extract, Transform, Load) per il caricamento massivo di dati. Inoltre, come tutto PostgreSQL, è progettata per utilizzo concorrente e per integrarsi con tutte le altre funzionalità, replica logica compresa.

Sicurezza a livello di riga

PostgreSQL continua a espandere le sue capacità nel campo della protezione dei dati, aggiungendo il supporto per la sicurezza a livello di riga – in inglese Row Level Security (RLS). RLS implementa un verso controllo di accesso al dato per riga e per colonna e si integra con stack esterni di sicurezza come SE Linux. PostgreSQL è già noto per essere “il più sicuro di default”. RLS consolida questa posizione, rendendolo la migliore scelta per applicazioni con elevati requisiti di sicurezza dei dati; in particolare, conformità a PCI, direttiva europea su Data Protection e standard di protezione dei dati in ambito sanitario.

RLS è l’apice di cinque anni di funzionalità sulla sicurezza aggiunte a PostgreSQL e comprende l’ampio lavoro svolto da KaiGai Kohei di NEC, Stephen Frost di Crunchy Data e Dean Rasheed. Grazie a RLS, gli amministratori di database possono impostare politiche di sicurezza per gestire quali righe particolari utenti sono autorizzati ad aggiornare o a vedere. Implementare la sicurezza del dato in questo modo rende il database resistente a exploit di tipo SQL injection, nonché ad altre falle di sicurezza a livello applicativo.

Funzionalità per i Big Data

PostgreSQL 9.5 include molteplici funzionalità per database di grandi dimensioni e per la loro integrazione con altri sistemi Big Data. Tali funzionalità riaffermano il ruolo dominante di PostgreSQL nel mercato open source dei Big Data, in forte crescita. Fra queste, vale la pena citare:

Indici BRIN

questo nuovo tipo di indice supporta la creazione di indici piccoli ma al tempo stesso molto efficienti per tabelle molto grandi, “naturalmente ordinate”. Per esempio, tabelle contenenti dati di log con miliardi di record possono essere indicizzate e ricercate nel 5% del tempo richiesto da un indice BTree tradizionale.

Ordinamenti più veloci

PostgreSQL riesce a ordinare più velocemente dati testuali e di tipo NUMERIC, utilizzando un algoritmo chiamato “chiavi abbreviate”. Questo algoritmo è in grado di accelerare query che necessitano di ordinare grandi moli di dati da 2 a 12 volte, e di velocizzare la creazione di indici fino a 20 volte.

CUBE, ROLLUP e GROUPING SET

queste nuove clausole dello standard SQL permettono di produrre report a più livelli di riepilogo utilizzando una sola query invece di molteplici, come in passato. CUBE inoltre consente di integrare PostgreSQL con strumenti di reporting come Tableau, tipici di ambienti Online Analytic Processing (OLAP).

Foreign Data Wrapper (FDW)

i FDW consentono già a PostgreSQL di essere utilizzato come motore di query per altri sistemi Big Data come Hadoop e Cassandra. La versione 9.5 aggiunge IMPORT FOREIGN SCHEMA e la propagazione (“pushdown“) delle JOIN, rendendo le connessioni per query a database esterni sia più facili da configurare che più efficienti.

TABLESAMPLE

questa clausola SQL consente di ottenere in modo veloce un campione statistico di una tabella enorme, senza la necessità di ordinamenti dispendiosi.

“Il nuovo indice BRIN di PostgreSQL 9.5 è una funzionalità molto potente che permette a Postgres di gestire e indicizzare volumi di dati che fino ad ora erano impraticabili, se non addirittura impossibili. È in grado di portare la scalabilità e le prestazioni oltre i limiti dei tradizionali database relazionali e rende PostgreSQL una soluzione perfetta per analytics con Big Data”, afferma Boyan Botev, Lead Database Administrator, Premier, Inc.

Vuoi saperne di più?

Per ulteriori informazioni e spiegazioni sulle funzionalità aggiunte in PostgreSQL 9.5, consulta il press kit ufficiale rilasciato dalla Comunità.

Segui inoltre la nostra serie di articoli in italiano su PostgreSQL 9.5.

Questi nuovi operatori potranno essere aggiunti alla clausola GROUP BY e permetteranno il raggiungimento di risultati aggregati in modo più veloce e con un codice più pulito di quanto fosse possibile fare in precedenza, utilizzando più query GROUP BY e UNION ALL.

Facciamo un esempio classico: supponiamo di voler analizzare le vendite di un negozio.

Identifichiamo quindi la vendita come il fatto da analizzare e per praticità d’esempio, consideriamo il prezzo come unica misura a cui siamo interessati. Le dimensioni della nostra analisi saranno il tempo, il luogo e il prodotto (lo script per la creazione delle tabelle e l’inserimento dei dati utilizzati in questi esempi possono essere scaricati da questo link www.dropbox.com/s/sk2n8uz3ddp01ss/create_tables_grouping_sets.sql?dl=0.

Vediamo adesso come utilizzare i nuovi operatori per condurre delle analisi sul db.

GROUPING SETS

Con GROUPING SETS è possibile poter definire insiemi differenti di campi su cui eseguire una GROUP BY e unirne insieme i risultati. Cerchiamo di comprendere con precisione il comportamento di questo operatore con un esempio.

Supponiamo di voler analizzare le vendite dei singoli negozi per anno, e di volere anche avere i dati annuali aggregati di tutti i negozi. Fino alla versione 9.4, avremo ottenuto questo risultato eseguendo UNION ALL:

SELECT l.nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY (l.nome_negozio, g.anno)
UNION ALL
SELECT NULL AS nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY anno
ORDER BY nome_negozio, anno;

Con la versione 9.5 possiamo utilizzare GROUPING SETS:

SELECT l.nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS ((l.nome_negozio, anno), anno)
ORDER BY l.nome_negozio, anno;

Come si può notare, è possibile specificare raggruppamenti su singoli campi o su insiemi di questi, purché ogni insieme sia delimitato da parentesi. L’output delle due query è identico:

 nome_negozio | anno | sum
--------------+------+-----
 Neg1         | 2014 |   7
 Neg1         | 2015 |  31
 Neg2         | 2014 |  10
 Neg2         | 2015 |  72
 Neg3         | 2014 |  20
 Neg3         | 2015 |  64
 Neg4         | 2014 |  32
 Neg4         | 2015 |  37
              | 2014 |  69
              | 2015 | 204

Tuttavia possiamo verificare come il piano della versione che usa GROUPING SETS sia nettamente migliore. Questo è il piano della versione con UNION ALL:

QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=359.27..362.45 rows=1270 width=68) (actual time=0.774..0.775 rows=10 loops=1)
       Sort Key: l.nome_negozio, g.anno
       Sort Method: quicksort  Memory: 25kB
       ->  Append  (cost=133.95..293.80 rows=1270 width=68) (actual time=0.379..0.708 rows=10 loops=1)
             ->  HashAggregate  (cost=133.95..147.33 rows=1070 width=68) (actual time=0.379..0.403 rows=8 loops=1)
                   Group Key: l.nome_negozio, g.anno
                   ->  Hash Join  (cost=75.80..125.93 rows=1070 width=68) (actual time=0.128..0.253 rows=32 loops=1)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=24.18..59.59 rows=1070 width=68) (actual time=0.073..0.150 rows=32 loops=1)
                               Hash Cond: (v.id_luogo = l.id)
                               ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.016..0.033 rows=32 loops=1)
                               ->  Hash  (cost=16.30..16.30 rows=630 width=36) (actual time=0.026..0.026 rows=4 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                     ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.012 rows=4 loops=1)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.023..0.023 rows=3 loops=1)
                               Buckets: 2048  Batches: 1  Memory Usage: 17kB
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.008 rows=3 loops=1)
             ->  Subquery Scan on "*SELECT* 2"  (cost=131.28..135.78 rows=200 width=68) (actual time=0.297..0.303 rows=2 loops=1)
                   ->  HashAggregate  (cost=131.28..133.78 rows=200 width=36) (actual time=0.294..0.297 rows=2 loops=1)
                         Group Key: g_1.anno
                         ->  Hash Join  (cost=75.80..125.93 rows=1070 width=36) (actual time=0.097..0.215 rows=32 loops=1)
                               Hash Cond: (v_1.id_data = g_1.id)
                               ->  Hash Join  (cost=24.18..59.59 rows=1070 width=36) (actual time=0.050..0.120 rows=32 loops=1)
                                     Hash Cond: (v_1.id_luogo = l_1.id)
                                     ->  Seq Scan on vendite v_1  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.006..0.021 rows=32 loops=1)
                                     ->  Hash  (cost=16.30..16.30 rows=630 width=4) (actual time=0.017..0.017 rows=4 loops=1)
                                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                           ->  Seq Scan on luoghi l_1  (cost=0.00..16.30 rows=630 width=4) (actual time=0.004..0.008 rows=4 loops=1)
                               ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.015..0.015 rows=3 loops=1)
                                     Buckets: 2048  Batches: 1  Memory Usage: 17kB
                                     ->  Seq Scan on giorni g_1  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.003..0.006 rows=3 loops=1)
     Planning time: 1.433 ms
     Execution time: 1.330 ms

Mentre questo è il piano della versione con GROUPING SETS, più rapido e più gradevole alla vista:

QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=271.81..274.99 rows=1270 width=68) (actual time=0.475..0.476 rows=10 loops=1)
       Sort Key: l.nome_negozio, g.anno
       Sort Method: quicksort  Memory: 25kB
       ->  GroupAggregate  (cost=179.76..206.34 rows=1270 width=68) (actual time=0.312..0.426 rows=10 loops=1)
             Group Key: g.anno, l.nome_negozio
             Group Key: g.anno
             ->  Sort  (cost=179.76..182.44 rows=1070 width=68) (actual time=0.287..0.299 rows=32 loops=1)
                   Sort Key: g.anno, l.nome_negozio
                   Sort Method: quicksort  Memory: 27kB
                   ->  Hash Join  (cost=75.80..125.93 rows=1070 width=68) (actual time=0.089..0.197 rows=32 loops=1)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=24.18..59.59 rows=1070 width=68) (actual time=0.059..0.124 rows=32 loops=1)
                               Hash Cond: (v.id_luogo = l.id)
                               ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.017..0.031 rows=32 loops=1)
                               ->  Hash  (cost=16.30..16.30 rows=630 width=36) (actual time=0.022..0.022 rows=4 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                     ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.010 rows=4 loops=1)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.013..0.013 rows=3 loops=1)
                               Buckets: 2048  Batches: 1  Memory Usage: 17kB
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.007 rows=3 loops=1)
     Planning time: 0.741 ms
     Execution time: 0.713 ms

Inoltre, potremmo voler raggruppate le nostre vendite per regione e categoria, con l’aggiunta di una riga che mostri il totale. In questo caso, utilizzeremo la seguente query:

SELECT l.regione, p.categoria, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
GROUP BY GROUPING SETS ((l.regione, p.categoria), ())
ORDER BY regione, categoria;

Ottenendo come risultato:

 regione | categoria | totale
---------+-----------+--------
 Toscana | cat1      |    104
 Toscana | cat2      |    100
 Umbria  | cat1      |     14
 Umbria  | cat2      |     55
         |           |    273

GROUPING SETS è l’operatore più generico dei tre, e permette di raggruppare dati in base a qualsiasi insieme di parametri definito dall’utente. CUBE e ROLLUP, come vedremo, sono solo comode scorciatoie per alcuni utilizzi molto comuni di GROUPING SETS.

CUBE

L’operatore CUBE raggruppa sull’insieme di tutte le combinazioni possibili dei vari campi passati come parametro. Supponiamo, ad esempio, di voler vedere i totali delle vendite organizzati per categoria, provincia e mese/anno. Lanciando la query:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY CUBE((g.mese, g.anno),p.categoria, l.provincia)
ORDER BY anno, mese, provincia, categoria;

otterremo il seguente risultato:

 provincia | categoria | mese | anno | totale
-----------+-----------+------+------+--------
 Firenze   | cat1      |   11 | 2014 |     20
 Firenze   |           |   11 | 2014 |     20
 Perugia   | cat1      |   11 | 2014 |      2
 Perugia   | cat2      |   11 | 2014 |     30
 Perugia   |           |   11 | 2014 |     32
 Prato     | cat1      |   11 | 2014 |     12
 Prato     | cat2      |   11 | 2014 |      5
 Prato     |           |   11 | 2014 |     17
           | cat1      |   11 | 2014 |     34
           | cat2      |   11 | 2014 |     35
           |           |   11 | 2014 |     69
 Firenze   | cat1      |   10 | 2015 |     20
 Firenze   | cat2      |   10 | 2015 |     20
 Firenze   |           |   10 | 2015 |     40
 Perugia   | cat1      |   10 | 2015 |     10
 Perugia   | cat2      |   10 | 2015 |      5
 Perugia   |           |   10 | 2015 |     15
 Prato     | cat1      |   10 | 2015 |     22
 Prato     | cat2      |   10 | 2015 |     25
 Prato     |           |   10 | 2015 |     47
           | cat1      |   10 | 2015 |     52
           | cat2      |   10 | 2015 |     50
           |           |   10 | 2015 |    102
 Firenze   | cat1      |   11 | 2015 |      4
 Firenze   | cat2      |   11 | 2015 |     20
 Firenze   |           |   11 | 2015 |     24
 Perugia   | cat1      |   11 | 2015 |      2
 Perugia   | cat2      |   11 | 2015 |     20
 Perugia   |           |   11 | 2015 |     22
 Prato     | cat1      |   11 | 2015 |     26
 Prato     | cat2      |   11 | 2015 |     30
 Prato     |           |   11 | 2015 |     56
           | cat1      |   11 | 2015 |     32
           | cat2      |   11 | 2015 |     70
           |           |   11 | 2015 |    102
 Firenze   | cat1      |      |      |     44
 Firenze   | cat2      |      |      |     40
 Firenze   |           |      |      |     84
 Perugia   | cat1      |      |      |     14
 Perugia   | cat2      |      |      |     55
 Perugia   |           |      |      |     69
 Prato     | cat1      |      |      |     60
 Prato     | cat2      |      |      |     60
 Prato     |           |      |      |    120
           | cat1      |      |      |    118
           | cat2      |      |      |    155
           |           |      |      |    273

Avremmo potuto scrivere la stessa query usando GROUPING SETS in questo modo:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS(
  ((g.mese, g.anno),p.categoria, l.provincia),
  ((g.mese, g.anno),p.categoria),
  ((g.mese, g.anno),l.provincia),
  (p.categoria, l.provincia),
  (g.mese, g.anno),
  p.categoria,
  l.provincia,
  ()
)
ORDER BY anno, mese, provincia, categoria;

Il piano di esecuzione è lo stesso per entrambe le versioni:

QUERY PLAN
    ------------------------------------------------------------------------------------------------------
     Sort  (cost=856.15..868.35 rows=4881 width=104)
       Sort Key: g.anno, g.mese, l.provincia, p.categoria
       ->  GroupAggregate  (cost=223.60..557.12 rows=4881 width=104)
             Group Key: p.categoria, l.provincia, g.mese, g.anno
             Group Key: p.categoria, l.provincia
             Group Key: p.categoria
             Group Key: ()
             Sort Key: g.mese, g.anno, p.categoria
               Group Key: g.mese, g.anno, p.categoria
               Group Key: g.mese, g.anno
             Sort Key: l.provincia, g.mese, g.anno
               Group Key: l.provincia, g.mese, g.anno
               Group Key: l.provincia
             ->  Sort  (cost=223.60..226.28 rows=1070 width=104)
                   Sort Key: p.categoria, l.provincia, g.mese, g.anno
                   ->  Hash Join  (cost=104.92..169.76 rows=1070 width=104)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=53.30..103.43 rows=1070 width=100)
                               Hash Cond: (v.id_prodotto = p.id)
                               ->  Hash Join  (cost=24.18..59.59 rows=1070 width=72)
                                     Hash Cond: (v.id_luogo = l.id)
                                     ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=44)
                                     ->  Hash  (cost=16.30..16.30 rows=630 width=36)
                                           ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36)
                               ->  Hash  (cost=18.50..18.50 rows=850 width=36)
                                     ->  Seq Scan on prodotti p  (cost=0.00..18.50 rows=850 width=36)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=12)
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=12)

ROLLUP

ROLLUP, come CUBE, è una semplificazione di GROUPING SETS. In questo caso i raggruppamenti sono effettuati prima su tutti i campi su cui è stato dichiarato l’operatore, poi su tutti meno l’ultimo, su tutti meno gli ultimi due e così via. Ad esempio, usando ROLLUP al posto di CUBE nell’ultima query otterremo:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY ROLLUP((g.mese, g.anno),p.categoria, l.provincia)
ORDER BY anno, mese, provincia, categoria;

 provincia | categoria | mese | anno | totale
-----------+-----------+------+------+--------
 Firenze   | cat1      |   11 | 2014 |     20
 Perugia   | cat1      |   11 | 2014 |      2
 Perugia   | cat2      |   11 | 2014 |     30
 Prato     | cat1      |   11 | 2014 |     12
 Prato     | cat2      |   11 | 2014 |      5
           | cat1      |   11 | 2014 |     34
           | cat2      |   11 | 2014 |     35
           |           |   11 | 2014 |     69
 Firenze   | cat1      |   10 | 2015 |     20
 Firenze   | cat2      |   10 | 2015 |     20
 Perugia   | cat1      |   10 | 2015 |     10
 Perugia   | cat2      |   10 | 2015 |      5
 Prato     | cat1      |   10 | 2015 |     22
 Prato     | cat2      |   10 | 2015 |     25
           | cat1      |   10 | 2015 |     52
           | cat2      |   10 | 2015 |     50
           |           |   10 | 2015 |    102
 Firenze   | cat1      |   11 | 2015 |      4
 Firenze   | cat2      |   11 | 2015 |     20
 Perugia   | cat1      |   11 | 2015 |      2
 Perugia   | cat2      |   11 | 2015 |     20
 Prato     | cat1      |   11 | 2015 |     26
 Prato     | cat2      |   11 | 2015 |     30
           | cat1      |   11 | 2015 |     32
           | cat2      |   11 | 2015 |     70
           |           |   11 | 2015 |    102
           |           |      |      |    273

ovvero, l’equivalante di:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS(
  ((g.mese, g.anno),p.categoria, l.provincia),
  ((g.mese, g.anno),p.categoria),
  (g.mese, g.anno),
  ()
)
ORDER BY anno, mese, provincia, categoria;

Conclusioni

Versione dopo versione PostgreSQL sta facendo passi da gigante in ambito OLAP e data warehouse – o, se preferite, BigData e Analytics. Oltre ai comandi mostrati in questo articolo, solo nella 9.5 ad esempio sono stati aggiunti gli indici BRIN, ereditarietà delle tabelle esterne e IMPORT FOREIGN SCHEMA, che permettono a PostgreSQL di gestire quantità ancora più grandi di dati e con più facilità.

Siamo sicuri che chi usa PostgreSQL per fare Business Intelligence apprezzerà notevolmente questa nuova major release.