TL; DR: JSONB può semplificare notevolmente lo sviluppo dello schema del database senza compromettere le prestazioni delle query.
Introduzione
Prendiamo un esempio classico, forse uno dei più antichi casi d'uso nel mondo dei database relazionali (database): abbiamo un'entità e dobbiamo salvare determinate proprietà (attributi) di quell'entità. Ma non tutti gli esemplari possono avere lo stesso insieme di proprietà; inoltre, in futuro, potrebbero essere aggiunte ulteriori proprietà.
Il modo più semplice per risolvere questo problema è creare una colonna nella tabella del database per ogni valore di proprietà e semplicemente riempire quelle necessarie per un determinato esemplare dell'entità. Ottimo! Problema risolto... fino a quando la tua tabella non conterrà milioni di registrazioni e non avrai bisogno di aggiungere un nuovo record.
Consideriamo il pattern EAV (), viene abbastanza frequentemente. Una tabella contiene entità (record), un'altra tabella contiene i nomi delle proprietà (attributi), e la terza tabella collega le entità ai loro attributi, contenendo il valore di questi attributi per l'entità attuale. Questo ti consente di avere diversi insiemi di proprietà per diversi oggetti e di aggiungere proprietà “al volo”, senza modificare le strutture del database.
Tuttavia, non scriverei questa nota se non ci fossero difetti nell'approccio basato su EVA. Ad esempio, per ottenere una o più entità che hanno ciascuna 1 attributo, sono necessari 2 join nella query: il primo è unione con la tabella degli attributi, il secondo è unione con la tabella dei valori. Se un'entità ha 2 attributi, servono già 4 join! Inoltre, tutti gli attributi di solito sono memorizzati come stringhe, il che porta a conversioni di tipo, sia per il risultato che per la condizione WHERE. Se scrivi molte query, questo è piuttosto dispendioso in termini di utilizzo delle risorse.
Nonostante questi evidenti svantaggi, l'EAV è stato a lungo utilizzato per risolvere questo tipo di problemi. Questi difetti erano inevitabili e semplicemente non c'era un'alternativa migliore.
Ma poi in PostgreSQL è arrivata una nuova 'tecnologia'…
A partire da PostgreSQL 9.4, è stato introdotto il tipo di dati JSONB per memorizzare dati JSON in formato binario. Sebbene la memorizzazione di JSON in questo formato richieda in genere un po' più di spazio e tempo rispetto al semplice JSON testuale, le operazioni su di esso sono molto più veloci. Inoltre, JSONB supporta l'indicizzazione, rendendo ancora più rapide le query.
Il tipo di dati JSONB ci consente di sostituire il pesante pattern EAV aggiungendo semplicemente una colonna JSONB nella nostra tabella delle entità, semplificando notevolmente il design del database. Ma molti sostengono che ciò debba comportare una diminuzione delle prestazioni… È per questo motivo che è emerso questo articolo.
Impostazione di un database di test
Per questa comparazione ho creato un database su una nuova installazione di PostgreSQL 9.5 su un server da 80 dollari. Ubuntu 14.04. Dopo aver configurato alcuni parametri in postgresql.conf, ho avviato script utilizzando psql. Sono state create le seguenti tabelle per rappresentare i dati in forma EAV:
CREATE TABLE entity (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT
);
CREATE TABLE entity_attribute (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE entity_attribute_value (
id SERIAL PRIMARY KEY,
entity_id INT REFERENCES entity(id),
entity_attribute_id INT REFERENCES entity_attribute(id),
value TEXT
);
Di seguito è riportata la tabella in cui verranno memorizzati gli stessi dati, ma con gli attributi nella colonna di tipo JSONB – properties.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Sembra molto più semplice, vero? Quindi sono stati aggiunti 10 milioni di record nelle tabelle delle entità (entity & entity_jsonb) e di conseguenza, sono stati riempiti con dati identici le tabelle in cui viene utilizzato il pattern EAV e l'approccio con la colonna JSONB – entity_jsonb.properties. In questo modo, abbiamo ottenuto diversi tipi di dati all'interno dell'intero set di proprietà. Ecco un esempio di dati:
{
id: 1
name: "Entity1"
description: "Test entity n. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Quindi, ora abbiamo dati identici per entrambe le opzioni. Iniziamo a confrontare le implementazioni in azione!
Semplificazione del design
In precedenza si è già detto che il design del database è stato notevolmente semplificato: una tabella, grazie all'uso di una colonna JSONB per le proprietà, anziché tre tabelle per EAV. Ma come si riflette questo nelle query?
-- EAV
UPDATE entity_attribute_value
SET value = 'blue'
WHERE entity_attribute_id = 1
AND entity_id = 120;
-- JSONB
UPDATE entity_jsonb
SET properties = jsonb_set(properties, '{"color"}', '"blue"')
WHERE id = 120;
Come possiamo vedere, l'ultima query non appare più semplice. Per aggiornare il valore di una proprietà nell'oggetto JSONB, dobbiamo usare la funzione , e dobbiamo passare il nostro nuovo valore come oggetto JSONB. Tuttavia, non abbiamo bisogno di conoscere alcun identificatore in anticipo. Guardando l'esempio con EAV, dobbiamo sapere sia entity_id che entity_attribute_id per eseguire l'aggiornamento. Se desideriamo aggiornare una proprietà nella colonna JSONB in base al nome dell'oggetto, tutto questo può essere fatto con una semplice riga.
Ora scegliamo l'entità che abbiamo appena aggiornato in base al suo nuovo colore:
-- EAV
SELECT e.name
FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties ->> 'color' = 'blue';
Ritengo che possiamo concordare sul fatto che il secondo è più breve (senza join!), e quindi più leggibile. Qui vince JSONB! Utilizziamo l'operatore JSON ->> per ottenere il colore come valore di testo dall'oggetto JSONB. Esiste anche un secondo modo per raggiungere lo stesso risultato nel modello JSONB utilizzando l'operatore @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
È un po' più complicato: stiamo controllando se l'oggetto JSON nella colonna delle proprietà contiene l'oggetto a destra dell'operatore @>. Meno leggibile, più performante (vedi oltre).
Semplifichiamo ulteriormente l'uso di JSONB quando è necessario selezionare più proprietà contemporaneamente. Qui è dove l'approccio JSONB si adatta meglio: selezioniamo semplicemente le proprietà come colonne aggiuntive nel nostro set di risultati senza dover effettuare join:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Con EAV ti serviranno 2 unioni per ogni proprietà che desideri interrogare. A mio avviso, le query sopra riportate mostrano una notevole semplificazione nel design del database. Puoi trovare ulteriori esempi su come scrivere query per JSONB anche nel post.
È ora di parlare delle prestazioni.
Prestazioni
Per confrontare le prestazioni, ho utilizzato negli interrogativi, per misurare il tempo di esecuzione. Ogni query è stata eseguita almeno tre volte, poiché alla prima il pianificatore di query richiede più tempo. Innanzitutto ho eseguito le query senza alcun indice. Ovviamente, questo ha avvantaggiato JSONB, poiché le unioni necessarie per EAV non potevano utilizzare indici (i campi delle chiavi esterne non erano indicizzati). Successivamente, ho creato un indice per 2 colonne delle chiavi esterne nella tabella dei valori EAV, e un indice per la colonna JSONB.
Gli aggiornamenti dei dati hanno mostrato i seguenti risultati in termini di tempo (in ms). Si noti che la scala è logaritmica:
Vediamo che JSONB è molto più veloce (> 50000 volte) rispetto a EAV, se non si utilizzano gli indici, per il motivo sopra indicato. Quando indicizziamo le colonne con le chiavi primarie, la differenza si riduce quasi a zero, ma JSONB è comunque 1,3 volte più veloce di EAV. Si noti che l'indice nella colonna JSONB non ha alcun effetto qui, poiché non stiamo utilizzando la colonna delle proprietà nei criteri di valutazione.
Per la selezione dei dati basata sul valore delle proprietà otteniamo i seguenti risultati (scala normale):
Si può notare che JSONB funziona di nuovo più velocemente di EAV senza indici, ma quando EAV ha indici, risulta comunque più veloce di JSONB. Tuttavia, ho notato che i tempi delle query JSONB erano identici, il che mi ha portato a capire che gli indici GIN non venivano attivati. Evidentemente, quando si utilizza un indice GIN per una colonna con proprietà popolate, esso funziona solo con l'operatore di inclusione @>. Ho utilizzato questo in un nuovo test, il che ha avuto un enorme impatto sui tempi: solo 0,153 ms! Questo è 15000 volte più veloce di EAV e 25000 volte più veloce dell'operatore ->>.
Penso che sia stato abbastanza veloce!
Dimensione delle tabelle del database
Confrontiamo le dimensioni delle tabelle utilizzando entrambi gli approcci. In psql possiamo mostrare la dimensione di tutte le tabelle e degli indici con il comando dti+
Per l'approccio EAV, le dimensioni delle tabelle sono di circa 3068 MB, mentre gli indici arrivano fino a 3427 MB, per un totale di 6,43 GB. Utilizzando l'approccio JSONB, si utilizzano 1817 MB per la tabella e 318 MB per gli indici, per un totale di 2,08 GB. Risultano quasi tre volte inferiori! Questo dato mi ha sorpreso un po', perché memorizziamo i nomi delle proprietà in ogni oggetto JSONB.
Tuttavia, i numeri parlano da soli: nell'EAV memorizziamo 2 chiavi esterne intere per il valore dell'attributo, con un costo aggiuntivo di 8 byte. Inoltre, in EAV tutti i valori delle proprietà sono memorizzati come testo, mentre JSONB utilizzerà valori numerici e logici quando possibile, risultando in un volume complessivo inferiore.
Risultati
In generale, credo che mantenere le proprietà delle entità nel formato JSONB possa semplificare notevolmente la progettazione e la gestione del database. Se esegui molte query, tutto ciò che è memorizzato in una singola tabella con l'entità funzionerà davvero in modo più efficiente. E il fatto che questo semplifichi le interazioni tra i dati è già un vantaggio, ma anche il risultato finale del database è tre volte più piccolo.
Inoltre, dai test effettuati, si può concludere che le perdite di performance sono davvero trascurabili. In alcuni casi, JSONB addirittura funziona più velocemente rispetto a EAV, il che lo rende ancora migliore. Tuttavia, questo test di riferimento, ovviamente, non copre tutti gli aspetti (ad esempio, entità con un numero molto elevato di proprietà, un aumento significativo del numero di proprietà dei dati esistenti,...), quindi, se hai suggerimenti su come migliorarlo, non esitare a lasciare commenti!
Fonte: habr.com
