🥇 PostgreSQL ۾ JSONB سان EAV کي تبديل ڪرڻ

ٽي ايل؛ DR: JSONB سوال جي ڪارڪردگي کي قربان ڪرڻ کان سواءِ ڊيٽابيس اسڪيما ڊولپمينٽ کي تمام گھڻو آسان ڪري سگھي ٿو.

تعارف

اچو ته هڪ ڪلاسيڪل مثال ڏيون شايد دنيا جي سڀ کان پراڻي استعمال جي ڪيسن مان هڪ تعلقي ڊيٽابيس (ڊيٽابيس) جي: اسان وٽ هڪ ادارو آهي، ۽ اسان کي هن اداري جي ڪجهه خاصيتن (صفات) کي بچائڻ جي ضرورت آهي. پر سڀني مثالن ۾ ملڪيت جو ساڳيو سيٽ نه ٿي سگھي ٿو، ۽ مستقبل ۾ وڌيڪ ملڪيت شامل ٿي سگھي ٿي.

هن مسئلي کي حل ڪرڻ جو آسان طريقو اهو آهي ته هر ملڪيت جي قيمت لاءِ ڊيٽابيس جدول ۾ هڪ ڪالم ٺاهيو، ۽ صرف انهن کي ڀريو جيڪي ڪنهن مخصوص اداري مثال لاءِ گهربل آهن. زبردست! مسئلو حل ٿي ويو... جيستائين توھان جي ٽيبل تي لکين رڪارڊ شامل آھن ۽ توھان کي نئون رڪارڊ شامل ڪرڻو پوندو.

EAV نموني تي غور ڪريو (وجود- وصف- قدر)، اهو اڪثر ٿئي ٿو. ھڪڙي جدول ۾ ادارن (ريڪارڊ) شامل آھن، ٻي جدول ملڪيت جا نالا (صفات) تي مشتمل آھي، ۽ ٽيون جدول ادارن کي انھن جي خاصيتن سان ملائي ٿو ۽ موجوده اداري لاءِ انھن خاصيتن جي قيمت تي مشتمل آھي. هي توهان کي صلاحيت ڏئي ٿو مختلف شين لاءِ پراپرٽيز جي مختلف سيٽن کي، ۽ پڻ شامل ڪريو پراپرٽيز کي بغير ڊيٽابيس جي جوڙجڪ کي تبديل ڪرڻ جي.

بهرحال، مان هي پوسٽ نه لکندس جيڪڏهن ايوا جي نقطه نظر ۾ ڪجهه گهٽتائي نه هجي ها. تنهن ڪري، مثال طور، هڪ يا وڌيڪ ادارا حاصل ڪرڻ لاءِ جن وٽ 1 وصف آهي، سوال ۾ 2 جوائن گهربل آهن: پهريون آهي انتساب جدول سان شامل ٿيڻ، ٻيو آهي وابستگي جدول سان شامل ٿيڻ. جيڪڏهن هڪ اداري ۾ 2 خاصيتون آهن، ته پوء 4 شامل ٿيڻ جي ضرورت آهي! اضافي طور تي، سڀئي خاصيتون عام طور تي اسٽرنگ جي طور تي ذخيرو ٿيل آهن، جنهن جي نتيجي ۾ نتيجو ۽ WHERE شق ٻنهي لاء قسم ڪاسٽنگ جي نتيجي ۾. جيڪڏهن توهان تمام گهڻا سوال لکندا آهيو، ته اهو وسيلن جي استعمال جي لحاظ کان ڪافي فضول آهي.

انهن واضح گهٽتائي جي باوجود، EAV ڊگهي استعمال ڪئي وئي آهي انهن قسمن جي مسئلن کي حل ڪرڻ لاء. اهي ناگزير نقص هئا، ۽ بس ڪو بهتر متبادل نه هو.
پر پوء هڪ نئين "ٽيڪنالاجي" ۾ ظاهر ٿيو PostgreSQL ...

PostgreSQL 9.4 سان شروع ڪندي، JSONB ڊيٽا جو قسم JSON بائنري ڊيٽا کي ذخيرو ڪرڻ لاءِ شامل ڪيو ويو. جيتوڻيڪ JSON کي هن فارميٽ ۾ ذخيرو ڪرڻ عام طور تي سادو متن JSON جي ڀيٽ ۾ ٿورو وڌيڪ جڳهه ۽ وقت وٺندو آهي، ان تي عمل ڪرڻ تمام تيز آهي. JSONB پڻ انڊيڪسنگ کي سپورٽ ڪري ٿو، جيڪو سوالن کي وڌيڪ تيز ڪري ٿو.

JSONB ڊيٽا جو قسم اسان کي اجازت ڏئي ٿو ته اسان جي ايٽمي ٽيبل تي صرف هڪ JSONB ڪالم شامل ڪندي بوجھل EAV نموني کي تبديل ڪري، ڊيٽابيس ڊيزائن کي تمام آسان بڻائي. پر ڪيترائي بحث ڪن ٿا ته اهو هجڻ گهرجي پيداوار ۾ گهٽتائي سان گڏ ... انهي ڪري مون هي مضمون لکيو.

ٽيسٽ ڊيٽابيس کي ترتيب ڏيڻ

ھن مقابلي لاءِ، مون ڊيٽابيس ٺاھيو PostgreSQL 9.5 جي تازي تنصيب تي $80 تي ڊيڊيڪل ايسوسيئيشن 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"
  } 
}

تنهنڪري هاڻي اسان وٽ ٻنهي اختيارن لاءِ ساڳي ڊيٽا آهي. اچو ته ڪم تي عملدرآمد جي مقابلي ڪرڻ شروع ڪريون!

پنھنجي ڊيزائن کي آسان ڪريو

اهو اڳ ۾ چيو ويو آهي ته ڊيٽابيس ڊيزائن کي تمام آسان ڪيو ويو آهي: هڪ ٽيبل، ملڪيت لاء JSONB ڪالمن استعمال ڪندي، EAV لاء ٽي ٽيبل استعمال ڪرڻ بدران. پر اها درخواستن ۾ ڪيئن ظاهر ٿئي ٿي؟ ھڪڙي اداري جي ملڪيت کي اپڊيٽ ڪرڻ ھن طرح نظر اچي ٿو:

-- 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 هتي کٽيو! اسان استعمال ڪريون ٿا JSON ->> آپريٽر کي JSONB اعتراض مان ٽيڪسٽ ويليو طور رنگ حاصل ڪرڻ لاءِ. ساڳيو نتيجو حاصل ڪرڻ جو ٻيو طريقو پڻ آهي 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 پرڏيهي ڪي ڪالمن تي هڪ انڊيڪس ٺاهيو، ان سان گڏ هڪ انڊيڪس GIN JSONB ڪالمن لاءِ.

ڊيٽا جي تازه ڪاري ڏيکاريا ھيٺ ڏنل نتيجا وقت جي لحاظ کان (ms ۾). نوٽ ڪريو ته ماپ logarithmic آهي:

اسان ڏسون ٿا ته JSONB تمام گهڻو (> 50000-x) EAV کان تيز آهي جيڪڏهن توهان انڊيڪس استعمال نٿا ڪريو، مٿي بيان ڪيل سبب جي ڪري. جڏهن اسان ڪالمن کي پرائمري ڪيز سان انڊيڪس ڪندا آهيون، فرق لڳ ڀڳ غائب ٿي ويندو آهي، پر JSONB اڃا EAV کان 1,3 ڀيرا تيز آهي. ياد رهي ته JSONB ڪالمن تي انڊيڪس جو هتي ڪو به اثر ناهي ڇو ته اسان تشخيص جي معيار ۾ ملڪيت ڪالمن استعمال نه ڪري رهيا آهيون.

ملڪيت جي قيمت جي بنياد تي ڊيٽا کي چونڊڻ لاء، اسان هيٺ ڏنل نتيجا حاصل ڪندا آهيون (عام پيماني تي):

توهان نوٽيس ڪري سگهو ٿا ته JSONB ٻيهر ڪم ڪري ٿو EAV کان بغير انڊيڪس جي، پر جڏهن EAV انڊيڪس سان، اهو اڃا به JSONB کان تيز ڪم ڪري ٿو. پر پوءِ مون ڏٺو ته JSONB سوالن جا وقت ساڳيا هئا، هن مون کي حقيقت ڏانهن اشارو ڪيو ته GIN انڊيڪس ڪم نه ڪندا آهن. ظاهري طور تي جڏهن توهان هڪ GIN انڊيڪس استعمال ڪريو ٿا ڪالمن تي آبادي واري ملڪيت سان، اهو صرف اثر وٺندو آهي جڏهن شامل آپريٽر @> استعمال ڪريو. مون هن کي نئين ٽيسٽ ۾ استعمال ڪيو ۽ اهو وقت تي تمام وڏو اثر پيو: صرف 0,153ms! اهو EAV کان 15000 ڀيرا تيز ۽ ->> آپريٽر کان 25000 ڀيرا تيز آهي.

منهنجو خيال آهي ته اهو ڪافي تيز هو!

ڊيٽابيس ٽيبل سائيز

اچو ته ٻنهي طريقن جي ٽيبل جي سائيز جو مقابلو ڪريو. psql ۾ اسان ڪمانڊ استعمال ڪندي سڀني جدولن ۽ انڊيڪس جي سائيز ڏيکاري سگھون ٿا ڊي ٽي آئي +

EAV اپروچ لاءِ، ٽيبل جون سائيزون 3068 MB جي لڳ ڀڳ آھن ۽ انڊيڪس 3427 MB تائين ڪل 6,43 GB لاءِ آھن. JSONB طريقو استعمال ڪري ٿو 1817 MB ٽيبل لاءِ ۽ 318 MB انڊيڪسز لاءِ، جيڪو 2,08 GB آهي. اهو ٻاهر ڦرندو 3 ڀيرا گهٽ! اها حقيقت مون کي ٿورو حيران ڪيو ڇو ته اسان هر JSONB اعتراض ۾ ملڪيت جا نالا ذخيرو ڪندا آهيون.

پر اڃا تائين، انگ پاڻ لاءِ ڳالهائين ٿا: EAV ۾ اسان 2 انٽيجر فارين ڪيچز کي في ايٽريبيٽ ويليو ذخيرو ڪندا آهيون، نتيجي ۾ 8 بائيٽس اضافي ڊيٽا. اضافي طور تي، EAV سڀني ملڪيت جي قيمتن کي متن جي طور تي محفوظ ڪري ٿو، جڏهن ته JSONB استعمال ڪندو عددي ۽ بوليان قدر اندروني طور تي جتي ممڪن هجي، نتيجي ۾ ننڍڙو فوٽ پرنٽ.

نتيجو

مجموعي طور تي، مان سمجهان ٿو ته JSONB فارميٽ ۾ اداري ملڪيتن کي محفوظ ڪرڻ توهان جي ڊيٽابيس کي ڊزائين ڪرڻ ۽ برقرار رکڻ تمام آسان بڻائي سگهي ٿو. جيڪڏھن توھان تمام گھڻا سوال ھلائي رھيا آھيو، ته پوءِ ھر شيءِ کي ساڳي جدول ۾ رکڻ سان اصل ۾ وڌيڪ ڪارائتو ڪم ڪندو. ۽ حقيقت اها آهي ته هي ڊيٽا جي وچ ۾ رابطي کي آسان بڻائي ٿو، اڳ ۾ ئي هڪ پلس آهي، پر نتيجو ڊيٽابيس حجم ۾ 3 ڀيرا ننڍو آهي.

انهي سان گڏ، ڪيل تجربن جي بنياد تي، اسان اهو نتيجو ڪري سگهون ٿا ته ڪارڪردگي نقصان تمام غير معمولي آهن. ڪجھ ڪيسن ۾، JSONB EAV کان به تيز آھي، ان کي بھتر بڻائيندي. بهرحال، هي معيار يقيناً سڀني پهلوئن کي نه ٿو ڍڪي (مثال طور ملڪيتن جي تمام وڏي تعداد سان گڏ ادارا، موجوده ڊيٽا جي ملڪيتن جي تعداد ۾ هڪ اهم اضافو،...)، پوءِ جيڪڏهن توهان وٽ ڪي تجويزون آهن ته انهن کي ڪيئن بهتر ڪجي ، مهرباني ڪري تبصرن ۾ ڇڏڻ لاءِ آزاد ٿيو!

جو ذريعو: www.habr.com

EAV کي JSONB سان PostgreSQL ۾ تبديل ڪرڻ