החלפת EAV ב-JSONB ב-PostgreSQL

בקיצור: JSONB יכול לפשט מאוד את פיתוח סכימת מסד הנתונים מבלי להתפשר על ביצועי השאילתות.

מבוא

בואו ניקח דוגמה קלאסית של כנראה אחד ממקרי השימוש העתיקים ביותר בעולם של מסדי נתונים יחסיים: יש לנו ישות, ואנחנו צריכים לאחסן מאפיינים (attributes) מסוימים של ישות זו. אבל לא לכל המופעים עשוי להיות אותו סט של מאפיינים, ובעתיד, ייתכן שיתווספו מאפיינים נוספים.

הדרך הקלה ביותר לפתור את הבעיה הזו היא ליצור עמודה בטבלת מסד הנתונים עבור כל ערך מאפיין, ופשוט למלא את העמודות שאתה צריך עבור מופע ישות מסוים. נהדר! הבעיה נפתרה... עד שהטבלה שלך מכילה מיליוני רשומות ואתה צריך להוסיף רשומה חדשה.

בואו נבחן את תבנית ה-EAV (ערך-תכונת-ישות), זה די נפוץ. טבלה אחת מכילה ישויות (רשומות), טבלה אחרת מכילה שמות מאפיינים (תכונות), וטבלה שלישית מקשרת ישויות לתכונות שלהן ומכילה את הערך של תכונות אלו עבור הישות הנוכחית. זה נותן לך את היכולת לקבל קבוצות שונות של מאפיינים עבור אובייקטים שונים, כמו גם להוסיף מאפיינים "תוך כדי תנועה" מבלי לשנות את מבנה מסד הנתונים.

עם זאת, לא הייתי כותב את הפוסט הזה אלמלא היו חסרונות בגישת ה-EVA. לדוגמה, כדי לקבל ישות אחת או יותר שיש להן מאפיין אחד לכל אחת, נדרשים 1 צירופים בשאילתה: הראשון הוא צירוף עם טבלת המאפיינים, השני הוא צירוף עם טבלת הערכים. אם לישות יש 2 מאפיינים, אז נדרשים 2 צירופים! בנוסף, כל המאפיינים מאוחסנים בדרך כלל כמחרוזות, מה שמוביל לשינוי סוג (typecasting) הן עבור התוצאה והן עבור תנאי ה-WHERE. אם כותבים הרבה שאילתות, זה די בזבזני מבחינת שימוש במשאבים.

למרות חסרונות ברורים אלה, EAV משמש זה מכבר לפתרון בעיות מסוג זה. אלה היו חסרונות בלתי נמנעים, ופשוט לא הייתה אלטרנטיבה טובה יותר.
אבל אז הופיעה "טכנולוגיה" חדשה ב-PostgreSQL...

החל מ-PostgreSQL 9.4, נוסף סוג הנתונים JSONB לאחסון נתוני JSON בינאריים. למרות שאחסון JSON בפורמט זה בדרך כלל דורש מעט יותר מקום וזמן מאשר JSON בטקסט רגיל, ביצוע פעולות עליו מהיר הרבה יותר. JSONB תומך גם באינדוקס, מה שהופך את השאילתות נגדו למהירות עוד יותר.

סוג הנתונים JSONB מאפשר לנו להחליף את תבנית EAV המסורבלת על ידי הוספת עמודת JSONB אחת בלבד לטבלת הישויות שלנו, מה שמפשט מאוד את עיצוב מסד הנתונים שלנו. אבל רבים טוענים שזה כרוך במחיר ביצועים... זו הסיבה שכתבתי את המאמר הזה.

הגדרת מסד נתונים לבדיקות

לצורך השוואה זו, יצרתי את מסד הנתונים בהתקנה חדשה של PostgreSQL 9.5 בגרסה של 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';

אני חושב שאנחנו יכולים להסכים שהשני קצר יותר (ללא join!), ולכן קריא יותר. 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 צירופים (joins) עבור כל מאפיין שתרצו לבצע שאילתה. לדעתי, השאילתות הנ"ל מראות פישוט גדול בעיצוב מסד הנתונים. ניתן גם לראות דוגמאות נוספות כיצד לכתוב שאילתות JSONB ב- זה שֶׁלְאַחַר.
עכשיו הגיע הזמן לדבר על ביצועים.

פרודוקטיביות

כדי להשוות את הביצועים בהם השתמשתי הסבר ניתוח בשאילתות, כדי לחשב את זמן הביצוע. כל שאילתה בוצעה לפחות שלוש פעמים, מכיוון שמתכנן השאילתות לוקח יותר זמן בפעם הראשונה. ראשית, ביצעתי את השאילתות ללא אינדקסים. ככל הנראה, זה היה יתרון של JSONB, מכיוון שהצירופים הנדרשים עבור EAV לא יכלו להשתמש באינדקסים (שדות המפתח הזר לא היו מאונדקסים). לאחר מכן, יצרתי אינדקס על 2 עמודות המפתח הזר של טבלת ערכי EAV, ואינדקס GIN עבור עמודת JSONB.

רענון הנתונים הראה את התוצאות הבאות בזמן (במילי-שניות). שימו לב שקנה ​​המידה הוא לוגריתמי:

אנו רואים ש-JSONB מהיר בהרבה (מעל פי 50000) מ-EAV כאשר איננו משתמשים באינדקסים, מהסיבה שצוינה לעיל. כאשר אנו יוצרים אינדקסים של עמודות המפתח הראשי, ההבדל כמעט נעלם, אך JSONB עדיין מהיר פי 1,3 מ-EAV. שימו לב שהאינדקס בעמודת JSONB אינו משפיע כאן, מכיוון שאיננו משתמשים בעמודת המאפיינים בקריטריוני ההערכה.

עבור בחירת נתונים המבוססים על ערך הנכס, נקבל את התוצאות הבאות (בקנה מידה רגיל):

ניתן לראות ש-JSONB שוב מהיר יותר מ-EAV ללא אינדקסים, אך כאשר EAV מאונדקס הוא עדיין מהיר יותר מ-JSONB. אבל אז ראיתי שהזמנים עבור שאילתות ה-JSONB היו זהים, מה שהוביל אותי לעובדה שאינדקס ה-GIN אינו מופעל. ככל הנראה, כאשר משתמשים באינדקס GIN על עמודה עם מאפיינים מאוכלסים, זה עובד רק כאשר משתמשים באופרטור ההכללה @>. השתמשתי בזה בבדיקה החדשה, וזה עשה הבדל עצום בתזמון: רק 0,153 מילישניות! זה פי 15000 מהיר יותר מ-EAV, ופי 25000 מהיר יותר מהאופרטור ->>.

אני חושב שזה היה די מהיר!

גודל טבלאות מסד הנתונים

בואו נשווה את גודל הטבלאות עבור שתי הגישות. ב-psql נוכל להציג את גודל כל הטבלאות והאינדקסים בעזרת הפקודה dti+

עבור גישת EAV, גדלי הטבלה הם כ-3068 מגה-בייט והאינדקסים הם עד 3427 מגה-בייט, שהם 6,43 ג'יגה-בייט בסך הכל. גישת JSONB משתמשת ב-1817 מגה-בייט עבור הטבלה ו-318 מגה-בייט עבור האינדקסים, שהם 2,08 ג'יגה-בייט. זה פי 3 פחות! עובדה זו הפתיעה אותי מעט, מכיוון שאנו מאחסנים את שמות המאפיינים בכל אובייקט JSONB.

אבל עדיין, המספרים מדברים בעד עצמם: ב-EAV אנו מאחסנים 2 מפתחות זרים שלמים לכל ערך תכונה, וכתוצאה מכך 8 בתים של נתונים נוספים. כמו כן, ב-EAV כל ערכי המאפיינים מאוחסנים כטקסט, בעוד ש-JSONB ישתמש בערכים מספריים ובוליאניים באופן פנימי במידת האפשר, וכתוצאה מכך טביעת רגל קטנה יותר.

תוצאות של

בסך הכל, אני חושב שאחסון מאפייני ישות ב-JSONB יכול להקל הרבה יותר על עיצוב ותחזוקת מסד הנתונים. אם אתם מבצעים הרבה שאילתות, שמירה על כל הנתונים באותה טבלה כמו הישות תעבוד בצורה יעילה יותר. והעובדה שזה מפשט את האינטראקציות בין הנתונים היא יתרון, אבל מסד הנתונים שנוצר גם קטן פי 3.

כמו כן, מהבדיקות שביצענו, אנו יכולים להסיק כי אובדן הביצועים הוא מינורי מאוד. במקרים מסוימים, JSONB אפילו מהיר יותר מ-EAV, מה שהופך אותו לטוב עוד יותר. עם זאת, בדיקת ביצועים זו בהחלט אינה מכסה את כל ההיבטים (למשל, ישויות עם מספר גדול מאוד של מאפיינים, עלייה משמעותית במספר המאפיינים של נתונים קיימים,...), אז אם יש לכם הצעות כיצד לשפר אותן, אל תהססו להשאיר תגובה!

מקור: www.habr.com