🥇DBA: maayong pag-organisar sa pag-synchronize ug pag-import

Alang sa komplikado nga pagproseso sa dagkong mga set sa datos (lainlain Mga proseso sa ETL: pag-import, pagkakabig ug pag-synchronize sa gawas nga tinubdan) kasagaran adunay panginahanglan temporaryo nga "hinumdomi" ug dali nga proseso usa ka butang nga dako.

Ang usa ka tipikal nga buluhaton sa niini nga matang kasagaran sama niini: “Didto ra departamento sa accounting nga gidiskarga gikan sa bangko sa kliyente ang katapusan nga nadawat nga bayad, kinahanglan nimo nga i-upload dayon kini sa website ug i-link kini sa imong mga account"

Apan kung ang gidaghanon sa kini nga "usa ka butang" nagsugod sa pagsukod sa gatusan ka mga megabytes, ug ang serbisyo kinahanglan nga magpadayon sa pagtrabaho kauban ang database 24x7, daghang mga epekto ang motungha nga makaguba sa imong kinabuhi.

Aron maatubang sila sa PostgreSQL (ug dili lamang niini), mahimo nimong gamiton ang pipila nga mga pag-optimize nga magtugot kanimo nga maproseso ang tanan nga mas paspas ug adunay gamay nga pagkonsumo sa kapanguhaan.

1. Asa ipadala?

Una, atong hukman kung asa nato ma-upload ang datos nga gusto natong "iproseso."

1.1. Temporaryo nga mga lamesa (TEMPORARY TABLE)

Sa prinsipyo, alang sa PostgreSQL temporaryo nga mga lamesa parehas sa uban. Busa, mga patuotuo sama sa “Ang tanang butang didto gitipigan lamang sa memorya, ug kini mahimong matapos”. Apan adunay usab daghang hinungdanon nga mga kalainan.

Ang imong kaugalingon nga "namespace" alang sa matag koneksyon sa database

Kung ang duha ka koneksyon mosulay sa pagkonektar sa samang higayon CREATE TABLE x, unya naay makakuha dili pagkatalagsaon nga sayup mga butang sa database.

Apan kung ang duha mosulay sa pagpatuman CREATE TEMPORARY TABLE x, unya ang duha mobuhat niini sa normal, ug ang tanan makadawat imong kopya mga lamesa. Ug walay bisan unsa nga managsama tali kanila.

"Pagdaot sa kaugalingon" kung gidiskonekta

Kung sirado ang koneksyon, ang tanan nga temporaryo nga mga lamesa awtomatiko nga mapapas, mao nga mano-mano DROP TABLE x walay kapuslanan gawas...

Kung nagtrabaho ka pinaagi sa pgbouncer sa transaction mode, unya ang database nagpadayon sa pagtuo nga kini nga koneksyon aktibo gihapon, ug niini nga temporaryo nga lamesa anaa gihapon.

Busa, ang pagsulay sa paghimo niini pag-usab, gikan sa lahi nga koneksyon sa pgbouncer, moresulta sa usa ka sayup. Apan kini mahimong likayan pinaagi sa paggamit CREATE TEMPORARY TABLE IF NOT EXISTS x.

Tinuod, mas maayo nga dili kini buhaton, tungod kay mahimo nimo "kalit" nga makit-an didto ang nahabilin nga datos gikan sa "kaniadto nga tag-iya". Hinuon, mas maayo nga basahon ang manwal ug tan-awa nga kung maghimo usa ka lamesa posible nga idugang ON COMMIT DROP - nga mao, sa diha nga ang transaksyon makompleto, ang lamesa awtomatikong mapapas.

Dili replikasyon

Tungod kay sila nahisakop lamang sa usa ka piho nga koneksyon, ang mga temporaryo nga lamesa dili gisundog. Apan kini nagwagtang sa panginahanglan alang sa doble nga pagrekord sa datos sa heap + WAL, mao nga ang INSERT/UPDATE/DELETE niini mas paspas.

Apan tungod kay ang usa ka temporaryo nga lamesa usa pa ka "halos ordinaryo" nga lamesa, dili usab kini mahimo sa usa ka replika. Labing menos alang sa karon, bisan kung ang katugbang nga patch dugay na nga nagpalibot.

1.2. UNLOGGED TABLE

Apan unsa ang imong buhaton, pananglitan, kung ikaw adunay usa ka matang sa lisud nga proseso sa ETL nga dili mapatuman sulod sa usa ka transaksyon, apan aduna ka pa pgbouncer sa transaction mode? ..

O ang dagan sa datos dako kaayo nga Walay igong bandwidth sa usa ka koneksyon gikan sa database (basaha, usa ka proseso kada CPU)?..

O pipila ka mga operasyon nagpadayon asynchronous nga sa lain-laing koneksyon?..

Adunay usa lamang ka kapilian dinhi - temporaryo nga paghimo og dili temporaryo nga lamesa. Pun, oo. Mao ni:

naghimo sa "akong kaugalingon" nga mga lamesa nga adunay labing kadaghan nga random nga mga ngalan aron dili mag-intersect sa bisan kinsa
kinuha: gipuno sila sa datos gikan sa gawas nga tinubdan
Pagbalhin: nakabig, napuno sa yawe nga linking fields
load: gibubo andam data ngadto sa target nga mga lamesa
gitangtang ang "akong" mga lamesa

Ug karon - usa ka langaw sa pahumot. Sa pagkatinuod, ang tanan nga nagsulat sa PostgreSQL mahitabo kaduha - una sa WAL, dayon ngadto sa lamesa/indeks nga mga lawas. Ang tanan nga kini gibuhat sa pagsuporta sa ACID ug husto data visibility sa taliwala sa COMMIT'nutty ug ROLLBACK'null nga mga transaksyon.

Apan wala namo kini kinahanglana! Naa namo ang tibuok proseso Bisan kung kini hingpit nga malampuson o dili.. Dili igsapayan kung pila ang mga intermediate nga transaksyon - dili kami interesado sa "pagpadayon sa proseso gikan sa tunga," labi na kung dili klaro kung diin kini.

Aron mahimo kini, ang mga nag-develop sa PostgreSQL, balik sa bersyon 9.1, nagpaila sa usa ka butang sama sa UNLOGGED nga mga lamesa:

Uban niini nga timailhan, ang lamesa gimugna ingon nga wala naka-log. Ang mga datos nga gisulat sa wala ma-log nga mga lamesa dili moagi sa write-ahead log (tan-awa ang Kapitulo 29), hinungdan nga ang maong mga lamesa pagtrabaho nga mas paspas kay sa naandan. Apan, dili sila luwas sa kapakyasan; sa kaso sa kapakyasan sa server o emergency shutdown, usa ka unloged nga lamesa awtomatikong giputol. Dugang pa, ang mga sulud sa wala ma-log nga lamesa dili gisundog sa mga slave server. Ang bisan unsang mga index nga gihimo sa usa ka wala ma-log nga lamesa awtomatik nga ma-unlog.

Sa laktod nga pagkasulti, kini mahimong mas paspas, apan kung ang database server "mahulog", kini dili maayo. Apan unsa ka sagad kini mahitabo, ug nahibal-an ba sa imong proseso sa ETL kung giunsa kini pagkorihir sa husto "gikan sa tunga" pagkahuman sa "pagpalagsik" sa database?..

Kung dili, ug ang kaso sa ibabaw parehas sa imoha, gamita UNLOGGEDapan dili gayud ayaw pagpagana niini nga hiyas sa tinuod nga mga lamesa, ang datos gikan diin mahal nimo.

1.3. ON COMMIT { DELETE ROWS | DROP}

Kini nga pagtukod nagtugot kanimo sa pagtino sa awtomatik nga kinaiya kung ang usa ka transaksyon mahuman sa paghimo sa usa ka lamesa.

sa ON COMMIT DROP Gisulat ko na sa ibabaw, kini nagmugna DROP TABLE, apan uban sa ON COMMIT DELETE ROWS ang sitwasyon mas makapaikag - kini namugna dinhi TRUNCATE TABLE.

Tungod kay ang tibuuk nga imprastraktura alang sa pagtipig sa meta-deskripsyon sa usa ka temporaryo nga lamesa parehas ra sa usa ka regular nga lamesa, nan Ang kanunay nga paghimo ug pagtangtang sa temporaryo nga mga lamesa nagdala sa grabe nga "paghubag" sa mga lamesa sa sistema pg_class, pg_attribute, pg_attrdef, pg_depend,…

Karon hunahunaa nga ikaw adunay usa ka trabahante sa usa ka direkta nga koneksyon sa database, nga nagbukas sa usa ka bag-ong transaksyon matag segundo, nagmugna, nagpuno, nagproseso ug nagtangtang sa usa ka temporaryo nga lamesa ... Adunay usa ka sobra nga basura nga natipon sa mga lamesa sa sistema, ug kini magpahinabog dugang nga preno sa matag operasyon.

Sa kinatibuk-an, ayaw pagbuhat niini! Sa kini nga kaso kini labi ka epektibo CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS kuhaa kini gikan sa siklo sa transaksyon - unya sa pagsugod sa matag bag-ong transaksyon ang mga lamesa naa na maglungtad (pagluwas sa usa ka tawag CREATE), apan mahimong walay sulod, salamat sa TRUNCATE (gi-save usab namo ang tawag niini) sa pagkompleto sa miaging transaksyon.

1.4. LIKOD... apil...

Gihisgutan nako sa sinugdanan nga ang usa sa kasagaran nga mga kaso sa paggamit alang sa temporaryo nga mga lamesa mao ang lainlaing mga klase sa pag-import - ug gikapoy nga gikopya-paste sa developer ang lista sa mga natad sa target nga lamesa sa deklarasyon sa iyang temporaryo...

Apan ang pagkatapulan mao ang makina sa pag-uswag! Mao nga paghimo og bag-ong lamesa "base sa sample" kini mahimong mas simple:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Tungod kay mahimo ka nga makahimo og daghang mga datos sa kini nga lamesa, ang pagpangita niini dili gyud kadali. Apan adunay usa ka tradisyonal nga solusyon niini - mga indeks! Ug, oo, ang usa ka temporaryo nga lamesa mahimo usab nga adunay mga indeks.

Tungod kay, sa kasagaran, ang gikinahanglan nga mga indeks motakdo sa mga indeks sa target nga lamesa, mahimo ka lang magsulat LIKE target_table INCLUDING INDEXES.

Kung kinahanglan nimo usab DEFAULT-values (pananglitan, aron mapuno ang panguna nga yawe nga mga kantidad), mahimo nimong gamiton LIKE target_table INCLUDING DEFAULTS. O yano- LIKE target_table INCLUDING ALL - mga kopya sa default, mga indeks, mga pagpugong,...

Apan dinhi kinahanglan nimo nga masabtan kana kung imong gibuhat import nga lamesa diha-diha dayon uban sa mga indeks, unya ang data magdugay sa pagkargakay sa kung una nimong pun-on ang tanan, ug unya i-roll up ang mga indeks - tan-awa kung giunsa kini paghimo ingon usa ka pananglitan pg_dump.

Sa laktud, RTFM!

2. Unsaon pagsulat?

Ingna lang ko - gamita kini COPY-agos imbes nga "pack" INSERT, acceleration usahay. Mahimo ka pa nga direkta gikan sa usa ka pre-generated file.

3. Unsaon pagproseso?

Busa, atong tugotan ang atong intro nga tan-awon sama niini:

ikaw adunay usa ka lamesa nga adunay datos sa kliyente nga gitipigan sa imong database 1M nga rekord
kada adlaw ang usa ka kliyente magpadala kanimo og bag-o bug-os nga "imahe"
gikan sa kasinatian nahibal-an nimo kana matag karon ug unya dili molapas sa 10K nga mga rekord ang giusab

Usa ka klasiko nga pananglitan sa ingon nga kahimtang mao ang base sa KLADR — Adunay daghang mga adres sa kinatibuk-an, apan sa matag senemana nga pag-upload adunay gamay ra nga mga pagbag-o (pag-ngalan sa mga pinuy-anan, paghiusa sa mga kadalanan, dagway sa bag-ong mga balay) bisan sa nasudnon nga sukod.

3.1. Bug-os nga synchronization algorithm

Alang sa kayano, ingnon ta nga dili nimo kinahanglan nga usbon ang datos - dad-a lang ang lamesa sa gusto nga porma, kana mao:

kuhaa tanan nga wala na
update ang tanan nga naglungtad na ug kinahanglan nga i-update
ipilit ang tanan nga wala pa mahitabo

Ngano nga ang mga operasyon kinahanglan buhaton sa kini nga han-ay? Tungod kay kini ang paagi nga ang gidak-on sa lamesa motubo nga gamay (Hinumdomi ang MVCC!).

DELETE GIKAN sa dst

Dili, siyempre mahimo ka nga mabuhi sa duha lang ka operasyon:

kuhaa (DELETE) tanan sa kinatibuk-an
ipilit tanan gikan sa bag-ong imahe

Apan sa samang higayon, salamat sa MVCC, Ang gidak-on sa lamesa modugang sa eksaktong kaduha! Ang pagkuha ug +1M nga mga hulagway sa mga rekord sa lamesa tungod sa 10K nga pag-update kay redundancy kaayo...

PUTOL sa dst

Ang usa ka labi ka eksperyensiyado nga developer nahibal-an nga ang tibuuk nga tablet mahimong malimpyohan nga barato kaayo:

klaro (TRUNCATE) ang tibuok lamesa
ipilit tanan gikan sa bag-ong imahe

Epektibo ang pamaagi, usahay medyo magamit, pero naay problema... Dugay namong idugang ang 1M nga mga rekord, mao nga dili namo makaya nga biyaan ang lamesa nga walay sulod sa tanang panahon (sama sa mahitabo nga walay pagputos niini sa usa ka transaksyon).

Buot pasabot:

nagsugod na mi dugay na nga transaksyon
TRUNCATE nagpahamtang Pag-access sa Eksklusibo- pagbabag
gihimo namo ang pagsal-ot sa dugay nga panahon, ug ang tanan niining panahona dili gani SELECT

Adunay dili maayo ...

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

Ang usa ka alternatibo mao ang pagpuno sa tanan ngadto sa usa ka bulag nga bag-ong lamesa, ug dayon pag-ilis sa ngalan niini puli sa daan. Pipila ka daotan nga gagmay nga mga butang:

sa gihapon Pag-access sa Eksklusibo, bisan tuod mas gamay nga panahon
ang tanan nga mga plano sa pangutana/statistika alang niini nga lamesa gi-reset, kinahanglan nga modagan ANALYZE
nabuak ang tanang langyaw nga yawe (FK) sa lamesa

Adunay usa ka WIP patch gikan sa Simon Riggs nga nagsugyot sa paghimo ALTER-usa ka operasyon sa pag-ilis sa lamesa nga lawas sa file nga lebel, nga walay paghikap statistics ug FK, apan wala mangolekta korum.

DELETE, UPDATE, INSERT

Mao nga, gihusay namon ang kapilian nga dili pag-block sa tulo nga mga operasyon. Halos tulo... Giunsa kini pagbuhat sa labing 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. Pag-import sa post-processing

Sa parehas nga KLADR, ang tanan nga nabag-o nga mga rekord kinahanglan nga dugang nga ipadagan pinaagi sa post-processing - na-normalize, gipasiugda ang mga keyword, ug gipamubu sa gikinahanglan nga mga istruktura. Apan giunsa nimo pagkahibalo- unsa gyud ang nausabnga dili komplikado ang code sa pag-synchronize, labing maayo nga wala’y paghikap niini?

Kung ang imong proseso lang adunay access sa pagsulat sa panahon sa pag-synchronize, nan mahimo nimong gamiton ang usa ka gatilyo nga mokolekta sa tanan nga mga pagbag-o alang kanamo:

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

Karon mahimo na namon nga magamit ang mga nag-trigger sa wala pa magsugod ang pag-synchronize (o mahimo kini pinaagi sa 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();

Ug dayon kalmado namon nga gikuha ang tanan nga mga pagbag-o nga kinahanglan namon gikan sa mga talaan sa log ug gipadagan kini pinaagi sa dugang nga mga tigdumala.

3.3. Pag-import sa Linked Sets

Sa ibabaw among gikonsiderar ang mga kaso kung ang mga istruktura sa datos sa gigikanan ug destinasyon parehas. Apan unsa man kung ang pag-upload gikan sa usa ka eksternal nga sistema adunay usa ka format nga lahi sa istruktura sa pagtipig sa among database?

Atong isipon nga pananglitan ang pagtipig sa mga kliyente ug ilang mga account, ang klasiko nga "daghanon-sa-usa" nga kapilian:

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

Apan ang pag-download gikan sa usa ka eksternal nga gigikanan moabut kanamo sa porma sa "tanan sa usa":

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

Dayag nga, ang datos sa kustomer mahimong madoble sa kini nga bersyon, ug ang panguna nga rekord mao ang "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

Alang sa modelo, isulod lang namo ang among data sa pagsulay, apan hinumdomi - COPY mas episyente!

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, atong i-highlight ang mga "pagputol" nga gitumong sa atong "mga kamatuoran". Sa among kaso, ang mga invoice nagtumong sa mga kustomer:

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

Aron sa husto nga pag-asoy sa mga account sa mga customer ID, kinahanglan una namon nga mahibal-an o maghimo kini nga mga identifier. Atong idugang ang mga natad ubos niini:

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

Gamiton nato ang pamaagi sa pag-synchronize sa lamesa nga gihulagway sa ibabaw nga adunay gamay nga pag-amendar - dili namo i-update o tangtangon ang bisan unsa sa target nga lamesa, tungod kay nag-import kami sa mga kliyente nga "apend-only":

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

Sa tinuud, ang tanan naa invoice_import Karon napuno na namo ang contact field client_id, diin atong isulod ang invoice.

Source: www.habr.com

DBA: maayong pag-organisar sa mga pag-synchronize ug pag-import