🥇DBA: sinkronizazioa eta inportazioak modu egokian antolatu

Datu multzo handien tratamendu konplexurako (desberdinak ETL prozesuak: inportazioak, bihurketak eta kanpoko iturri batekin sinkronizatzea) askotan beharra dago aldi baterako "gogoratu" eta berehala azkar prozesatu zerbait bolumentsua.

Mota honetako zeregin tipiko batek honelako soinua izan ohi du: "Hementxe bezeroen bankutik deskargatutako kontabilitate saila jasotako azken ordainketak, webgunera azkar igo eta zure kontuekin lotu behar dituzu"

Baina "zerbait" horren bolumena ehunka megabytetan neurtzen hasten denean, eta zerbitzuak datu-basearekin 24x7 lanean jarraitu behar duenean, zure bizitza hondatuko duten albo-ondorio asko sortzen dira.

Horiei PostgreSQL-n (eta ez bertan bakarrik) aurre egiteko, dena azkarrago eta baliabide gutxiago kontsumituz prozesatzeko aukera emango duten optimizazio batzuk erabil ditzakezu.

1. Nora bidali?

Lehenik eta behin, erabaki dezagun non "prozesatu" nahi ditugun datuak kargatu ditzakegun.

1.1. Aldi baterako taulak (ALDIBATERAKO TAULA)

Printzipioz, PostgreSQLrentzat aldi baterako taulak beste edozeinen berdinak dira. Horregatik, sineskeriak bezalakoak "Han dena memorian bakarrik gordetzen da, eta amaitu daiteke". Baina badira hainbat desberdintasun esanguratsu ere.

Zure "izen-espazioa" datu-baserako konexio bakoitzeko

Bi konexio aldi berean konektatzen saiatzen badira CREATE TABLE x, orduan norbaitek lortuko du zalantzarik gabe ez berezitasun errorea datu-baseko objektuak.

Baina biak exekutatzen saiatzen badira CREATE TEMPORARY TABLE x, orduan biek normalean egingo dute, eta denek lortuko dute zure kopia mahaiak. Eta haien artean ez da ezer amankomunik izango.

"Autosuntsitu" deskonektatzean

Konexioa ixten denean, aldi baterako taula guztiak automatikoki ezabatzen dira, eskuz, beraz DROP TABLE x ez du ezertarako balio izan ezik...

Lanean ari bazara pgbouncer transakzio moduan, orduan datu-baseak konexio hau oraindik aktibo dagoela sinesten jarraitzen du, eta bertan aldi baterako taula hau oraindik existitzen da.

Hori dela eta, berriro sortzen saiatzeak, beste konexio batetik pgbouncer-era, errore bat eragingo du. Baina hori saihestu daiteke erabiliz CREATE TEMPORARY TABLE IF NOT EXISTS x.

Egia da, hobe da hori ez egitea hala ere, orduan "bat-batean" aurki ditzakezulako bertan "aurreko jabearen" geratzen diren datuak. Horren ordez, askoz hobe da eskuliburua irakurtzea eta taula bat sortzean gehitzea posible dela ikustea ON COMMIT DROP - hau da, transakzioa amaitzen denean, taula automatikoki ezabatuko da.

Erreplika eza

Konexio zehatz bati bakarrik dagozkionez, aldi baterako taulak ez dira errepikatzen. Baina horrek datuak bikoitzaren erregistroaren beharra ezabatzen du pila + WAL-n, beraz, txertatu/eguneratu/ezabatu askoz azkarragoa da.

Baina behin-behineko taula bat oraindik ere "ia arrunta" den taula bat denez, ezin da erreplika batean ere sortu. Oraingoz behintzat, dagokion adabakia aspalditik zirkulatzen ari den arren.

1.2. REGISTRATUTAKO TAULA

Baina zer egin behar duzu, adibidez, transakzio batean inplementatu ezin den ETL prozesu astuna baduzu, baina oraindik ere baduzu pgbouncer transakzio moduan? ..

Edo datu-fluxua hain handia da Ez dago nahikoa banda-zabalera konexio batean datu-base batetik (irakurri, prozesu bat CPU bakoitzeko)?...

Edo operazio batzuk egiten ari dira modu asinkronikoan lotura ezberdinetan?...

Hemen aukera bakarra dago - aldi baterako ez-aldi baterako mahai bat sortu. Puntua, bai. Hori da:

"nire propioak" taulak sortu zituen gehienez ausazko izenekin, inorekin gurutzatzeko
Erauzi: kanpoko iturri bateko datuekin bete ditu
Eraldatu: bihurtuta, lotzeko gako-eremuak beteta
Karga: prest dauden datuak helburu tauletan isurtzen ditu
"nire" taulak ezabatu ditu

Eta orain - euli bat ukenduan. Izan ere, PostgreSQL-en idazketa guztiak bi aldiz gertatzen dira - WALen lehenengoa, gero taula/aurkibideen gorputzetan sartu. Hori guztia ACID onartzeko eta datuen ikusgarritasuna zuzentzeko egiten da COMMIT'intxaurra eta ROLLBACK'transakzio nuluak.

Baina ez dugu hau behar! Prozesu osoa dugu Edo guztiz arrakastatsua izan zen edo ez.. Berdin du tarteko zenbat transakzio egongo diren - ez zaigu interesatzen "prozesua erditik jarraitzea", batez ere non zegoen argi ez dagoenean.

Horretarako, PostgreSQL garatzaileek, 9.1 bertsioan, horrelako gauza bat sartu zuten LOGGATUTAKO taulak:

Adierazpen honekin, taula erregistratu gabeko gisa sortzen da. Erregistratu gabeko tauletan idatzitako datuak ez dira idazketa aurreratuaren erregistrotik pasatzen (ikus 29. kapitulua), eta, ondorioz, taula horiek lan egin ohi baino askoz azkarrago. Hala ere, ez dira porrotatik salbu; zerbitzariaren hutsegite edo larrialdiko itzaltze kasuan, erregistratu gabeko taula bat automatikoki moztuta. Gainera, erregistratu gabeko taularen edukia ez errepikatu zerbitzari esklaboei. Erregistratu gabeko taula batean sortutako indizeak automatikoki erregistratu gabe geratzen dira.

Labur esanda, askoz azkarragoa izango da, baina datu-basearen zerbitzaria "erortzen" bada, desatsegina izango da. Baina zenbat maiz gertatzen da hori, eta zure ETL prozesuak badaki hori zuzen nola zuzentzen "erditik" datu-basea "biziberritu" ondoren?...

Hala ez bada, eta goiko kasua zurearen antzekoa bada, erabili UNLOGGED, baina inoiz ez ez gaitu atributu hau benetako tauletan, zuretzako maite duzun datuak.

1.3. ON COMMIT { EZABATU ERRENKADAK | DROP}

Eraikuntza honek portaera automatikoa zehazteko aukera ematen dizu transakzio bat osatzen denean taula bat sortzean.

Про ON COMMIT DROP Dagoeneko goian idatzi nuen, sortzen du DROP TABLE, baina ON COMMIT DELETE ROWS egoera interesgarriagoa da - hemen sortzen da TRUNCATE TABLE.

Aldi baterako taula baten meta-deskribapena gordetzeko azpiegitura osoa taula arrunt baten berdina denez, orduan Aldi baterako taulak etengabe sortzeak eta ezabatzeak sistema-taulen "hantura" larria dakar pg_class, pg_attribute, pg_attrdef, pg_depend,...

Orain imajinatu datu-basearekin zuzeneko konexioan dagoen langile bat duzula, eta horrek segunduro transakzio berri bat irekitzen duela, behin-behineko taula bat sortu, bete, prozesatu eta ezabatzen du... Sistemako tauletan zabor gehiegi pilatuko da, eta horrek balazta gehigarriak eragingo ditu eragiketa bakoitzean.

Oro har, ez egin hau! Kasu honetan askoz eraginkorragoa da CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS kendu transakzio-ziklotik - gero transakzio berri bakoitzaren hasieran taulak dagoeneko daude existituko da (gorde deia CREATE), baina hutsik egongo da, eskerrik asko TRUNCATE (bere deia ere gorde dugu) aurreko transakzioa osatzerakoan.

1.4. ATZENDU... BARNE...

Hasieran aipatu nuen behin-behineko taulen erabilera kasu tipikoetako bat hainbat inportazio mota direla - eta garatzaileak nekatuta kopiatzen du xede-taularen eremuen zerrenda bere aldi baterako deklarazioan...

Baina alferkeria da aurrerapenaren motorra! Horregatik sortu taula berri bat "laginaren arabera" askoz errazagoa izan daiteke:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Taula honetan datu asko sor ditzakezunez gero, bilaketa ez da inoiz azkarra izango. Baina horretarako irtenbide tradizional bat dago: indizeak! Eta, bai, aldi baterako taula batek indizeak ere izan ditzake.

Sarritan beharrezkoak diren indizeak helburu-taularen indizeekin bat datoz, besterik gabe idatzi dezakezu LIKE target_table INCLUDING INDEXES.

Zuk ere behar baduzu DEFAULT-balioak (adibidez, lehen gako-balioak betetzeko), erabil ditzakezu LIKE target_table INCLUDING DEFAULTS. Edo besterik gabe - LIKE target_table INCLUDING ALL — lehenetsiak, indizeak, mugak,... kopiatzen ditu

Baina hemen hori ulertu behar duzu sortu baduzu inportatu taula berehala indizeekin, orduan datuek denbora gehiago beharko dute kargatzekobaino lehen dena betetzen baduzu, eta ondoren indizeak bildu baino ez - begiratu nola egiten duen adibide gisa pg_dump.

Oro har, RTFM!

2. Nola idatzi?

Utzidazu esan: erabili COPY-fluxua "pack"-en ordez INSERT, azelerazioa batzuetan. Aurrez sortutako fitxategi batetik zuzenean ere egin dezakezu.

3. Nola prozesatu?

Beraz, utz dezagun gure sarrera honelako itxura:

zure datu-basean gordetako bezeroen datuak dituen taula bat duzu 1M erregistro
egunero bezero batek berri bat bidaltzen dizu "irudi" osoa
esperientziaz badakizu noizean behin ez dira 10K erregistro baino gehiago aldatzen

Egoera horren adibide klasiko bat da KLADR oinarria — Helbide asko daude guztira, baina astero igoera bakoitzean oso aldaketa gutxi gertatzen dira (asentamenduen izena izendatzea, kaleak uztartzea, etxe berrien agerpena) maila nazionalean ere.

3.1. Sinkronizazio-algoritmo osoa

Sinpletasunerako, demagun datuak berregituratu behar ez dituzula ere; ekarri taula nahi duzun formara, hau da:

kendu jada existitzen ez dena
eguneratzea lehendik zegoen eta eguneratu beharreko guztia
txertatzeko oraindik gertatu ez dena

Zergatik egin behar dira eragiketak ordena honetan? Horrela mahaiaren tamaina gutxien haziko delako (gogoratu MVCC!).

EZABATU DST

Ez, noski, bi eragiketa besterik ez dituzu aurrera egin:

kendu (DELETE) oro har dena
txertatzeko dena irudi berritik

Baina, aldi berean, MVCCri esker, Mahaiaren tamaina zehazki bi aldiz handituko da! 1K eguneratze baten ondorioz taulako erregistroen +10 milioi irudi lortzea oso erredundantzia da...

MOZTU dst

Garatzaile esperientziadun batek badaki tableta osoa nahiko merke garbitu daitekeela:

garbi (TRUNCATE) taula osoa
txertatzeko dena irudi berritik

Metodoa eraginkorra da, batzuetan nahiko aplikagarria, baina arazo bat dago... Denbora luzez 1M erregistro gehituko ditugu, beraz, ezin dugu mahaia hutsik utzi denbora honetan guztian (transakzio bakar batean bildu gabe gertatuko den bezala).

Horrek esan nahi du:

hasten ari gara iraupen luzeko transakzioa
TRUNCATE inposatzen du Sarbide esklusiboa-blokeatzea
txertaketa denbora luzez egiten dugu, eta beste guztiak momentu honetan ezin ere SELECT

Zerbait ez doa ondo...

ALTER TAULA… ERREZENTZEA… / EROSI TAULA…

Alternatiba bat dena taula berri batean betetzea da, eta, ondoren, izena aldatu besterik ez dago zaharraren ordez. Pare bat gauza txiki gaizto:

oraindik ere Sarbide esklusiboa, nahiz eta denbora nabarmen gutxiago
Taula honetako kontsulta-plan/estatistika guztiak berrezarri dira, ANALYZE exekutatu behar da
atzerriko gako guztiak hautsita daude (FK) mahaira

Simon Riggsen WIP adabaki bat zegoen egitea iradokitzen zuena ALTER-Fitxategi mailan taularen gorputza ordezkatzeko eragiketa bat, estatistikak eta FK ukitu gabe, baina ez zuen quoruma bildu.

EZABATU, EGUNERATU, txertatu

Beraz, hiru eragiketen blokeorik gabeko aukerarekin finkatzen gara. Ia hiru... Nola egin hau modu eraginkorrenean?

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

KLADR berean, aldatutako erregistro guztiak postprozesamenduaren bidez exekutatu behar dira, normalizatuta, gako-hitzak nabarmenduta eta beharrezko egituretara murriztu. Baina nola dakizu... zer aldatu zen zehazkisinkronizazio kodea zaildu gabe, hobe da batere ukitu gabe?

Sinkronizazioaren unean zure prozesuak idazteko sarbidea badu soilik, aldaketa guztiak bilduko dituen abiarazle bat erabil dezakezu:

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

Orain abiarazleak aplika ditzakegu sinkronizazioa hasi aurretik (edo gaitu horiek bidez 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();

Eta gero, lasaitasunez ateratzen ditugu erregistro-tauletatik behar ditugun aldaketa guztiak eta kudeatzaile osagarrien bidez exekutatzen ditugu.

3.3. Lotutako multzoak inportatzea

Goian iturburuaren eta helmugako datu-egiturak berdinak diren kasuak aztertu ditugu. Baina zer gertatzen da kanpoko sistema batetik igotzeak gure datu-baseko biltegiratze-egituraren formatu desberdina badu?

Har dezagun adibide gisa bezeroen eta haien kontuen biltegiratzea, "askoren arteko" aukera klasikoa:

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

Baina kanpoko iturri batetik deskargatzea "dena batean" moduan datorkigu:

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

Jakina, bertsio honetan bezeroaren datuak bikoiztu daitezke, eta erregistro nagusia "kontua" da:

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

Ereduari dagokionez, gure probako datuak txertatuko ditugu, baina gogoratu - COPY eraginkorragoa!

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

Lehenik eta behin, nabarmendu ditzagun gure “gertaerak” aipatzen dituzten “mozketak”. Gure kasuan, fakturak bezeroei dagozkie:

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

Kontuak bezeroen IDekin behar bezala lotzeko, lehenik identifikatzaile hauek aurkitu edo sortu behar ditugu. Gehi ditzagun eremuak haien azpian:

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

Erabili dezagun goian deskribatutako taulak sinkronizatzeko metodoa zuzenketa txiki batekin - ez dugu xede-taulan ezer eguneratuko edo ezabatuko, bezeroak "erantsitzeko soilik" inportatzen ditugulako:

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

Egia esan, dena dago barruan invoice_import Orain harremanetarako eremua bete dugu client_id, eta horrekin faktura sartuko dugu.

Iturria: www.habr.com

DBA: sinkronizazioak eta inportazioak modu egokian antolatu