Postgres: bloat, pg_repack ואילוצים דחויים

השפעת הנפיחות על טבלאות ואינדקסים ידועה ברבים והיא קיימת לא רק ב-Postgres. ישנן דרכים להתמודד עם זה מחוץ לקופסה, כמו VACUUM FULL או CLUSTER, אבל הם נועלים שולחנות בזמן הפעולה ולכן לא תמיד ניתן להשתמש בהם.

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

מאמר זה נכתב על בסיס הנאום שלי ב-PgConf.Russia 2020.

מדוע מתרחשת נפיחות?

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

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

נניח שיש לנו טבלה שהוספנו אליה מספר רשומות. נתונים חדשים הופיעו בעמוד הראשון של הקובץ שבו מאוחסנת הטבלה. אלו הן גרסאות חיות של שורות הזמינות לטרנזקציות אחרות לאחר commit (למען הפשטות, נניח שרמת הבידוד היא Read Committed).

לאחר מכן עדכנו את אחד הערכים, ובכך סימנו את הגרסה הישנה כלא רלוונטית יותר.

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

לפוסטגרס יש מנגנון לִשְׁאוֹב, שמנקה גרסאות מיושנות ומפנה מקום לנתונים חדשים. אבל אם הוא לא מוגדר בצורה אגרסיבית מספיק או שהוא עסוק בעבודה בטבלאות אחרות, אז "נתוני זבל" נשארים, ועלינו להשתמש בדפים נוספים עבור נתונים חדשים.

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

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

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

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

יש לי נפיחות?

ישנן מספר דרכים לקבוע אם יש לך נפיחות. הרעיון של הראשון הוא להשתמש בסטטיסטיקה פנימית של Postgres, המכילה מידע משוער על מספר השורות בטבלאות, מספר שורות "חיות" וכו'. אתה יכול למצוא וריאציות רבות של סקריפטים מוכנים באינטרנט. לקחנו כבסיס תַסרִיט מ-PostgreSQL Experts, שיכולים להעריך טבלאות נפיחות יחד עם מדדי טוסט ו-bloat btree. מניסיוננו, השגיאה שלו היא 10-20%.

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

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

דרכים להילחם בנפיחות

ל-Postgres יש כמה דרכים להתמודד עם נפיחות מחוץ לקופסה, אבל הן לא תמיד מתאימות לכולם.

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

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

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

מה לעשות במצב שבו הגדרתם AUTOVACUUM, אבל הנפיחות ממשיכה לגדול.

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

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

קבוצה REINDEX דומה לשניים הקודמים, אך בונה מחדש אינדקס ספציפי או את כל האינדקסים של הטבלה. המנעולים מעט חלשים יותר: ShareLock על הטבלה (מונע שינויים, אך מאפשר בחירה) ו-AccessExclusiveLock על האינדקס שנבנה מחדש (חוסם שאילתות באמצעות אינדקס זה). עם זאת, בגרסה ה-12 של Postgres הופיע פרמטר במקביל, המאפשר לך לבנות מחדש את האינדקס מבלי לחסום הוספה, שינוי או מחיקה במקביל של רשומות.

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

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

איך pg_repack עובד

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

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

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

הכל נראה מצוין בתיאוריה, אבל מה קורה בפועל? בדקנו את pg_repack ללא עומס ותחת עומס, ובדקנו את פעולתו במקרה של עצירה מוקדמת (במילים אחרות, באמצעות Ctrl+C). כל הבדיקות היו חיוביות.

הלכנו לחנות המזון - ואז הכל לא הלך כמו שציפינו.

פנקייק ראשון במבצע

באשכול הראשון קיבלנו שגיאה לגבי הפרה של אילוץ ייחודי:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

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

אילוצים דחויים: מדוע הם נחוצים וכיצד הם פועלים

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

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

נניח שהיינו צריכים להחליף את המכונית הראשונה והשנייה. הפתרון הפשוט הוא לעדכן את הערך הראשון לשני, והשני לראשון:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

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

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

איך אני יכול לעשות את זה אחרת? אפשרות ראשונה: הוסף תחליף ערך נוסף להזמנה שמובטחת שלא תתקיים בטבלה, למשל "-1". בתכנות, זה נקרא "החלפת ערכים של שני משתנים דרך שלישי". החיסרון היחיד של שיטה זו הוא העדכון הנוסף.

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

אפשרות שלישית: להפוך את האילוץ לדחות כך שהוא ייבדק רק בזמן ההתחייבות:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

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

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

באופן כללי, בהתאם לסוג האילוץ, ל-Postgres יש שלוש רמות של פירוט לבדיקתן: רמות שורה, עסקה וביטוי.

מקור: מגרשים

CHECK ו- NOT NULL תמיד מסומנים ברמת השורה; עבור מגבלות אחרות, כפי שניתן לראות מהטבלה, ישנן אפשרויות שונות. אתה יכול לקרוא עוד כאן.

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

שיפור של pg_repack

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

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

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

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

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

לפיכך, מהות הבעיה נעוצה ב"השהיה" של הבדיקה: בטבלה המקורית היא מתרחשת בזמן ה-commit, ובטבלה החדשה בזמן ביצוע הפקודה sql. המשמעות היא שעלינו לוודא שהבדיקות מבוצעות זהה בשני המקרים: או תמיד באיחור, או תמיד מיד.

אז איזה רעיונות היו לנו?

צור אינדקס דומה לדחוי

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

כדי להבטיח שההמחאות יהיו תמיד במצב לא נדחה, יצרנו אינדקס חדש הדומה לאילוץ הנדחה המקורי:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

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

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

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

החלף אינדקסים בטבלה החדשה באילוצים דחויים מהטבלה המקורית

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

כדי לבדוק את השינויים שלנו, כתבנו מבחן פשוט:

• טבלה עם אילוץ דחוי ורשומה אחת;
• הכנס נתונים בלולאה שמתנגשת עם רשומה קיימת;
• בצע עדכון - הנתונים אינם מתנגשים יותר;
• לבצע את השינויים.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

הגרסה המקורית של pg_repack תמיד קרסה בהוספה הראשונה, הגרסה ששונתה עבדה ללא שגיאות. גדול.

אנחנו הולכים לייצור ושוב מקבלים שגיאה באותו שלב של העתקת נתונים מטבלת היומן לאחד חדש:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

מצב קלאסי: הכל עובד בסביבות בדיקה, אבל לא בייצור?!

APPLY_COUNT והצומת של שתי קבוצות

התחלנו לנתח את הקוד ממש שורה אחר שורה וגילינו נקודה חשובה: נתונים מועברים מטבלת היומן לחדשה באצווה, הקבוע APPLY_COUNT הצביע על גודל האצווה:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

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

APPLY_COUNT שווה ל-1000 רשומות, מה שמסביר מדוע הבדיקות שלנו היו מוצלחות - הן לא כיסו את המקרה של "צומת אצווה". השתמשנו בשתי פקודות - הוספה ועדכון, כך שבדיוק 500 טרנזקציות של שתי פקודות תמיד הוצבו באצווה ולא נתקלנו בבעיות. לאחר הוספת העדכון השני, העריכה שלנו הפסיקה לעבוד:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

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

סירוב מאצווה

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

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

אז החלטנו להתחיל עם האפשרות הראשונה, מכיוון שהיא פשוטה יותר. ראשית, היה צורך להבין האם עסקה ארוכה תהווה בעיה אמיתית. מכיוון שההעברה העיקרית של הנתונים מהטבלה הישנה לחדשה מתרחשת גם בעסקה אחת ארוכה, השאלה הפכה ל"כמה נגדיל את העסקה הזו?" משך העסקה הראשונה תלוי בעיקר בגודל השולחן. משך הזמן של חדש תלוי בכמה שינויים מצטברים בטבלה במהלך העברת הנתונים, כלומר. על עוצמת העומס. ריצת pg_repack התרחשה בזמן של עומס שירות מינימלי, ונפח השינויים היה קטן באופן לא פרופורציונלי בהשוואה לגודל המקורי של הטבלה. החלטנו שאנחנו יכולים להזניח את הזמן של עסקה חדשה (לשם השוואה, בממוצע זה 1 שעה ו-2-3 דקות).

הניסויים היו חיוביים. השקה גם בהפקה. לשם הבהירות, הנה תמונה בגודל של אחד מבסיסי הנתונים לאחר ההפעלה:

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

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

ממצאים

מה אנחנו יכולים להמליץ ​​על סמך הניסיון שלנו:

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

מקור: www.habr.com