DBA: kompetentingai organizuoti sinchronizavimą ir importavimą

Sudėtingam didelių duomenų rinkinių (skirtingų ETL procesai: importas, konvertavimas ir sinchronizavimas su išoriniu šaltiniu) dažnai yra poreikis laikinai „prisiminti“ ir nedelsiant greitai apdoroti kažkas didelės apimties.

Tipiška tokio pobūdžio užduotis paprastai skamba maždaug taip: "Štai čia buhalterija iškrauta iš kliento banko paskutinius gautus mokėjimus, turite juos greitai įkelti į svetainę ir susieti su savo paskyromis“.

Bet kai šio „kažko“ tūris pradeda matuotis šimtais megabaitų, o paslauga turi toliau dirbti su duomenų baze 24x7, atsiranda daug šalutinių poveikių, kurie sugadins jūsų gyvenimą.

Norėdami su jais susidoroti PostgreSQL (ir ne tik jame), galite naudoti tam tikrus optimizavimus, kurie leis viską apdoroti greičiau ir sunaudojant mažiau išteklių.

1. Kur siųsti?

Pirmiausia nuspręskime, kur galime įkelti duomenis, kuriuos norime „apdoroti“.

1.1. Laikini stalai (LAIKINA LENTELĖ)

Iš esmės PostgreSQL laikinosios lentelės yra tokios pačios kaip ir bet kurios kitos. Todėl prietarai mėgsta „Viskas saugoma tik atmintyje ir gali baigtis“. Tačiau yra ir keletas reikšmingų skirtumų.

Kiekvienam prisijungimui prie duomenų bazės turite savo „vardų erdvę“.

Jei du ryšiai bando prisijungti vienu metu CREATE TABLE x, tada kas nors tikrai gaus nepakartojamumo klaida duomenų bazės objektai.

Bet jei abu bando įvykdyti CREATE TEMPORARY TABLE x, tada abu tai darys normaliai, ir visi gaus jūsų kopija lenteles. Ir nieko bendro tarp jų nebus.

„Savęs sunaikinimas“ atsijungiant

Kai ryšys nutraukiamas, visos laikinosios lentelės automatiškai ištrinamos, taigi rankiniu būdu DROP TABLE x nėra jokios prasmės, išskyrus...

Jei dirbate per pgbouncer operacijos režimu, tada duomenų bazė ir toliau mano, kad šis ryšys vis dar aktyvus ir joje vis dar yra ši laikina lentelė.

Todėl bandant jį sukurti dar kartą, naudojant kitą ryšį su pgbouncer, atsiras klaida. Bet tai galima apeiti naudojant CREATE TEMPORARY TABLE IF NOT EXISTS x.

Tiesa, geriau to nedaryti, nes tada „staiga“ galite rasti duomenis, likusius iš „ankstesnio savininko“. Vietoj to daug geriau perskaityti vadovą ir pamatyti, kad kuriant lentelę galima pridėti ON COMMIT DROP - tai yra, kai operacija bus baigta, lentelė bus automatiškai ištrinta.

Nereplikacija

Kadangi jos priklauso tik konkrečiam ryšiui, laikinosios lentelės nėra kartojamos. Bet tai pašalina dvigubo duomenų įrašymo poreikį krūvoje + WAL, todėl INSERT/UPDATE/DELETE į jį vyksta žymiai greičiau.

Tačiau kadangi laikina lentelė vis dar yra „beveik įprasta“ lentelė, jos negalima sukurti ir kopijoje. Bent jau kol kas, nors atitinkamas pleistras cirkuliuoja jau seniai.

1.2. NEPRISIJUNGTAS STALAS

Bet ką daryti, pavyzdžiui, jei turite kokį nors sudėtingą ETL procesą, kurio negalima įgyvendinti per vieną operaciją, bet vis tiek turite pgbouncer operacijos režimu? ..

Arba duomenų srautas toks didelis, kad Vieno ryšio pralaidumo nepakanka iš duomenų bazės (skaityti, vienas procesas kiekvienam procesoriui)?..

Arba vyksta kokios nors operacijos asinchroniškai skirtinguose ryšiuose?..

Čia yra tik vienas variantas - laikinai sukurti nelaikiną lentelę. Kalba, taip. Tai yra:

• sukūrė "savo" lenteles su maksimaliai atsitiktiniais pavadinimais, kad su niekuo nesikirstų
• Išgauti: užpildė juos duomenimis iš išorinio šaltinio
• Transformuoti: konvertuota, užpildyti raktų susiejimo laukai
• Įkelti: supylė paruoštus duomenis į tikslines lenteles
• ištrynė „mano“ lenteles

O dabar – musė tepalu. Faktiškai, visi įrašai PostgreSQL nutinka du kartus - pirmiausia WAL, tada į lentelės / rodyklės elementus. Visa tai daroma siekiant palaikyti ACID ir ištaisyti duomenų matomumą tarp COMMIT' susirūpinęs ir ROLLBACK„Nulinės operacijos.

Bet mums to nereikia! Mes turime visą procesą Arba tai buvo visiškai sėkminga, arba ne.. Nesvarbu, kiek bus tarpinių sandorių – mes nesame suinteresuoti „tęsti procesą nuo vidurio“, ypač kai neaišku, kur jis buvo.

Norėdami tai padaryti, PostgreSQL kūrėjai, dar 9.1 versijoje, pristatė tokį dalyką kaip NEPRISIJUNGTI stalai:

Su šia nuoroda lentelė sukuriama kaip neužsiregistravusi. Duomenys, įrašyti į neužregistruotas lenteles, nepatenka per įrašymo į priekį žurnalą (žr. 29 skyrių), todėl tokios lentelės dirbti daug greičiau nei įprastai. Tačiau jie nėra apsaugoti nuo nesėkmės; serverio gedimo ar avarinio išjungimo atveju neužregistruota lentelė automatiškai sutrumpintas. Be to, neprisijungusios lentelės turinys nepakartojama prie vergų serverių. Bet kokie indeksai, sukurti neprisijungusioje lentelėje, automatiškai išjungiami.

Trumpai tariant, tai bus daug greičiau, bet jei duomenų bazės serveris „nukris“, bus nemalonu. Tačiau kaip dažnai tai nutinka ir ar jūsų ETL procesas žino, kaip tai teisingai ištaisyti „iš vidurio“ po „atgaivinimo“ duomenų bazės?

Jei ne, o aukščiau pateiktas atvejis panašus į jūsų, naudokite UNLOGGEDbet niekada neįjungti šio atributo tikrose lentelėse, kurių duomenys jums brangūs.

1.3. ĮSIPAREIGOJANT { IŠTRINTI EILTELES | DROP}

Ši konstrukcija leidžia nurodyti automatinį elgesį, kai kuriant lentelę užbaigiama operacija.

apie ON COMMIT DROP Jau rašiau aukščiau, tai generuoja DROP TABLE, bet su ON COMMIT DELETE ROWS situacija įdomesnė – čia generuojama TRUNCATE TABLE.

Kadangi visa laikinos lentelės metaaprašo saugojimo infrastruktūra yra lygiai tokia pati kaip ir įprastos lentelės, tada Nuolatinis laikinų lentelių kūrimas ir naikinimas sukelia didelį sistemos lentelių „brinkimą“. pg_class, pg_attribute, pg_attrdef, pg_depend,…

Dabar įsivaizduokite, kad turite tiesioginį ryšį su duomenų baze darbuotoją, kuris kas sekundę atidaro naują operaciją, sukuria, užpildo, apdoroja ir ištrina laikiną lentelę... Sisteminėse lentelėse susikaups perteklius šiukšlių ir tai sukels papildomų stabdžių kiekvienai operacijai.

Apskritai, nedarykite to! Šiuo atveju jis yra daug efektyvesnis CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS išimkite jį iš operacijų ciklo – tada kiekvienos naujos operacijos pradžioje lentelės jau yra egzistuos (išsaugokite skambutį CREATE), bet bus tuščia, ačiū TRUNCATE (taip pat išsaugojome jo skambutį) atlikdami ankstesnę operaciją.

1.4. PATINKA...ĮSKAITANT...

Pradžioje minėjau, kad vienas iš tipiškų laikinųjų lentelių naudojimo atvejų yra įvairus importas – ir kūrėjas pavargęs kopijuoja-įklijuoja tikslinės lentelės laukų sąrašą į savo laikinųjų...

Tačiau tinginystė yra pažangos variklis! Štai kodėl sukurti naują lentelę „pagal pavyzdį“ gali būti daug paprasčiau:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Kadangi tada į šią lentelę galite sugeneruoti daug duomenų, paieška joje niekada nebus greita. Tačiau yra tradicinis sprendimas – indeksai! Ir taip, laikinoje lentelėje taip pat gali būti indeksų.

Kadangi dažnai reikalingi indeksai sutampa su tikslinės lentelės indeksais, galite tiesiog parašyti LIKE target_table INCLUDING INDEXES.

Jei taip pat reikia DEFAULT-reikšmes (pavyzdžiui, norėdami užpildyti pirminio rakto reikšmes), galite naudoti LIKE target_table INCLUDING DEFAULTS. Arba tiesiog - LIKE target_table INCLUDING ALL - kopijuoja numatytuosius nustatymus, indeksus, apribojimus,...

Bet čia jūs turite suprasti, kad jei sukūrėte importuoti lentelę iš karto su indeksais, tada duomenų įkėlimas užtruks ilgiaunei jei iš pradžių viską užpildysite, o tik tada suverstumėte indeksus – pažiūrėkite, kaip tai daroma kaip pavyzdį pg_dump.

Apskritai, RTFM!

2. Kaip rašyti?

Pasakysiu tik – naudokitės COPY- srautas vietoj „pack“ INSERT, pagreitis kartais. Jūs netgi galite tiesiogiai iš iš anksto sugeneruoto failo.

3. Kaip apdoroti?

Taigi, tegul mūsų įvadas atrodo maždaug taip:

• turite lentelę su duomenų bazėje saugomais kliento duomenimis 1 milijonas įrašų
• kiekvieną dieną klientas atsiunčia jums naują visas "vaizdas"
• iš patirties žinai, kad laikas nuo laiko pakeičiama ne daugiau kaip 10 tūkst. įrašų

Klasikinis tokios situacijos pavyzdys yra KLADR bazė — adresų iš viso labai daug, tačiau kiekviename savaitiniame įkėlime pakeitimų (gyvenviečių pervadinimas, gatvių sujungimas, naujų namų atsiradimas) net šalies mastu labai mažai.

3.1. Pilnas sinchronizacijos algoritmas

Paprastumo dėlei tarkime, kad jums net nereikia pertvarkyti duomenų – tiesiog perkelkite lentelę į norimą formą, tai yra:

• pašalinti viskas, ko nebėra
• atnaujinti viskas, kas jau egzistavo ir turi būti atnaujinta
• įterpti viskas, kas dar neįvyko

Kodėl operacijos turi būti atliekamos tokia tvarka? Nes taip stalo dydis augs minimaliai (prisimink MVCC!).

IŠTRINTI IŠ dst

Ne, žinoma, galite atlikti tik dvi operacijas:

• pašalinti (DELETE) viskas apskritai
• įterpti viskas iš naujo įvaizdžio

Tačiau tuo pat metu MVCC dėka Stalo dydis padidės lygiai du kartus! Gauti +1 mln. įrašų vaizdų lentelėje dėl 10 XNUMX atnaujinimo yra labai nereikalinga...

TRUNCATE dst

Labiau patyręs kūrėjas žino, kad visą planšetinį kompiuterį galima išvalyti gana pigiai:

• aišku (TRUNCATE) visą lentelę
• įterpti viskas iš naujo įvaizdžio

Metodas yra veiksmingas, kartais visai tinka, bet yra bėda... Dar ilgai papildysime 1 mln. įrašų, todėl negalime sau leisti visą šį laiką palikti tuščią lentelę (kaip atsitiks jos nesuvyniojus į vieną operaciją).

Tai reiškia:

• mes pradedame ilgalaikis sandoris
• TRUNCATE primeta Išskirtinė prieiga- blokavimas
• mes darome įterpimą ilgą laiką, o visi kiti šiuo metu net negali SELECT

Kažkas nesiseka...

PAKEISTI LENTELĘ… Pervardykite… / NUMESTI LENTELĘ…

Alternatyva yra užpildyti viską į atskirą naują lentelę ir tiesiog pervadinti ją vietoj senosios. Pora nemalonių smulkmenų:

• dar irgi Išskirtinė prieiga, nors ir žymiai mažiau laiko
• visi šios lentelės užklausų planai / statistika yra nustatyti iš naujo, reikia paleisti ANALIZĖ
• visi svetimi raktai sulaužyti (FK) prie stalo

Buvo Simono Riggso WIP pleistras, kuriame buvo pasiūlyta padaryti ALTER-operacija, skirta lentelės korpusui pakeisti failo lygiu, neliečiant statistikos ir FK, tačiau nesurinktas kvorumas.

IŠTRINTI, ATNAUJINTI, Įterpti

Taigi, apsisprendžiame trijų operacijų neblokavimo parinktimi. Beveik trys... Kaip tai padaryti efektyviausiai?

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

Tame pačiame KLADR visi pakeisti įrašai turi būti papildomai apdorojami – normalizuojami, paryškinami raktiniai žodžiai ir sumažinamos iki reikiamų struktūrų. Bet kaip tu žinai - kas tiksliai pasikeitėneapsunkinus sinchronizacijos kodo, idealiu atveju jo visai neliečiant?

Jei tik jūsų procesas turi rašymo prieigą sinchronizavimo metu, galite naudoti aktyviklį, kuris už mus surinks visus pakeitimus:

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

Dabar galime pritaikyti aktyviklius prieš pradėdami sinchronizavimą (arba įgalinti juos naudodami 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();

Tada mes ramiai ištraukiame visus reikiamus pakeitimus iš žurnalų lentelių ir vykdome juos per papildomus tvarkykles.

3.3. Susietų rinkinių importavimas

Aukščiau aptarėme atvejus, kai šaltinio ir paskirties duomenų struktūros yra vienodos. O kas, jei įkėlimo iš išorinės sistemos formatas skiriasi nuo saugojimo struktūros mūsų duomenų bazėje?

Paimkime kaip pavyzdį klientų ir jų paskyrų saugojimą, klasikinę parinktį „daug prieš vieną“:

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

Tačiau atsisiuntimas iš išorinio šaltinio mums pateikiamas „viskas viename“ forma:

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

Akivaizdu, kad šioje versijoje klientų duomenys gali būti dubliuojami, o pagrindinis įrašas yra „sąskaita“:

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

Modeliui tiesiog įterpsime savo bandymo duomenis, bet atminkite - COPY efektyvesnis!

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

Pirmiausia paryškinkime tuos „kirpimus“, kuriuos nurodo mūsų „faktai“. Mūsų atveju sąskaitos faktūros yra skirtos klientams:

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

Norėdami teisingai susieti paskyras su klientų ID, pirmiausia turime išsiaiškinti arba sugeneruoti šiuos identifikatorius. Po jais pridėkime laukus:

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

Naudokime aukščiau aprašytą lentelės sinchronizavimo metodą su nedideliu pakeitimu – tikslinėje lentelėje nieko neatnaujinsime ir neištrinsime, nes klientus importuojame „tik pridėti“:

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

Tiesą sakant, viskas yra invoice_import Dabar užpildėme kontaktinį lauką client_id, su kuriuo įterpsime sąskaitą faktūrą.

Šaltinis: www.habr.com