Analüüsime konkreetset näidet.

Kontekst:  Tegemist on PostgreSQL andmebaasiga, kus muuhulgas hakatakse hoidma andmeid keelemudelite kohta. See andmebaas on osa süsteemist, mis võimaldab teha andmebaaside suhtes eestikeelseid päringuid.

Probleemipüstitus: "Proovisin leida, mida oleks keelemudelite registris veel vaja hoiustada, et kogu info olemas oleks. Hetkel Keelemudeli tabelis olevad mittekohustuslikud veerud konteksti_pikkus ja parameetrid asendaksin kohustusliku veeruga parameetrid, mille tüüp oleks jsonb. Kuna keelemudelitel ei ole alati täpselt samasugust parameetrite valikut ja väärtuseid defineeritud/avalikustatud ning parameetrid saavad olla erinevat tüüpi, siis tundus see parim lahendus. Kaalusin parameetritele eraldi klassifikaatori loomist, et parameetrite nimetusi hoiustada ja seejärel eraldi vahetabelis hoida konkreetse keelemudeli ja konkreetse parameetri väärtust, aga selle puhul tekiks probleem just väärtuste hoiustamisel, kui need saavad olla erinevat tüüpi ning iga keelemudeli puhul erinevad (lisaks min, max ning vaikeväärtused). Igale tüübile oma veeru tekitamine oleks ruumi raiskamine ning alternatiivina peaks sealgi jsoni kasutusele võtma. Samuti keelemudeliga arutades, pakuti, et otse keelemudeli tabelis jsonb tüüpi veeru kasutamine on tulevikku vaadates tõenäolisemalt parem lahendus ning et eraldi klassifikaatori ning vahetabeli kasutamine muudaks andmebaasi disaini liialt laialivalguvaks ja selle haldamine oleks keerulisem.

Ehk kokkuvõttes, kas võin tabelis Keelemudel kasutada parameetrid veeru andmetüübina jsonb, et paindlikult hoiustada keelemudeli erinevate parameetrite infot?"

Analüüs:

Pakutud lahendus on kindlasti üks arvestatav võimalus. 

Analüüsin järgnevalt JSONB veerul põhinevat lahendust ja Olem-Atribuut-Väärtus (Entity-Attribute-Value, EAV) lahendust, kus andmebaasis on tabelid:

Andmetüüp (andmetüüp_kood, nimetus, on_aktiivne)
Primaarvõti (andmetüüp_kood)
Alternatiivvõti(nimetus)

Keelemudeli_atribuut (keelemudeli_atribuut_kood, nimetus, andmetüüp_kood, on_aktiivne)
Primaarvõti (keelemudeli_atribuut_kood)
Alternatiivvõti(nimetus)
Välisvõti (andmetüüp_kood) Viitab Andmetüüp (andmetüüp_kood)

Keelemudeli_atribuudi_väärtus (keelemudeli_atribuudi_väärtus_id, keelemudeli_atribuut_kood, keelemudel_id, väärtus, kehtivuse_algus, kehtivuse_lõpp)
Primaarvõti (keelemudeli_atribuudi_väärtus_id)
Alternatiivvõti (keelemudeli_atribuut_kood, keelemudel_id, kehtivuse_algus)
Välisvõti (keelemudeli_atribuut_kood) Viitab Keelemudeli_atribuut (keelemudeli_atribuut_kood)
Välisvõti (keelemudel_id) Viitab Keelemudel (keelemudel_id)

Kui andmebaasi tasemel ei ole ühtegi piirangut:
  • JSONB: Saan takistamatult registreerida mistahes võtmeid ja nende väärtuseid. Kui kogemata on ühes JSON objektis võtmeks "max_token" ja teises "maxx_token" või "maxtoken", siis olen registreerinud kaks eraldi atribuuti.
  • EAV: Atribuudid, mille väärtuseid registreerida, on eeldefineeritud. Andmesisestaja neid käigult juurdeleiutada ei saa. Kui on vaja hakata registreerima uue atribuudi väärtuseid, siis peab klassifikaatorite halduri õigustes kasutaja selle registreerima.
Kui sagedasti lisandub antud süsteemi uusi atribuute?
  • Pigem harva
Kui tahan andmebaasi tasemel jõustada piirangu, et arvuliste atribuutide väärtused on arvud:
  • JSONB: Võimalik. Tuleb lisada CHECK kitsendus. Tuleb kasutada PostgreSQL SQL murde uuemaid konstruktsioone. Vaadake näidet selle lõputöö lisast 3. 
  • EAV: Tuleb lisada BEFORE INSERT OR UPDATE triger tabelile Keelemudeli_atribuudi_väärtus, mis kasutaks kasutaja-defineeritud funktsiooni is_numeric.
Kui tahan jõustada andmebaasi tasemel mõnda täiendavat piirangut ühele atribuudile (nt min_token peab olema suurem kui 0):
  • JSONB: Võimalik. Tuleb kasutada PostgreSQL SQL murde uuemaid konstruktsioone. Vaadake näidet selle lõputöö lisast 3. 
  • EAV: Võimalik. Tuleb lisada CHECK kitsendus kujul CHECK (NOT (P) OR Q). Näide: CHECK (NOT (keelemudeli_atribuut_kood=4) OR väärtus::integer>0)
Kui tahan jõustada andmebaasi tasemel mõnda täiendavat piirangut mitmele atribuudile (nt max_token peab olema suurem kui min_token):
  • JSONB: Võimalik. Tuleb lisada CHECK kitsendus. Tuleb kasutada PostgreSQL SQL murde uuemaid konstruktsioone. Vaadake näidet selle lõputöö lisast 3.
  • EAV: Võimalik. Vajab keerukamat trigeri loogikat ning ilmselt ka jõustatavate reeglite andmebaasi tasemel kirjeldamist.
Kui tahan kasutada neid atribuudi väärtuseid andmebaasis tehtavates päringutes koos teistes tabelites olevate andmetega:
  • JSONB: Võimalik. Tuleb kasutada PostgreSQL SQL murde uuemaid konstruktsioone. Vaadake näidet. 
  • EAV: Võimalik. Saab kasutada vanemaid ja tuttavamaid SQL konstruktsioone. Samas keerulisemad päringud lähevad väga keeruliseks.
Atribuudi väärtuste uuendamine:
  • JSONB: Võimalik. Tuleb kasutada PostgreSQL SQL murde uuemaid konstruktsioone.
  • EAV: Võimalik. Lihtne.
Kui tahan säilitada atribuutide väärtuste ajalugu:
  • JSONB: Pakutud üks ja ainus JSONB veerg on ebapiisav.
  • EAV: Pakutud tabelite struktuur juba arvestab sellega.
Jõudlus ja indekseerimine:
  • JSONB: JSONB tüüpi veergudele saab luua GIN (Generalized Inverted Index) indekseid. Need on efektiivsed päringuteks, mis kontrollivad võtmete olemasolu, väärtuste olemasolu või võtme-väärtuse paaride leidmist. Ühe keelemudeli kõigi parameetrite lugemine on lihtne ja kiire – loetakse üks rida ja üks veerg. Kuna GIN-indeksite uuendamine on B-puu indeksitega võrreldes aeglasem, siis võib vähendada andmete uuendamise kiirust.
  • EAV: Nõuab hoolikalt läbimõeldud B-puu indekseid, näiteks liitindeksit (keelemudel_id, keelemudeli_atribuut_kood) tabelis Keelemudeli_atribuudi_väärtus, et kiirendada ühe mudeli kõigi atribuutide lugemist. Ühe keelemudeli kõigi atribuutide väärtuste saamiseks on vaja teha ühendamisoperatsioon mitme tabeli vahel ja lugeda mitu rida (Keelemudeli_atribuudi_väärtus tabelist), mis on ilmselt aeglasem kui ühe JSONB väärtuse lugemine. Samas üksikute atribuutide lisamine või muutmine on väga kiire ja efektiivne.
Kas jõudluse aspekt on antud juhul oluline? 
  • Pigem ei ole, sest keelemudeleid ja nende atribuute on vähe.
Arenduskogemuse poolest on rakenduste tegijad JSON-i kasutamisega harjunud. Andmebaasi poolelt on võimalused päringute tegemiseks olemas, kuid see nõuab SQLi kirjutajatelt harjumist.

Soovitus: Kõike eelnevat arvesse võttes on mul küsimus, kas see kõik tasub ära, selles mõttes, et milleks neid atribuudi väärtuseid üldse kasutatakse. Kas antud juhul ei oleks parem see, et tuvastada need mõned atribuudid, mille väärtuseid päriselt vaja läheb ning lisada need mittekohustuslike veergudena tabelisse Keelemudel?

Kaaluda võib ka hübriidlahendust:
  1. Tuvastage põhiatribuudid: Analüüsige, millised 3–5 atribuuti on kõige levinumad, kõige sagedamini päringutes kasutatavad või äriloogika seisukohalt kõige olulisemad (nt konteksti_pikkus, max_tokenid, temperatuur).
  2. Lisage need eraldi veergudena: Lisage need atribuudid otse Keelemudel tabelisse oma õige andmetüübiga (nt konteksti_pikkus INTEGER, temperatuur NUMERIC(3, 2)). Need veerud võivad olla mittekohustuslikud, et sobida mudelitele, millel vastav atribuut puudub.
    • Eelised: Saate nende kriitiliste andmete jaoks tugeva tüübikontrolli, lihtsad ja kiired päringud (WHERE konteksti_pikkus > 4000), standardse indekseerimise ja suurepärase andmete terviklikkuse.
  3. Kasutage JSONB ülejäänud jaoks: Lisage tabelisse Keelemudel veerg nimega lisa_parameetrid (või muud_parameetrid) andmetüübiga JSONB.
    • Eelised: See veerg püüab kinni kõik ülejäänud, haruldased, mudeli-spetsiifilised või tulevikus lisanduvad parameetrid. See annab teile paindlikkuse, ilma, et ohverdaksite struktuuri seal, kus see on oluline.