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.

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.