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

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

מבוא

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

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

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

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

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

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

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

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

לצורך השוואה זו, יצרתי את מסד הנתונים על התקנה חדשה של PostgreSQL 9.5 ב-build $80 DigitalOcean אובונטו 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 לא יכלו להשתמש באינדקסים (שדות מפתח זרים לא נוספו לאינדקס). לאחר מכן יצרתי אינדקס על 2 עמודות המפתח הזר של טבלת הערך EAV, וכן אינדקס ג'ין עבור עמודת JSONB.

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

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

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

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

אני חושב שזה היה מספיק מהיר!

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

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

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

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

תוצאות של

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

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

מקור: www.habr.com