TL; DR: JSONB schema evolutionis datorum simpliciorem reddere potest sine interrogatione implenda.
introduction
Exemplum demus classicum verisimiliter unum ex vetustissimis casibus utendi in mundo database relationis (database): ens habemus, et certas proprietates huius entitatis servare oportet. Sed non omnes instantiae possunt eandem proprietatem habere, et plures proprietates in futuro addi.
Facillimus modus ad hanc quaestionem solvendam est columnam in mensa datorum creare pro cuiusque pretii proprietate, et simpliciter replere ea quae ad instantiam entitatis specificae requiruntur. Magna! Problema solvitur... donec mensa tua decies centena millia monumentorum contineat et opus novum recordum addere.
Considerans exemplum EAV (), saepius occurrit. Una tabula continet res (recordationes), alia tabula continet nomina proprietatum (attributa), tertia tabula res cum suis attributis consociat et valorem continet illarum attributorum pro ente currente. Hoc tibi facultatem dat habere varias proprietates pro diversis objectis, et etiam adde proprietates in musca sine mutando structuram datorum.
Non tamen scriberem hanc stationem si non aliquae downsionis ad Evam accederent. Exempli gratia, ut unum vel plura entia quae cuique tribuunt 1 habeant, 2 nexus in interrogatione requiruntur: prima est iuncta cum tabula attributa, secunda est iuncta cum valore mensae. Si res habet 2 attributa, opus est 4 conjunctiones! Accedit, omnia attributa more chordarum condita sunt, quae proveniunt specie fusurae ad utrumque effectum et ubi clausulae. Si multum queries scribes, tunc satis prodigus est in terminis subsidiorum uteris.
Quamvis huiusmodi defectus manifesti, EAV has difficultates solvendas esse diu adhibitum est. Haec erant inevitabilia vitia, et nihil aliud melius fuit.
Sed tunc nova "technologia" in PostgreSQL... apparuit.
Incipiens a PostgreSQL 9.4, forma data JSONB addita est ad notitias binarias copia JSON. Etsi JSON in hac forma deponenda typice accipit paulo plus spatii et temporis quam textum planum JSON, operationes in ea perficiendo multo velocius est. JSONB etiam indexing adiuvat, quod queries etiam velocius facit.
Typus notitiarum JSONB nobis permittit ut exemplar gravia reponere EAV addendo unam tantum columnam JSONB ad mensam entitatis nostrae, valde simpliciorem datorum designationem. Sed multi contendunt hoc debere esse cum diminutione in fructibus... Ideo scripsi hunc articulum.
Test database erigens
Ad hanc comparationem database in novam institutionem PostgreSQL 9.5 in $80 constructum creavi Decuria 14.04. In postgresql.conf i ran parametri aliquot profecti scriptum per psql. Tabulae sequentes factae sunt ut notitias in forma EAV exhiberent;
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
);
Infra tabulam ubi eadem notitia reponenda est, sed cum attributis in columna JSONB typo β rerum.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Multum simplicius spectat, annon? Tum ad tabulas entitas adiecta est (rem & entity_jsonb) 10 decies centena millia monumentorum, ideoque mensa repleta est eadem notitia utens exemplari EAV et accessu cum columna JSONB - entity_jsonb.properties. Sic varia genera notitiarum diversarum in integris proprietatum statutis accepimus. Exemplum data:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Nunc ergo eadem notitia utrique bene. Incipiamus comparet implementations ad opus!
Simpliciorem tuum consilium
Antea declarabatur consilium datorum valde simpliciorem esse: una tabula, utens columna JSONB pro proprietatibus, loco tribus tabulis EAV utens. Sed quomodo hoc in petitionibus resultat? Adaequationis res una entitas similis est:
-- 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;
Ut videre potes, postrema petitio simpliciorem non spectat. Ad valorem proprietatis in JSONB obiecto uti functione ac transeat novum valorem pro JSONB objecto. Sed identifier ante cognoscere non oportet. Exemplum EAV spectans, scire necesse est tam entitatem_id quam aliquid_attribute_id ad perficiendum renovationem. Si proprietatem vis renovare in columna JSONB innixa obiecto nomine, tunc omnia in una linea simplici facta sunt.
Nunc rem eligemus quae mox renovata est in novo 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';
Secundam breviorem (no join!), et ideo magis lectam, consentire posse puto. JSONB hic vincit! Utimur JSON ->> operante ut color ut textum valorem ex objecto JSONB obtineat. Alter etiam modus est ut eundem exitum consequamur in exemplari JSONB utente operante @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Hoc paulo magis complicatum est: reprehendo videre an obiectum JSON in suis proprietatibus columna contineat objectum quod est ad jus operantis @>. Minus legendum, fructuosius (vide infra).
Facilius etiam faciamus utendo JSONB cum multas proprietates simul eligere debes. Haec ubi JSONB appropinquant, vere in: simpliciter proprietates selectas, sicut additae columnae in nostro eventu positae, sine necessitate coniunctionum:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Cum EAV necesse est 2 nexus pro singulis proprietatibus quaesitum velis. Opinor, interrogationes praedictae magnam simplicitatem in datorum consilio demonstrant. Plura exempla vide quomodo queries scribere JSONB, etiam in Apost.
Nunc tempus est de effectu loqui.
productivity
Comparare perficientur eram in queritur, tempus execu- tionis computare. Utraque quaestio ter saltem supplicium est quod quaesitum consilium iam primum accipit. Primum queries sine ullis indicibus cucurri. Uti patet, hoc commodum erat JSONB, quia nexus pro EAV requisiti indicibus uti non poterant (agri clavium exteri non sunt recensiti). Post haec indicem in 2 columnis praecipuis exteris EAV tabellae pretii, necnon indicem feci ad JSONB columnae.
Notitia renovationis sequentis eventus per tempora ostendit (in ms). Nota quod scala logarithmica est;
Videmus JSONB multo citius (> 50000-x) velociorem esse quam EAV si, ratione supra dicta, indicibus non uteris. Cum indicem columnarum primis clavibus, differentia paene evanescit, sed JSONB adhuc 1,3 temporibus velocior est quam EAV. Nota quod index columnae in JSONB hic nullum effectum habet, quoniam columna proprietatis in indiciis aestimationis non utimur.
Ad notitias eligendas secundum valorem proprietatis, sequentia consequimur (scale normaliter);
Potes animadvertere JSONB iterum citius quam EAV sine indicibus operari, sed cum EAV cum indicibus adhuc velocius quam JSONB operatur. Sed tunc vidi tempora quaesita JSONB eadem esse, hoc me ad id impulit, quod indices GIN non operantur. Videtur quod cum indice GIN in columna uteris cum proprietatibus hominum popularibus, effectum tantum obtinet cum operator @> includere. Hoc in novo experimento usus sum et in tempore magnum momentum habuit: tantum 0,153ms! Hoc est 15000 temporum velocius quam EAV et 25000 vicibus velocius quam operator ->>.
Puto hercle!
Database mensa magnitudine
Ambo accessus moles mensae comparemus. In psql magnitudinem tabularum omnium et indices uti mandato possumus ostendere dti+
Ad accessum EAV, tabulae magnitudinum circa 3068 MB et indices usque ad 3427 MB pro summa 6,43 GB. Accessus JSONB utitur 1817 MB ad mensam et 318 MB ad indices, qui est 2,08 GB. III minus evenit! Quod quidem me parum miratur, quod nominum proprietatem condimus in omni JSONB objecto.
Sed tamen numeri pro se loquuntur: in EAV condimus 2 claves integros extraneos valorem per attributum, inde in 8 bytes notitiarum additarum. Accedit, EAV omnia bona quasi textus bona reponit, dum JSONB valores numericos et booleanos intus utetur, ubi fieri potest, minore vestigio consequens.
results
Overall, I think being salvis proprietatibus in JSONB format can make designing and maintaining your database much easier. Si quaestiones multum curris, omnia in eadem tabula retinens ut ens actu efficacius operabitur. Quod simpliciorem commercium notitiarum inter se iam plus est, inde autem database inde plus minus est in volumine.
Etiam, ex probatis praestitis, concludere possumus damna perficienda valde parva esse. In quibusdam, JSONB vel velocior est quam EAV, magis etiam faciens. Nihilominus, hoc Probatio sane omnes aspectus non comprehendit (exempli causa cum permagnus numerus proprietatum, notabilis auctus in numero proprietatum exsistentium notitiarum, ...), ita si quid suggestiones habes in quomodo eas emendare , placet liberum in commentarios relinquere!
Source: www.habr.com