TL; DR: JSONB può semplificare notevolmente lo sviluppo dello schema del database senza compromettere le prestazioni delle query.
Introduzione
Facciamo un esempio classico, probabilmente uno dei più antichi casi d'uso nel mondo dei database relazionali: abbiamo un'entità e dobbiamo memorizzare alcune proprietà (attributi) di questa entità. Tuttavia, non tutti gli oggetti possono avere lo stesso insieme di proprietà e, inoltre, in futuro potrebbe essere necessario aggiungere ulteriori proprietà.
Il modo più semplice per risolvere questo problema è creare una colonna nella tabella del database per ogni valore di proprietà e semplicemente compilare quelle necessarie per un determinato oggetto. Ottimo! Problema risolto... fino a quando la vostra tabella non contiene milioni di record e non avete bisogno di aggiungere una nuova registrazione.
Consideriamo il pattern EAV (), che si incontra abbastanza spesso. Una tabella contiene entità (record), un'altra tabella contiene i nomi delle proprietà (attributi), e una terza tabella collega le entità ai loro attributi e contiene il valore di questi attributi per l'entità attuale. Questo ti consente di avere set di proprietà diversi per oggetti differenti e di aggiungere proprietà "al volo", senza modificare la struttura del database.
Tuttavia, non scriverei questa nota se non ci fossero svantaggi nell'approccio EVA. Ad esempio, per ottenere una o più entità che hanno un attributo, sono necessari 2 join nella query: il primo è un join con la tabella degli attributi, il secondo è un join con la tabella dei valori. Se un'entità ha 2 attributi, servono già 4 join! Inoltre, tutti gli attributi sono generalmente 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 risorse.
Nonostante questi evidenti svantaggi, l'EAV è già da tempo utilizzato per affrontare questo tipo di problemi. Questi erano difetti inevitabili e non c'era semplicemente una migliore alternativa.
Ma poi in PostgreSQL è emersa una nuova "tecnologia"...
A partire da PostgreSQL 9.4, è stato aggiunto il tipo di dato JSONB per conservare dati JSON binari. Sebbene la memorizzazione dei JSON in questo formato richieda solitamente un po' più di spazio e tempo rispetto al JSON in formato testo semplice, l'esecuzione delle operazioni su di esso avviene molto più rapidamente. Inoltre, JSONB supporta l'indicizzazione, il che rende le query su di esso ancora più veloci.
Il tipo di dato JSONB ci consente di sostituire il pesante schema EAV semplicemente aggiungendo una sola colonna JSONB alla nostra tabella delle entità, semplificando notevolmente il design del database. Tuttavia, molti sostengono che ciò debba comportare una riduzione delle prestazioni… È per questo motivo che è nato questo articolo.
Impostazione del database di test
Per questo confronto ho creato un database su una nuova installazione di PostgreSQL 9.5 su un'installazione da 80 dollari Ubuntu 14.04. Dopo aver configurato alcuni parametri in postgresql.conf, ho eseguito uno script utilizzando psql. Per rappresentare i dati in formato EAV sono state create le seguenti tabelle:
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 una tabella in cui verranno memorizzati gli stessi dati, ma con gli attributi in una colonna di tipo JSONB – properties.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Sembra molto più semplice, non è vero? Poi sono state aggiunte alle tabelle delle entità (entity & entity_jsonb) 10 milioni di record, e di conseguenza, sono stati riempiti con gli stessi dati le tabelle dove si utilizza lo schema EAV e l'approccio con la colonna JSONB – entity_jsonb.properties. Così abbiamo ottenuto diversi tipi di dati tra l'insieme di proprietà. Esempio di dati:
{
id: 1
name: "Entity1"
description: "Entità di test n. 1"
properties: {
color: "rosso"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgio"
}
}Quindi ora abbiamo dati identici per le due varianti. Iniziamo a confrontare le implementazioni in azione!
Semplificazione del design
È stato già detto in precedenza che il design del database è stato notevolmente semplificato: una tabella, grazie all'uso della colonna JSONB per le proprietà, invece di utilizzare tre tabelle per EAV. Ma come si riflette questo nelle query? L'aggiornamento di una proprietà dell'entità si presenta come segue:
-- EAV
UPDATE entity_attribute_value
SET value = 'blu'
WHERE entity_attribute_id = 1
AND entity_id = 120;
-- JSONB
UPDATE entity_jsonb
SET properties = jsonb_set(properties, '{"color"}', '"blu"')
WHERE id = 120;
Come vediamo, l'ultima query non sembra più semplice. Per aggiornare il valore di una proprietà in un oggetto JSONB, dobbiamo utilizzare la funzione , e dobbiamo passare il nostro nuovo valore come oggetto JSONB. Tuttavia, non dobbiamo conoscere alcun identificatore in anticipo. Guardando l'esempio con EAV, dobbiamo conoscere sia l'entity_id che l'entity_attribute_id per eseguire l'aggiornamento. Se vuoi aggiornare una proprietà nella colonna JSONB basandoti sul nome dell'oggetto, tutto questo si fa in 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 = 'blu';
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties ->> 'color' = 'blu';
Credo che possiamo concordare sul fatto che il secondo è più corto (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 ottenere lo stesso risultato nel modello JSONB utilizzando l'operatore @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blu"}';
Questo è un po' più complicato: stiamo verificando se l'oggetto JSON nella colonna delle proprietà contiene l'oggetto che si trova a destra dell'operatore @>. Meno leggibile, più performante (vedi oltre).
Semplifichiamo ulteriormente l'uso di JSONB quando hai bisogno di selezionare più proprietà contemporaneamente. Qui è dove il metodo JSONB si adatta veramente: semplicemente selezioniamo le proprietà come colonne aggiuntive nel nostro set di risultati senza necessità di join:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Con EAV avrai bisogno di 2 join per ogni proprietà che desideri interrogare. A mio parere, le query sopra mostrano un grande semplificazione nel design del database. È possibile vedere ulteriori esempi su come scrivere query in JSONB anche nel post.
Ora è il momento di parlare delle prestazioni.
Prestazioni
Per confrontare le prestazioni, ho utilizzato nelle query, per misurare il tempo di esecuzione. Ogni query è stata eseguita almeno tre volte, poiché la prima volta il pianificatore delle query richiede più tempo. Inizialmente ho eseguito le query senza alcun indice. Ovviamente, ciò ha avvantaggiato JSONB, poiché i join necessari per EAV non potevano utilizzare indici (i campi delle chiavi esterne non erano indicizzati). Dopo, ho creato un indice per due colonne delle chiavi esterne nella tabella dei valori EAV, oltre a un indice per la colonna JSONB.
Gli aggiornamenti dei dati hanno mostrato i seguenti risultati sui tempi (in ms). Si prega di notare che la scala è logaritmica:
Si osserva che JSONB è molto più (> 50000 volte) veloce di EAV se non si utilizzano indici, a causa del motivo sopra indicato. Quando indicizziamo le colonne con le chiavi primarie, la differenza quasi scompare, ma JSONB è ancora 1,3 volte più veloce di EAV. Si noti che l'indice nella colonna JSONB qui non ha alcun impatto, poiché non utilizziamo la colonna delle proprietà nei criteri di valutazione.
Per la selezione dei dati in base al valore della 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 – comunque risulta più veloce di JSONB. Tuttavia, ho notato che il tempo per le query JSONB era lo stesso, il che mi ha portato a pensare che l'indice GIN non si attivasse. Presumibilmente, quando si utilizza un indice GIN per una colonna con proprietà popolate, questo funziona solo quando si usa l'operatore di inclusione @>. Ho utilizzato questo in un nuovo test, il che ha avuto un enorme impatto sul tempo: 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 con entrambi gli approcci. In psql possiamo mostrari la dimensione di tutte le tabelle e degli indici con il comando dti+
Per l'approccio EAV, le dimensioni delle tabelle ammontano a circa 3068 MB, e gli indici fino a 3427 MB, il che in totale equivale a 6,43 GB. Utilizzando l'approccio con JSONB si utilizzano 1817 MB per la tabella e 318 MB per gli indici, per un totale di 2,08 GB. Risultando in tre volte di meno! Questo fatto mi ha sorpreso un po', poiché memorizziamo i nomi delle proprietà in ogni oggetto JSONB.
Tuttavia, i numeri parlano chiaro: in EAV conserviamo 2 chiavi esterne intere per il valore dell'attributo, il che si traduce in 8 byte di dati aggiuntivi. Inoltre, in EAV tutti i valori delle proprietà sono memorizzati come testo, mentre JSONB utilizza valori numerici e booleani dove possibile, risultando in una dimensione complessiva inferiore.
Conclusioni
In generale, penso che conservare le proprietà delle entità nel formato JSONB possa semplificare notevolmente la progettazione e la manutenzione del vostro database. Se eseguite molte query, tutto ciò che è memorizzato in una sola tabella con l'entità funzionerà davvero in modo più efficiente. Il fatto che ciò semplifichi l'interazione tra i dati è già un vantaggio, ma anche il database risultante è tre volte più piccolo in dimensione.
Inoltre, dai test svolti, si può concludere che le perdite di performance sono molto trascurabili. In alcuni casi, JSONB funziona anche 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 significativo aumento delle proprietà dei dati esistenti, ecc.), quindi, se avete suggerimenti su come migliorarlo, non esitate a lasciarli nei commenti!
Fonte: habr.com
