Prestazioni del tipo JSONB in PostgreSQL 9.4

Potete consultare l'articolo originale su

http://blog.2ndquadrant.it/prestazioni-del-tipo-jsonb-postgresql-9-4/

La versione 9.4 di PostgreSQL introduce il tipo di dato JSONB[1], una
rappresentazione specializzata dei dati JSON, in grado di rendere
PostgreSQL competitivo nel gestire quella che in questo momento è la
"lingua franca" per lo scambio di dati attraverso servizi web. È
interessante fare alcuni test per verificarne le prestazioni
effettive.
________________________________________________________________

Base di dati di test

Utilizziamo come base di dati le recensioni degli utenti di Amazon
del 1998 in formato JSON. Il file
customer_reviews_nested_1998.json.gz[2] è scaricabile dal sito di
Citus Data.
Il file, una volta decompresso, occupa 209 MB e contiene circa 600k
record in formato JSON, con una struttura simile a quella seguente:

{
"customer_id": "ATVPDKIKX0DER",
"product": {
"category": "Arts & Photography",
"group": "Book",
"id": "1854103040",
"sales_rank": 72019,
"similar_ids": [
"1854102664",
"0893815381",
"0893816493",
"3037664959",
"089381296X"
],
"subcategory": "Art",
"title": "The Age of Innocence"
},
"review": {
"date": "1995-08-10",
"helpful_votes": 5,
"rating": 5,
"votes": 12
}
}
________________________________________________________________

Dimensioni

I dati possono essere caricati in un database PostgreSQL usando il
tipo di dati JSONB con i seguenti comandi:

CREATE TABLE reviews(review jsonb);
\copy reviews FROM 'customer_reviews_nested_1998.json'
VACUUM ANALYZE reviews;

La tabella risultante occuperà circa 268 MB, con un costo aggiuntivo
di memorizzazione su disco di circa il 28%. Se proviamo a caricare
gli stessi dati usando il tipo JSON, che li memorizza come testo, il
risultato sarà una tabella di 233 MB, con un incremento di spazio di
circa l'11%. Il motivo di questa differenza è che le strutture
interne di JSONB, che servono ad accedere ai dati senza analizzare
ogni volta tutto il documento, hanno un costo in termini di spazio
occupato.
________________________________________________________________

Accesso ai dati

Una volta memorizzati i dati nel database, per potervi accedere in
maniera efficiente è necessario creare un indice. Prima della
versione 9.4 di PostgreSQL, l'unica opzione per indicizzare il
contenuto di un campo contente JSON era quella di utilizzare un
indice B-tree su un'espressione di ricerca specifica. Per esempio, se
vogliamo effettuare ricerche per categoria di prodotto utilizzeremo:

CREATE INDEX on reviews ((review #>> '{product,category}'));

L'indice appena creato occupa 21 MB, cioè circa il 10% dei dati
originali, e permetterà di eseguire query che abbiano all'interno
della clausola WHERE l'espressione esatta "review #>>
{product,category}", come ad esempio:

SELECT
review #>> '{product,title}' AS title,
avg((review #>> '{review,rating}')::int)
FROM reviews
WHERE review #>> '{product,category}' = 'Fitness & Yoga'
GROUP BY 1 ORDER BY 2;
title | avg
---------------------------------------------------+--------------------
Kathy Smith - New Yoga Challenge | 1.6666666666666667
Pumping Iron 2 | 2.0000000000000000
Kathy Smith - New Yoga Basics | 3.0000000000000000
Men Are from Mars, Women Are from Venus | 4.0000000000000000
Kathy Smith - Functionally Fit - Peak Fat Burning | 4.5000000000000000
Kathy Smith - Pregnancy Workout | 5.0000000000000000
(6 rows)

La query impiega circa 0.180 ms per essere eseguita sulla macchina di
test, ma l'indice che è stato creato è altamente specifico e non è
usabile per ricerche diverse.
A partire dalla versione 9.4, il tipo di dati JSONB supporta
l'utilizzo di indici inversi (GIN, General inverted Indexes), che
permettono di indicizzare le componenti di un oggetto complesso.
Andiamo quindi a creare un indice GIN sulla nostra tabella reviews
con in seguente comando:

CREATE INDEX on reviews USING GIN (review);

L'indice risultante occupa 64 MB su disco, che è circa il 30% della
dimensione della tabella originale. Tale indice può essere utilizzato
per velocizzare i seguenti operatori:
* JSON @> JSON è sottoinsieme
* JSON ? TEXT contiene un valore
* JSON ?& TEXT[] contiene tutti i valori
* JSON ?| TEXT[] contiene almeno un valore

La query precedente deve quindi essere riscritta usando l'operatore
@> per cercare le righe che contengono '{"product": {"category":
"Fitness & Yoga"}}':

SELECT
review #>> '{product,title}' AS title,
avg((review #>> '{review,rating}')::int)
FROM reviews
WHERE review @> '{"product": {"category": "Fitness & Yoga"}}'
GROUP BY 1 ORDER BY 2;

La query impiega circa 1.100 ms per essere eseguita sulla macchina di
test e l'indice che è stato creato è flessibile ed è possibile usarlo
per qualsiasi ricerca all'interno dei dati JSON.

In realtà spesso la sola operazione utilizzata nelle applicazioni è
la ricerca per sottoinsieme, in tal caso è possibile usare un indice
GIN diverso, che supporta solo l'operazione @> ed è quindi
considerevolmente più piccolo. La sintassi per creare questo tipo di
indice "ottimizzato" è la seguente:

CREATE INDEX on reviews USING GIN (review jsonb_path_ops);

L'indice risultante occupa solamente 46 MB cioè solo il 22% della
dimensione dei dati originale e grazie a questa sua dimensione
ridotta viene usato da PostgreSQL con maggiore efficienza,
permettendo di eseguire la query precedente in soli 0.167 ms, con un
incremento di prestazioni del 650% rispetto all'indice GIN originale
e del 8% rispetto all'indice B-tree specifico usato inizialmente,
tutto questo senza perdere di generalità per quanto riguarda le
possibili operazioni di ricerca.
________________________________________________________________

Conclusioni

Con l'introduzione del tipo JSONB e gli indici GIN, costruiti con gli
operatori jsonb_path_ops, PostgreSQL unisce l'elasticità del formato
JSON a una velocità di accesso ai dati strabiliante.
Oggi è quindi possibile memorizzare e elaborare dati in formato JSON
con elevate prestazioni, godendo allo stesso tempo della robustezza e
della flessibilità a cui PostgreSQL ci ha abituato negli anni.

Riferimenti

1. http://blog.2ndquadrant.it/nosql-con-postgresql-9-4-e-jsonb/
2. http://examples.citusdata.com/customer_reviews_nested_1998.json.gz

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it