PostgreSQL এ JSONB এর সাথে EAV প্রতিস্থাপন করা হচ্ছে

TL; DR: JSONB কোয়েরি পারফরম্যান্সকে ত্যাগ না করেই ডাটাবেস স্কিমা বিকাশকে ব্যাপকভাবে সহজ করতে পারে।

ভূমিকা

আসুন একটি রিলেশনাল ডাটাবেসের (ডাটাবেস) বিশ্বের প্রাচীনতম ব্যবহারের ক্ষেত্রে সম্ভবত একটি ক্লাসিক উদাহরণ দেওয়া যাক: আমাদের একটি সত্তা আছে এবং আমাদের এই সত্তার নির্দিষ্ট বৈশিষ্ট্য (গুণাবলী) সংরক্ষণ করতে হবে। কিন্তু সব দৃষ্টান্তে একই বৈশিষ্ট্যের সেট নাও থাকতে পারে এবং ভবিষ্যতে আরও বৈশিষ্ট্য যোগ করা হতে পারে।

এই সমস্যাটি সমাধান করার সবচেয়ে সহজ উপায় হল প্রতিটি সম্পত্তির মানের জন্য ডাটাবেস টেবিলে একটি কলাম তৈরি করা এবং একটি নির্দিষ্ট সত্তা দৃষ্টান্তের জন্য প্রয়োজনীয়গুলি পূরণ করা। দারুণ! সমস্যা সমাধান করা হয়েছে... যতক্ষণ না আপনার টেবিলে লক্ষ লক্ষ রেকর্ড রয়েছে এবং আপনাকে একটি নতুন রেকর্ড যোগ করতে হবে।

EAV প্যাটার্ন বিবেচনা করুন (সত্তা-অ্যাট্রিবিউট-মান), এটি প্রায়ই ঘটে। একটি টেবিলে সত্তা (রেকর্ড) রয়েছে, অন্য একটি টেবিলে সম্পত্তির নাম (গুণাবলী) রয়েছে এবং তৃতীয় টেবিলটি সত্তাকে তাদের বৈশিষ্ট্যগুলির সাথে সংযুক্ত করে এবং বর্তমান সত্তার জন্য সেই বৈশিষ্ট্যগুলির মান রয়েছে৷ এটি আপনাকে বিভিন্ন অবজেক্টের জন্য বিভিন্ন বৈশিষ্ট্যের সেট করার ক্ষমতা দেয় এবং ডাটাবেস গঠন পরিবর্তন না করেই ফ্লাইতে বৈশিষ্ট্য যোগ করে।

যাইহোক, যদি ইভা পদ্ধতির কিছু খারাপ দিক না থাকে তবে আমি এই পোস্টটি লিখব না। সুতরাং, উদাহরণস্বরূপ, প্রতিটি 1টি বৈশিষ্ট্যযুক্ত এক বা একাধিক সত্তা পেতে, ক্যোয়ারীতে 2টি যোগদান প্রয়োজন: প্রথমটি বৈশিষ্ট্য টেবিলের সাথে একটি যোগদান, দ্বিতীয়টি মান টেবিলের সাথে একটি যোগদান। যদি একটি সত্তার 2টি বৈশিষ্ট্য থাকে, তাহলে 4টি যোগদান প্রয়োজন! উপরন্তু, সমস্ত বৈশিষ্ট্য সাধারণত স্ট্রিং হিসাবে সংরক্ষণ করা হয়, যার ফলে ফলাফল এবং WHERE ক্লজ উভয়ের জন্য টাইপ কাস্টিং হয়। আপনি যদি অনেক প্রশ্ন লেখেন, তাহলে রিসোর্স ব্যবহারের ক্ষেত্রে এটি বেশ অপচয়।

এই সুস্পষ্ট ত্রুটিগুলি সত্ত্বেও, এই ধরনের সমস্যাগুলি সমাধান করার জন্য EAV দীর্ঘকাল ব্যবহার করা হয়েছে। এগুলি অনিবার্য ত্রুটি ছিল এবং এর চেয়ে ভাল বিকল্প আর ছিল না।
কিন্তু তারপর পোস্টগ্রেএসকিউএল-এ একটি নতুন "প্রযুক্তি" উপস্থিত হয়েছিল...

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"
  } 
}

সুতরাং এখন উভয় বিকল্পের জন্য আমাদের কাছে একই ডেটা রয়েছে। আসুন কর্মক্ষেত্রে বাস্তবায়নের তুলনা শুরু করি!

আপনার নকশা সরলীকরণ

এটি পূর্বে বলা হয়েছিল যে ডাটাবেস ডিজাইনটি ব্যাপকভাবে সরলীকৃত হয়েছিল: একটি টেবিল, বৈশিষ্ট্যগুলির জন্য একটি 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 এখানে জিতেছে! আমরা 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 কলামের জন্য।

ডেটা আপডেটটি সময়ের পরিপ্রেক্ষিতে নিম্নলিখিত ফলাফলগুলি দেখিয়েছে (এমএসে)। মনে রাখবেন যে স্কেলটি লগারিদমিক:

আমরা দেখতে পাচ্ছি যে JSONB EAV এর চেয়ে অনেক দ্রুত (> 50000-x) যদি আপনি সূচী ব্যবহার না করেন, উপরে উল্লিখিত কারণে। যখন আমরা প্রাথমিক কীগুলির সাথে কলামগুলিকে সূচী করি, তখন পার্থক্য প্রায় অদৃশ্য হয়ে যায়, তবে JSONB এখনও EAV থেকে 1,3 গুণ দ্রুত। মনে রাখবেন যে JSONB কলামের সূচী এখানে কোন প্রভাব নেই কারণ আমরা মূল্যায়নের মানদণ্ডে সম্পত্তি কলাম ব্যবহার করছি না।

সম্পত্তি মানের উপর ভিত্তি করে ডেটা নির্বাচন করার জন্য, আমরা নিম্নলিখিত ফলাফল (সাধারণ স্কেল) পাই:

আপনি লক্ষ্য করতে পারেন যে JSONB আবার সূচী ছাড়াই EAV এর চেয়ে দ্রুত কাজ করে, কিন্তু যখন EAV সূচী সহ, এটি এখনও JSONB এর চেয়ে দ্রুত কাজ করে। কিন্তু তারপর আমি দেখেছি যে JSONB প্রশ্নের জন্য সময় একই ছিল, এটি আমাকে জিআইএন সূচকগুলি কাজ করে না এই বিষয়টিতে প্ররোচিত করেছিল। দৃশ্যত আপনি যখন জনবহুল বৈশিষ্ট্য সহ একটি কলামে একটি জিআইএন সূচক ব্যবহার করেন, এটি শুধুমাত্র অন্তর্ভুক্ত অপারেটর @> ব্যবহার করার সময় কার্যকর হয়। আমি এটি একটি নতুন পরীক্ষায় ব্যবহার করেছি এবং এটি সময়ের উপর একটি বিশাল প্রভাব ফেলেছে: শুধুমাত্র 0,153ms! এটি EAV থেকে 15000 গুণ দ্রুত এবং ->> অপারেটরের চেয়ে 25000 গুণ দ্রুত।

আমি মনে করি এটা যথেষ্ট দ্রুত ছিল!

ডাটাবেস টেবিলের আকার

আসুন উভয় পদ্ধতির জন্য টেবিলের আকার তুলনা করি। psql-এ আমরা কমান্ড ব্যবহার করে সমস্ত টেবিল এবং ইনডেক্সের আকার দেখাতে পারি dti+

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