Message-ID: <54D9E370.2080502@2ndquadrant.it>
Date: Tue, 10 Feb 2015 11:54:40 +0100
From: Marco Nenciarini <marco.nenciarini@2ndquadrant.it>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: pgsql-it-generale@postgresql.org
Subject: Prestazioni del tipo JSONB in PostgreSQL 9.4
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="f2jMbmoGpStqmH5D6K8BXcaJjCwBn1AoC"
Precedence: bulk
Sender: pgsql-it-generale-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--f2jMbmoGpStqmH5D6K8BXcaJjCwBn1AoC
Content-Type: text/plain; charset=iso-8859-15
Content-Transfer-Encoding: quoted-printable

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 =E8 la=

   "lingua franca" per lo scambio di dati attraverso servizi web. =C8
   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] =E8 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=E0 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=E0 una tabella di 233 MB, con un incremento di spazio di=

   circa l'11%. Il motivo di questa differenza =E8 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 =E8 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=E8 circa il 10% dei dati
   originali, e permetter=E0 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}' =3D '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 =E8 stato creato =E8 altamente specifico e non =E8=

   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 =E8 circa il 30% della
   dimensione della tabella originale. Tale indice pu=F2 essere utilizzat=
o
   per velocizzare i seguenti operatori:
     * JSON @> JSON =E8 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 =E8 stato creato =E8 flessibile ed =E8 possibile u=
sarlo
   per qualsiasi ricerca all'interno dei dati JSON.

   In realt=E0 spesso la sola operazione utilizzata nelle applicazioni =E8=

   la ricerca per sottoinsieme, in tal caso =E8 possibile usare un indice=

   GIN diverso, che supporta solo l'operazione @> ed =E8 quindi
   considerevolmente pi=F9 piccolo. La sintassi per creare questo tipo di=

   indice "ottimizzato" =E8 la seguente:

   CREATE INDEX on reviews USING GIN (review jsonb_path_ops);

   L'indice risultante occupa solamente 46 MB cio=E8 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=E0 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=E0 del formato=

   JSON a una velocit=E0 di accesso ai dati strabiliante.
   Oggi =E8 quindi possibile memorizzare e elaborare dati in formato JSON=

   con elevate prestazioni, godendo allo stesso tempo della robustezza e
   della flessibilit=E0 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


--=20
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it


--f2jMbmoGpStqmH5D6K8BXcaJjCwBn1AoC
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIcBAEBCAAGBQJU2eNyAAoJEFifA/AbpVA4CRIQAJhqpkIGTt6hCyhi20v+APDQ
LfGjqhBhUJjerRYKeUnP0lIKyvzw+4ad+r2zYH6SEmUloQNh8iumNJZ7IHEucn85
vwziJyYuXHpXomhhtsOO6sM/Ju7PQEYsADGCFLDHAQDvRWbZY4YC3HBzfq269MDU
vVgmwdkz657DzgJYV6h3H3oEaa5DbV2K/u6fo5dBhXEFx36nJuAHLV0lmEJcVvAB
7pwxC0drRzudt8b8BLO5AvvXsRZGJlB3YuO+WD0kfJ17KEZYN618/gO6q68DUTLs
js+NvUwJA3vxpi+tuDjEWNPlUQHPzODCMYMI3OFfv9RkyjpGZiOXib4lHAPeNhew
oAyg+s961iGvO3wAxD8IJ2vd+yEARy4VMz1CgBEApW5W2wrKfCEyJYokdLtl1g1B
gNaavhzkDtBEf9nk4Uyj2ZpDlqtKrG2RDuAZIIsCqehT07pFD/4yXC8Lom50bCKh
xR2DMIXplTl8B/+q6po0C4jjdxFLBNjP+h12lwZTkblKuPvFs00jIfHvqMG7X0Y9
njmIQGDadnU2SSHFfebEPrewzLXXcwbsb7Ujndbk6llm0etEo6Q/NCwJ/l29UnGl
16FtDQ5ybHOOsoq6mmYlw3DYcY28E7Gmv+A3mrUJzSeep023Oa/+GMV5RwoypjBI
pRiwxdmN250YYJaMenzE
=Na0D
-----END PGP SIGNATURE-----

--f2jMbmoGpStqmH5D6K8BXcaJjCwBn1AoC--