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.

Le query UPSERT servono a gestire in modo efficiente i casi in cui ad una tabella vogliamo aggiornare il contenuto di una riga oppure inserirne una nuova.

UPSERT = IF NOT found THEN insert ELSE update

Quello che segue è un piccolo esempio pratico che mette in mostra il funzionamento delle interrogazioni UPSERT.

Relazioni fra film e compositori

IMdb è un sito che contiene molte informazioni su film e personale di produzione. Oltre ad utilizzare la sua interfaccia web è anche possibile ottenere i dati direttamente in formato testo.

In questo esempio useremo il file composers.list, di cui quello che segue è un estratto:

Abraham, Alexander William      The Play Front (2010)
                        Voices from the Basement (2010)
                        "Rwby" (2012) {A Minor Hiccup (#2.3)}
                        "Rwby" (2012) {Best Day Ever (#2.1)}
                        "Rwby" (2012) {Black and White (#1.16)}
                        "Rwby" (2012) {Breach (#2.12)}
                        "Rwby" (2012) {Burning the Candle (#2.6)}
                        "Rwby" (2012) {Dance Dance Infiltration (#2.7)}
                        "Rwby" (2012) {Extracurricular (#2.5)}
                        "Rwby" (2012) {Field Trip (#2.8)}

Aceto, Robby            101 Ways to Retire--or Not! (2007) (V)
                        Freak the Language (2015)
                        Hidden Books: The Art of Kumi Korf (2011)
                        Inside (2013/IV)
                        Invisible Ink (2011)
                        Jungle Warfare College (2013)  (music composer)
                        Mr. Stokes' Mission (2012)

Goldshein, Steve        Red vs. Blue Season 9 (2011)
                        Whispering Willows (2014) (VG)
                        "Rwby" (2012) {A Minor Hiccup (#2.3)}
                        "Rwby" (2012) {Breach (#2.12)}
                        "Rwby" (2012) {Field Trip (#2.8)}
                        "Rwby" (2012) {Forever Fall (#1.13)}
                        "Rwby" (2012) {Forever Fall: Part 2 (#1.14)}
                        "Rwby" (2012) {Jaunedice (#1.11)}
                        "Rwby" (2012) {Jaunedice: Part 2 (#1.12)}
                        "Rwby" (2012) {Mountain Glenn (#2.10)}
                        "Rwby" (2012) {No Brakes (#2.11)}
                        "Rwby" (2012) {Painting the Town... (#2.4)}
                        "Rwby" (2012) {Search and Destroy (#2.9)}
                        "Rwby" (2012) {The Emerald Forest (#1.6)}
                        "Rwby" (2012) {The First Step: Part 2 (#1.5)}
                        "Rwby" (2012) {Welcome to Beacon (#2.2)}
                        "X-Ray and Vav" (2014) {Operation: Rescue Friend (#1.2)}
                        "X-Ray and Vav" (2014) {X-Ray & Vav Rise (#1.1)}

Il file è composto di associazioni fra nomi di compositori e film. I dati non hanno un ordine particolare e sia il nome del compositore che il nome del film possono essere ripetuti più volte.

Supponiamo di voler popolare queste tabelle:

-- In questa tabella dovremo inserire un record
-- per ogni compositore. Il campo how_many_films dovra'
-- contenere il numero di film nei quali il compositore
-- ha lavorato
CREATE TABLE composer (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(1024) NOT NULL UNIQUE,
    how_many_films INTEGER NOT NULL DEFAULT 1
);

-- In questa tabella dovremo inserire un record
-- per ogni film. Il campo how_many_composers
-- dovra' contenere il numero dei compositori
-- che hanno lavorato nel film
CREATE TABLE film (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(1024) NOT NULL UNIQUE,
    how_many_composers INTEGER NOT NULL DEFAULT 1
);

-- In questa tabella dovremo inserire un record
-- per ogni coppia compositore/film
CREATE TABLE composer_film (
    id SERIAL NOT NULL PRIMARY KEY,
    composer_id INTEGER NOT NULL,
    film_id INTEGER NOT NULL,
    CONSTRAINT fk_composer_film_composer 
      FOREIGN KEY (composer_id) REFERENCES composer (id),
    CONSTRAINT fk_composer_film_film
      FOREIGN KEY (film_id) REFERENCES film (id)
);

Adesso analizzeremo due strategie per l’inserimento di dati. Le istruzioni che seguono dovranno essere ripetute per ogni riga del file.

Strategia classica

Come prima cosa occorre inserire il compositore, se non esiste già:

SELECT id FROM composer WHERE name='Abraham, Alexander William';
-- ==> (nessun risultato, quindi lo devo inserire)

INSERT INTO composer (name) VALUES ('Abraham, Alexander William') 
RETURNING (id);
-- ==> 22

Se abbiamo già un id per il compositore, dobbiamo aggiornare il numero di film nei quali ha lavorato:

UPDATE composer SET how_many_films=how_many_films+1 WHERE id=12;

Analogamente a quanto fatto per i compositori, inseriamo i film:

SELECT id FROM film WHERE name='The Play Front (2010)'
-- ==> (nessun risultato, quindi lo devo inserire)

INSERT INTO film (name) VALUES ('The Play Front (2010)') RETURNING (id);
-- ==> 454  

Se il film è già presente nella tabella, basterà incrementare il numero di compositori:

UPDATE film SET how_many_composers=how_many_composers+1 WHERE id=343; 

Una volta gestite le tabelle composer e film possiamo inserire i dati nella tabella composer_film:

INSERT INTO composer_film (composer_id, film_id) VALUES (22, 343);  

In totale eseguiremo:

• due query per ogni compositore (controllo e inserimento oppure controllo e aggiornamento);
• due query per ogni film (controllo e inserimento oppure controllo e aggiornamento);
• una query di inserimento per ogni coppia film/compositore.

NOTA: Per una maggiore efficacia a livello didattico, abbiamo semplificato la gestione delle transazioni negli esempi precedenti (che non escludono infatti possibili casi di concorrenza sugli stessi record).

Strategia UPSERT

Utilizzando l’istruzione UPSERT il primo passo si semplifica perché PostgreSQL controlla per noi l’esistenza del compositore:

INSERT INTO composer (name) VALUES ('Abraham, Alexander William')
ON CONFLICT (name) DO UPDATE SET how_many_films=composer.how_many_films+1 
RETURNING (id)
-- ==> 22 (funziona indipendentemente dall'esistenza del film)

Cosa analoga succede anche per l’inserimento del film:

INSERT INTO film (name) VALUES ('"Rwby" (2012) {Best Day Ever (#2.1)}')
ON CONFLICT (name) DO UPDATE SET how_many_composers=film.how_many_composers+1 
RETURNING (id)
-- ==> 343 (funziona indipendentemente dall'esistenza del film)

Non rimane che inserire la relazione fra le due tabelle:

INSERT INTO composer_film (composer_id, film_id) VALUES (22, 343);

In totale eseguiremo:

• una query di upsert per i compositori;
• una query di upsert per i film;
• una query di inserimento per ogni coppia film/compositore.

Con UPSERT risparmiamo le query di controllo, cioè una query per ogni film ed una query per ogni compositore. Visto che il file dal quale stiamo leggendo i dati contiene circa un milione di righe ci aspettiamo che le performance del programma che utilizza le UPSERT siano nettamente migliori.

Conclusione

Le UPSERT sono istruzioni molto comode per gli sviluppatori: permettono di esprimere in una singola interrogazione combinazioni di istruzioni molto frequenti. Nel nostro caso, con una unica istruzione abbiamo inserito un nuovo record e ne abbiamo aggiornato uno già presente.

Quello che abbiamo applicato è ovviamente un caso particolare di UPSERT e per la documentazione completa rimandiamo al manuale di PostgreSQL, che ne documenta tutte le funzionalità.