🥇PostgreSQLలో EAVని JSONBతో భర్తీ చేయడం

TL; DR: JSONB ప్రశ్న పనితీరును త్యాగం చేయకుండా డేటాబేస్ స్కీమా డెవలప్‌మెంట్‌ను చాలా సులభతరం చేస్తుంది.

పరిచయం

రిలేషనల్ డేటాబేస్ (డేటాబేస్) ప్రపంచంలోని పురాతన వినియోగ సందర్భాలలో బహుశా ఒకదానికి ఒక క్లాసిక్ ఉదాహరణ ఇద్దాం: మనకు ఒక ఎంటిటీ ఉంది మరియు మేము ఈ ఎంటిటీ యొక్క నిర్దిష్ట లక్షణాలను (గుణాలు) సేవ్ చేయాలి. కానీ అన్ని సందర్భాలు ఒకే విధమైన లక్షణాలను కలిగి ఉండకపోవచ్చు మరియు భవిష్యత్తులో మరిన్ని లక్షణాలు జోడించబడవచ్చు.

ఈ సమస్యను పరిష్కరించడానికి సులభమైన మార్గం ఏమిటంటే, ప్రతి ఆస్తి విలువ కోసం డేటాబేస్ పట్టికలో నిలువు వరుసను సృష్టించడం మరియు నిర్దిష్ట ఎంటిటీ ఉదాహరణకి అవసరమైన వాటిని పూరించండి. గొప్ప! సమస్య పరిష్కరించబడింది... మీ పట్టిక మిలియన్ల కొద్దీ రికార్డ్‌లను కలిగి ఉండే వరకు మరియు మీరు కొత్త రికార్డ్‌ను జోడించాలి.

EAV నమూనాను పరిగణించండి (ఎంటిటీ-లక్షణం-విలువ), ఇది చాలా తరచుగా జరుగుతుంది. ఒక టేబుల్‌లో ఎంటిటీలు (రికార్డ్‌లు), మరొక టేబుల్‌లో ప్రాపర్టీ పేర్లు (లక్షణాలు) ఉన్నాయి మరియు మూడవ టేబుల్ ఎంటిటీలను వాటి లక్షణాలతో అనుబంధిస్తుంది మరియు ప్రస్తుత ఎంటిటీకి సంబంధించిన ఆ లక్షణాల విలువను కలిగి ఉంటుంది. ఇది విభిన్న వస్తువుల కోసం విభిన్న సెట్ల లక్షణాలను కలిగి ఉండే సామర్థ్యాన్ని మీకు అందిస్తుంది మరియు డేటాబేస్ నిర్మాణాన్ని మార్చకుండా ఫ్లైలో లక్షణాలను కూడా జోడించవచ్చు.

అయితే, EVA విధానానికి కొన్ని ప్రతికూలతలు లేకుంటే నేను ఈ పోస్ట్ రాయను. కాబట్టి, ఉదాహరణకు, ఒక్కొక్కటి 1 లక్షణాన్ని కలిగి ఉన్న ఒకటి లేదా అంతకంటే ఎక్కువ ఎంటిటీలను పొందేందుకు, ప్రశ్నలో 2 చేరికలు అవసరం: మొదటిది అట్రిబ్యూట్ టేబుల్‌తో చేరడం, రెండవది విలువల పట్టికతో చేరడం. ఒక ఎంటిటీకి 2 గుణాలు ఉంటే, 4 చేరికలు అవసరం! అదనంగా, అన్ని అట్రిబ్యూట్‌లు సాధారణంగా స్ట్రింగ్‌లుగా నిల్వ చేయబడతాయి, దీని ఫలితంగా ఫలితం మరియు WHERE నిబంధన రెండింటికీ టైప్ కాస్టింగ్ జరుగుతుంది. మీరు చాలా ప్రశ్నలను వ్రాస్తే, వనరుల వినియోగం పరంగా ఇది చాలా వ్యర్థం.

ఈ స్పష్టమైన లోపాలు ఉన్నప్పటికీ, ఈ రకమైన సమస్యలను పరిష్కరించడానికి EAV చాలా కాలంగా ఉపయోగించబడింది. ఇవి అనివార్యమైన లోపాలు మరియు మంచి ప్రత్యామ్నాయం లేదు.
అయితే PostgreSQLలో కొత్త “టెక్నాలజీ” కనిపించింది...

PostgreSQL 9.4తో ప్రారంభించి, JSON బైనరీ డేటాను నిల్వ చేయడానికి JSONB డేటా రకం జోడించబడింది. ఈ ఫార్మాట్‌లో JSONని నిల్వ చేయడానికి సాధారణంగా సాదా టెక్స్ట్ JSON కంటే కొంచెం ఎక్కువ స్థలం మరియు సమయాన్ని తీసుకుంటుంది, దానిపై కార్యకలాపాలు చేయడం చాలా వేగంగా ఉంటుంది. JSONB ఇండెక్సింగ్‌కు కూడా మద్దతు ఇస్తుంది, ఇది ప్రశ్నలను మరింత వేగవంతం చేస్తుంది.

JSONB డేటా రకం డేటాబేస్ డిజైన్‌ను చాలా సులభతరం చేస్తూ మా ఎంటిటీ టేబుల్‌కి కేవలం ఒక JSONB కాలమ్‌ని జోడించడం ద్వారా గజిబిజిగా ఉన్న EAV నమూనాను భర్తీ చేయడానికి అనుమతిస్తుంది. అయితే దీనితో పాటు ఉత్పాదకత తగ్గుతుందని పలువురు వాదిస్తున్నారు... అందుకే ఈ వ్యాసం రాశాను.

పరీక్ష డేటాబేస్ను ఏర్పాటు చేస్తోంది

ఈ పోలిక కోసం, నేను $9.5 బిల్డ్‌లో PostgreSQL 80 యొక్క తాజా ఇన్‌స్టాలేషన్‌పై డేటాబేస్‌ను సృష్టించాను DigitalOcean Ubuntu 14.04 postgresql.conf లో కొన్ని పారామీటర్లను కాన్ఫిగర్ చేసిన తర్వాత నేను రన్ చేసాను ఈ psql ఉపయోగించి స్క్రిప్ట్. డేటాను 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
);

క్రింద అదే డేటా నిల్వ చేయబడే పట్టిక ఉంది, కానీ JSONB రకం కాలమ్‌లోని లక్షణాలతో – లక్షణాలు.

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

చాలా సరళంగా కనిపిస్తోంది, కాదా? అప్పుడు అది ఎంటిటీ టేబుల్‌లకు జోడించబడింది (పరిధి & entity_jsonb) 10 మిలియన్ రికార్డులు, మరియు తదనుగుణంగా, EAV నమూనా మరియు JSONB కాలమ్‌తో ఉన్న విధానాన్ని ఉపయోగించి టేబుల్ అదే డేటాతో నింపబడింది - entity_jsonb.properties. ఈ విధంగా, మేము మొత్తం లక్షణాల సెట్‌లో అనేక విభిన్న డేటా రకాలను అందుకున్నాము. ఉదాహరణ డేటా:

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

కాబట్టి ఇప్పుడు మనకు రెండు ఎంపికల కోసం ఒకే డేటా ఉంది. పనిలో అమలులను పోల్చడం ప్రారంభిద్దాం!

మీ డిజైన్‌ను సరళీకృతం చేయండి

డేటాబేస్ రూపకల్పన చాలా సరళీకృతం చేయబడిందని గతంలో పేర్కొనబడింది: EAV కోసం మూడు పట్టికలను ఉపయోగించకుండా, లక్షణాల కోసం JSONB నిలువు వరుసను ఉపయోగించడం ద్వారా ఒక టేబుల్. అయితే ఇది అభ్యర్థనలలో ఎలా ప్రతిబింబిస్తుంది? ఒక ఎంటిటీ ప్రాపర్టీని అప్‌డేట్ చేయడం ఇలా కనిపిస్తుంది:

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

మీరు చూడగలిగినట్లుగా, చివరి అభ్యర్థన సరళంగా కనిపించడం లేదు. JSONB ఆబ్జెక్ట్‌లో ఆస్తి విలువను అప్‌డేట్ చేయడానికి మనం ఫంక్షన్‌ని ఉపయోగించాలి jsonb_set(), మరియు మా కొత్త విలువను JSONB ఆబ్జెక్ట్‌గా పాస్ చేయాలి. అయితే, మనం ముందుగా ఏ ఐడెంటిఫైయర్ గురించి తెలుసుకోవాల్సిన అవసరం లేదు. EAV ఉదాహరణను పరిశీలిస్తే, అప్‌డేట్ చేయడానికి మనం entity_id మరియు entity_attribute_id రెండింటినీ తెలుసుకోవాలి. మీరు ఆబ్జెక్ట్ పేరు ఆధారంగా JSONB నిలువు వరుసలో ప్రాపర్టీని అప్‌డేట్ చేయాలనుకుంటే, అదంతా ఒక సాధారణ లైన్‌లో జరుగుతుంది.

ఇప్పుడు మనం ఇప్పుడే అప్‌డేట్ చేసిన ఎంటిటీని దాని కొత్త రంగు ఆధారంగా ఎంచుకుందాం:

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

రెండవది చిన్నది (చేరవద్దు!) మరియు మరింత చదవగలిగేది అని మనం అంగీకరించగలమని నేను భావిస్తున్నాను. JSONB ఇక్కడ గెలుపొందింది! JSONB ఆబ్జెక్ట్ నుండి రంగును వచన విలువగా పొందడానికి మేము JSON ->> ఆపరేటర్‌ని ఉపయోగిస్తాము. @> ఆపరేటర్‌ని ఉపయోగించి JSONB మోడల్‌లో అదే ఫలితాన్ని సాధించడానికి రెండవ మార్గం కూడా ఉంది:

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

ఇది కొంచెం క్లిష్టంగా ఉంటుంది: JSON ఆబ్జెక్ట్ దాని ప్రాపర్టీల కాలమ్‌లో @> ఆపరేటర్‌కు కుడి వైపున ఉన్న ఆబ్జెక్ట్‌ని కలిగి ఉందో లేదో మేము తనిఖీ చేస్తాము. తక్కువ రీడబుల్, ఎక్కువ ఉత్పాదకత (క్రింద చూడండి).

మీరు ఒకేసారి బహుళ ప్రాపర్టీలను ఎంచుకోవలసి వచ్చినప్పుడు JSONBని మరింత సులభతరం చేద్దాం. ఇక్కడే JSONB విధానం నిజంగా వస్తుంది: మేము చేరాల్సిన అవసరం లేకుండా మా ఫలితం సెట్‌లో లక్షణాలను అదనపు నిలువు వరుసలుగా ఎంచుకుంటాము:

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

EAVతో మీరు ప్రశ్నించాలనుకునే ప్రతి ఆస్తికి 2 చేరికలు అవసరం. నా అభిప్రాయం ప్రకారం, పై ప్రశ్నలు డేటాబేస్ రూపకల్పనలో గొప్ప సరళీకరణను చూపుతాయి. JSONB ప్రశ్నలను ఎలా వ్రాయాలి అనేదానికి మరిన్ని ఉదాహరణలను చూడండి ఈ పోస్ట్.
ఇప్పుడు పనితీరు గురించి మాట్లాడే సమయం వచ్చింది.

ఉత్పాదకత

పనితీరును పోల్చడానికి నేను ఉపయోగించాను విశ్లేషించి వివరించండి ప్రశ్నలలో, అమలు సమయాన్ని లెక్కించడానికి. ప్రశ్న ప్లానర్ మొదటిసారి ఎక్కువ సమయం తీసుకుంటుంది కాబట్టి ప్రతి ప్రశ్న కనీసం మూడు సార్లు అమలు చేయబడింది. మొదట నేను ఎటువంటి సూచికలు లేకుండా ప్రశ్నలను అమలు చేసాను. సహజంగానే, ఇది JSONB యొక్క ప్రయోజనం, ఎందుకంటే EAVకి అవసరమైన జాయిన్‌లు ఇండెక్స్‌లను ఉపయోగించలేవు (విదేశీ కీ ఫీల్డ్‌లు ఇండెక్స్ చేయబడలేదు). దీని తర్వాత నేను EAV విలువ పట్టికలోని 2 విదేశీ కీ నిలువు వరుసలపై ఒక సూచికను అలాగే ఒక సూచికను సృష్టించాను జిన్ JSONB కాలమ్ కోసం.

డేటా అప్‌డేట్ సమయం పరంగా క్రింది ఫలితాలను చూపింది (మిసెలలో). స్కేల్ లాగరిథమిక్ అని గమనించండి:

PostgreSQLలో EAVని JSONBతో భర్తీ చేస్తోంది

పైన పేర్కొన్న కారణంతో మీరు ఇండెక్స్‌లను ఉపయోగించనట్లయితే, JSONB EAV కంటే చాలా (> 50000-x) వేగవంతమైనదని మేము చూస్తాము. మేము ప్రాథమిక కీలతో నిలువు వరుసలను సూచిక చేసినప్పుడు, వ్యత్యాసం దాదాపు అదృశ్యమవుతుంది, అయితే JSONB ఇప్పటికీ EAV కంటే 1,3 రెట్లు వేగంగా ఉంటుంది. మేము మూల్యాంకన ప్రమాణాలలో ప్రాపర్టీ కాలమ్‌ని ఉపయోగించనందున JSONB కాలమ్‌లోని సూచిక ఇక్కడ ఎటువంటి ప్రభావం చూపదని గమనించండి.

ఆస్తి విలువ ఆధారంగా డేటాను ఎంచుకోవడం కోసం, మేము ఈ క్రింది ఫలితాలను పొందుతాము (సాధారణ స్కేల్):

PostgreSQLలో EAVని JSONBతో భర్తీ చేస్తోంది

JSONB మళ్లీ ఇండెక్స్‌లు లేకుండా EAV కంటే వేగంగా పని చేస్తుందని మీరు గమనించవచ్చు, అయితే EAV ఇండెక్స్‌లతో ఉన్నప్పుడు, ఇది ఇప్పటికీ JSONB కంటే వేగంగా పని చేస్తుంది. కానీ JSONB ప్రశ్నల సమయాలు ఒకే విధంగా ఉన్నాయని నేను చూశాను, ఇది GIN సూచికలు పని చేయవని నన్ను ప్రేరేపించింది. మీరు జనాదరణ పొందిన లక్షణాలతో కాలమ్‌లో GIN సూచికను ఉపయోగించినప్పుడు, అది చేర్చబడిన ఆపరేటర్ @>ని ఉపయోగిస్తున్నప్పుడు మాత్రమే ప్రభావం చూపుతుంది. నేను దీన్ని కొత్త పరీక్షలో ఉపయోగించాను మరియు ఇది సమయంపై భారీ ప్రభావాన్ని చూపింది: కేవలం 0,153ms మాత్రమే! ఇది EAV కంటే 15000 రెట్లు వేగంగా మరియు ->> ఆపరేటర్ కంటే 25000 రెట్లు వేగంగా ఉంటుంది.

ఇది తగినంత వేగంగా ఉందని నేను భావిస్తున్నాను!

డేటాబేస్ పట్టిక పరిమాణం

రెండు విధానాలకు పట్టిక పరిమాణాలను సరిపోల్చండి. psqlలో మనం ఆదేశాన్ని ఉపయోగించి అన్ని పట్టికలు మరియు సూచికల పరిమాణాన్ని చూపవచ్చు dti+

PostgreSQLలో EAVని JSONBతో భర్తీ చేస్తోంది

EAV విధానం కోసం, పట్టిక పరిమాణాలు దాదాపు 3068 MB మరియు మొత్తం 3427 GB కోసం సూచికలు 6,43 MB వరకు ఉంటాయి. JSONB విధానం పట్టిక కోసం 1817 MBని మరియు సూచికల కోసం 318 MBని ఉపయోగిస్తుంది, ఇది 2,08 GB. ఇది 3 రెట్లు తక్కువగా మారుతుంది! మేము ప్రతి JSONB ఆబ్జెక్ట్‌లో ఆస్తి పేర్లను నిల్వ చేస్తున్నందున ఈ వాస్తవం నన్ను కొద్దిగా ఆశ్చర్యపరిచింది.

కానీ ఇప్పటికీ, సంఖ్యలు తమకు తాముగా మాట్లాడుకుంటాయి: EAVలో మేము ప్రతి లక్షణం విలువకు 2 పూర్ణాంక విదేశీ కీలను నిల్వ చేస్తాము, ఫలితంగా 8 బైట్‌ల అదనపు డేటా వస్తుంది. అదనంగా, EAV అన్ని ప్రాపర్టీ విలువలను టెక్స్ట్‌గా నిల్వ చేస్తుంది, అయితే JSONB సాధ్యమైన చోట అంతర్గతంగా సంఖ్యా మరియు బూలియన్ విలువలను ఉపయోగిస్తుంది, ఫలితంగా చిన్న పాదముద్ర ఉంటుంది.

ఫలితాలు

మొత్తంమీద, JSONB ఫార్మాట్‌లో ఎంటిటీ ప్రాపర్టీలను సేవ్ చేయడం వల్ల మీ డేటాబేస్ రూపకల్పన మరియు నిర్వహణ చాలా సులభం అవుతుందని నేను భావిస్తున్నాను. మీరు చాలా ప్రశ్నలను నడుపుతున్నట్లయితే, ఎంటిటీ వలె ప్రతిదానిని ఒకే పట్టికలో ఉంచడం వాస్తవానికి మరింత సమర్థవంతంగా పని చేస్తుంది. మరియు ఇది డేటా మధ్య పరస్పర చర్యను సులభతరం చేస్తుందనే వాస్తవం ఇప్పటికే ఒక ప్లస్, కానీ ఫలితంగా డేటాబేస్ వాల్యూమ్లో 3 రెట్లు తక్కువగా ఉంటుంది.

అలాగే, నిర్వహించిన పరీక్షల ఆధారంగా, పనితీరు నష్టం చాలా తక్కువగా ఉందని మేము నిర్ధారించగలము. కొన్ని సందర్భాల్లో, JSONB EAV కంటే వేగంగా ఉంటుంది, ఇది మరింత మెరుగ్గా ఉంటుంది. అయితే, ఈ బెంచ్‌మార్క్ అన్ని అంశాలను కవర్ చేయదు (ఉదా. చాలా పెద్ద సంఖ్యలో ప్రాపర్టీలు కలిగిన ఎంటిటీలు, ఇప్పటికే ఉన్న డేటా యొక్క లక్షణాల సంఖ్యలో గణనీయమైన పెరుగుదల,...), కాబట్టి వాటిని ఎలా మెరుగుపరచాలనే దానిపై మీకు ఏవైనా సూచనలు ఉంటే , దయచేసి వ్యాఖ్యలలో సంకోచించకండి!

మూలం: www.habr.com