TL; DR: JSONB può semplificare notevolmente lo sviluppo dello schema del database senza sacrificare le prestazioni delle query.
Introduzione
Diamo un classico esempio di uno dei casi d'uso probabilmente più antichi al mondo di un database relazionale (database): abbiamo un'entità e dobbiamo salvare alcune proprietà (attributi) di questa entità. Ma non tutte le istanze potrebbero avere lo stesso set di proprietà e altre proprietà potrebbero essere aggiunte in futuro.
Il modo più semplice per risolvere questo problema è creare una colonna nella tabella del database per ciascun valore di proprietà e compilare semplicemente quelli necessari per un'istanza di entità specifica. Grande! Problema risolto... finché la tabella non contiene milioni di record e non è necessario aggiungere un nuovo record.
Considera il modello EAV (), si verifica abbastanza spesso. Una tabella contiene entità (record), un'altra tabella contiene nomi di proprietà (attributi) e una terza tabella associa le entità ai relativi attributi e contiene il valore di tali attributi per l'entità corrente. Ciò ti dà la possibilità di avere diversi set di proprietà per oggetti diversi e anche di aggiungere proprietà al volo senza modificare la struttura del database.
Tuttavia, non scriverei questo post se non ci fossero alcuni aspetti negativi nell'approccio EVA. Quindi, ad esempio, per ottenere una o più entità che abbiano 1 attributo ciascuna, nella query sono necessarie 2 join: la prima è una join con la tabella degli attributi, la seconda è una join con la tabella dei valori. Se un'entità ha 2 attributi, sono necessari 4 join! Inoltre, tutti gli attributi vengono generalmente archiviati come stringhe, il che si traduce in un casting del tipo sia per il risultato che per la clausola WHERE. Se scrivi molte query, questo è piuttosto dispendioso in termini di utilizzo delle risorse.
Nonostante queste evidenti carenze, l’EAV è stato utilizzato a lungo per risolvere questo tipo di problemi. Queste erano carenze inevitabili e semplicemente non esisteva un’alternativa migliore.
Ma poi in PostgreSQL è apparsa una nuova “tecnologia”...
A partire da PostgreSQL 9.4, il tipo di dati JSONB è stato aggiunto per archiviare i dati binari JSON. Sebbene l'archiviazione di JSON in questo formato richieda in genere un po' più di spazio e tempo rispetto al testo semplice JSON, l'esecuzione di operazioni su di esso è molto più veloce. JSONB supporta anche l'indicizzazione, il che rende le query ancora più veloci.
Il tipo di dati JSONB ci consente di sostituire l'ingombrante modello EAV aggiungendo solo una colonna JSONB alla nostra tabella delle entità, semplificando notevolmente la progettazione del database. Ma molti sostengono che questo dovrebbe essere accompagnato da una diminuzione della produttività... Ecco perché ho scritto questo articolo.
Creazione di un database di test
Per questo confronto, ho creato il database su una nuova installazione di PostgreSQL 9.5 sulla build da $ 80 Ubuntu 14.04. Dopo aver impostato alcuni parametri in postgresql.conf ho eseguito script utilizzando psql. Per presentare 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 archiviati gli stessi dati, ma con attributi in una colonna di tipo JSONB – proprietà.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Sembra molto più semplice, vero? Quindi è stato aggiunto alle tabelle delle entità (entità & entità_jsonb) 10 milioni di record e, di conseguenza, la tabella è stata riempita con gli stessi dati utilizzando il modello EAV e l'approccio con una colonna JSONB - entità_jsonb.properties. Pertanto, abbiamo ricevuto diversi tipi di dati diversi nell'intero set di proprietà. Dati di esempio:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Quindi ora abbiamo gli stessi dati per entrambe le opzioni. Iniziamo a confrontare le implementazioni sul lavoro!
Semplifica il tuo design
In precedenza è stato affermato che la progettazione del database è stata notevolmente semplificata: una tabella, utilizzando una colonna JSONB per le proprietà, invece di utilizzare tre tabelle per EAV. Ma come si riflette questo nelle richieste? L'aggiornamento di una proprietà dell'entità si presenta così:
-- 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 puoi vedere, l'ultima richiesta non sembra più semplice. Per aggiornare il valore di una proprietà in un oggetto JSONB dobbiamo utilizzare la funzione e dovrebbe passare il nostro nuovo valore come oggetto JSONB. Tuttavia, non abbiamo bisogno di conoscere alcun identificatore in anticipo. Guardando l'esempio EAV, dobbiamo conoscere sia entità_id che entità_attribute_id per eseguire l'aggiornamento. Se desideri aggiornare una proprietà in una colonna JSONB in base al nome dell'oggetto, tutto viene eseguito in una semplice riga.
Ora selezioniamo 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';
Penso che possiamo essere d'accordo sul fatto che il secondo è più breve (no join!), e quindi più leggibile. JSONB vince qui! Usiamo l'operatore JSON ->> per ottenere il colore come valore di testo da un 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": "blue"}';
Questo è un po' più complicato: controlliamo per vedere se l'oggetto JSON nella sua colonna delle proprietà contiene un oggetto che si trova a destra dell'operatore @>. Meno leggibile, più produttivo (vedi sotto).
Rendiamo l'utilizzo di JSONB ancora più semplice quando è necessario selezionare più proprietà contemporaneamente. È qui che entra realmente in gioco l'approccio JSONB: selezioniamo semplicemente le proprietà come colonne aggiuntive nel nostro set di risultati senza la 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 di cui sopra mostrano una grande semplificazione nella progettazione del database. Vedi altri esempi su come scrivere query JSONB, anche in inviare.
Ora è il momento di parlare di prestazioni.
Производительность
Per confrontare le prestazioni ho usato nelle query, per calcolare il tempo di esecuzione. Ogni query è stata eseguita almeno tre volte perché il pianificatore di query impiega più tempo la prima volta. Per prima cosa ho eseguito le query senza indici. Questo era ovviamente un vantaggio di JSONB, poiché i join richiesti per EAV non potevano utilizzare indici (i campi chiave esterni non erano indicizzati). Successivamente ho creato un indice sulle 2 colonne di chiave esterna della tabella dei valori EAV, nonché un indice per una colonna JSONB.
L'aggiornamento dei dati ha mostrato i seguenti risultati in termini di tempo (in ms). Tieni presente che la scala è logaritmica:
Vediamo che JSONB è molto (> 50000-x) più veloce di EAV se non si utilizzano indici, per il motivo sopra indicato. Quando indicizziamo colonne con chiavi primarie, la differenza quasi scompare, ma JSONB è ancora 1,3 volte più veloce di EAV. Tieni presente che l'indice sulla colonna JSONB non ha alcun effetto in questo caso poiché non stiamo utilizzando la colonna delle proprietà nei criteri di valutazione.
Selezionando i dati in base al valore della proprietà, otteniamo i seguenti risultati (scala normale):
Puoi notare che JSONB funziona ancora più velocemente di EAV senza indici, ma quando EAV con indici funziona ancora più velocemente di JSONB. Ma poi ho visto che i tempi per le query JSONB erano gli stessi, questo mi ha portato al fatto che gli indici GIN non funzionano. Apparentemente quando si utilizza un indice GIN su una colonna con proprietà popolate, ha effetto solo quando si utilizza l'operatore di inclusione @>. L'ho usato in un nuovo test e ha avuto un impatto enorme sul tempo: solo 0,153 ms! Questo è 15000 volte più veloce dell'EAV e 25000 volte più veloce dell'operatore ->>.
Penso che sia stato abbastanza veloce!
Dimensioni della tabella del database
Confrontiamo le dimensioni della tabella per entrambi gli approcci. In psql possiamo mostrare la dimensione di tutte le tabelle e gli indici usando il comando dti+
Per l'approccio EAV, le dimensioni delle tabelle sono circa 3068 MB e gli indici fino a 3427 MB per un totale di 6,43 GB. L'approccio JSONB utilizza 1817 MB per la tabella e 318 MB per gli indici, ovvero 2,08 GB. Risulta 3 volte meno! Questo fatto mi ha sorpreso un po' perché memorizziamo i nomi delle proprietà in ogni oggetto JSONB.
Tuttavia, i numeri parlano da soli: in EAV memorizziamo 2 chiavi esterne intere per valore di attributo, risultando in 8 byte di dati aggiuntivi. Inoltre, EAV memorizza tutti i valori delle proprietà come testo, mentre JSONB utilizzerà internamente valori numerici e booleani ove possibile, con un conseguente ingombro ridotto.
Risultati di
Nel complesso, penso che il salvataggio delle proprietà dell'entità nel formato JSONB possa rendere molto più semplice la progettazione e la manutenzione del database. Se stai eseguendo molte query, mantenere tutto nella stessa tabella dell'entità funzionerà in modo più efficiente. E il fatto che ciò semplifichi l'interazione tra i dati è già un vantaggio, ma il database risultante ha un volume 3 volte inferiore.
Inoltre, sulla base dei test effettuati, possiamo concludere che le perdite di prestazioni sono molto insignificanti. In alcuni casi, JSONB è addirittura più veloce di EAV, rendendolo ancora migliore. Tuttavia, questo benchmark 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 migliorarli , non esitate a lasciare nei commenti!
Fonte: habr.com