🥇DBA: mahusay na ayusin ang pag-synchronize at pag-import

Para sa kumplikadong pagproseso ng malalaking set ng data (iba Mga proseso ng ETL: mga pag-import, mga conversion at pag-synchronize sa isang panlabas na pinagmulan) kadalasang mayroong pangangailangan pansamantalang "tandaan" at agad na iproseso isang bagay na napakalaki.

Ang isang tipikal na gawain ng ganitong uri ay karaniwang parang ganito: "Dito departamento ng accounting na diskargado mula sa bangko ng kliyente ang huling natanggap na mga pagbabayad, kailangan mong mabilis na i-upload ang mga ito sa website at i-link ang mga ito sa iyong mga account.”

Ngunit kapag ang dami ng "isang bagay" na ito ay nagsimulang sukatin sa daan-daang megabytes, at ang serbisyo ay dapat na patuloy na gumana sa database 24x7, maraming mga side effect ang lumitaw na sumira sa iyong buhay.

Upang harapin ang mga ito sa PostgreSQL (at hindi lamang dito), maaari kang gumamit ng ilang mga pag-optimize na magbibigay-daan sa iyong iproseso ang lahat nang mas mabilis at may mas kaunting pagkonsumo ng mapagkukunan.

1. Saan ipapadala?

Una, magpasya tayo kung saan natin mai-upload ang data na gusto nating "iproseso."

1.1. Mga pansamantalang talahanayan (TEMPORARY TABLE)

Sa prinsipyo, para sa PostgreSQL pansamantalang mga talahanayan ay pareho sa anumang iba pa. Samakatuwid, tulad ng mga pamahiin "Lahat ng bagay doon ay nakaimbak lamang sa memorya, at maaari itong magwakas". Ngunit mayroon ding ilang makabuluhang pagkakaiba.

Ang iyong sariling "namespace" para sa bawat koneksyon sa database

Kung ang dalawang koneksyon ay subukang kumonekta sa parehong oras CREATE TABLE x, tapos may makukuha talaga error sa hindi natatangi mga bagay sa database.

Ngunit kung parehong subukang isagawa CREATE TEMPORARY TABLE x, pagkatapos ay pareho itong gagawin nang normal, at lahat ay makakakuha iyong kopya mga mesa. At walang magiging pagkakatulad sa pagitan nila.

"Self-destruct" kapag dinidiskonekta

Kapag ang koneksyon ay sarado, ang lahat ng mga pansamantalang talahanayan ay awtomatikong tatanggalin, kaya mano-mano DROP TABLE x walang patutunguhan kundi...

Kung nagsusumikap ka pgbouncer sa transaction mode, pagkatapos ay patuloy na naniniwala ang database na ang koneksyon na ito ay aktibo pa rin, at sa loob nito ay umiiral pa rin ang pansamantalang talahanayang ito.

Samakatuwid, ang pagsisikap na gawin itong muli, mula sa ibang koneksyon sa pgbouncer, ay magreresulta sa isang error. Ngunit ito ay maaaring iwasan sa pamamagitan ng paggamit CREATE TEMPORARY TABLE IF NOT EXISTS x.

Totoo, mas mahusay na huwag gawin ito, dahil maaari mong "biglang" mahanap doon ang data na natitira mula sa "nakaraang may-ari". Sa halip, mas mainam na basahin ang manu-manong at makita na kapag lumilikha ng talahanayan ay posibleng magdagdag ON COMMIT DROP - ibig sabihin, kapag nakumpleto ang transaksyon, awtomatikong tatanggalin ang talahanayan.

Hindi pagtitiklop

Dahil nabibilang lamang ang mga ito sa isang partikular na koneksyon, ang mga pansamantalang talahanayan ay hindi ginagaya. Pero inaalis nito ang pangangailangan para sa dobleng pag-record ng data sa heap + WAL, kaya ang INSERT/UPDATE/DELETE dito ay mas mabilis.

Ngunit dahil ang isang pansamantalang talahanayan ay isang "halos ordinaryong" talahanayan pa rin, hindi rin ito maaaring gawin sa isang replika. Hindi bababa sa ngayon, kahit na ang kaukulang patch ay umiikot sa mahabang panahon.

1.2. UNLOGGED TABLE

Ngunit ano ang dapat mong gawin, halimbawa, kung mayroon kang ilang uri ng masalimuot na proseso ng ETL na hindi maipapatupad sa loob ng isang transaksyon, ngunit mayroon ka pa ring pgbouncer sa transaction mode? ..

O kaya napakalaki ng daloy ng data Walang sapat na bandwidth sa isang koneksyon mula sa isang database (basahin, isang proseso bawat CPU)?..

O may mga operasyon na nangyayari asynchronously sa iba't ibang koneksyon?..

Mayroon lamang isang pagpipilian dito - pansamantalang lumikha ng isang hindi pansamantalang talahanayan. Pun, oo. Yan ay:

lumikha ng "aking sarili" na mga talahanayan na may pinakamaraming random na mga pangalan upang hindi magsalubong sa sinuman
Kunin: pinunan sila ng data mula sa isang panlabas na pinagmulan
Ibahin ang anyo: na-convert, napunan ng mga key linking field
Load: nagbuhos ng handa na data sa mga target na talahanayan
tinanggal ang "aking" mga talahanayan

At ngayon - isang langaw sa pamahid. Sa katunayan, lahat ng pagsusulat sa PostgreSQL ay nangyayari nang dalawang beses - una sa WAL, pagkatapos ay sa talahanayan/index na katawan. Ginagawa ang lahat ng ito upang suportahan ang ACID at tamang visibility ng data sa pagitan COMMIT'nutty at ROLLBACK'null transactions.

Ngunit hindi natin ito kailangan! Nasa amin ang buong proseso Alinman ito ay ganap na matagumpay o hindi.. Hindi mahalaga kung gaano karaming mga intermediate na transaksyon ang magkakaroon - hindi kami interesado sa "pagpapatuloy ng proseso mula sa gitna," lalo na kapag hindi malinaw kung nasaan ito.

Upang gawin ito, ang mga nag-develop ng PostgreSQL, pabalik sa bersyon 9.1, ay nagpakilala ng isang bagay bilang UNLOGGED na mga talahanayan:

Gamit ang indikasyon na ito, ang talahanayan ay nilikha bilang hindi naka-log. Ang data na isinulat sa hindi naka-log na mga talahanayan ay hindi dumaan sa write-ahead log (tingnan ang Kabanata 29), na nagiging sanhi ng naturang mga talahanayan upang gumana nang mas mabilis kaysa karaniwan. Gayunpaman, hindi sila immune sa kabiguan; sa kaso ng pagkabigo ng server o emergency shutdown, isang hindi naka-log na talahanayan awtomatikong naputol. Bukod pa rito, ang mga nilalaman ng naka-unlog na talahanayan hindi ginagaya sa mga slave server. Awtomatikong nagiging hindi naka-log ang anumang mga index na ginawa sa isang naka-unlog na talahanayan.

Sa maikling salita, ito ay magiging mas mabilis, ngunit kung ang database server ay "bumagsak", ito ay hindi kanais-nais. Ngunit gaano kadalas ito nangyayari, at alam ba ng iyong proseso ng ETL kung paano itama ito nang tama "mula sa gitna" pagkatapos "i-revitalize" ang database?..

Kung hindi, at ang kaso sa itaas ay katulad ng sa iyo, gamitin UNLOGGEDngunit hindi kailanman huwag paganahin ang katangiang ito sa mga totoong talahanayan, ang data mula sa kung saan ay mahal sa iyo.

1.3. ON COMMIT { DELETE ROWS | DROP}

Binibigyang-daan ka ng construct na ito na tukuyin ang awtomatikong pag-uugali kapag nakumpleto ang isang transaksyon kapag gumagawa ng talahanayan.

Tungkol sa ON COMMIT DROP Naisulat ko na sa itaas, ito ay bumubuo DROP TABLE, pero may ON COMMIT DELETE ROWS ang sitwasyon ay mas kawili-wili - ito ay nabuo dito TRUNCATE TABLE.

Dahil ang buong imprastraktura para sa pag-iimbak ng meta-deskripsyon ng isang pansamantalang talahanayan ay eksaktong kapareho ng sa isang regular na talahanayan, kung gayon Ang patuloy na paglikha at pagtanggal ng mga pansamantalang talahanayan ay humahantong sa matinding "pamamaga" ng mga talahanayan ng system pg_class, pg_attribute, pg_attrdef, pg_depend,…

Ngayon isipin na mayroon kang isang manggagawa sa isang direktang koneksyon sa database, na nagbubukas ng isang bagong transaksyon bawat segundo, lumilikha, pumupuno, nagpoproseso at nagtatanggal ng isang pansamantalang talahanayan... Magkakaroon ng labis na basura na naipon sa mga talahanayan ng system, at magdudulot ito ng dagdag na preno para sa bawat operasyon.

Sa pangkalahatan, huwag gawin ito! Sa kasong ito, ito ay mas epektibo CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS alisin ito sa cycle ng transaksyon - pagkatapos ay sa simula ng bawat bagong transaksyon ang mga talahanayan ay na ay iiral (i-save ang isang tawag CREATE), ngunit magiging walang laman, Salamat kay TRUNCATE (na-save din namin ang tawag nito) nang makumpleto ang nakaraang transaksyon.

1.4. KATULAD...KASAMA...

Nabanggit ko sa simula na ang isa sa mga karaniwang kaso ng paggamit para sa mga pansamantalang talahanayan ay iba't ibang uri ng mga pag-import - at ang developer ay pagod na kinokopya-paste ang listahan ng mga patlang ng target na talahanayan sa deklarasyon ng kanyang pansamantalang...

Ngunit ang katamaran ang makina ng pag-unlad! kaya lang lumikha ng bagong talahanayan "batay sa sample" maaari itong maging mas simple:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Dahil makakabuo ka ng maraming data sa talahanayang ito, hindi magiging mabilis ang paghahanap dito. Ngunit mayroong isang tradisyonal na solusyon dito - mga index! At oo, ang isang pansamantalang talahanayan ay maaari ding magkaroon ng mga index.

Dahil, madalas, ang mga kinakailangang index ay nag-tutugma sa mga index ng target na talahanayan, maaari mo lamang isulat LIKE target_table INCLUDING INDEXES.

Kung kailangan mo rin DEFAULT-values (halimbawa, upang punan ang mga pangunahing halaga ng key), maaari mong gamitin LIKE target_table INCLUDING DEFAULTS. O kaya lang- LIKE target_table INCLUDING ALL — kinopya ang mga default, index, hadlang,...

Ngunit dito kailangan mong maunawaan na kung nilikha mo mag-import kaagad ng talahanayan na may mga index, pagkatapos ay mas magtatagal ang pag-load ng datakaysa sa kung una mong punan ang lahat, at pagkatapos ay i-roll up ang mga index - tingnan kung paano ito ginagawa bilang isang halimbawa pg_dump.

Sa pangkalahatan, RTFM!

2. Paano sumulat?

Sabihin ko lang - gamitin ito COPY-daloy sa halip na "pack" INSERT, acceleration minsan. Maaari ka ring direkta mula sa isang paunang nabuong file.

3. Paano iproseso?

Kaya, hayaan nating magmukhang ganito ang ating intro:

mayroon kang isang talahanayan na may data ng kliyente na nakaimbak sa iyong database 1M record
araw-araw na pinadalhan ka ng isang kliyente ng bago buong "larawan"
mula sa karanasan alam mo na paminsan-minsan hindi hihigit sa 10K na tala ang nabago

Ang isang klasikong halimbawa ng ganitong sitwasyon ay base ng KLADR — maraming mga address sa kabuuan, ngunit sa bawat lingguhang pag-upload ay napakakaunting mga pagbabago (pagpapalit ng pangalan ng mga pamayanan, pagsasama-sama ng mga kalye, hitsura ng mga bagong bahay) kahit na sa pambansang saklaw.

3.1. Buong algorithm ng pag-synchronize

Para sa pagiging simple, sabihin nating hindi mo na kailangan pang baguhin ang data - dalhin lang ang talahanayan sa nais na form, iyon ay:

alisin lahat ng bagay na wala na
mag-upgrade lahat ng mayroon na at kailangang i-update
ipasok lahat ng hindi pa nangyayari

Bakit dapat gawin ang mga operasyon sa ganitong pagkakasunud-sunod? Dahil ito ay kung paano ang laki ng talahanayan ay lalago nang kaunti (tandaan MVCC!).

TANGGALIN MULA SA dst

Hindi, siyempre makakapagpatuloy ka sa dalawang operasyon lang:

alisin (DELETE) lahat sa pangkalahatan
ipasok lahat mula sa bagong imahe

Ngunit sa parehong oras, salamat sa MVCC, Ang laki ng talahanayan ay tataas nang eksaktong dalawang beses! Ang pagkuha ng +1M na larawan ng mga tala sa talahanayan dahil sa isang 10K na update ay sobrang redundancy...

TRUNCATE dst

Alam ng isang mas may karanasan na developer na ang buong tablet ay maaaring linisin nang mura:

upang i-clear (TRUNCATE) ang buong mesa
ipasok lahat mula sa bagong imahe

Ang pamamaraan ay epektibo, minsan medyo applicable, ngunit may problema... Magdaragdag kami ng 1M na tala sa mahabang panahon, kaya hindi namin kayang iwanang walang laman ang talahanayan sa lahat ng oras na ito (tulad ng mangyayari nang hindi ito binabalot sa isang transaksyon).

Ibig sabihin:

nagsisimula na kami matagal na transaksyon
TRUNCATE nagpapataw I-access ang Eksklusibo-pagharang
ginagawa namin ang pagpapasok sa loob ng mahabang panahon, at ang iba pa sa oras na ito hindi pwede SELECT

May hindi maganda...

ALTER TABLE... RENAME... / DROP TABLE...

Ang isang alternatibo ay punan ang lahat sa isang hiwalay na bagong talahanayan, at pagkatapos ay palitan lamang ang pangalan nito sa halip na ang luma. Ang ilang mga pangit na maliliit na bagay:

pa rin I-access ang Eksklusibo, kahit na makabuluhang mas kaunting oras
lahat ng query plan/statistics para sa table na ito ay ni-reset, kailangang tumakbo ANALYZE
lahat ng foreign key ay sira (FK) sa mesa

Mayroong WIP patch mula kay Simon Riggs na nagmungkahi ng paggawa ALTER-isang operasyon upang palitan ang katawan ng talahanayan sa antas ng file, nang hindi hinahawakan ang mga istatistika at FK, ngunit hindi nakakolekta ng korum.

I-DELETE, I-UPDATE, INSERT

Kaya, naninirahan kami sa opsyon na hindi pagharang ng tatlong operasyon. Halos tatlo... Paano ito gagawin nang mas epektibo?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Mag-import ng post-processing

Sa parehong KLADR, ang lahat ng nabagong talaan ay dapat na dagdagan na patakbuhin sa pamamagitan ng post-processing - na-normalize, naka-highlight ang mga keyword, at binawasan sa mga kinakailangang istruktura. Pero paano mo malalaman- kung ano ang eksaktong nagbagonang hindi ginagawang kumplikado ang code ng pag-synchronize, mas mabuti nang hindi ito hinahawakan?

Kung ang iyong proseso lang ang may access sa pagsulat sa oras ng pag-synchronize, maaari kang gumamit ng trigger na kokolekta sa lahat ng mga pagbabago para sa amin:

-- целевые таблицы
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;

Ngayon ay maaari na tayong maglapat ng mga trigger bago simulan ang pag-synchronize (o paganahin ang mga ito sa pamamagitan ng 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();

At pagkatapos ay mahinahon naming kinukuha ang lahat ng mga pagbabagong kailangan namin mula sa mga log table at patakbuhin ang mga ito sa pamamagitan ng mga karagdagang handler.

3.3. Pag-import ng Mga Naka-link na Set

Sa itaas ay isinasaalang-alang namin ang mga kaso kapag ang mga istruktura ng data ng pinagmulan at patutunguhan ay pareho. Ngunit paano kung ang pag-upload mula sa isang panlabas na sistema ay may format na iba sa istraktura ng imbakan sa aming database?

Kunin natin bilang halimbawa ang storage ng mga kliyente at ang kanilang mga account, ang klasikong opsyong "many-to-one":

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)
);

Ngunit ang pag-download mula sa isang panlabas na mapagkukunan ay dumating sa amin sa anyo ng "lahat sa isa":

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

Malinaw, ang data ng customer ay maaaring ma-duplicate sa bersyong ito, at ang pangunahing tala ay "account":

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

Para sa modelo, ilalagay lang namin ang aming data ng pagsubok, ngunit tandaan - COPY mas mahusay!

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);

Una, bigyang-diin natin ang mga "pagputol" na tinutukoy ng ating "mga katotohanan". Sa aming kaso, ang mga invoice ay tumutukoy sa mga customer:

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

Upang maiugnay nang tama ang mga account sa mga customer ID, kailangan muna naming alamin o buuin ang mga identifier na ito. Magdagdag tayo ng mga field sa ilalim ng mga ito:

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

Gamitin natin ang paraan ng pag-synchronize ng talahanayan na inilarawan sa itaas na may maliit na pag-amyenda - hindi kami mag-a-update o magde-delete ng anuman sa target na talahanayan, dahil nag-i-import kami ng mga kliyente na "idagdag-lamang":

-- проставляем в таблице импорта 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; -- прикладной ключ

Actually, nasa loob na lahat invoice_import Ngayon ay napunan na namin ang field ng contact client_id, kung saan ilalagay namin ang invoice.

Pinagmulan: www.habr.com

DBA: mahusay na ayusin ang mga pag-synchronize at pag-import