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.

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.