Reposuit EAV cum JSONB in ​​PostgreSQL .

Breviter: JSONB evolutionem schematis datorum magnopere simplificare potest sine detrimento celeritatis interrogationum.

introduction

Exemplum classicum sumamus, fortasse unum ex veterrimis usibus in mundo databaserum relationalium: entitatem habemus, et proprietates quasdam (attributa) huius entitatis conservare debemus. Sed non omnes instantiae eundem proprietatum gregem habere possunt, et plures proprietates in futuro addendae esse possunt.

Simplicissima huius problematis solutio est columnam in tabula datorum pro quolibet valore proprietatis creare et simpliciter eas quae necessariae sunt pro certa instantia entitatis implere. Optime! Problema solutum est... donec tabula tua milliones inscriptionum contineat et novum inscriptionem addere debeas.

Consideremus exemplar EAV (Entitas-Attributum-Valor), satis commune est. Una tabula entitates (notas) continet, altera tabula nomina proprietatum (attributa) continet, et tertia tabula entitates cum attributis suis coniungit et valores harum attributorum pro entitate currenti continet. Hoc tibi permittit ut varias proprietates pro diversis obiectis habeas, necnon proprietates ex tempore addas, sine mutatione structurae basis datorum.

Attamen, hunc nuntium non scriberem nisi quaedam incommoda methodi EVA adsint. Exempli gratia, ad recuperandam unam vel plures entitates cum singulis attributis, duas coniunctiones in quaestione requiruntur: primam cum tabula attributorum, secundam cum tabula valorum. Si entitas duas attributa habet, quattuor coniunctiones requiruntur! Praeterea, omnia attributa typice ut litterae servantur, quod ad coercitionem typi et pro resultato et pro clausula WHERE ducit. Si multas quaestiones scribis, hoc satis prodigum est quod ad usum opum attinet.

Quamquam haec vitia manifesta sunt, EAV diu ad huiusmodi problemata solvenda adhibita est. Haec vitia inevitabilia erant, et melior alternativa simpliciter non erat.
Sed deinde nova "technologia" in PostgreSQL apparuit...

Ab PostgreSQL 9.4 incipiens, typus datorum JSONB additus est ad data JSON binaria conservanda. Quamquam JSON hoc formato conservatio plerumque paulo plus spatii et temporis quam JSON textus simplicis requirit, operationes cum eo multo celeriores sunt. JSONB etiam indicem sustinet, interrogationes etiam celeriores faciens.

Typus datorum JSONB nobis permittit ut exemplar EAV difficile substituamus addendo tantum unam columnam JSONB ad tabulam entitatum nostrarum, designationem basis datorum insigniter simplificando. Attamen multi argumentantur hoc detrimentum in efficacia habere... Haec est causa cur hunc articulum scripsi.

Constitutio basis datorum probationum

Ad hanc comparationem, basim datorum in nova institutione PostgreSQL 9.5 in constructione $80 creavi. DigitalOcean Ubuntu 14.04. После настройки некоторых параметров в postgresql.conf я запустил haec scriptum utens psql. Ad data ut EAV exhibenda, hae tabulae creatae sunt:

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 est tabula ubi eadem data, sed cum attributis in columna JSONB, servabuntur – rerum.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

Multo simplicius videtur, nonne? Deinde additum est tabulis entitatum (rem & entitas_jsonb) decem miliones inscriptionum, et proinde tabula identicis datis impleta est ubi exemplar EAV et modus cum columna JSONB adhibiti sunt – entity_jsonb.propertiesIta, plura genera datorum per totum proprietatum gregem obtinuimus. Exempla datorum:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

Itaque nunc eadem data pro ambabus optionibus habemus. Incipiamus comparare implementationes in vita reali!

Simplificatio designii

Ante dictum est formam datorum insigniter simplificatam esse: unam tabulam, columnam JSONB pro proprietatibus utens, loco trium tabularum pro EAV. Sed quomodo hoc in interrogationes vertitur? Proprietatis singularis entitatis renovatio hoc modo apparet:

-- 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, ultima interrogatio non simplicior videtur. Ad valorem proprietatis in objecto JSONB renovandum, functionem uti debemus `jsonb_set()`...et novum valorem nostrum ut obiectum JSONB transmittere debemus. Nihilominus, nullum identificatorem antea scire debemus. Exemplum EAV inspiciendo, et `entity_id` et `entity_attribute_id` scire debemus ad renovationem perficiendam. Si proprietatem in columna JSONB secundum nomen obiecti renovare vis, omnia una simplici linea fiunt.

Nunc entitatem quam modo renovavimus secundum novum colorem eligamus:

-- 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';

Credo nos consentire posse secundum brevius esse (sine iunctura!) et ideo facilius legibile. JSONB hic vincit! Operatore JSON ->> utimur ad colorem ut valorem textualem ex objecto JSONB recuperandum. Est etiam altera via ad idem exitum in exemplo JSONB assequendum utens operatore @>:

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

Hoc paulo implicatius est: exploramus num obiectum JSON in columna proprietatum obiectum in dextra parte operatoris @> contineat. Minus legibile, efficacius (vide infra).

Simplicem etiam usum JSONB exsequamur cum plures proprietates simul eligere necesse est. Hic est ubi methodus JSONB maxime eminet: proprietates simpliciter ut columnas additionales in nostro eventuum grege eligimus, sine necessitate iuncturarum:

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

Cum EAV, duabus iuncturis pro qualibet proprietate quam interrogare vis opus erit. Mea sententia, interrogationes supra simplificationem significantem in designio basis datorum demonstrant. Plura exempla scribendi interrogationes JSONB etiam videre potes in haec post
Nunc tempus est de effectu disserere.

productivity

Ad comparandam efficaciam quam usus sum EXPLICARE ANALYZARE in interrogationibus ad tempus exsecutionis computandum. Quaeque interrogatio saltem ter exsecuta est, quia consiliator interrogationum diutius prima vice consumit. Primo, interrogationes sine ullis indicibus cucurri. Hoc manifeste commodum JSONB praebuit, cum iuncturae necessariae pro EAV indicibus uti non poterant (campi clavium externarum non indicabantur). Post hoc, indicem in duabus columnis clavium externarum tabulae valorum EAV creavi, necnon indicem... PEDICA pro columna JSONB.

Renovationes datorum sequentia tempora (in ms) ostenderunt. Nota scalam logarithmicam esse:

Videmus JSONB significanter (>50000x) celeriorem esse quam EAV sine indicibus, ob causam supra dictam. Cum columnas clavis primariae indicamus, differentia fere evanescit, sed JSONB adhuc 1,3x celerior est quam EAV. Nota indicem in columna JSONB hic nullum effectum habere, cum columnam proprietatis in criteriis aestimationis non utamur.

Ad selectionem datorum secundum valorem proprietatis, haec eventa (scala normali) obtinemus:

Vides JSONB iterum celerius esse quam EAV sine indicibus, sed cum EAV indicatur, adhuc celerius est quam JSONB. Sed deinde vidi tempora interrogationum JSONB eadem esse, quod me ad hoc duxit indices GIN non excitari. Apparet, cum indicem GIN in columna cum proprietatibus plenis uteris, tantum effectum habere cum operatore inclusionis @> adhibetur. Hoc in novo experimento usus sum, et magnum impulsum in tempus habuit: tantum 0,153 ms! Hoc est 15000 vicibus celerius quam EAV et 25000 vicibus celerius quam operatore ->>.

Credo id satis celeriter fuisse!

Magnitudo tabularum basis datorum

Magnitudines tabularum pro utroque modo comparemus. In psql, magnitudinem omnium tabularum et indicum ostendere possumus utentes mandato dti+

Methodo EAV adhibita, magnitudines tabularum sunt circiter 3068 MB, et indices usque ad 3427 MB, in summa 6,43 GB. Methodo JSONB adhibita, tabula utitur 1817 MB et indices 318 MB, in summa 2,08 GB. Hoc est tertia pars magnitudinis! Hoc me paulum miratum est, cum nomina proprietatum in singulis obiectis JSONB reponemus.

Sed numeri per se loquuntur: in EAV, duas claves externas integras per valorem attributi reponimus, quod octo octetos notitiarum additionalium efficit. Praeterea, in EAV, omnes valores proprietatum ut textus servantur, dum JSONB valores numericos et logicos interne, ubi fieri potest, adhibebit, quod spatium minus occupat.

results

Summa summarum, mea sententia, proprietates entitatum in forma JSONB servandae designum et sustentationem basis datorum tuae magnopere simplificare possunt. Si multas interrogationes facis, omnia in eadem tabula ac entitas servare vere efficacius erit. Quod interactiones datorum simplificat iam commodum est, sed basis datorum resultans etiam triplo minor est.

Praeterea, ex resultatibus probationis comparativae, concludere possumus iacturam perfunctionis minimam esse. Interdum, JSONB etiam celerius quam EAV fungitur, quod eum etiam melius reddit. Attamen, haec probatio certe non omnes aspectus amplectitur (e.g., entitates cum numero proprietatum magno, augmentum significans numeri proprietatum in datis existentibus, etc.), ergo si quas suggestiones ad emendationem habes, eas in commentariis relinque!

Source: www.habr.com