DBA: ארגן במיומנות סנכרון וייבוא

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

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

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

כדי להתמודד איתם ב-PostgreSQL (ולא רק בו), תוכלו להשתמש בכמה אופטימיזציות שיאפשרו לכם לעבד הכל מהר יותר ועם פחות צריכת משאבים.

1. לאן לשלוח?

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

1.1. טבלאות זמניות (TEMPORARY TABLE)

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

"מרחב שמות" משלך עבור כל חיבור למסד הנתונים

אם שני חיבורים נסה להתחבר בו זמנית CREATE TABLE x, אז מישהו בהחלט יקבל שגיאת אי ייחוד אובייקטי מסד נתונים.

אבל אם שניהם מנסים לבצע CREATE TEMPORARY TABLE x, אז שניהם יעשו זאת כרגיל, וכולם יקבלו העותק שלך שולחנות. ולא יהיה שום דבר משותף ביניהם.

"הרס עצמי" בעת ניתוק

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

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

לכן, ניסיון ליצור אותו שוב, מחיבור אחר ל-pgbouncer, יגרום לשגיאה. אבל ניתן לעקוף זאת על ידי שימוש CREATE TEMPORARY TABLE IF NOT EXISTS x.

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

אי שכפול

מכיוון שהם שייכים רק לחיבור מסוים, טבלאות זמניות אינן משוכפלות. אבל זה מבטל את הצורך ברישום כפול של נתונים בערימה + WAL, אז INSERT/UPDATE/DELETE לתוכו הוא הרבה יותר מהיר.

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

1.2. טבלה לא רשומה

אבל מה עליך לעשות, למשל, אם יש לך איזשהו תהליך ETL מסורבל שלא ניתן ליישם בעסקה אחת, אבל עדיין יש לך pgbouncer במצב עסקה? ..

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

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

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

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

ועכשיו - זבוב במשחה. למעשה, כל הכתיבה ב-PostgreSQL מתרחשת פעמיים - ראשון ב-WAL, ואז לתוך גופי הטבלה/אינדקס. כל זה נעשה כדי לתמוך ב-ACID ולנראות נתונים נכונה ביניהם COMMIT' מטורף ו ROLLBACK'עסקאות אפס.

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

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

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

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

אם לא, והמקרה שלמעלה דומה לשלך, השתמש UNLOGGEDאבל אף פעם אל תפעיל את התכונה הזו בטבלאות אמיתיות, שהנתונים מהם יקרים לך.

1.3. ב-COMMIT { מחק שורות | יְרִידָה}

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

על ON COMMIT DROP כבר כתבתי למעלה, זה מייצר DROP TABLE, אבל עם ON COMMIT DELETE ROWS המצב יותר מעניין - הוא נוצר כאן TRUNCATE TABLE.

מכיוון שכל התשתית לאחסון המטא-תיאור של טבלה זמנית זהה לחלוטין לזו של טבלה רגילה, אז יצירה ומחיקה מתמדת של טבלאות זמניות מובילה ל"נפיחות" חמורה של טבלאות המערכת pg_class, pg_attribute, pg_attrdef, pg_depend,...

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

באופן כללי, אל תעשה את זה! במקרה הזה זה הרבה יותר יעיל CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS להוציא אותו ממחזור העסקאות - אז בתחילת כל עסקה חדשה הטבלאות כבר נמצאות יהיה קיים (שמור שיחה CREATE), אבל יהיה ריק, הודות ל TRUNCATE (שמרנו גם את השיחה שלו) בעת השלמת העסקה הקודמת.

1.4. לייק...כולל...

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

אבל עצלות היא מנוע הקידמה! בגלל זה צור טבלה חדשה "מבוסס על מדגם" זה יכול להיות הרבה יותר פשוט:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

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

מכיוון שלעתים קרובות האינדקסים הנדרשים עולים בקנה אחד עם האינדקסים של טבלת היעד, אתה יכול פשוט לכתוב LIKE target_table INCLUDING INDEXES.

אם גם אתה צריך DEFAULT-ערכים (לדוגמה, כדי למלא את ערכי המפתח הראשי), אתה יכול להשתמש LIKE target_table INCLUDING DEFAULTS. או בפשטות - LIKE target_table INCLUDING ALL - מעתיק ברירות מחדל, אינדקסים, אילוצים,...

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

באופן כללי RTFM!

2. איך כותבים?

תן לי רק לומר - השתמש בו COPY-זרימה במקום "חבילה" INSERT, האצה לפעמים. אתה יכול אפילו ישירות מקובץ שנוצר מראש.

3. איך לעבד?

אז בוא נגיד שהמבוא שלנו נראה בערך כך:

• יש לך טבלה עם נתוני לקוח המאוחסנים במסד הנתונים שלך 1 מיליון שיאים
• כל יום לקוח שולח לך אחד חדש "תמונה" מלאה
• מניסיון אתה יודע את זה מדי פעם לא יותר מ-10K רשומות משתנות

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

3.1. אלגוריתם סנכרון מלא

לשם הפשטות, נניח שאתה אפילו לא צריך לבנות מחדש את הנתונים - פשוט הכנס את הטבלה לצורה הרצויה, כלומר:

• להסיר כל מה שכבר לא קיים
• עדכון כל מה שכבר קיים וצריך לעדכן
• הכנס כל מה שעדיין לא קרה

למה צריך לעשות את הפעולות בסדר הזה? כי ככה גודל השולחן יגדל מינימלית (זכור MVCC!).

מחק מ-dst

לא, כמובן שאתה יכול להסתדר עם שתי פעולות בלבד:

• להסיר (DELETE) הכל באופן כללי
• הכנס הכל מהתמונה החדשה

אבל במקביל, הודות ל-MVCC, גודל השולחן יגדל בדיוק פעמיים! קבלת +1M תמונות של רשומות בטבלה עקב עדכון של 10K היא כל כך יתירות...

TRUNCATE dst

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

• ברור (TRUNCATE) כל השולחן
• הכנס הכל מהתמונה החדשה

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

אשר אומר:

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

משהו לא הולך טוב...

שנה טבלה... שנה שם... / זרוק טבלה...

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

• גם עדיין גישה בלעדית, אם כי פחות זמן משמעותית
• כל תוכניות השאילתה/נתונים סטטיסטיים עבור טבלה זו מאופסים, צריך להפעיל את ANALYZE
• כל המפתחות הזרים שבורים (FK) לשולחן

היה תיקון WIP מסיימון ריגס שהציע לעשות ALTER-פעולה להחלפת גוף הטבלה ברמת הקובץ, ללא נגיעה בסטטיסטיקה וב-FK, אך לא אספה מניין.

מחק, עדכן, הוסף

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

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие

-- вставляем отсутствующие
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. ייבוא ​​לאחר עיבוד

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

אם רק לתהליך שלך יש גישת כתיבה בזמן הסנכרון, אז אתה יכול להשתמש בטריגר שיאסוף עבורנו את כל השינויים:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

כעת אנו יכולים להחיל טריגרים לפני התחלת הסנכרון (או להפעיל אותם באמצעות ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

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

3.3. ייבוא ​​סטים מקושרים

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

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

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

אבל ההורדה ממקור חיצוני מגיעה אלינו בצורה של "הכל באחד":

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

ברור שניתן לשכפל נתוני לקוחות בגרסה זו, והרשומה העיקרית היא "חשבון":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

עבור המודל, פשוט נוסיף את נתוני הבדיקה שלנו, אך זכרו - COPY יותר יעיל!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

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

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

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

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

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

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

למעשה, הכל בפנים invoice_import עכשיו יש לנו את שדה יצירת הקשר שמולא client_id, איתה נכניס את החשבונית.

מקור: www.habr.com