TL; DR: JSONB pò simplificà assai u sviluppu di schema di basa di dati senza sacrificà a prestazione di query.
Introduzione
Demu un esempiu classicu di prubabilmente unu di i casi di usu più antichi in u mondu di una basa di dati relazionale (base di dati): avemu una entità, è avemu bisognu di salvà certe proprietà (attributi) di sta entità. Ma micca tutti i casi ponu avè u listessu settore di pruprietà, è più proprietà ponu esse aghjuntu in u futuru.
A manera più faciule di risolve stu prublema hè di creà una colonna in a tavula di basa di dati per ogni valore di a pruprietà, è simpricimenti cumpiendu quelli chì sò necessarii per una istanza di entità specifica. Perfettu! Prublemu risoltu ... finu à chì a vostra tavola cuntene milioni di dischi è avete bisognu di aghjunghje un novu record.
Cunsiderate u mudellu EAV (), si faci abbastanza spessu. Una tavola cuntene entità (records), una altra tavola cuntene nomi di pruprietà (attributi), è una terza tavola associa entità cù i so attributi è cuntene u valore di quelli attributi per l'entità attuale. Questu vi dà l'abilità di avè diverse setti di pruprietà per diversi oggetti, è ancu aghjunghje proprietà nantu à a mosca senza cambià a struttura di basa di dati.
Tuttavia, ùn saria micca scrittu stu post s'ellu ùn ci era micca qualchì svantaghju à l'approcciu EVA. Cusì, per esempiu, per ottene una o più entità chì anu 1 attributu ognunu, 2 joins sò necessarii in a dumanda: u primu hè un join cù a tabella di attributi, u sicondu hè un join cù a tabella di valori. Se una entità hà 2 attributi, allora 4 joins sò necessarii! Inoltre, tutti l'attributi sò tipicamente almacenati cum'è strings, chì si traduce in un casting di tipu sia per u risultatu sia per a clausola WHERE. Sè vo scrivite assai dumande, allora questu hè abbastanza persu in termini di usu di risorse.
Nunustanti sti difetti evidenti, EAV hè statu longu usatu pi risolviri sti tipi di prublemi. Quessi eranu difetti inevitabbili, è ùn ci era solu alternativa megliu.
Ma dopu una nova "tecnologia" apparsu in PostgreSQL ...
Partendu da PostgreSQL 9.4, u tipu di dati JSONB hè statu aghjuntu per almacenà e dati binari JSON. Ancu se l'almacenamiento di JSON in questu formatu generalmente piglia un pocu di più spaziu è tempu da u testu chjaru JSON, eseguisce operazioni nantu à questu hè assai più veloce. JSONB supporta ancu l'indexazione, chì rende e dumande ancu più veloce.
U tipu di dati JSONB ci permette di rimpiazzà u ingombrante mudellu EAV aghjunghjendu una sola colonna JSONB à a nostra tabella di entità, simplificendu assai u disignu di a basa di dati. Ma parechji sustene chì questu deve esse accumpagnatu da una diminuzione di a produtividade... Hè per quessa chì aghju scrittu stu articulu.
Configurazione di una basa di dati di prova
Per questu paragone, aghju creatu a basa di dati nantu à una nova installazione di PostgreSQL 9.5 nantu à a custruzzione di $ 80. Ubuntu 14.04 Dopu avè cunfiguratu certi parametri in postgresql.conf aghju eseguitu script cù psql. E seguenti tabelle sò state create per presentà e 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
);
A sottu hè una tavula induve i stessi dati seranu guardati, ma cù attributi in una colonna di tipu JSONB - Pruprietà.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Sembra assai più simplice, ùn hè micca? Allora hè statu aghjuntu à e tavule di entità (entità & entità_jsonb) 10 milioni di dischi, è per quessa, a tavula hè stata piena di i stessi dati cù u mudellu EAV è l'approcciu cù una colonna JSONB - entity_jsonb.properties. Cusì, avemu ricivutu parechji tippi di dati diffirenti trà l'inseme di proprietà. Dati di esempiu:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Allora avà avemu i stessi dati per e duie opzioni. Cuminciamu à paragunà implementazioni à u travagliu!
Simplificà u vostru disignu
Hè statu dichjaratu prima chì u disignu di a basa di dati era assai simplificatu: una tavola, utilizendu una colonna JSONB per e proprietà, invece di utilizà trè tavule per EAV. Ma cumu si riflette questu in e dumande? L'aghjurnà a pruprietà di una entità hè cusì:
-- 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;
Comu pudete vede, l'ultima dumanda ùn pare micca più simplice. Per aghjurnà u valore di una pruprietà in un oggettu JSONB avemu aduprà a funzione , è deve passà u nostru novu valore cum'è un oggettu JSONB. Tuttavia, ùn avemu micca bisognu di cunnosce in anticipu alcun identificatore. Fighjendu l'esempiu EAV, avemu bisognu di cunnosce l'entity_id è l'entity_attribute_id per fà l'aghjurnamentu. Se vulete aghjurnà una pruprietà in una colonna JSONB basatu annantu à u nome di l'ughjettu, allora tuttu hè fattu in una linea simplice.
Avà selezziunate l'entità chì avemu appena aghjurnatu basatu annantu à u so novu culore:
-- 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';
Pensu chì pudemu accettà chì a seconda hè più corta (senza join !), è dunque più leggibile. JSONB vince quì! Utilizemu l'operatore JSON ->> per uttene u culore cum'è un valore di testu da un ughjettu JSONB. Ci hè ancu una seconda manera di ottene u listessu risultatu in u mudellu JSONB cù l'operatore @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Questu hè un pocu più complicatu: cuntrollamu per vede se l'ughjettu JSON in a so colonna di proprietà cuntene un ughjettu chì hè à a diritta di l'operatore @>. Meno leggibile, più produtivu (vede sottu).
Facemu l'usu di JSONB ancu più faciule quandu avete bisognu di selezziunà parechje proprietà in una volta. Hè quì chì l'approcciu JSONB vene veramente in: selezziunate solu pruprietà cum'è colonne supplementari in u nostru risultatu senza avè bisognu di unisce:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Cù EAV avete bisognu di 2 unisce per ogni pruprietà chì vulete dumandà. In u mo parè, e dumande sopra mostranu una grande simplificazione in u disignu di basa di dati. Vede più esempi di cumu scrive dumande JSONB, ancu in postu.
Avà hè u tempu di parlà di performance.
Produttività
Per paragunà u rendiment chì aghju utilizatu in dumande, per calculà u tempu di esecuzione. Ogni dumanda hè stata eseguita almenu trè volte perchè u pianificatore di query dura più longu a prima volta. Prima aghju eseguitu e dumande senza alcunu indici. Ovviamente, questu era un vantaghju di JSONB, postu chì e unione necessarie per EAV ùn puderanu micca aduprà indici (i campi di chjave straneri ùn sò micca indiziati). Dopu questu, aghju creatu un indice nantu à e 2 colonne di chjave straniera di a tabella di valori EAV, è ancu un indice. per una colonna JSONB.
L'aghjurnamentu di dati hà dimustratu i seguenti risultati in termini di tempu (in ms). Nota chì a scala hè logaritmica:
Avemu vistu chì JSONB hè assai (> 50000-x) più veloce di EAV se ùn utilizate micca indici, per u mutivu dichjarata sopra. Quandu avemu indici culonni cù chjave primaria, a diffarenza quasi sparisce, ma JSONB hè sempre 1,3 volte più veloce di EAV. Nota chì l'indici nantu à a colonna JSONB ùn hà micca effettu quì postu chì ùn avemu micca aduprà a colonna di pruprietà in i criterii di valutazione.
Per a selezzione di dati basati nantu à u valore di a pruprietà, avemu i risultati seguenti (scala normale):
Puderete nutà chì JSONB torna torna più veloce di EAV senza indici, ma quandu EAV cù indici, travaglia sempre più veloce di JSONB. Ma dopu aghju vistu chì i tempi per e dumande JSONB eranu listessi, questu m'hà purtatu à u fattu chì l'indici GIN ùn funziona micca. Apparentemente quandu utilizate un indici GIN nantu à una colonna cù pruprietà populate, hè solu effettue quandu utilizate l'operatore include @>. Aghju utilizatu questu in una nova prova è hà avutu un impattu enormu nantu à u tempu: solu 0,153ms! Questu hè 15000 volte più veloce di EAV è 25000 volte più veloce di l'operatore ->>.
Pensu chì era abbastanza veloce!
Dimensione di a tabella di basa di dati
Comparamu e dimensioni di a tavola per i dui approcci. In psql pudemu vede a dimensione di tutti i tavulini è l'indici usendu u cumandamentu dti+
Per l'approcciu EAV, e dimensioni di a tavola sò intornu à 3068 MB è indici finu à 3427 MB per un totale di 6,43 GB. L'approcciu JSONB usa 1817 MB per a tavula è 318 MB per l'indici, chì hè 2,08 GB. Risulta 3 volte menu! Stu fattu m'hà sorpresu un pocu perchè guardamu i nomi di pruprietà in ogni ughjettu JSONB.
Ma sempre, i numeri parlanu per elli stessi: in EAV almacenemu 2 chjavi stranieri interi per valore d'attributu, risultatu in 8 bytes di dati supplementari. Inoltre, EAV guarda tutti i valori di pruprietà cum'è testu, mentre chì JSONB utilizerà valori numerichi è booleani in l'internu induve pussibule, risultatu in una impronta più chjuca.
Risultati
In generale, pensu chì a salvezza di e proprietà di l'entità in u formatu JSONB pò fà u disignu è u mantenimentu di a vostra basa di dati assai più faciule. Sè vo site parechje dumande, allora mantene tuttu in a listessa tavola cum'è l'entità hà da travaglià in modu più efficace. E u fattu chì questu simplifica l'interazzione trà e dati hè digià un plus, ma a basa di dati resultanti hè 3 volte più chjuca in voluminu.
Inoltre, basatu nantu à e teste realizate, pudemu cuncludi chì a perdita di rendiment hè assai insignificante. In certi casi, JSONB hè ancu più veloce di EAV, facendu ancu megliu. Tuttavia, stu benchmark di sicuru ùn copre micca tutti l'aspetti (per esempiu, entità cù un gran numaru di pruprietà, un incrementu significativu di u numeru di pruprietà di dati esistenti, ...), dunque s'ellu avete qualchì suggerimentu per migliurà. , per piacè sentite liberu di lascià in i cumenti!
Source: www.habr.com
