🥇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 - یعنی با تکمیل تراکنش، جدول به طور خودکار حذف می شود.

عدم تکرار

از آنجا که آنها فقط به یک اتصال خاص تعلق دارند، جداول موقت تکرار نمی شوند. ولی این امر نیاز به ثبت مضاعف داده ها را از بین می برد در heap + WAL، بنابراین INSERT/UPDATE/DELETE در آن به طور قابل توجهی سریعتر است.

اما از آنجایی که یک جدول موقت هنوز یک جدول "تقریبا معمولی" است، نمی توان آن را روی یک ماکت نیز ایجاد کرد. حداقل در حال حاضر، اگرچه پچ مربوطه برای مدت طولانی در گردش بوده است.

1.2. میز بدون ثبت نام

اما چه باید کرد، برای مثال، اگر نوعی فرآیند ETL دست و پا گیر دارید که در یک تراکنش قابل پیاده سازی نیست، اما هنوز دارید pgbouncer در حالت تراکنش؟ ..

یا جریان داده آنقدر زیاد است که پهنای باند کافی در یک اتصال وجود ندارد از یک پایگاه داده (بخوانید، یک پردازش در هر CPU)؟..

یا چند عملیات در حال انجام است به صورت ناهمزمان در اتصالات مختلف؟..

اینجا فقط یک گزینه وجود دارد - به طور موقت یک جدول غیر موقت ایجاد کنید. جناس، آره به این معنا که:

جداول "خودم" را با نام های حداکثر تصادفی ایجاد کرد تا با کسی تلاقی نداشته باشد
عصاره: آنها را با داده های یک منبع خارجی پر کرد
دگرگون کردن: تبدیل شده، در فیلدهای پیوند کلیدی پر شده است
بار: داده های آماده را در جداول هدف ریخت
جداول "من" را حذف کرد

و اکنون - یک مگس در پماد. در حقیقت، همه نوشته ها در PostgreSQL دو بار اتفاق می افتد - اولین بار در وال، سپس به بدنه های جدول/شاخص. همه اینها برای پشتیبانی از ACID و مشاهده صحیح داده ها بین آنها انجام می شود COMMITآجیل و ROLLBACKتراکنش های پوچ

اما ما به این نیاز نداریم! ما کل فرآیند را داریم یا کاملاً موفق بود یا نبود.. مهم نیست که چند تراکنش میانی وجود خواهد داشت - ما علاقه ای به "ادامه روند از وسط" نداریم، به خصوص زمانی که مشخص نیست کجا بوده است.

برای انجام این کار، توسعه دهندگان PostgreSQL، در نسخه 9.1، چیزی را معرفی کردند جداول UNLOGGED:

با این علامت، جدول به صورت ثبت نشده ایجاد می شود. داده های نوشته شده در جداول لاگین نشده از لاگ پیش از نوشتن عبور نمی کند (به فصل 29 مراجعه کنید) و باعث می شود چنین جداولی خیلی سریعتر از حد معمول کار کنید. با این حال، آنها از شکست مصون نیستند. در صورت خرابی سرور یا خاموش شدن اضطراری، یک جدول لاگین نشده است به طور خودکار کوتاه شده است. علاوه بر این، محتویات جدول ثبت نشده است تکرار نشده است به سرورهای برده هر فهرستی که روی یک جدول لاگین نشده ایجاد می‌شود، به‌طور خودکار از سیستم خارج می‌شود.

به طور خلاصه بسیار سریعتر خواهد بود، اما اگر سرور پایگاه داده "سقوط" کند، ناخوشایند خواهد بود. اما هر چند وقت یک‌بار این اتفاق می‌افتد، و آیا فرآیند ETL شما می‌داند چگونه پس از «احیای» پایگاه‌داده، آن را به درستی «از وسط» تصحیح کند؟

اگر نه، و مورد بالا مشابه مورد شماست، استفاده کنید UNLOGGEDولی هرگز این ویژگی را در جداول واقعی فعال نکنید، داده هایی که از آن برای شما عزیز است.

1.3. ON COMIT {حذف ردیف ها | قطره}

این ساختار به شما این امکان را می دهد که رفتار خودکار را هنگام تکمیل تراکنش هنگام ایجاد جدول مشخص کنید.

بر 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-values (به عنوان مثال، برای پر کردن مقادیر کلید اصلی)، می توانید استفاده کنید LIKE target_table INCLUDING DEFAULTS. یا به سادگی - LIKE target_table INCLUDING ALL - کپی پیش فرض ها، فهرست ها، محدودیت ها، ...

اما در اینجا باید درک کنید که اگر ایجاد کردید جدول را بلافاصله با شاخص ها وارد کنید، سپس بارگیری داده ها بیشتر طول می کشداگر ابتدا همه چیز را پر کنید، و فقط پس از آن فهرست ها را جمع کنید - به عنوان مثال ببینید چگونه این کار را انجام می دهد pg_dump.

به طور کلی، RTFM!

2. چگونه بنویسیم؟

بگذارید فقط بگویم - از آن استفاده کنید COPY-جریان به جای "بسته" INSERT, شتاب در مواقعی. حتی می توانید مستقیماً از یک فایل از پیش تولید شده استفاده کنید.

3. چگونه پردازش کنیم؟

بنابراین، اجازه دهید مقدمه ما چیزی شبیه به این باشد:

شما یک جدول با داده های مشتری ذخیره شده در پایگاه داده خود دارید رکورد 1 میلیون
هر روز مشتری یک مشتری جدید برای شما ارسال می کند "تصویر" کامل
از تجربه شما می دانید که هر از گاهی بیش از 10 هزار رکورد تغییر نمی کند

یک مثال کلاسیک از چنین وضعیتی است پایگاه KLADR — در کل آدرس های زیادی وجود دارد، اما در هر بارگذاری هفتگی تغییرات بسیار کمی (تغییر نام شهرک ها، ترکیب خیابان ها، ظاهر خانه های جدید) حتی در مقیاس ملی وجود دارد.

3.1. الگوریتم همگام سازی کامل

برای سادگی، بیایید بگوییم که شما حتی نیازی به تغییر ساختار داده ندارید - فقط جدول را به شکل دلخواه بیاورید، یعنی:

حذف هر چیزی که دیگر وجود ندارد
تازه کردن همه چیزهایی که قبلا وجود داشته و باید به روز شوند
درج کنید همه چیزهایی که هنوز اتفاق نیفتاده است

چرا باید عملیات به این ترتیب انجام شود؟ زیرا به این صورت است که اندازه میز به حداقل می رسد (MVCC را به خاطر بسپار!).

حذف از dst

نه، البته شما می توانید تنها با دو عملیات به نتیجه برسید:

حذف (DELETE) همه چیز به طور کلی
درج کنید همه از تصویر جدید

اما در عین حال، به لطف MVCC، اندازه جدول دقیقا دو برابر افزایش می یابد! دریافت +1 میلیون تصویر از رکوردها در جدول به دلیل به روز رسانی 10K بسیار اضافی است...

TRUNCATE dst

یک توسعه دهنده با تجربه تر می داند که کل تبلت را می توان کاملاً ارزان تمیز کرد:

برای روشن شدن (TRUNCATE) کل جدول
درج کنید همه از تصویر جدید

روش موثر است، گاهی اوقات کاملا قابل اجرا است، اما مشکلی وجود دارد... ما برای مدت طولانی 1 میلیون رکورد اضافه خواهیم کرد، بنابراین نمی توانیم جدول را برای تمام این مدت خالی بگذاریم (همانطور که بدون بسته بندی آن در یک تراکنش اتفاق می افتد).

یعنی:

ما شروع می کنیم معامله طولانی مدت
TRUNCATE تحمیل می کند دسترسی انحصاری-مسدود کردن
ما درج را برای مدت طولانی انجام می دهیم، و بقیه در این زمان حتی نمی تواند SELECT

یه چیزی خوب پیش نمیره...

تغییر جدول… تغییر نام… / رها کردن جدول…

یک جایگزین این است که همه چیز را در یک جدول جدید جداگانه پر کنید و سپس به سادگی آن را به جای جدول قدیمی تغییر نام دهید. چند تا چیز کوچک بد:

هنوز هم دسترسی انحصاری، اگرچه زمان قابل توجهی کمتر است
همه طرح‌ها/آمار پرس و جو برای این جدول بازنشانی می‌شوند، باید ANALYZE را اجرا کنید
تمام کلیدهای خارجی خراب است (FK) به میز

یک پچ WIP از Simon Riggs وجود داشت که پیشنهاد ساخت را می داد 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

DBA: هماهنگ سازی ها و واردات را به درستی سازماندهی می کند